CODEWITHSUNDEEP
rpurrr
dad
dad

Reputation: 1335

List of dataframes to one DF; Extract date column from each DF in list, pass all values to single DF w/ columns 1985-2017)

I have a list of 169 dataframes (assetcount_dfs) corresponding to squares on a geographical grid that each contain a bundle of assets. I would like to fill a separate dataframe counting the number of assets that begin on each date, per square, for years 1985-2017.

Here's how this list of dataframes is structured:

 Square1_DF (3 rows/assets)   | x | y | dates char[1989, N/A, 1991]
 ...
 Square169_DF (1 row/asset)   | x | y | dates char[2002]

I want to convert this to one dataframe counting these dates, in 'dateDF': 

            | 1989 | 1990 | ... | 2015 | 2016 | 2017 
 Square 1      0      1            3      2      0      
 ...
 Square 169    0      0            0      1      3

Here's a toy sample of my data. Within each of the data frames in assetcount_dfs, the 'val' column represents the dates I want to populate dateDF with:

  sdf1 <- data.frame(a = c("1","4","5","1"), x = c("sdf","asf","asdf","sdf"), val = c("2014","2012","#N/A", "2001"))
  sdf2 <- data.frame(a = c("1","4"), x = c("sdf","asdf"), val = c("#N/A","2011"))
  sdf3 <- data.frame(a = c("1","4","5","1","1"), x = c("sdf","asf","asdf","sdf","sdf"), val = c("2010","2015","2000","2002", "2003"))

  assetcount_dfs <- list(sdf1 = sdf1,sdf2 = sdf2,sdf3 = sdf3)

  date_range <- 1985:2017
  dateDF <- data.frame(matrix(ncol = length(date_range),nrow = 3))     # actual length is 169 rows, only using 3 for this example
  colnames(dateDF) <- paste0('X',1985:2017) # name columns 'X'DATE
  rownames(dateDF) <- names(assetcount_dfs)
  dateDF[] <- 0          # filled with zeroes

Current attempt

Within each dataframe's 'val' column, I want to check if any of the date values were in the range 1985-2017, and if so, add them to dateDF's X--- date column.

I tried using 'purr' (like lapply) to operate on each DF but I'm struggling to understand where to go from here.

invisible(map(listx, function(df) {

for (i in df$val){
    if (as.integer(i) %in% 1985:2017){
    datesDF_colName <- paste0('X',i)
    dateDF[substitute(df), datesDF_colName] <- dateDF[[datesDF_colName]] + 1 
      # Attempt to set dateDF value at [grid-square DF's name / row, Column based on Year ]
    } 

}}))

# Output:    
# Error in `[<-.data.frame`(`*tmp*`, substitute(df), datesDF_colName, value = 
# c(1,  : 
#  anyNA() applied to non-(list or vector) of type 'language'
# Called from: `[<-.data.frame`(`*tmp*`, substitute(df), datesDF_colName, 
# value = c(1, 
# 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 
# 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 

# Note my sample code for 'listx' for some reason generates DFs with factors, although I am currently dealing with character arrays.

Upvotes: 0

Views: 103

Answers (1)

crazybilly
crazybilly

Reputation: 3092

I'd use the tidyverse() to handle this. Instead of trying to edit dateDF in a loop, count how often a year appears together with a dataframe ID, then reshape the data into the format that you're looking for. 

library(tidyverse)

assets2  <- assetcount_dfs %>% 
  # combine all the small data frames into a single big df
  bind_rows(.id = 'rowdf') %>% 
  # toss out the N/A values so they don't get counted
  filter(val != "#N/A")


simpleDateDF <- assets2 %>% 
  # count each year and what data frame it's from
  count(rowdf, val) %>% 
  # spread the years out into columns, using 0 as the default
  spread(val, n, fill = 0)

Upvotes: 1

Related Questions