Error map_df across files in a folder using R

user_9393218 0
Viewed 945

So I have a folder of files each of "almost" identical CSV's. They each look something like:

EMP ID  WORK DATE   WORK HOURS   JOB TITLE  MGMT CTR
  002    01/02/2019          8     Janitor        44
  003    01/03/2019         29     Analyst       044
  004    01/02/2019        400      Barber         2
   ...

I say almost because some of them have a few extra variables but I only care about two of them.

Using the following code I can theoretically combine and group them together based on WORK DATE and WORK HRS (the two variables I care about).

test <- list.files(path = "path", full.names = TRUE) %>%
  map_dfr(read.csv) %>%
  select(WORK.DATE,WORK.HRS) %>% 
  group_by(WORK.DATE) %>%
  summarize(hour_sum = sum(WORK.HRS))

I do this and I get an error:

Error: Can't combine `..1$JOB.NUM` <double> and `..2$JOB.NUM` <character>.

It seems these variables are from one or two odd files. But I have no need for them and I thought the select statement would help...it didn't. I tried with readr's read_csv.

test <- list.files(path = "path", full.names = TRUE) %>%
    map_dfr(read_csv) %>%
    select(`WORK DATE`,`WORK HRS`) %>% 
  group_by(`WORK DATE`) %>%
    summarize(hour_sum = sum(`WORK HRS`))

And get a similar error:

Error: Can't combine `MGMT CTR` <double> and `MGMT CTR` <character>.

Truly I only care about those two variables, and so long as WORK DATE is a character or date and WORK HRS is an integer, we are good.

purrr tidyverse csv r
1 Answers

The issue would be that some dataset have columns that doesn't match the type. An option would be convert to a single type within map_df and then change the type

library(dplyr)
library(purrr)
library(readr)
library(lubridate)
list.files(path = "path", full.names = TRUE) %>%
     map_dfr(~ read_csv(.x) %>% 
                  mutate(across(everything(), as.character))) %>% 
     select(`WORK DATE`,`WORK HRS`) %>% 
     type.convert(as.is = TRUE) %>%
     group_by(`WORK DATE` = mdy(`WORK DATE`)) %>%
     summarize(hour_sum = sum(`WORK HRS`)) # assume that "WORK HRS" is numeric
user_37322710
Related