Replace values in a column

Question

This is a sample of my data frame:

    I  UserID | Day_of_week | hour |   min 
   #1    1           1          0     0     
   #2    1           1          0     30     
   #3    1           1          1     0     
   #4    1           1          1     30   
   #5    1           1          2     0      
   #6    1           1          2     30   
   ...   ...         ...       ...    ...  
   #10    1          2          0     0     
   #11    1          2          0     30    
   #12    1          2          1     0     
   #13    1          2          1     30    
   ...   ...         ...       ...    ...

The column Day_of_week goes from 1 to 7 where:

2 = Monday, 3 = Tuesday .... 6 = Friday

and 7 and 1 are Saturday and Sunday respectively.

I want to change in the column of Day_of_Week by the names of week. Something like this:

       I  UserID | Day_of_week | hour |   min 
       #1    1       Sunday        0     0     
       #2    1       Sunday        0     30     
       #3    1       Sunday        1     0      
       ...   ...         ...      ...    ...  
       #10    1      Monday        0     0     
       #11    1      Monday        0     30    
       #12    1      Monday        1     0     
       #13    1      Monday        1     30    
       ...   ...         ...      ...    ...

I think vector indexing should be enough, what have you tried? — r2evans, Feb 01 '18 at 05:49
But I need to read the values in the day of week column to change by their week name. if (day_of_week == 1) <- Sunday and so on — user147460, Feb 01 '18 at 05:54
You can use `weekdays` to generate a vector to subset: `df$Day_of_week <- weekdays(as.Date('1970-01-01') + 2 + 1:7)[df$Day_of_week]` or more directly, `weekdays(as.Date('1970-01-01') + 2 + df$Day_of_week)` — alistaire, Feb 01 '18 at 05:55
Did you try the code? One of the harder things to convince new R programmers is that you don't have to do everything in a loop (not a personal attack, this is a common "hard thing to do well"). — r2evans, Feb 01 '18 at 05:56
`weekdays.Date(1)` fails. But okay, it requires either `POSIXt` or `Date`, good catch. — r2evans, Feb 01 '18 at 05:59
Sorry, missed that, good work as always, alistaire. (You aren't at the rstudio::conf by chance, are you?) — r2evans, Feb 01 '18 at 06:00
@r2evans (No, sadly. I need to get a job that pays for me to go to such things. Or use R, for that matter...) — alistaire, Feb 01 '18 at 06:40
Related post https://stackoverflow.com/questions/35636315/replace-values-in-a-dataframe-based-on-lookup-table — zx8754, Feb 01 '18 at 07:24

score 3 · Accepted Answer · answered Feb 01 '18 at 06:03

Just use a vector.

dow <- c("Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday")
set.seed(2)
df <- data.frame(Day_of_week = sample(7, size=20, replace=TRUE))
head(df)
#   Day_of_week
# 1           2
# 2           5
# 3           5
# 4           2
# 5           7
# 6           7
df$Day_of_week <- dow[df$Day_of_week]    
head(df)
#   Day_of_week
# 1      Monday
# 2    Thursday
# 3    Thursday
# 4      Monday
# 5    Saturday
# 6    Saturday

score 2 · Answer 2 · answered Feb 01 '18 at 06:17

 library(expss)

 #Creating a sample dataset like yours
 Day_of_week <- c(1,7,1,7,1,3,2,2,7,2,2,4,3,5,3)
 userid <- c(1,3,1,1,1,3,2,2,4,4,3,3,32,3,3)
 TableExample <- as.data.frame(cbind(userid, Day_of_week))


 #Creating a week day reference table
 DayID <- c(1,2,3,4,5,6,7)
 DayName <- c("Sunday", "Monday", "Tuesday", "Wed", "Thur", "Friday", "Sat")
 Weektable <- as.data.frame(cbind(DayID,DayName))

 #Vlookup formula to look for x in the referencetable
 FindWeekFunc <- function(x){
   vlookup(x,Weektable,2)}

 #maply to find teh result
 TableExample$Name_of_Day <- mapply(FindWeekFunc, Day_of_week)

Niranjan Agnihotri · Answer 3 · 2018-02-01T06:52:19.877

1

I think you can convert the column to a factor variable.

you can try something like below

df$day_of_week <- factor(df$day_of_week,
                          labels = c("Sunday", "Monday","Tuesday","Wednesday", "Thursday", "Friday", "Saturday"),
                          levels = c(1,2,3,4,5,6,7))

I hope this should work.

edited Feb 01 '18 at 06:52

answered Feb 01 '18 at 05:59

Niranjan Agnihotri

916
2
11
19

You mean to say I should have completed the names of Weekdays and the levels right? Could you elaborate so that I can correct myself. – Niranjan Agnihotri Feb 01 '18 at 06:50

score 0 · Answer 4 · answered Feb 01 '18 at 05:56

Make a function that takes a number

library(dplyr)

day_of_week <- function(num){

  ifelse(num == 1, "Sunday", 
         ifelse(num ==2, "Monday", 
                ifelse(num == 3, "Tuesday", 
                       ifelse(num == 4, "Wednesday", 
                              ifelse(num == 5, "Thursday", 
                                     ifelse(num == 6, "Friday", "Saturday"))))))

  }

Then use dplyr to mutate your dataframe

dat <- 
  data.frame(x = rnorm(10), 
             day = sample(1:7, 10, TRUE)) %>%
  mutate(day_of_week = day %>% day_of_week())

> dat
               x day day_of_week
1  -0.4235498649   4   Wednesday
2   1.0366219455   2      Monday
3   0.4280368042   7    Saturday
4  -1.0335602213   5    Thursday
5  -0.6016099023   5    Thursday
6   1.4246527069   2      Monday
7  -0.3303051311   6      Friday
8  -0.0009648844   4   Wednesday
9   1.0168062274   6      Friday
10  0.2896002781   7    Saturday

Steven: I applaud your tenacity here, but this is grossly inefficient. I strongly encourage the use of vectorized indexing, as suggested in my comment. — r2evans, Feb 01 '18 at 05:57
@r2evans One of the many results of learning R during my graduate student days was that I did a lot of things by "brute force." You suggested a new concept to me; I'll start building it into my workflow. — Steven, Feb 01 '18 at 06:06

score 0 · Answer 5 · answered Feb 01 '18 at 07:15

You can use the base::weekdays function if you turn the numbers into dates by adding them to any particular date that's a Saturday:

df <- data.frame(I = c(1L, 2L, 3L, 4L, 5L, 6L, 10L, 11L, 12L, 13L), 
                 UserID = c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), 
                 Day_of_week = c(1L, 1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L), 
                 hour = c(0L, 0L, 1L, 1L, 2L, 2L, 0L, 0L, 1L, 1L), 
                 min = c(0L, 30L, 0L, 30L, 0L, 30L, 0L, 30L, 0L, 30L))

df$weekday <- weekdays(as.Date('1970-01-01') + 2 + df$Day_of_week)

df
#>     I UserID Day_of_week hour min weekday
#> 1   1      1           1    0   0  Sunday
#> 2   2      1           1    0  30  Sunday
#> 3   3      1           1    1   0  Sunday
#> 4   4      1           1    1  30  Sunday
#> 5   5      1           1    2   0  Sunday
#> 6   6      1           1    2  30  Sunday
#> 7  10      1           2    0   0  Monday
#> 8  11      1           2    0  30  Monday
#> 9  12      1           2    1   0  Monday
#> 10 13      1           2    1  30  Monday

Replace values in a column

5 Answers5