how can i combine multiple excel files with different headers into one sheet using pandas?

To get the headers to write to your xlsx file you can change header=True when you all df.to_excel().

The modified code is:

import pandas as pd
import os
import tkinter as tk
from tkinter import filedialog

root = tk.Tk()
root.withdraw()

in_path = filedialog.askdirectory()
listing = os.listdir(in_path)

files_xlsx = [f for f in listing if f[-4:] == 'xlsx']

df = pd.DataFrame()

for infile in listing:
    file_data = pd.read_excel(in_path + '/' + infile,
                              header=0,
                              encoding = "ANSI")
    df = df.append(file_data, sort=False)


out_path = in_path + ' Combined.xlsx'

writer = pd.ExcelWriter(out_path, engine='xlsxwriter')

df.to_excel(writer,
            sheet_name='Combined',
            index=False,
            header=True)

writer.save()

and gives me an output like this with some test data

table

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top