Merge every dataframe in a folder

Question

I have .csv files within multiple folders which look like this:

File1

Count    2002_Crop_1   2002_Crop_2   Ecoregion
 20      Corn          Soy           46
 15      Barley        Oats          46

File 2

Count   2003_Crop_1    2003_Crop_2  Ecoregion
24      Corn           Soy          46
18      Barley         Oats         46

for each folder I want to merge all of the files within.

My desired output would be something like this:

Crop_1  Crop_2 2002_Count  2003_Count  Ecoregion
Corn    Soy    20          24          46
Barley  Oats   15          18          46

In reality there are 10 files in each folder, not just 2, that need to be merged.

I am using this code as of now:

import pandas as pd, os
#pathway to all the folders
folders=r'G:\Stefano\CDL_Trajectory\combined_eco_folders'
for folder in os.listdir(folders):
    for f in os.listdir(os.path.join(folders,folder)):   
            dfs=pd.read_csv(os.path.join(folders,folder,f))   #turn each file from each folder into a dataframe
            df = reduce(lambda left,right: pd.merge(left,right,on=[dfs[dfs.columns[1]], dfs[dfs.columns[2]]],how='outer'),dfs) #merge all the dataframes based on column location

but this returns: TypeError: string indices must be integers, not Series

score 2 · Accepted Answer · edited May 23 '17 at 10:28

Use glob.glob to traverse a directory at a fixed depth.
Try to avoid calling pd.merge repeatedly if you can help it. Each call to pd.merge creates a new DataFrame. So all the data in each intermediate result has to be copied into the new DataFrame. Doing this in a loop leads to quadratic copying, which is bad for performance.
If you do some column name wrangling to change, for instance,
```
['Count', '2002_Crop_1', '2002_Crop_2', 'Ecoregion']
```
to
```
['2002_Count', 'Crop_1', 'Crop_2', 'Ecoregion']
```
then you can use ['Crop_1', 'Crop_2', 'Ecoregion'] as the index for each DataFrame, and combine all the DataFrames with one call to pd.concat.

import pandas as pd
import glob

folders=r'G:\Stefano\CDL_Trajectory\combined_eco_folders'
dfs = []

for filename in glob.glob(os.path.join(folders, *['*']*2)):
    df = pd.read_csv(filename, sep='\s+')
    columns = [col.split('_', 1) for col in df.columns]

    prefix = next(col[0] for col in columns if len(col) > 1)
    columns = [col[1] if len(col) > 1 else col[0] for col in columns]
    df.columns = columns

    df = df.set_index([col for col in df.columns if col != 'Count'])
    df = df.rename(columns={'Count':'{}_Count'.format(prefix)})

    dfs.append(df)

result = pd.concat(dfs, axis=1)
result = result.sortlevel(axis=1)
result = result.reset_index()
print(result)

yields

   Crop_1 Crop_2  Ecoregion  2002_Count  2003_Count
0    Corn    Soy         46          20          24
1  Barley   Oats         46          15          18

Merge every dataframe in a folder

1 Answers1