Odtnhj

Been having a difficult time trying to code this problem out. So effectively I am trying to select the cell in a wide data frame where the values in one column are contained with the column name's string. I generally use the tidyverse in my workflows and cannot get anything in that verse to work. Tried applying, for looping through the rows. Got some things to work but they are VERY slow. The attached data frame snippet is just the first 10 rows of a 1.8 million row data frame. So using tidy::gather is out of the question here. Any thoughts on how I could go about accomplishing this would be of great use because it comes up a lot more often than I would have expected.

Data can be found here

library(tidyverse)
library(foreach)

df <- read_csv('test_data.csv')

So here I am trying to find the variable for fire_year that is contained within the wide var_ fields. So for instance, here if fire_year = 1998 then I would want to capture the value in the column named var_1998. This is the closest I have got to getting a solution (and it works!) but it takes forever on the full data frame:

df_slim <- foreach(df=iter(df, by='row'), .combine=rbind, 
 .packages = c('dplyr', "tidyverse")) %do% 
 df_out <- df %>%
 gather(key = key, value = out_var, -fpa_id, -fire_year) %>%
 separate(key,
 into = c("tmp1", 'zyear'),
 sep = "_") %>%
 mutate(var = ifelse(fire_year == zyear, out_var, NA)) %>%
 na.omit() %>%
 dplyr::select(fpa_id, fire_year, var)
 return(df_out)
 

I cannot figure fast, efficient way to accomplish for the life of me! At this point I have calculated that it will take 160 hours to complete this for loop on the 1.7 million row data frame! If someone could point me in the right direction I would be eternally grateful!

Thanks!

I am not 100% sure what you need, but here is my take on it (using data.table)

library(data.table)

 dt <- data.table(test_data)

 setkey(dt, "fire_year")
 for(i in unique(dt[["fire_year"]]))
 dt[fire_year == i, var:= get(paste("var", i, sep = "_"))]
 

And then subset the cols you need

dt_slim <- dt[,.SD, .SDcols = c("fpa_id", "fire_year", "var")]
dt_slim

 fpa_id fire_year var
 1: FS-1418827 2004 0
 2: FS-1418835 2004 9
 3: FS-1418845 2004 0
 4: FS-1418847 2004 0
 5: FS-1418849 2004 0
 6: FS-1418851 2004 0
 7: FS-1418859 2004 0
 8: FS-1418826 2005 0
 9: FS-1418854 2005 0
10: FS-1418856 2005 114

Not tested on your 1.8 million rows. Should be relativly fast. Interested on the timings though...