add data from different excel files using python

Question

I had a code that work perfectly for 2 files. But now, I want to add several excel files but it's not working.

Here is my code :

import pandas as pd

a = pd.read_excel('alex.xlsx', index_col=None, header=None)
b = pd.read_excel('blago.xlsx', index_col=None, header=None)
c = pd.read_excel('claude.xlsx', index_col=None, header=None)
d = pd.read_excel('cristian.xlsx', index_col=None, header=None)
e = pd.read_excel('eric.xlsx', index_col=None, header=None)
f = pd.read_excel('haou.xlsx', index_col=None, header=None)
g = pd.read_excel('iulian.xlsx', index_col=None, header=None)
h = pd.read_excel('luca.xlsx', index_col=None, header=None)
i = pd.read_excel('maricel.xlsx', index_col=None, header=None)
j = pd.read_excel('michel.xlsx', index_col=None, header=None)
k = pd.read_excel('noel.xlsx', index_col=None, header=None)
l = pd.read_excel('paul.xlsx', index_col=None, header=None)




z = a.add(b, c, d, e, f, g, h, i, j, k, l, fill_value=0)

z.to_excel("z.xlsx")

try pd.concat. It can combine a list of dataframes.

l_l_l_l_l_l_l_l
– l_l_l_l_l_l_l_l

2020-04-03 20:38:48 +00:00
Commented Apr 3, 2020 at 20:38 — l_l_l_l_l_l_l_l
– l_l_l_l_l_l_l_l, Commented Apr 3, 2020 at 20:38

megamind · Accepted Answer · 2020-04-03 22:35:02Z

1

The add function doesn't join two dataframes, it adds to the values in a dataframe. I suggest using concat instead and also using the glob module to avoid a separate call for each individual file like so:

import pandas as pd
import glob

a = pd.concat([pd.read_excel(file, index_col=None, header=None) for file in glob.glob('*.xlsx')], ignore_index = True)
a.fillna(0, inplace = True)
a.to_excel("a.xlsx")

answered Apr 3, 2020 at 22:35

megamind

755 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

add data from different excel files using python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related