User defined function for analyzing multiple csvs, create variables, and count NAs by group in R

user_13177744 0
Viewed 37

I have roughly 100 datasets with different variables (and different numbers of variables) but each dataset has a household ID (hh_ID) as an identifier. Variables represent survey questions. Each csv represents a different type of survey. I want to write a custom function that counts the number of times a household was asked a question and the number of times they skipped a question (NA). The issue I'm having is in renaming the variables and counting across csvs.

Let's say two dataframes look like this:

hh_ID <- c(1,1,2,2,2)
question1 <- c(NA,1,0,0,0)
question2 <- c(1,1,NA,0,0)
df1 <- data.frame(hh_ID, question1, question2)

hh_ID <- c(1,1,1,2,2)
question3 <- c(NA,NA,0,0,0)
question4 <- c(1,1,1,NA,NA)
df2 <- data.frame(hh_ID, question3, question4)

## > df1
##   hh_ID question1 question2
## 1     1        NA         1
## 2     1         1         1
## 3     2         0        NA
## 4     2         0         0
## 5     2         0         0
## > df2
##   hh_ID question3 question4
## 1     1        NA         1
## 2     1        NA         1
## 3     1         0         1
## 4     2         0        NA
## 5     2         0        NA

I need the final dataframe to look like this:

question1_count <- c(2,3)
question1_NAs   <- c(1,0)
question2_count <- c(2,3)
question2_NAs   <- c(0,1)
question3_count <- c(3,2)
question3_NAs   <- c(2,0)
question4_count <- c(3,2)
question4_NAs <- c(0,2)
finaldf <- data.frame(unique(hh_ID),question1_count, question1_NAs,question2_count,question2_NAs,question3_count,question3_NAs, question4_count,question4_NAs) 

## > finaldf
##   unique.hh_ID. question1_count question1_NAs question2_count question2_NAs question3_count question3_NAs question4_count question4_NAs
## 1             1               2             1               2             0               3             2               3             0
## 2             2               3             0               3             1               2             0               2             2

This is what I have so far:

# read in each dta file
filenames <- list.files(path=mydirectory, pattern=".*dta")
for (i in 1:length(filenames)){
assign(filenames[i], read_dta(paste("", filenames[i], sep=''))
)}

variable_NA_count <- function(dataset, col_name){
temp <- dataset %>% group_by(hh_ID) %>% summarise(question_count = n()) 
temp1 <- aggregate(col_name ~ hh_ID, data=dataset, function(x) {sum(is.na(x))}, na.action = NULL)
final <- merge(temp, temp1, by = "hh_ID")
return(final)}

frequency <- function(dataset, col_name){
temp <- variable_NA_count(dataset, col_name)
temp <- temp %>% select(question1_count = question_count,
                        question1_NAs = col_name)}

The issue is that I want each variable name to end in "_count" and "_NAs" without explicitly writing "question1_count = question_count". I have hundreds of variables across csvs so I need a function that reads every csv, reads in every column name, counts the number of times a household was asked a question, and the number of times they didn't respond. I've tried various ways such as paste function but keep hitting a wall.

Thanks!

r function functional-programming
2 Answers

You can make great use of dplyr's summarize_all function:

It will summarize all the columns in a df with one or more given functions, creating smart columns names (starting from the original column name and adding the func name).

library(dplyr)

df1 %>%
  group_by(hh_ID) %>% 
  summarize_all(.funs = list(count = ~n(), NAs = ~sum(is.na(.))))
#> # A tibble: 2 x 5
#>   hh_ID question1_count question2_count question1_NAs question2_NAs
#>   <dbl>           <int>           <int>         <int>         <int>
#> 1     1               2               2             1             0
#> 2     2               3               3             0             1

Created on 2020-04-01 by the reprex package (v0.3.0)

We can apply the same operation to a list of dataframes using purrr's map function:

library(dplyr)
library(purrr)

list(df1, df2) %>% 
  map(~{
    .x %>%
      group_by(hh_ID) %>% 
      summarize_all(.funs = list(count = ~n(), NAs = ~sum(is.na(.))))
  }) %>% 
  reduce(full_join)
#> Joining, by = "hh_ID"
#> # A tibble: 2 x 9
#>   hh_ID question1_count question2_count question1_NAs question2_NAs
#>   <dbl>           <int>           <int>         <int>         <int>
#> 1     1               2               2             1             0
#> 2     2               3               3             0             1
#> # … with 4 more variables: question3_count <int>, question4_count <int>,
#> #   question3_NAs <int>, question4_NAs <int>

Created on 2020-04-01 by the reprex package (v0.3.0)

map returns a list of dataframes, but we want to join them using full_join (or any other *_join you deem appropriate)

Finally we can glue it together reading the files: list.files(path=mydirectory, pattern=".*dta") returns a character vector and we can apply map to that.

For each file, read it, summarize and join:

library(dplyr)
library(purrr)
library(haven)

list.files(path=mydirectory, pattern=".*dta") %>% 
  map(~{
    read_dta(.x) %>%
      group_by(hh_ID) %>% 
      summarize_all(.funs = list(count = ~n(), NAs = ~sum(is.na(.))))
  }) %>% 
  reduce(full_join)

Created on 2020-04-01 by the reprex package (v0.3.0)

(Output not show as I don't have any directory with *.dta file in it)

user_12612810

I suggest a quick solution although it is not exactly in the format you expect.

     list..res <- lapply(list(df1,df2), 
                function(x) setDT(x)[,lapply(.SD,function(x) {  
         list(.N,sum(is.na(x)))}),by=hh_ID][,`:=`(index=1:.N,type=c("count", 
                                                              "no..na")),hh_ID])

For each data.frame, I convert it into a data.table (library(data.table)), then for each question, I count the number of question, count the number of NA, and count the number of NA. Lastly I added the a column type and index

 ## + + > list..res
## [[1]]
##    hh_ID question1 question2 index   type
## 1:     1         2         2     1  count
## 2:     1         1         0     2 no..na
## 3:     2         3         3     1  count
## 4:     2         0         1     2 no..na

## [[2]]
##    hh_ID question3 question4 index   type
## 1:     1         3         3     1  count
## 2:     1         2         0     2 no..na
## 3:     2         2         2     1  count
## 4:     2         0         2     2 no..na

Then we can Reduce this list by merging.

Reduce(function(x,y) merge(x,y,by=c("hh_ID","type","index")), list..res)

##    hh_ID   type index question1 question2 question3 question4
## 1:     1  count     1         2         2         3         3
## 2:     1 no..na     2         1         0         2         0
## 3:     2  count     1         3         3         2         2
## 4:     2 no..na     2         0         1         0         2

Finally instead of list(df1,df2) you can put the list of data.frames.

filenames <- list.files(path=mydirectory, pattern=".*dta")
df..list <- lapply(filenames, read_dta)
user_20814270
Related