CODEWITHSUNDEEP
pythonpandasmergeglob
Lumos
Lumos

Reputation: 1383

pandas merge all files in the same directory

I'm trying to pd.merge to merge all files in the same directory to one file. Here's an example of the inputs, they are excel files in the same directory:

df1:
A     B     C
a     1     dog
b     0     dog
c     2     cat

df2:
A     D     E
a     2     bird
b     4     lion
c     1     cat

df3:
A     F      G
a     1     seal
b     20    raccoon
c     2     squirrel

Here is the output I want:

A     B     C      D     E     F     G     
a     1     dog    2     bird  1     seal
b     0     dog    4     lion  20    raccoon
c     2     cat    1     cat   2     squirrel

My code:

  files = glob.glob('/Users/files/*.xlsx')
  all_data = pd.merge(files, how='left')

Error:

  TypeError: merge() missing 1 required positional argument: 'right'

Is merge only working for merging two files?

Thanks for your help!

Upvotes: 0

Views: 2128

Answers (1)

Zero
Zero

Reputation: 76927

Using pd.concat, with index set on A

In [20]: pd.concat(
            [pd.read_excel(f).set_index('A') for f in glob.glob('*.xlsx')],
            axis=1).reset_index()
Out[20]:
   A  B    C  D     E   F         G
0  a  1  dog  2  bird   1      seal
1  b  0  dog  4  lion  20   raccoon
2  c  2  cat  1   cat   2  squirrel

Upvotes: 1

Related Questions