Merging excel sheets using pandas

user_15140736 0
Viewed 64

I have a quick script using python and pandas thats supposed to compare two excel sheets, grab the information that i need and create a new file. However when it creates the new file or if i just print it for testing one of the columns is coming back empty depending on where i merge (left of right)

    import pandas as pd

base_data = pd.read_excel("UpdatedList.xls") - #this sheet has Names and clock number
today_data = pd.read_excel("LocationUP.xlsx") - #this sheet has Names and where employees are working.

merge_data = base_data[["Names", "Clock Number" ]].merge(today_data[["Names", "Job"]], on="Names", how="right") #this line merges the two and creates a new files using the excel row "Names" as a merging key

# merge_data.to_excel("EmployeeLocationInner.xlsx", index=False)
print(merge_data.to_string())
pandas excel python
1 Answers

I haven't double checked your merge, but rather than sending your merged data to a string, you should use pd.to_excel()

something like:

merge_data.to_excel('merged_data.xlsx', sheet_name='merged')

If you need to save multiple sheets, looks the documentation - there are directions there. https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_excel.html

As far as merges, left or right will select what data you want to keep, so check your merge type to make sure you are keeping the data you want. I would advise trying each type of merge, and seeing what you want. It might be helpful to learn about different merge types. Here is a nice reference, or just google sql join types: https://www.w3schools.com/sql/sql_join.asp

For reference, pd.merge() https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.merge.html

user_126638870
Related