3

I have a very ugly dataset that is a flat file of a relational database. A minimal reproducible example is here:

df <- data.frame(col1 = c(letters[1:4],"c"), 
                  col1.p = 1:5, 
                  col2 = c("a","c","l","c","l"), 
                 col2.p = 6:10,
                  col3= letters[3:7],
                 col3.p = 11:20)

I need to be able to identify the '.p' value for the 'col#' that has the "c". My previous question on SO got the first part: In R, find the column that contains a string in for each row. Which I'm providing for context. 

tmp <- which(projectdata=='Transmission and Distribution of Electricity', arr.ind=TRUE)
cnt <- ave(tmp[,"row"], tmp[,"row"], FUN=seq_along)
maxnames <- paste0("max",sequence(max(cnt)))
projectdata[maxnames] <- NA
projectdata[maxnames][cbind(tmp[,"row"],cnt)] <- names(projectdata)[tmp[,"col"]]
rm(tmp, cnt, maxnames)

This results in a dataframe that looks like this: 

df
   col1 col1.p col2 col2.p col3 col3.p max1
1     a      1    a      6    c     11 col3
2     b      2    c      7    d     12 col2
3     c      3    l      8    e     13 col1
4     d      4    c      9    f     14 col2
5     c      5    l     10    g     15 col1
6     a      1    a      6    c     16 col3
7     b      2    c      7    d     17 col2
8     c      3    l      8    e     18 col1
9     d      4    c      9    f     19 col2
10    c      5    l     10    g     20 col1

When I tried to get the ".p" that matched the value in "max1", I kept getting errors. I thought the approach would be:

df %>%
   mutate(my.p = eval(as.name(paste0(max1,'.p'))))
Error: object 'col3.p' not found

Clearly, this did not work, so I thought maybe this was similar to passing a column name in a function, where I need to use 'get'. That also didn't work. 

df %>%
   mutate(my.p = get(as.name(paste0(max1,'.p'))))
Error: invalid first argument
df %>%
   mutate(my.p = get(paste0(max1,'.p')))
Error: object 'col3.p' not found

I found something that gets rid of this error, using data.table from a different, but related problem, here: http://codereply.com/answer/7y2ra3/dplyr-error-object-found-using-rle-mutate.html. However, it gives me "col3.p" for every row. This is max1 for the first row, df$max1[1]

library('dplyr')
library('data.table') # must have the data.table package
df %>%
  tbl_dt(df) %>% 
  mutate(my.p = get(paste0(max1,'.p')))

Source: local data table [10 x 8]

   col1 col1.p col2 col2.p col3 col3.p max1 my.p
1     a      1    a      6    c     11 col3   11
2     b      2    c      7    d     12 col2   12
3     c      3    l      8    e     13 col1   13
4     d      4    c      9    f     14 col2   14
5     c      5    l     10    g     15 col1   15
6     a      1    a      6    c     16 col3   16
7     b      2    c      7    d     17 col2   17
8     c      3    l      8    e     18 col1   18
9     d      4    c      9    f     19 col2   19
10    c      5    l     10    g     20 col1   20

Using the lazyeval interp approach (from this SO: Hot to pass dynamic column names in dplyr into custom function?) doesn't work for me. Perhaps I am implementing it incorrectly?

library(lazyeval)
library(dplyr)
df %>%
  mutate_(my.p = interp(~colp, colp = as.name(paste0(max1,'.p'))))

I get an error: 

Error in paste0(max1, ".p") : object 'max1' not found

Ideally, I will have the new column my.p equal the appropriate p based on the column identified in max1.

I can do this all with ifelse, but I am trying to do it with less code and to make it applicable to the next ugly flat table.

Community
  • 1
  • 1
jessi
  • 1,438
  • 1
  • 23
  • 36
  • If you are using `data.table`. `setDT(df)[, my.p:= get(paste0(max1, '.p')), 1:nrow(df)]` should get the output you wanted – akrun Aug 27 '15 at 02:55
  • 1
    I'm not sure why this response doesn't look like an answer, @akrun, but it does work. If it turns into an answer, I can accept it. Thanks for your help. Why doesn't it work to put in dplyr `mutate(my.p=get(paste0(max1, '.p'))` ? I'd really like to understand that. – jessi Aug 27 '15 at 03:58
  • I can't figure it out either. I'm going to use data.table. However, it is strange dplyr behavior. Other SO topics suggest, as I show above, that it should work with the interp. If I was sure it was a bug, I'd be on github. – jessi Sep 03 '15 at 21:32

1 Answers1

0

We can do this with data.table. We convert the 'data.frame' to 'data.table' (setDT(df)), grouped by the the row sequence, we get the value of the paste output, and assign (:=) it to a new column ('my.p').

library(data.table)
setDT(df)[, my.p:= get(paste0(max1, '.p')), 1:nrow(df)]
df
#    col1 col1.p col2 col2.p col3 col3.p max1 my.p
# 1:    a      1    a      6    c     11 col3   11
# 2:    b      2    c      7    d     12 col2    7
# 3:    c      3    l      8    e     13 col1    3
# 4:    d      4    c      9    f     14 col2    9
# 5:    c      5    l     10    g     15 col1    5
# 6:    a      1    a      6    c     16 col3   16
# 7:    b      2    c      7    d     17 col2    7
# 8:    c      3    l      8    e     18 col1    3
# 9:    d      4    c      9    f     19 col2    9
#10:    c      5    l     10    g     20 col1    5
akrun
  • 874,273
  • 37
  • 540
  • 662