Replace multiple values in a column with new values

Question

I have a table with a column which has data in week_month_year format

Table 1
1_1_2014
1_2_2014
2_3_2015
5_7_2019
XXXXXXX
YYYYYYY

I want to replace the values in the table with W1 and month name, like

W1_Jan_2014
W1_Feb_2014
xxxxxxx
yyyyyyy

Week numbers go from 1-7 and I have years from 2014-2020.How do I solve this? Another constraint is I have other values as well in this column which should remain as it is if the data is not like this.

Hi Shivam Sarin. Please read the info about [how to ask a good question](https://stackoverflow.com/help/how-to-ask) and how to give a [minimale reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example/5963610#5963610). That way you can help others to help you! — dario, Mar 03 '20 at 11:39
What are you talking about? Asking for a MRE is not "me being on a high horse"... — dario, Mar 03 '20 at 13:57
Also I really don't understand your point: Multiple people commented on the lack of information but somehow you decide to attack me, even though i shared useful links to help you improve your question?! Thanks mate... — dario, Mar 03 '20 at 14:04

score 3 · Accepted Answer · answered Mar 03 '20 at 12:16

3

Or a simple base R solution:

sapply(strsplit(table_1, "_"), 
       function(x) paste0("W", x[1], "_", month.abb[as.numeric(x[2])], "_", x[3]))
#> [1] "W1_Jan_2014" "W1_Feb_2014" "W2_Mar_2015" "W5_Jul_2019"

answered Mar 03 '20 at 12:16

Allan Cameron

147,086
7
49
87

score 2 · Answer 2 · answered Mar 03 '20 at 11:57

2

It would be nice to have more information on the values that weren't to be altered as the below may be needlessly complex or not complex enough, but you can try:

library(stringr)
library(dplyr)

vec <- c("1_1_2014",
         "1_2_2014",
         "2_3_2015",
         "5_7_2019",
         "XXXXXXX",
         "YYYYYYY")

if_else(str_detect(vec, "\\d_\\d+_"), paste0("W", str_replace(vec, "(?<=_)\\d+(?=_)", function(x) month.abb[as.numeric(x)])), vec)

[1] "W1_Jan_2014" "W1_Feb_2014" "W2_Mar_2015" "W5_Jul_2019" "XXXXXXX"     "YYYYYYY"

answered Mar 03 '20 at 11:57

Ritchie Sacramento

29,890
4
48
56

This is probably the most robust solution. +1 – Allan Cameron Mar 03 '20 at 12:19
Hello, Thanks for the solution, I can't share more information as it is sensitive data, Hence I've mentioned that the table contains more values apart from the W_M_Year data and there are more than a million rows. Hence didn't share more. – Shivam Sarin Mar 03 '20 at 12:41

score 0 · Answer 3 · answered Mar 03 '20 at 11:52

You are actually asking multiple questions ..

 library(tidyverse)

 d <- tibble(`Table 1`= c("1_1_2014", "1_2_2014", "2_3_2015", "5_7_2019"))

 lookup <- set_names(c("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"),
                     as.character(1:12))

 d %>%
   mutate(`Table 1`= str_c("W", str_extract(`Table 1`, "^\\d+"), "_", 
                           lookup[str_split(`Table 1`, "_", simplify = TRUE)[, 2]],
                           "_", str_extract(`Table 1`, "\\d+$")))

 # A tibble: 4 x 1
 # `Table 1`  
 # <chr>      
 # 1 W1_Jan_2014
 # 2 W1_Feb_2014
 # 3 W2_Mar_2015
 # 4 W5_Jul_2019

Hello, Thanks for the solution, but I'm sorry I fail to see how is it asking multiple questions. My dataset is like this. How would you ask if you had to solve the same problem? — Shivam Sarin, Mar 03 '20 at 13:25

Replace multiple values in a column with new values

3 Answers3