CODEWITHSUNDEEP
r
Zachary Lehmann
Zachary Lehmann

Reputation: 45

Pivot_wider and Group at the same time?

I have a large data frame that looks like this:

   Location       Dttm          Parameter      Unit         Value
1  Airport 2018-01-01 12:00:00 Wind Direction  deg          60
2  Airport 2018-01-01 12:00:00 Wind Speed      m/sec        3.45

Obviously there are many more rows that this, I'm just showing an example of the data. I need to pivot the data so there is a column for wind direction and wind speed. But when I run the pivot_wider function I get something like this:

   Location      Dttm          Unit     Wind Direction   Wind Speed   
1  Airport 2018-01-01 12:00:00 deg          60                NULL
2  Airport 2018-01-01 12:00:00 m/sec        NULL              3.45

I've tried various kinds of group_by methods but haven't found anything that gets me what I really need, which is this:

   Location      Dttm              Wind Direction   Wind Speed   
1  Airport 2018-01-01 12:00:00          60             3.45

I thought pivot_wider would do this for me if I set the id_cols to Dttm but that hasn't worked either. Not even sure how to really google for this solution, so any help is appreciated!!

Upvotes: 1

Views: 7022

Answers (2)

steveb
steveb

Reputation: 5532

Using pivot_wider may be better but another possibility is to use tidyr::spread such as below.

library(tidyr)
library(dplyr)

df1 <- structure(list(Location = c("Airport", "Airport"), Dttm = structure(c(1514826000, 
1514826000), class = c("POSIXct", "POSIXt"), tzone = ""), Parameter = c("Wind Direction", 
"Wind Speed"), Unit = c("deg", "m/sec"), Value = c(60, 3.45)), row.names = c("1", 
"2"), class = "data.frame")

df1 %>%
  select(-Unit) %>%
  spread(Parameter, Value)

which outputs

  Location                Dttm Wind Direction Wind Speed
1  Airport 2018-01-01 09:00:00             60       3.45

Upvotes: 1

akrun
akrun

Reputation: 887173

We can remove the Unit column before doing the pivot_wider

library(dplyr)
library(tidyr)
df1 %>%
   select(-Unit) %>%
   pivot_wider(names_from = Parameter, values_from = Value)
# A tibble: 1 x 4
#   Location Dttm                `Wind Direction` `Wind Speed`
#   <chr>    <dttm>                         <dbl>        <dbl>
#1 Airport  2018-01-01 12:00:00               60         3.45

O specify the id_cols argument in pivot_wider (as showed by @IceCreamToucan)

df1 %>% 
   pivot_wider(id_cols = -Unit, names_from = Parameter, values_from = Value)

If there are duplicate rows for the group, create a sequence column for the group before the pivot_wider

df1 %>% 
   group_by(Parameter) %>% 
   mutate(rn = row_number()) %>% 
   pivot_wider(id_cols = -Unit, names_from = Parameter, values_from = Value) %>% 
   select(-rn)

data

df1 <- structure(list(Location = c("Airport", "Airport"), Dttm = structure(c(1514826000, 
1514826000), class = c("POSIXct", "POSIXt"), tzone = ""), Parameter = c("Wind Direction", 
"Wind Speed"), Unit = c("deg", "m/sec"), Value = c(60, 3.45)), row.names = c("1", 
"2"), class = "data.frame")

Upvotes: 5

Related Questions