Search code examples
pythonxlrd

using python and xlrd to combine/merge 2 different spreadsheets

I have 2 separate excel spreadsheets spreadsheet 1 is as such:

ID   tin   name   date
1    21043 Bob   8/1/2019
2    45667 Jim   7/1/2018
3    69780 Sal   4/24/2017

The 2nd spreadsheet is as such:

ID   tin   job
1    21043 02
2    76544 02
3    45667 04

I am trying to figure out how to match the 2 spreadsheets and make 1 list as such:

ID    tin    name    date    job
1     21043  Bob    8/1/2019  02
2     45667  Jim    7/1/2018  04
3     69780  Sal    4/24/2017
4     76544                   02

the common denominator is the "tin" but i have to merge the ones that duplicate, but then add the ones from both sheets that dont duplicate..

I am new to python and VERY new to xlrd so i cannot seem to even figure out the best terms to use to google an example.

I found some information on a next(iter statement but after countless attempts i could not figure out a useful way to use it to combine.

Is there an easy way or am i "up a creek"??

Thank you,

Bob

Solution

  • You can use pandas for this. Pandas uses xlrd and other excel readers under the hood.

    You will do something like this:

    df1 = pandas.read_excel('file1.xls', sheet_name='...')
df2 = pandas.read_excel('file2.xls', sheet_name='...')
df1.merge(df2, how='outer')

    You may need some variation of this depending on your column names.. see pandas merge