Home > OS >  How to change the year and month values when creating a new variable in R
How to change the year and month values when creating a new variable in R

Time:01-23

Example of data

pr=structure(list(MDM_Key = 1:7, year = c(2022L, 2022L, 2022L, 2022L, 
2020L, 2022L, 2022L), month = c(3L, 3L, 8L, 3L, 6L, 10L, 11L), 
    recommended_price = c(110.338476, 813.4039876, 661.4400121, 
    117.4398283, 118.3484346, 10.95458941, 34.70254434), profit = c(19610173.66, 
    30172191.56, 10546493.33, 11550829.54, 6311699.08, 21433932.57, 
    9216609.05), date = c("01.03.2022", "01.03.2022", "01.08.2022", 
    "01.03.2022", "01.06.2020", "01.10.2022", "01.11.2022"), 
    subgroup_id = c("01.01.2002", "01.02.2002", "01.02.2002", 
    "01.01.2002", "05.06.2003", "05.02.2001", "05.01.2001")), class = "data.frame", row.names = c(NA, 
-7L))

I need to change the year and month values, by condition. If year=2022, then change it to 2023 and change all month values to 01. If the value of the year is less than 2022, then change all its values to 2022, and change all values of the month to 12. Result into new variable month_id

in this little example desired output would be

  MDM_Key year month recommended_price   profit       date subgroup_id
1       1 2022     3         110.33848 19610174 01.03.2022  01.01.2002
2       2 2022     3         813.40399 30172192 01.03.2022  01.02.2002
3       3 2022     8         661.44001 10546493 01.08.2022  01.02.2002
4       4 2022     3         117.43983 11550830 01.03.2022  01.01.2002
5       5 2020     6         118.34843  6311699 01.06.2020  05.06.2003
6       6 2022    10          10.95459 21433933 01.10.2022  05.02.2001
7       7 2022    11          34.70254  9216609 01.11.2022  05.01.2001
  month_id
1   202301
2   202301
3   202301
4   202301
5   202212
6   202301
7   202301

What is the easiest way to make such transformations. Thanks for your valuable help

CodePudding user response:

dplyr::case_when() inside dplyr::mutate works fine as well.

pr %>% mutate(monthid = case_when(year == 2022 ~ "202301",
                                  year < 2022 ~ "202212"))

CodePudding user response:

I think the following one-liner should suffice:

within(pr, month_id <- ifelse(year == 2022, "202301", "202212"))
#>   MDM_Key year month recommended_price   profit       date subgroup_id month_id
#> 1       1 2022     3         110.33848 19610174 01.03.2022  01.01.2002   202301
#> 2       2 2022     3         813.40399 30172192 01.03.2022  01.02.2002   202301
#> 3       3 2022     8         661.44001 10546493 01.08.2022  01.02.2002   202301
#> 4       4 2022     3         117.43983 11550830 01.03.2022  01.01.2002   202301
#> 5       5 2020     6         118.34843  6311699 01.06.2020  05.06.2003   202212
#> 6       6 2022    10          10.95459 21433933 01.10.2022  05.02.2001   202301
#> 7       7 2022    11          34.70254  9216609 01.11.2022  05.01.2001   202301

Created on 2023-01-22 with reprex v2.0.2

  •  Tags:  
  • r
  • Related