1

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!!

Zachary Lehmann
  • 15
  • 1
  • 5

2 Answers2

5

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")
akrun
  • 874,273
  • 37
  • 540
  • 662
  • When I did this it says there isn't a unique value in Value. So I added a row number to identify my rows, but I still get the same behavior as before?? Not sure what I'm missing from your solution. – Zachary Lehmann Dec 30 '19 at 20:48
  • 1
    You can also specify this within `pivot_wider`, i.e. this works with no `select`: `df1 %>% pivot_wider(id_cols = -Unit, names_from = Parameter, values_from = Value)` – IceCreamToucan Dec 30 '19 at 20:50
  • @ZacharyLehmann May be you have duplicate rows. I would create a sequence column by that group before the pivot_wider ii.e. `df1 %>% group_by(Parameter) %>% mutate(rn = row_number()) %>% pivot_wider(id_cols = -Unit, names_from = Parameter, values_from = Value)` – akrun Dec 30 '19 at 20:51
  • 1
    @akrun thanks! It appears that the data did have duplicate rows. Your above solution worked, thank you! – Zachary Lehmann Jan 02 '20 at 14:00
1

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
steveb
  • 5,382
  • 2
  • 27
  • 36