R reshape by day, month, year

Question

I have a simple table in the following format:

Date          val
2005-01-01    15 
2005-01-02    18 
2005-01-03    20
...

And am trying to reshape it to the following "wide" column format:

Year  Month  day1     day2     day3     day4     ...day31
2005  01     day1val  day2val  day3val  day4val  ...day31val
2005  02     day1val  day2val  day3val  day4val  ...day31val

I've successfully split the date column into three separate d,m,y columns using

dates_separated <- data.frame(year = as.numeric(format(input_df$DATE, format = "%Y")),
             month = as.numeric(format(input_df$DATE, format = "%m")),
             day = as.numeric(format(input_df$DATE, format = "%d")))
output_df <- cbind(input_df, dates_sep)

I'm trying to use the reshape function to get this done, but am finding my output could be more complicated than it can handle. Is there another function I should be using here?

Edit: I don't believe this was a duplicate of what was suggested. markdly's answer below did exactly what I needed. Thanks!

You've `"split the date column into three separate d,m,y columns"`. Can you edit the question and post the code? — Rui Barradas, Oct 31 '17 at 21:44

score 2 · Accepted Answer · answered Nov 02 '17 at 06:23

For the sake of completeness, here is a solution using the dcast() function.

OP's input_df consists only of two columns Date and val. So, let's create a full year of sample data by

set.seed(1234L)
input_df <- data.frame(Date = as.Date("2005-01-01") + 0:364, 
                       val = sample(100:999, 365L, TRUE))

The dcast() function is available from the reshape2 and the data.table packages. Here, data.table is used because of its handy year(), month(), and mday() functions:

library(data.table)
dcast(input_df, year(Date) + month(Date) ~ mday(Date))

   year(Date) month(Date)   1   2   3   4   5   6   7   8   9  10  11  12  13  14  15  16  17  18  19  20  21  22  23  24  25  26  27  28  29  30  31
1        2005           1 202 660 648 661 874 676 108 309 699 562 724 590 354 931 363 853 357 340 268 309 384 372 243 135 296 829 573 923 848 141 510
2        2005           2 338 374 556 262 783 281 332 992 826 598 681 380 659 396 551 709 536 319 788 166 378 745 554 237 553 544 776 257  NA  NA  NA
3        2005           3 863 878 137 385 112 315 735 377 557 146 608 209 903 113 804 180 567 445 163 388 701 933 524 228 589 276 908 450 379 244 906
4        2005           4 249 910 220 218 194 560 370 124 378 767 131 608 352 283 220 393 239 216 491 134 741 190 955 209 297 921 951 351 211 817  NA
5        2005           5 769 924 995 948 537 355 326 552 547 386 966 670 214 480 922 521 917 637 668 882 552 985 391 533 421 664 767 609 982 619 495
6        2005           6 305 173 865 311 989 641 998 438 599 486 618 489 302 176 673 487 165 822 392 781 625 737 484 409 783 481 604 204 372 530  NA
7        2005           7 410 640 168 960 119 857 669 379 768 675 993 215 894 829 839 851 759 984 675 694 575 385 791 573 759 376 463 283 987 609 352
8        2005           8 266 782 610 938 674 730 531 865 480 128 332 401 220 549 821 403 558 544 817 610 196 826 610 291 774 376 540 990 481 319 295
9        2005           9 720 982 529 796 616 969 817 578 636 337 351 158 606 336 102 630 568 860 126 639 341 208 190 773 114 144 772 421 783 438  NA
10       2005          10 819 123 555 839 590 340 410 432 486 926 805 764 352 511 358 726 838 689 472 956 318 647 782 724 203 672 378 417 982 584 499
11       2005          11 954 507 271 992 593 791 922 713 466 466 231 277 272 467 413 851 278 875 457 237 405 430 484 267 692 928 760 894 958 275  NA
12       2005          12 525 447 436 125 936 469 960 344 565 980 432 379 130 700 928 140 281 769 217 737 998 949 633 758 538 791 102 602 514 396 851

To prettify the result, Year and Month can be computed in advance:

