CODEWITHSUNDEEP
pythonpandas
Dmitry
Dmitry

Reputation: 1

Python Pandas join a few files

I import a few xlsx files into pandas dataframe. It works fine, but my problem that it copies all the data under each other (so I have 10 excel file with 100 lines = 1000 lines).

I need the Dataframe with 100 lines and 10 columns, so each file will be copied next to each other and not below.

Are there any ideas how to do it?

import os
import pandas as pd

os.chdir('C:/Users/folder/')

path = ('C:/Users/folder/')
files = os.listdir(path)

allNames = pd.DataFrame()

for f in files:
    info = pd.read_excel(f,'Sheet1')
    allNames = allNames.append(info)

writer = pd.ExcelWriter ('Output.xlsx')
allNames.to_excel(writer, 'Copy')
writer.save()

Upvotes: 0

Views: 39

Answers (2)

mac13k
mac13k

Reputation: 2663

You can feed your spreadsheets as an array of dataframes directly to pd.concat():

import os
import pandas as pd

os.chdir('C:/Users/folder/')

path = ('C:/Users/folder/')
files = os.listdir(path)

allNames = pd.concat([pd.read_excel(f,'Sheet1') for f in files], axis=1)

writer = pd.ExcelWriter ('Output.xlsx')
allNames.to_excel(writer, 'Copy')
writer.save()

Upvotes: 1

yoskovia
yoskovia

Reputation: 360

Instead of stacking the tables vertically like this:

allNames = allNames.append(info)

You'll want to concatenate them horizontally like this:

allNames = pd.concat([allNames , info], axis=1)

Upvotes: 0

Related Questions