{"id":38927,"date":"2022-12-29T13:15:41","date_gmt":"2022-12-29T13:15:41","guid":{"rendered":"https:\/\/www.askpython.com\/?p=38927"},"modified":"2023-05-12T04:17:12","modified_gmt":"2023-05-12T04:17:12","slug":"combine-csv-files-using-python","status":"publish","type":"post","link":"https:\/\/www.askpython.com\/python-modules\/pandas\/combine-csv-files-using-python","title":{"rendered":"How to combine CSV files using Python?"},"content":{"rendered":"\n

Often while working with CSV files, we need to deal with large datasets. Depending on the requirements of the data analysis, we may find that all the required data is not present in a single CSV file. Then the need arises to merge multiple files to get the desired data. However, copy-pasting the required columns from one file to another and that too from large datasets is not the best way to around it. <\/p>\n\n\n\n

To solve this problem, we will learn how to use the append<\/code>, merge<\/code> and concat<\/code> methods from Pandas to combine CSV files. <\/p>\n\n\n\n

\n\n\n\n

Combining Multiple CSV Files together<\/h2>\n\n\n\n

To begin with, let’s create sample CSV files that we will be using.<\/p>\n\n\n\n

\"Csv
CSV File 1<\/figcaption><\/figure>\n\n\n\n
\"Csv
CSV File 2<\/figcaption><\/figure>\n\n\n\n
\"Csv
CSV File 3<\/figcaption><\/figure>\n\n\n\n

Notice that, all three files have the same columns or headers i.e. ‘name’<\/strong>, ‘age’<\/strong> and ‘score’<\/strong>. Also, file 1 and file 3 have a common entry for the ‘name’<\/strong> column which is Sam<\/em>, but the rest of the values are different in these files.<\/p>\n\n\n\n

Note that, in the below examples we are considering that all the CSV files are in the same folder as your Python code file. If this is not the case for you, please specify the paths accordingly while trying out the examples by yourself.
All the examples were executed in a Jupyter notebook.<\/p>\n\n\n\n

\n\n\n\n

Different Ways to Combine CSV Files in Python<\/h2>\n\n\n\n

Before starting, we will be creating a list of the CSV files that will be used in the examples below as follows:<\/p>\n\n\n

\nimport glob\n\n# list all csv files only\ncsv_files = glob.glob('*.{}'.format('csv'))\ncsv_files\n<\/pre><\/div>\n\n\n
Output:<\/strong><\/p>\n\n\n
\n['csv_file_1.csv', 'csv_file_2.csv', 'csv_file_3.csv']\n<\/pre><\/div>\n\n\n
Method 1: append()<\/h3>\n\n\n\n
Let’s look at the append method<\/a> here to merge the three CSV files.<\/p>\n\n\n
\nimport pandas as pd\n\ndf_csv_append = pd.DataFrame()\n\n# append the CSV files\nfor file in csv_files:\n    df = pd.read_csv(file)\n    df_csv_append = df_csv_append.append(df, ignore_index=True)\n\ndf_csv_append\n<\/pre><\/div>\n\n\n
Output:<\/strong><\/p>\n\n\n\n
\"Output<\/figure>\n\n\n\n
The append<\/code> method, as the name suggests, appends each file’s data frame to the end of the previous one.  In the above code, we first create a data frame to store the result named df_csv_append<\/strong>. Then, we iterate through the list and read each CSV file and append it to the data frame df_csv_append<\/strong>. <\/p>\n\n\n\n
\n\n\n\n
Method 2: concat()<\/h3>\n\n\n\n
Another method used to combine CSV files is the Pandas concat()<\/code> method<\/a>. This method requires a series of objects as a parameter, hence we first create a series of the data frame objects of each CSV file and then apply the concat()<\/code> method to it.<\/p>\n\n\n
\nimport pandas as pd\n\ndf_csv_concat = pd.concat([pd.read_csv(file) for file in csv_files ], ignore_index=True)\ndf_csv_concat\n<\/pre><\/div>\n\n\n
An easier-to-understand way of writing this code is:<\/p>\n\n\n
\nl = []\n\nfor f in csv_files:\n    l.append(pd.read_csv(f))\n    \ndf_res = pd.concat(l, ignore_index=True)\ndf_res\n<\/pre><\/div>\n\n\n
Both the above codes when executed produce the same output as shown below.<\/p>\n\n\n\n
\"Output<\/figure>\n\n\n\n
Notice that the resulting data frame is the same as that of the append()<\/code> method.<\/p>\n\n\n\n
\n\n\n\n
Method 3: merge()<\/h3>\n\n\n\n
The merge<\/code> method is used to join very large data frames. A join can be performed on two data frames at a time. We can specify the key based on which the join is to be performed. <\/p>\n\n\n\n
It is a good practice to choose a key that is unique for each entry in the data frame, in order to avoid duplication of rows. We can also specify the type of join we wish to perform i.e. either of ‘inner’<\/em>, ‘outer’<\/em>, ‘left’<\/em>, ‘right’<\/em> or ‘cross’ <\/em>join.<\/p>\n\n\n\n
We need to first read each CSV file<\/a> into a separate data frame.<\/p>\n\n\n
\nimport pandas as pd\n\ndf1 = pd.read_csv('csv_file_1.csv')\ndf2 = pd.read_csv('csv_file_2.csv')\ndf3 = pd.read_csv('csv_file_3.csv')\n<\/pre><\/div>\n\n\n
\"Df1\"
df1<\/figcaption><\/figure>\n\n\n\n
\"Df2\"
df2<\/figcaption><\/figure>\n\n\n\n
\"Df3\"
df3<\/figcaption><\/figure>\n\n\n\n
Joining df1 and df2:<\/strong><\/p>\n\n\n
\ndf_merged = df1.merge(df2, how='outer')\ndf_merged\n<\/pre><\/div>\n\n\n
Output:<\/strong><\/p>\n\n\n\n
\"Merge
Merge Output 1<\/figcaption><\/figure>\n\n\n\n
Joining df1 and df3 based on the key ‘name’.<\/p>\n\n\n
\ndf_merged = df1.merge(df3, on="name", how='outer')\ndf_merged\n<\/pre><\/div>\n\n\n
Output:<\/strong><\/p>\n\n\n\n
\"Merge
Merge Output 2<\/figcaption><\/figure>\n\n\n\n
df1 and df3, both have an entry for the name ‘Sam’ and the age and score values for both of them are different. Hence, in the resulting data frame, there are columns for representing the entries from both df1 and df3.  Since John <\/em>and Bob<\/em> are not common in the data frames df1 and df3, their values are NaN wherever applicable.<\/p>\n\n\n\n
\n\n\n\n
Conclusion<\/h2>\n\n\n\n
In this article, we learned about the Pandas methods namely concat<\/strong>, merge <\/strong>and append <\/strong>and how to use them to combine CSV files using Python.<\/p>\n\n\n\n
\n\n\n\n
References<\/h2>\n\n\n\n