How to re-arrange a dataframe creating four continuous variables from two ones and a third cathegorical variable with two levels?

Question

I have next dataframe:

df <- data.frame(Date= as.Date(c("2020-08-04","2020-08-04","2020-08-06","2020-08-06","2020-08-07","2020-08-07")),
                   Period= c("Day","Night","Day","Night","Day","Night"),
                   State.1= c(1, 0.45,0.48,0.32,0.29,0.87),
                   State.2= c(0, 0.55,0.28,0.62,0.79,0.17))
  
  df

         Date Period State.1 State.2
1 2020-08-04    Day    1.00    0.00
2 2020-08-04  Night    0.45    0.55
3 2020-08-06    Day    0.48    0.28
4 2020-08-06  Night    0.32    0.62
5 2020-08-07    Day    0.29    0.79
6 2020-08-07  Night    0.87    0.17

In which I indicate per date and Period some values for State.1 and State.2. For illustration purposes, I would like to re-arrange my dataframe incorporating Period within both State.1 and State.2. I would expect to get next:

df2

       Date State.1_day State.1_night State.2_day State.2_night
1 2020-08-04        1.00          0.45        0.00          0.55
2 2020-08-05          NA            NA          NA            NA
3 2020-08-06        0.48          0.32        0.28          0.62
4 2020-08-07        0.29          0.87        0.79          0.17

How could I do it? I tried melt() but I couldn't get what I wanted.

Thanks in advance.

Allan Cameron · Accepted Answer · 2020-11-10T20:06:31.170

You could do:

tidyr::pivot_wider(df, names_from = Period, values_from = c("State.1", State.2))
#> # A tibble: 3 x 5
#>   Date       State.1_Day State.1_Night State.2_Day State.2_Night
#>   <date>           <dbl>         <dbl>       <dbl>         <dbl>
#> 1 2020-08-04       1              0.45        0             0.55
#> 2 2020-08-06       0.48           0.32        0.28          0.62
#> 3 2020-08-07       0.290          0.87        0.79          0.17

Or if you want a record for each day even if that day is not present in your original data frame, you could do:

dplyr::left_join(data.frame(Date = seq(min(df$Date), max(df$Date), by = "day")),
                 tidyr::pivot_wider(df, names_from = Period, 
                                   values_from = c("State.1", State.2)))

#>         Date State.1_Day State.1_Night State.2_Day State.2_Night
#> 1 2020-08-04        1.00          0.45        0.00          0.55
#> 2 2020-08-05          NA            NA          NA            NA
#> 3 2020-08-06        0.48          0.32        0.28          0.62
#> 4 2020-08-07        0.29          0.87        0.79          0.17

Thanks @Allan Cameron. I was just writing to you to ask you if you knew how to do to include days with no data. But I have checked that you have improved your answer by yourself before I asked you. I am interested in including dates with no data because after that, I want to plot it and I want dates with no data to appear in my plot. Thanks again — Dekike, Nov 10 '20 at 20:08

score 2 · Answer 2 · answered Nov 10 '20 at 22:24

2

We can use data.table

library(data.table)
dcast(setDT(df), Date ~ Period, value.var = c("State.1", "State.2"))

answered Nov 10 '20 at 22:24

akrun

874,273
37
540
662

How to re-arrange a dataframe creating four continuous variables from two ones and a third cathegorical variable with two levels?

2 Answers2