0

I'am trying to consolidate many excel files into one Master excel file but my "many files" has different headers names. So I want to consolidate all those into one. (they have similar words in headers).

This is my code right now:

excel_files = glob.glob(location)

df1 = pd.DataFrame()

for excel_file in excel_files:
df2 = pd.read_excel(excel_file, header=None)
df1 = pd.concat([df1,df2])

df1.to_excel("...")

what I get:

Ticket ID Vendor ID Supplier ID Supplier original ID
1234 ABCD
5678 DCCD
9876 AADS

What I want:

Ticket ID Supplier ID
1234 ABCD
5678 DCCD
9876 AADS

Please help!

Marcelo Estrada
  • 5
  • 2
  • 1
    It's best to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example) to enable others to help you. And [don't provide datasets as an image](https://meta.stackoverflow.com/questions/285551/why-should-i-not-upload-images-of-code-data-errors-when-asking-a-question), check [How to make pandas examples](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) instead. Note to reduce the dataset to the necessary minimum. – MagnusO_O Oct 17 '22 at 15:04

1 Answers1

0

Dude go with pd.merge(...) and pd.concat()

Like described here:

Christoph Schwalbe
  • 81
  • 1
  • 4
  • I have re- formulated my question. I have already concatenate my files but the headers have different names so I want to get all that headers into one. – Marcelo Estrada Oct 17 '22 at 15:30
  • just go with pd.merge(...) You have to decide which kind of join you want. It purely depends on your data. I see only one excel file above. And you concat it with ? ... one empty Dataframe with another Dataframe. Sure you get want you ordered. – Christoph Schwalbe Oct 18 '22 at 17:32
  • one good solution change the header names in original data OR even better ... read original data and map the header to similiar dataset. just manipulate the dataframe header (please check the docs). So will be your data sources become same headers for same data. Than concat or try merge data. – Christoph Schwalbe Oct 18 '22 at 17:35