Spread data set by unique rows

Question

I have a dataset that i'm trying to spread out so that each date is on a unique row. I've been trying to achieve this using pivot_wider in the dplyr package but its not been giving me my desired result.

My current data set looks like this

   date        PX_LAST  AVG   INDICATOR
1  2001-01-01  500      145   shib
2  2001-01-01  600      599   shic
3  2001-01-02  750      759   shid
4  2001-01-02  550      569   shie
5  2001-01-02  300      563   shif
6  2001-01-03  330      449   shig
7  2001-01-04  350      329   shih
8  2001-01-04  390      324   shim
9  2001-01-05  100      219   chuw
10 2001-01-06  105      438   woej
11 2001-01-06  250      212   eirw
12 2001-01-07  125      394   erji
13 2001-01-07  129      390   odfj

I am trying to make my output look like this

date        PX_LAST  AVG   INDICATOR PX_LAST.1  AVG.1   INDICATOR.1 PX_LAST.2 AVG.2 INDICATOR.2 
2001-01-01  500      145   shib      600        599     shic        NA        NA      NA                    
2001-01-02  750      759   shid      550        569     shie        300       563     shif
2001-01-03  330      449   shig      NA         NA      NA          NA        NA      NA
2001-01-04  350      329   shih      390        324     shim        NA        NA      NA
2001-01-05  100      219   chuw      NA         NA      NA          NA        NA      NA
2001-01-06  105      438   woej      250        212     eirw        NA        NA      NA
2001-01-07  125      394   erji      129        390     odfj        NA        NA      NA

my code:

df%>%pivot_wider(id_cols= date, values_from(PX_LAST, AVG, INDICATOR), values_fill = NA)

however, this and other code options i tried do not produce the require output for me.

score 2 · Accepted Answer · answered Jul 19 '21 at 10:09

You can use -

library(dplyr)
library(tidyr)

df %>%
  group_by(date) %>%
  mutate(index = row_number()) %>%
  ungroup %>%
  pivot_wider(names_from = index, values_from = PX_LAST:INDICATOR)

#  date       PX_LAST_1 PX_LAST_2 PX_LAST_3 AVG_1 AVG_2 AVG_3 INDICATOR_1
#  <chr>          <int>     <int>     <int> <int> <int> <int> <chr>      
#1 2001-01-01       500       600        NA   145   599    NA shib       
#2 2001-01-02       750       550       300   759   569   563 shid       
#3 2001-01-03       330        NA        NA   449    NA    NA shig       
#4 2001-01-04       350       390        NA   329   324    NA shih       
#5 2001-01-05       100        NA        NA   219    NA    NA chuw       
#6 2001-01-06       105       250        NA   438   212    NA woej       
#7 2001-01-07       125       129        NA   394   390    NA erji       
# … with 2 more variables: INDICATOR_2 <chr>, INDICATOR_3 <chr>

Spread data set by unique rows

1 Answers1