Зачем это нужно?
Копипаст вручную — это, может, и не самый плохой вариант для небольшого количества файлов.
Но представьте, что вам нужно объединить 100+ файлов… готовы ли вы сделать все вручную? Эта затея весьма утомительна и чревата ошибками.
Для файлов с одинаковой структурой таблицы (те же заголовки и количество столбцов) можно воспользоваться скриптом на Python.
Шаг 1: Импортируйте пакеты и задайте рабочую директорию
Измените “/mydir” на нужную вам директорию.
Шаг 2: Воспользуйтесь glob для сопоставления с шаблоном ‘csv’
Сопоставьте шаблон (‘csv’) и сохраните список имен файлов в переменной ‘all_filenames’. Здесь можно почитать о сопоставлении регулярных выражений.
How to merge multiple CSV files with Python
In this guide, I'll show you several ways to merge/combine multiple CSV files into a single one by using Python (it'll work as well for text and other files). There will be bonus — how to merge multiple CSV files with one liner for Linux and Windows. Finally with a few lines of code you will be able to combine hundreds of files with full control of loaded data — you can convert all the CSV files into a Pandas DataFrame and then mark each row from which CSV file is coming.
- data_201901.csv
- data_201902.csv
- data_201903.csv
Steps to merge multiple CSV(identical) files with Python
Note: that we assume — all files have the same number of columns and identical information inside
Short code example — concatenating all CSV files in Downloads folder:
Step 1: Import modules and set the working directory
First we will start with loading the required modules for the program and selecting working folder:
Step 2: Match CSV files by pattern
Next step is to collect all files needed to be combined. This will be done by:
The next code: data_*.csv match only files:
- starting with data_
- with file extension .csv
You can customize the selection for your needs having in mind that regex matching is used.
Step 3: Combine all files in the list and export as CSV
The final step is to load all selected files into a single DataFrame and converted it back to csv if needed:
Note that you may change the separator by: sep=',' or change the headers and rows which to be loaded
You can find more about converting DataFrame to CSV file here: pandas.DataFrame.to_csv
Full Code
Below you can find the full code which can be used for merging multiple CSV files.
Steps to merge multiple CSV(identical) files with Python with trace
Now let's say that you want to merge multiple CSV files into a single DataFrame but also to have a column which represents from which file the row is coming. Something like:
| row | col | col2 | file |
|---|---|---|---|
| 1 | A | B | data_201901.csv |
| 2 | C | D | data_201902.csv |
This can be achieved very easy by small change of the code above:
In this example we iterate over all selected files, then we extract the files names and create a column which contains this name.
Combine multiple CSV files when the columns are different
Sometimes the CSV files will differ for some columns or they might be the same only in the wrong order to be wrong. In this example you can find how to combine CSV files without identical structure:
Pandas will align the data by this method: pd.concat . In case of a missing column the rows for a given CSV file will contain NaN values:
| row | col | col2 | col_201901 | file |
|---|---|---|---|---|
| 1 | A | B | AA | data_201901.csv |
| 2 | C | D | NaN | data_201902.csv |
If you need to compare two csv files for differences with Python and Pandas you can check: Python Pandas Compare Two CSV files based on a Column
More about pandas concat: pandas.concat
Bonus: Merge multiple files with Windows/Linux
Linux
Sometimes it's enough to use the tools coming natively from your OS or in case of huge files. Using python to concatenate multiple huge files might be challenging. In this case for Linux it can be used:
In this case we are working in the current folder by matching all files starting with data_ . This is important because if you try to execute something like:
You will try to merge the newly output file as well which may cause issues. Another important note is that this will skip the first lines or headers of each file. In order to include headers you can do:
If the commands above are not working for you then you can try with the next two. The first one will merge all csv files but have problems if the files ends without new line:
The second one will merge the files and will add new line at the end of them:
How To Combine Multiple CSV Files In Python
As this course is being progressively released, whenever a new article and video is released, after initially git cloning the repository. You will need to run this command within your command line / terminal (from the root directory of the course):
This will pull any recent changes that have been made on the github.com version of the course and will allow you to easily get fresh content as it is added.
Learning Outcomes
- To learn what the pd.concat() method is and how it works
- Learn how to combine multiple csv files using Pandas
Firstly let’s say that we have 5, 10 or 100 .csv files. Combining all of these by hand can be incredibly tiring and definitely deserves to be automated. Therefore in today’s exercise, we’ll combine multiple csv files within only 8 lines of code.
For this tutorial, I’ve already prepared 5 top pages .csv reports from Ahrefs which can be found in the following directory:
One of the problems with automatically detecting csv files is that the names are dynamically generated. Therefore we will be using the .csv file extension name and a python package called glob to automatically detect all of the files ending with a .csv name within a specific working directory.
Import packages and set the working directory
You will need to change “/directory” to your specific directory.
By writing pwd within the command line, we can identify the exact file path that these Ahrefs top page .csv files are located in:
Let’s now move into our desired working directory where the csv files are:
Now let’s running !ls and !pwd just to show that we have changed directory:
Pro-tip: using ! before a linux command allows you to run the unix/linux commands within a jupyter notebook file!
Step 2: Use Global To Match The Pattern ‘.csv’
We will now match the file pattern (‘.csv’) within all of the files located in the current working directory.
Step 3: Let’s Combine All Of The Files Within The List And Export as a CSV
In the code below we will read all of the csv’s and will then use the pd.concat() method to stack every dataframe one on top of another.
But before we do that, let’s make sure that we can get one result within a pandas dataframe by adding the appropriate encoding:
- UTF-16 (This is a specific encoding type).
- \t (tab delimited data).
Now let’s break down what the above line of code does, firstly we loop over all of the filenames and assign them one by one to the f variable. Each csv file is then read & converted into a pandas dataframe with:
Then we concatenate all of the dataframes together and stack them one on top of each other using:
That’s it, within 8 lines of code you’re now able to easily combine as many .csv files as you want!
Как объединить несколько CSV файлов в один?
Есть несколько CSV файлов, которые я хочу объединить в один.
Изначально воспользовался ответом, который предлагает следующее:
Проблема оказалась в том, что при объединении не добавляется перенос строки после очередного файла (т.е. первая строка второго файла оказывается совмещена с последней строкой первого файла). Кроме того, каждый CSV файл имеет заголовок (одинаковый для всех), который хорошо бы убрать у всех фалов, кроме первого.