Fill a dataframe column with values of others according to a specific column

Question

My dataframe looks like:

I tried to add a new column and fill it with the value of the column number (intervals + 3) for each row.

For example if intervals = 3 , I want to get the value of DF[,3+3]

I tried this, but it didn't work :

DF$new_col <- DF[,DF$intervals[]+3]

So for `intervals == 3`, you want to get the value of the column `solarNoon`? — LAP, Jul 03 '18 at 09:06
Please provide your example as plain text (using `dput` for example) instead of pictures. It's easier for SO community to answer and for search engine to be efficient — Emmanuel-Lin, Jul 03 '18 at 09:14
@Emmanuel-Lin sorry this is my first post here but I will take your comment into consideration next time — A.Bsh, Jul 03 '18 at 12:51
@LAP yes exactly, the new column will take values from: midnight, sunrise, solarNoon, sunset, night, lastnight columns according to values in "intervals" column. So if intervals == 3, I want to get the value of solarNoon column if intervals == 2, I get the value of sunrise, etc. — A.Bsh, Jul 03 '18 at 12:59
You might also be able to create a `data.table` solution (which would probably be very fast) with the help of this thread: https://stackoverflow.com/questions/33310179/select-values-from-different-columns-based-on-a-variable-containing-column-names — LAP, Jul 03 '18 at 13:38

score 0 · Answer 1 · answered Jul 03 '18 at 10:03

Though the description of the problem is not absolutely clear from the question. Still check the following for loop based solution if its solves your query. Please note since no reproducible data was provided, I have generated a dummy data for this purpose.

> df<-data.frame(D1=c(2,8,1,5,2),D2=c(7,3,5,6,8),D3=c(9,6,4,1,0),D4=c(12,20,61,80,91),Interval=c(1,2,1,0,1))
> df
  D1 D2 D3 D4 Interval
1  2  7  9 12        1
2  8  3  6 20        2
3  1  5  4 61        1
4  5  6  1 80        0
5  2  8  0 91        1
> for (i in 1:nrow(df)){
+ df[i,6]<-df[i,df$Interval[i]+3]
+ }
> df
  D1 D2 D3 D4 Interval V6
1  2  7  9 12        1 12
2  8  3  6 20        2  2
3  1  5  4 61        1 61
4  5  6  1 80        0  1
5  2  8  0 91        1 91

thank you for your answer it's exactly what i want but as the dataframe is very large I would like to avoid using a for loop . — A.Bsh, Jul 03 '18 at 13:11

score 0 · Accepted Answer · answered Jul 03 '18 at 13:31

I'm not sure how fast it is, but here's a solution using split and mapply.

Some example data:

set.seed(1)
df <- data.frame(var1 = 1:10,
                 var2 = 11:20,
                 var3 = 21:30,
                 intervals = sample(0:2, 10, replace = T))

   var1 var2 var3 intervals
1     1   11   21         0
2     2   12   22         1
3     3   13   23         1
4     4   14   24         2
5     5   15   25         0
6     6   16   26         2
7     7   17   27         2
8     8   18   28         1
9     9   19   29         1
10   10   20   30         0

We first sort the dataframe by intervals:

df <- df[order(df$intervals),]

   var1 var2 var3 intervals
1     1   11   21         0
5     5   15   25         0
10   10   20   30         0
2     2   12   22         1
3     3   13   23         1
8     8   18   28         1
9     9   19   29         1
4     4   14   24         2
6     6   16   26         2
7     7   17   27         2

Now we split the data into subsets for every value of intervals.

df1 <- split(df, df$intervals)

Now we use mapply to simultaneously loop over the list of subsets and the vector unique(df$intervals)+1 (for you it would be +3) to select the right values.

newvalues <- mapply(function(x, y){
  x[, y]
}, df1, unique(df$intervals)+1)

Finally the feed the values back to the original, sorted dataframe by using unlist.

df$new <- unlist(newvalues)

Result:

   var1 var2 var3 intervals new
1     1   11   21         0   1
5     5   15   25         0   5
10   10   20   30         0  10
2     2   12   22         1  12
3     3   13   23         1  13
8     8   18   28         1  18
9     9   19   29         1  19
4     4   14   24         2  24
6     6   16   26         2  26
7     7   17   27         2  27

Fill a dataframe column with values of others according to a specific column

2 Answers2