How to Build a Pandas DataFrame from Multiple Files
Tags: tutorial, python, beginners, datascience
Published: 2021-03-28 05:13:58

>In any Data Science project, the steps of **Importing Data** followed by **Data Cleaning** and **Exploratory Data Analysis(EDA)** are extremely important.

Let us say we have the required dataset in a CSV file, but the dataset is stored across multiple files, instead of a single file. We would ideally like to read in the data from multiple files into a single pandas DataFrame for use in subsequent steps. 

The most straightforward way to do it is to read in the data from each of those files into separate DataFrames and then concatenate them suitably into a single large DataFrame. This can be memory inefficient and involves writing redundant code. So, is there a better way to do it? Yes, there is. In this blog post, let us try to understand through simple examples.

Let us import pandas under its usual alias pd.
```python
import pandas as pd
```
****

## Building a DataFrame from multiple files (row-wise)
We shall use a sample dataset for our example; let us read the data from http://bit.ly/smallstocks into a DataFrame stocks using the `read_csv()` method of pandas.
```python
stocks = pd.read_csv('http://bit.ly/smallstocks', parse_dates=['Date']) 
print(stocks)
```
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/eq4yjz7px2i3k45crx4q.jpg)

Let us say, this data was stored in 3 separate CSV files, one for each day, named `stocks1.csv`, `stocks2.csv` and `stocks3.csv` as shown below.
```python
stocks1 = pd.read_csv('data/stocks1.csv')
stocks2 = pd.read_csv('data/stocks2.csv')
stocks3 = pd.read_csv('data/stocks3.csv')
```
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ismapameci2ci9z7rx0x.jpg)

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2u83laea3rgywl8i7l35.jpg)

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vwpuc65j5hdsohstlxx1.jpg)


As said earlier, a naive approach would be to read in each of these CSV files into separate DataFrames, as shown above, and then concatenate them, but this would become cumbersome as the number of such files increases. A better solution is to use the built-in `glob` module. Let us import `glob`.
```python
import glob
```
We can pass in a pattern to `glob()`, including wildcard characters, and it will return a list of all files that match that pattern.
```python
stock_files = sorted(glob('data/stocks*.csv'))
```
In this case, glob is looking in the “**data**” subdirectory for all CSV files that *start with* the word “**stocks**”. `glob` returns filenames in an arbitrary order, which is why we have sorted the list using Python’s built-in `sorted()` method.

Now that we’ve collected all the files over which our dataset is spread across, we can use a generator expression to read in each of the files using `read_csv()` and pass the results to the `concat()` function, which will concatenate the rows into a single DataFrame.
```python
pd.concat((pd.read_csv(file) for file in stock_files))
```
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qrj0zuaxctfaqx7kls7v.jpg)
Looks like we’ve successfully accomplished bringing in all data from the three files into a single DataFrame, but, there are duplicate values in the index. To avoid that, we can set the `ignore_index` argument to `True` to tell the `concat()` function to ignore the index and use the default integer index instead.
```python
pd.concat((pd.read_csv(file) for file in stock_files), ignore_index=True)
```
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ujfbyl5getdseryhs4a8.jpg)

> This method is useful when each file contains rows from our dataset. What if each file instead contains columns from our dataset?

****

## Building a DataFrame from multiple files (column-wise)
Here’s an example in which the drinks dataset has been split into two CSV files, and each of the files
```python
drinks = pd.read_csv('http://bit.ly/drinksbycountry')
```
```python
drinks1 = pd.read_csv('data/drinks1.csv').head()
drinks2 = pd.read_csv('data/drinks2.csv').head()
```
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/s2bbsc1d0kq3mb57m7bg.jpg)

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ozai9u9rqan603e2x4d1.jpg)

Similar to the procedure we followed earlier, we’ll start by using glob()
```python
drink_files = sorted(glob('data/drinks*.csv'))
```
And this time, we’ll tell the `concat()` function to concatenate along with the columns by specifying the axis argument as ‘columns’.
```python
pd.concat((pd.read_csv(file) for file in drink_files), axis='columns').head()
```
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ei4jog6d6ugrj30xno0t.jpg)

We see that we’ve obtained a single DataFrame with all six columns. We had considered simple examples to illustrate the use. In practice, where we have datasets chunked across multiple files, this could be a lot more helpful.

Thank you for reading!😊

## References
[1] https://docs.python.org/3/library/glob.html
[2] https://github.com/justmarkham/pandas-videos/blob/master/top_25_pandas_tricks.ipynb
[3] https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_csv.html

Cover image: Photo by <a href="https://unsplash.com/@markusspiske?utm_source=unsplash&utm_medium=referral&utm_content=creditCopyText">Markus Spiske</a> on <a href="/s/photos/build?utm_source=unsplash&utm_medium=referral&utm_content=creditCopyText">Unsplash</a>