How to add month column based on numeric day value

user_1198246 0
Viewed 54

I have over 20 years of data where I have a column with years from 1999 to 2020. I also have another column with days. However, my day column is numeric. For example, January 1, 1999 is noted as "1", January 2, 1999 is noted as "2", January 3, 1999 is noted as "3" and so on up to December 31, 1999 labeled as "365". Then the next year starts again as year 2000 with day 1 as January 1, 2000.......day 32 is February 1, 2000, day 33 is February 2, 2000 and so on up to 2020. I need to create a new column with the month based on the day value. For example, day 1 to 31 should be January, day 32 60 should be February and so on all the way to December for multiple years. Attached is a small dataset to illustrate what I am trying to do. I haven't tried anything because I have not idea how I can do this. I read about format and as.Date but found nothing close to this example.

sal <- structure(list(year = c(1999, 1999, 1999, 1999, 1999, 1999, 1999, 
1999, 1999, 1999, 1999, 1999, 1999, 1999, 1999, 1999, 1999, 1999, 
1999, 1999, 1999, 1999, 1999, 1999, 1999, 1999, 1999, 1999, 1999, 
1999, 1999, 1999, 1999, 1999, 1999, 1999, 1999, 1999, 1999, 1999, 
1999, 1999, 1999, 1999), day = c(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, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 
43, 44)), row.names = c(NA, -44L), class = c("tbl_df", "tbl", 
"data.frame"))

 Ending dataset should look like below:

   year day  month
1  1999   1  January
2  1999   2  January
3  1999   3  ...
4  1999   4  ...
5  1999   5  ...
6  1999   6
7  1999   7
8  1999   8
9  1999   9
10 1999  10
11 1999  11
12 1999  12
13 1999  13
14 1999  14
15 1999  15
16 1999  16
17 1999  17
18 1999  18
19 1999  19
20 1999  20
21 1999  21
22 1999  22
23 1999  23
24 1999  24
25 1999  25
26 1999  26
27 1999  27
28 1999  28
29 1999  29
30 1999  30  ...
31 1999  31  January
32 1999  32  February
33 1999  33  February
34 1999  34  ...
35 1999  35
36 1999  36
37 1999  37
38 1999  38
39 1999  39
40 1999  40
41 1999  41
42 1999  42
43 1999  43
44 1999  44
45   NA  NA
r
3 Answers

To get the date

sal$date=as.Date(sal$day-1,origin=paste0(sal$year,"-01-01"))

to get the month name

format(sal$date,"%B")
user_29749510

You could use:

library(tidyverse)
library(lubridate)
sal %>%
  mutate(date = as.Date(paste0(year, '-01-01')) + days(day - 1),
         month = month.name[month(date)])
user_83802720

With data.table you could do:

require(data.table)

setDT(sal)
sal[ , month := format(as.IDate(paste0(year, '-', day), format = '%Y-%j'), '%B') ]
user_47428890
Related