How should I approach merging (full joining) multiple (>100) CSV files with a common key but inconsistent number of rows?

user_13538007 0
Viewed 537

Before I dive into the question, here is a similar problem asked but there is not yet a solution.

So, I am working in R, and there is a folder in my working directory called columns that contains 198 similar .csv files with the name format of a 6-digit integer (e.g. 100000) that increases inconsistently (since the name of those files are actually names for each variable).

Now, I have would like to full join them, but somehow I have to import all of those files into R and then join them. Naturally, I thought about using a list to contain those files and then use a loop to join them. This is the code I tried to use:

#These are the first 3 columns containing identifiers
matrix_starter <- read_csv("files/matrix_starter.csv")

## import_multiple_csv_files_to_R
# Purpose: Import multiple csv files to the Global Environment in R

# set working directory
setwd("columns")

# list all csv files from the current directory
list.files(pattern=".csv$") # use the pattern argument to define a common pattern for import files with regex. Here: .csv

# create a list from these files
list.filenames <- list.files(pattern=".csv$")
#list.filenames

# create an empty list that will serve as a container to receive the incoming files
list.data <- list()

# create a loop to read in your data
for (i in 1:length(list.filenames))
{
list.data[[i]] <- read.csv(list.filenames[i])
list.data[[i]] <- list.data[[i]] %>% 
  select(`Occupation.Title`,`X2018.Employment`) %>% 
  rename(`Occupation title` = `Occupation.Title`) #%>% 
  #rename(list.filenames[i] = `X2018.Employment`)
}

# add the names of your data to the list
names(list.data) <- list.filenames

# now you can index one of your tables like this
list.data$`113300.csv`

# or this
list.data[1]

# source: https://www.edureka.co/community/1902/how-can-i-import-multiple-csv-files-into-r

The chunk above solve the importing part. Now I have a list of .csv files. Next, I would like to join them:

for (i in 1:length(list.filenames)){
matrix_starter <- matrix_starter %>% full_join(list.data[[i]], by = `Occupation title`)
}

However, this does not work nicely. I end up with somewhere around 47,000 rows, to which I only expect around 1700 rows. Please let me know your opinion.

tidyverse r
3 Answers

Reading the files into R as a list and including the file name as a column can be done like this:

files <- list.files(path = path,
                    full.names = TRUE,
                    all.files = FALSE)
files <- files[!file.info(files)$isdir]

data <- lapply(files,
               function(x) {
                 data <- read_xls(
                   x,
                   sheet = 1
                 )
                 data$File_name <- x
                 data
                 })

I am assuming now that all your excel files have the same structure: the same columns and column types. If that is the case you can use dplyr::bind_rows to create one combined data frame. You could off course loop through the list and left_join the list elements. E.g. by using Reduce and merge.

Update based on mihndang's comment. Is this what you are after when you say: Is there a way to use the file name to name the column and also not include the columns of file names? 

library(dplyr)
library(stringr)

path <- "./files"
files <- list.files(path = path,
                    full.names = TRUE,
                    all.files = FALSE)
files <- files[!file.info(files)$isdir]

data <- lapply(files,
               function(x) {
                 read.csv(x, stringsAsFactors = FALSE)
               })

col1 <- paste0(str_sub(basename(files[1]), start = 1, end = -5), ": Values")
col2 <- paste0(str_sub(basename(files[1]), start = 1, end = -5), ": Character")
df1 <- data[[1]] %>%
  rename(!!col1 := Value,
         !!col2 := Character)

I created two simple .csv files in ./files: file1.csv and file2.csv. I read them into a list. I extract the first list element (the DF) and work out column names in a variable. I then rename the columns in the DF by passing the two variables to them. The column name includes the file name. Result:

> View(df1)
> df1
   file1: Values file1: Character
1              1                a
2              2                b
3              3                c
4              4                d
5              5                e
6              6                f
7              7                g
8              8                h
9              9                i
10            10                j
user_77953960

I guess you are looking for : 

result <- Reduce(function(x, y) merge(x, y, by = `Occupation title`, all = TRUE), list.data)

which can be done using purrrs Reduce as well : 

result <- purrr::reduce(list.data, dplyr::full_join, by = `Occupation title`)
user_39629140

When you do full join it adds every combination and gives us the tables. if you are looking for unique records then you might want to use left join where keep dataframe/table on left whose all columns you want keep as reference and keep the file you want to join on right. Hope this helps.

user_80073730
Related