dcast(setDT(input_df)[, Year := year(Date)][, Month := month(Date)], 
      Year + Month ~ sprintf("day%02i", mday(Date)), value.var = "val")

    Year Month day01 day02 day03 day04 day05 day06 day07 day08 day09 day10 day11 day12 day13 day14 day15 day16 day17 day18 day19 day20 ...
 1: 2005     1   202   660   648   661   874   676   108   309   699   562   724   590   354   931   363   853   357   340   268   309
 2: 2005     2   338   374   556   262   783   281   332   992   826   598   681   380   659   396   551   709   536   319   788   166
 3: 2005     3   863   878   137   385   112   315   735   377   557   146   608   209   903   113   804   180   567   445   163   388
 4: 2005     4   249   910   220   218   194   560   370   124   378   767   131   608   352   283   220   393   239   216   491   134
 5: 2005     5   769   924   995   948   537   355   326   552   547   386   966   670   214   480   922   521   917   637   668   882
 6: 2005     6   305   173   865   311   989   641   998   438   599   486   618   489   302   176   673   487   165   822   392   781
 7: 2005     7   410   640   168   960   119   857   669   379   768   675   993   215   894   829   839   851   759   984   675   694
 8: 2005     8   266   782   610   938   674   730   531   865   480   128   332   401   220   549   821   403   558   544   817   610
 9: 2005     9   720   982   529   796   616   969   817   578   636   337   351   158   606   336   102   630   568   860   126   639
10: 2005    10   819   123   555   839   590   340   410   432   486   926   805   764   352   511   358   726   838   689   472   956
11: 2005    11   954   507   271   992   593   791   922   713   466   466   231   277   272   467   413   851   278   875   457   237
12: 2005    12   525   447   436   125   936   469   960   344   565   980   432   379   130   700   928   140   281   769   217   737

Note that here sprintf("Day%02i", mday(Date)) is used to keep the columns ordered. Using paste0("day", day) as in markdly's answer, the columns would be in the wrong order:

day1 day10 day11 day12 day13 day14 day15 day16 day17 day18 day19 day2 day20 ...

Thanks so much for this (sorry for the late follow-up). Just to be clear, the 2nd example using setDT--all that does is rename the columns, correct? Also, I noticed that first example without using the sprintf command resulted in the proper column order. Should I be sorting regardless? Thanks! — mykonos, Dec 19 '17 at 21:17
Yes, you are right. The purpose of the 2nd example is to reproduce the expected output as close as possible including the column names. In the 1st example, `mday(Date)` is of type integer which sorts the columns in the correct order. — Uwe, Dec 20 '17 at 01:10

score 1 · Answer 2 · answered Oct 31 '17 at 22:01

If you can add actual data to your question it really helps others to post answers. For example, here's some data for 5 days in each month in 2015:

set.seed(123)
df <- expand.grid(year = 2015, month = 1:12, day = 1:5)
df$val <- sample.int(1000, nrow(df))
head(df)

#>   year month day val
#> 1 2015     1   1 288
#> 2 2015     2   1 788
#> 3 2015     3   1 409
#> 4 2015     4   1 881
#> 5 2015     5   1 937
#> 6 2015     6   1  46

This can be converted to the desired format using tidyr::spread:

library(dplyr)
library(tidyr)
df %>% 
  mutate(day = paste0("day", day)) %>%
  spread(day, val)

#>    year month day1 day2 day3 day4 day5
#> 1  2015     1  288  670  640  732  254
#> 2  2015     2  788  566  691  209  816
#> 3  2015     3  409  102  530  307   44
#> 4  2015     4  881  993  579  223  420
#> 5  2015     5  937  243  282  138  758
#> 6  2015     6   46   42  143  398  116
#> 7  2015     7  525  323  935  397  531
#> 8  2015     8  887  996  875  353  196
#> 9  2015     9  548  872  669  146  121
#> 10 2015    10  453  679  770  133  711
#> 11 2015    11  948  627   24  961  844
#> 12 2015    12  449  972  462  445  957

R reshape by day, month, year

2 Answers2