We have a few CSV files that have the same columns. We want to merge them together while retaining information about which file each row came from.
Let’s say the CSV files are file{1,2,3,4}.csv. This is file1.csv:
library(tidyverse)
read_csv("file1.csv")Red <dbl> | Blue <dbl> | Green <dbl> | Yellow <dbl> | |
|---|---|---|---|---|
| 1638.545 | 7987.766 | 7124 | 1.4354 | |
| 1687.404 | 8847.459 | 2203 | 1.2051 | |
| 1616.909 | 12550.755 | 5788 | 8.0743 | |
| 3628.540 | 4502.794 | 62606 | 4.2754 |
The other three files are similar.
This is the complete solution using tidyverse libraries:
library(tidyverse)
csv_names = paste0("file", 1:4, ".csv")
csv_names %>% map(read_csv) %>% set_names(csv_names) %>% bind_rows(.id = "file")file <chr> | Red <dbl> | Blue <dbl> | Green <dbl> | Yellow <dbl> |
|---|---|---|---|---|
| file1.csv | 1638.545 | 7987.766 | 7124 | 1.4354 |
| file1.csv | 1687.404 | 8847.459 | 2203 | 1.2051 |
| file1.csv | 1616.909 | 12550.755 | 5788 | 8.0743 |
| file1.csv | 3628.540 | 4502.794 | 62606 | 4.2754 |
| file2.csv | 1740.481 | 7855.782 | 7392 | 1.4672 |
| file2.csv | 1779.065 | 9546.185 | 2370 | 1.3121 |
| file2.csv | 1495.420 | 11824.846 | 5829 | 7.7501 |
| file2.csv | 3552.941 | 4461.808 | 62523 | 4.2801 |
| file3.csv | 1741.486 | 8141.417 | 6731 | 1.4672 |
| file3.csv | 1904.724 | 8872.139 | 2920 | 1.2273 |
The first step is to get all the file names in a list. If your filenames have a regular pattern, you can construct that easily with paste. paste0 is a special case of paste with the joining delimiter set to "".
csv_names = paste0("file", 1:4, ".csv")
csv_names[1] "file1.csv" "file2.csv" "file3.csv" "file4.csv"Now we want to import each of these files into a data frame (technically a tibble since we’re using tidyverse). Fortunately purr has a map function that works very similarly to map function in Javascript, which I therefore found much more intuitive to use than the more popular lapply variety of functions in R.
csv_dfs = csv_names %>% map(read_csv)
length(csv_dfs)[1] 4csv_dfs[[3]]Red <dbl> | Blue <dbl> | Green <dbl> | Yellow <dbl> | |
|---|---|---|---|---|
| 1741.486 | 8141.417 | 6731 | 1.4672 | |
| 1904.724 | 8872.139 | 2920 | 1.2273 | |
| 1494.952 | 12617.704 | 5801 | 8.0773 | |
| 3541.462 | 4369.841 | 62508 | 4.2818 |
The bind_rows function binds rows together, and has a handy .id argument that creates a new column holding data that links back the rows to the data frames they came from.
csv_dfs %>% bind_rows(.id="filename")filename <chr> | Red <dbl> | Blue <dbl> | Green <dbl> | Yellow <dbl> |
|---|---|---|---|---|
| 1 | 1638.545 | 7987.766 | 7124 | 1.4354 |
| 1 | 1687.404 | 8847.459 | 2203 | 1.2051 |
| 1 | 1616.909 | 12550.755 | 5788 | 8.0743 |
| 1 | 3628.540 | 4502.794 | 62606 | 4.2754 |
| 2 | 1740.481 | 7855.782 | 7392 | 1.4672 |
| 2 | 1779.065 | 9546.185 | 2370 | 1.3121 |
| 2 | 1495.420 | 11824.846 | 5829 | 7.7501 |
| 2 | 3552.941 | 4461.808 | 62523 | 4.2801 |
This is still not quite exactly what we want, because unless we name the elements in our list, bind_rows does not does not know what to put in the .id column, so it just uses a numeric sequence instead. lists in R are almost like dictionaries in python, but some elements only has a value and no key. Right now our list has only values and no keys. Setting the name for all of them can be done in one shot using set_names.
names(csv_dfs)
[1] "file1.csv" "file2.csv" "file3.csv" "file4.csv"Now bind_rows can use these names to set the values in the .id column:
csv_dfs %>% bind_rows(.id="filename")filename <chr> | Red <dbl> | Blue <dbl> | Green <dbl> | Yellow <dbl> |
|---|---|---|---|---|
| file1.csv | 1638.545 | 7987.766 | 7124 | 1.4354 |
| file1.csv | 1687.404 | 8847.459 | 2203 | 1.2051 |
| file1.csv | 1616.909 | 12550.755 | 5788 | 8.0743 |
| file1.csv | 3628.540 | 4502.794 | 62606 | 4.2754 |
| file2.csv | 1740.481 | 7855.782 | 7392 | 1.4672 |
| file2.csv | 1779.065 | 9546.185 | 2370 | 1.3121 |
| file2.csv | 1495.420 | 11824.846 | 5829 | 7.7501 |
| file2.csv | 3552.941 | 4461.808 | 62523 | 4.2801 |
| file3.csv | 1741.486 | 8141.417 | 6731 | 1.4672 |
| file3.csv | 1904.724 | 8872.139 | 2920 | 1.2273 |
We’re done!
You may want to modify each individual CSV before merging. For example, your original csv files may have 100s of columns but you only want to keep a few, and you don’t want to merge first and then select for efficiency. It is easy to do so by creating a wrapper function around read_csv:
csv_extract = function(csv_name) {
read_csv(csv_name) %>%
select(Red, Blue, Green) # Dropping Yellow because we're not interested.
}
csv_names = paste0("file", 1:4, ".csv")
csv_names %>% map(csv_extract) %>% set_names(csv_names) %>% bind_rows(.id = "file")file <chr> | Red <dbl> | Blue <dbl> | Green <dbl> | |
|---|---|---|---|---|
| file1.csv | 1638.545 | 7987.766 | 7124 | |
| file1.csv | 1687.404 | 8847.459 | 2203 | |
| file1.csv | 1616.909 | 12550.755 | 5788 | |
| file1.csv | 3628.540 | 4502.794 | 62606 | |
| file2.csv | 1740.481 | 7855.782 | 7392 | |
| file2.csv | 1779.065 | 9546.185 | 2370 | |
| file2.csv | 1495.420 | 11824.846 | 5829 | |
| file2.csv | 3552.941 | 4461.808 | 62523 | |
| file3.csv | 1741.486 | 8141.417 | 6731 | |
| file3.csv | 1904.724 | 8872.139 | 2920 |