python excel数据框_使用python大熊猫添加新的数据框的现有excel表

I currently have this code. It works perfectly.

It loops through excel files in a folder,

removes the first 2 rows,

Then saves them out as individual excel files,

and it also saves the the files in the loop as an appended file.

Currently the appended file overwrites the existing file each time I run the code.

I need to append the new data to the bottom of the already existing excel sheet ('master_data.xlsx)

dfList = []

path = 'C:\\Test\\TestRawFile'

newpath = 'C:\\Path\\To\\New\\Folder'

for fn in os.listdir(path):

# Absolute file path

file = os.path.join(path, fn)

if os.path.isfile(file):

# Import the excel file and call it xlsx_file

xlsx_file = pd.ExcelFile(file)

# View the excel files sheet names

xlsx_file.sheet_names

# Load the xlsx files Data sheet as a dataframe

df = xlsx_file.parse('Sheet1',header= None)

df_NoHeader = df[2:]

data = df_NoHeader

# Save individual dataframe

data.to_excel(os.path.join(newpath, fn))

dfList.append(data)

appended_data = pd.concat(dfList)

appended_data.to_excel(os.path.join(newpath, 'master_data.xlsx'))

I thought this would be a simple task, but I guess not.

I think I need to bring in the master_data.xlsx file as a dataframe, then match the index up with the new appended data, and save it back out. Or maybe there is an easier way. Any Help is appreciated.

解决方案

You can use openpyxl engine in conjunction with startrow parameter:

In [48]: writer = pd.ExcelWriter('c:/temp/test.xlsx', engine='openpyxl')

In [49]: df.to_excel(writer, index=False)

In [50]: df.to_excel(writer, startrow=len(df)+2, index=False)

In [51]: writer.save()

c:/temp/test.xlsx:

PS you may also want to specify header=None if you don't want to duplicate column names...