CODEWITHSUNDEEP
pythonpandasdataframemergeglob
Sanch
Sanch

Reputation: 407

Adding dataframe column names based on filename after merging using Glob

I have Excel files in a folder, all in the same format with data for all countries in the world in the sheet 'Dataset2' in each file.

I have merged all files together into one using glob, but I need to know which file (i.e. which country) each column comes from.

Is there a way to do this?

import glob
import os
import pandas as pd

os.chdir("Countries/")
extension = 'xlsx'

all_filenames = [i for i in glob.glob('*.{}'.format(extension))]

combined = pd.concat([pd.read_excel(f, sheet_name='Dataset2') for f in all_filenames ],axis=1, ignore_index=True)

combined.to_excel( "New/combined.xlsx", index=False, encoding='utf-8-sig')

Upvotes: 0

Views: 1724

Answers (2)

Umar.H
Umar.H

Reputation: 23099

if you're using os module try path.basename and adding this to the key argument in concat:

import glob import os import pandas as pd

os.chdir(r"C:\Users\Umar.Hussain\OneDrive - Ricoh Europe PLC\Documents\Excels")
extension = 'xlsx'

all_filenames = [i for i in glob.glob('*.{}'.format(extension))]
names = [os.path.basename(f) for f in all_filenames]

combined = pd.concat([pd.read_excel(f, sheet_name='Sheet1') for f in all_filenames],keys=names,axis=1 )

as your using axis=1 this will add the keys to the header, so may want to read the excels first and add it to a list like :

dfs = []
for file in all_filenames:
    df = pd.read_excel(file)
    df['source'] = os.path.basename(file)
    dfs.append(df)

Upvotes: 0

Dan
Dan

Reputation: 1587

You could unpack the list comprehension into a for-loop and add an additional column to each data file, something like this:

import glob
import os
import pandas as pd

os.chdir("Countries/")
extension = 'xlsx'

all_filenames = [i for i in glob.glob('*.{}'.format(extension))]

file_list = []
for f in all_filenames:
    data = pd.read_excel(f, sheet_name='Dataset2')
    data['source_file'] = f  # create a column with the name of the file
    file_list.append(data)

combined = pd.concat(file_list, axis=1, ignore_index=True)

combined.to_excel( "New/combined.xlsx", index=False, encoding='utf-8-sig')

Upvotes: 3

Related Questions