CODEWITHSUNDEEP
rdateformattingformatchronological
thankfulperson
thankfulperson

Reputation: 1

Converting month column table to chronological order in R

I have a table of the following format:

Initial Table Formatting

And I'm seeking an output resembling the following:

Date Value
January 1659 Value 1
February 1659 Value 2
March 1659 Value 3
April 1659 Value 4

and so on (numerical representations of the Month and Year are perfectly fine also.

I've attempted using merge operations but I'm thinking there must be an easier way (possibly using packages). I've found somewhat similar questions asked but none obviously applicable yet.

Upvotes: 0

Views: 34

Answers (1)

Allan Cameron
Allan Cameron

Reputation: 174468

You can use pivot_longer and unite, both from the tidyr package:

library(tidyr)

pivot_longer(df, -Year) |>
  unite(date, name, Year, sep = " ")
#> # A tibble: 120 x 2
#>    date     value
#>    <chr>    <int>
#>  1 Jan 1659    68
#>  2 Feb 1659    97
#>  3 Mar 1659    89
#>  4 Apr 1659    74
#>  5 May 1659    44
#>  6 Jun 1659     2
#>  7 Jul 1659    81
#>  8 Aug 1659    22
#>  9 Sep 1659    87
#> 10 Oct 1659     1
#> # ... with 110 more rows

Data used

set.seed(1)

df <- cbind(1659:1668, replicate(12, sample(99, 10))) |>
  as.data.frame() |>
  setNames(c("Year", month.abb))

df
#>    Year Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
#> 1  1659  68  97  89  74  44   2  81  22  87   1  76  43
#> 2  1660  39  85  37  42  25  45  13  93  83  43  39   1
#> 3  1661   1  21  34  38  70  18  40  28  90  59  24  29
#> 4  1662  34  54  99  20  39  22  89  48  48  26  53  78
#> 5  1663  87  74  44  28  51  78  48  33  64  15  92  22
#> 6  1664  43   7  79  96  42  65  96  45  94  58  86  70
#> 7  1665  14  73  33  44   6  70  23  21  60  29  40  28
#> 8  1666  82  79  84  87  24  87  84  31  51  24  83  37
#> 9  1667  59  98  35  70  32  93  29  17  34  42  90  61
#> 10 1668  51  37  70  40  14  75  98  73  10  48  35  46

Created on 2022-11-29 with reprex v2.0.2

Upvotes: 0

Related Questions