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
– r2evans, Commented Feb 1, 2018 at 5: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
– user147460, Commented Feb 1, 2018 at 5: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
– alistaire, Commented Feb 1, 2018 at 5:55
@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
– alistaire, Commented Feb 1, 2018 at 6:40

r2evans · Accepted Answer · 2018-02-01 06:03:34Z

3

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

answered Feb 1, 2018 at 6:03

r2evans

167k8 gold badges92 silver badges176 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user5015341 · Accepted Answer · 2018-02-01 06:17:11Z

2

 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)

answered Feb 1, 2018 at 6:17

user5015341

Comments

Niranjan Agnihotri · Accepted Answer · 2018-02-01 06:52:19Z

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 1, 2018 at 6:52

answered Feb 1, 2018 at 5:59

Niranjan Agnihotri

1,1162 gold badges12 silver badges19 bronze badges

1 Comment

Niranjan Agnihotri Over a year ago

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.

Steven · Accepted Answer · 2018-02-01 05:56:28Z

0

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

answered Feb 1, 2018 at 5:56

Steven

3,33427 silver badges54 bronze badges

2 Comments

r2evans Over a year ago

Steven: I applaud your tenacity here, but this is grossly inefficient. I strongly encourage the use of vectorized indexing, as suggested in my comment.

Steven Over a year ago

@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.

alistaire · Accepted Answer · 2018-02-01 07:15:11Z

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

Collectives™ on Stack Overflow

Replace values in a column

5 Answers 5

Comments

Comments

1 Comment

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related