Compare columns from different excel files and add a column at the beginning of each with the output
Question: I want to start this by saying that I'm not an Excel expert so I kindly need
some help.

Let's assume that I have 3 excel files: `main.xlsx`, `1.xlsx` and `2.xlsx`. In
all of them I have a column named `Serial Numbers`. I have to:

  * lookup for all _serial numbers_ in `1.xlsx` and `2.xlsx` and verify if they are in `main.xlsx`.

If a serial number is find:

  * on the last column of `main.xlsx`, on the same row with the _serial number_ that was find write `OK + name_of_the_file_in which_it_was_found`. Else, write `NOK`. At the same time, write in `1.xlsx` and `2.xlsx` `ok` or `nok` on the last column if the _serial number_ was found or not.

_Mention_ : `serial number` can be on different columns on `1.xlsx` and
`2.xlsx`

### Example:

**main.xlsx**

    
    
    name date serial number phone status
    a      b      abcd        c         <-- ok,2.xlsx
    b      c      1234        d         <-- ok,1.xlsx
    c      d      3456        e         <-- ok,1.xlsx
    d      e      4567        f         <-- NOK
    e      f                  g         <-- skip,don't write anything to status column
    

**1.xlsx**

    
    
    name date serial number phone status
    a      b      1234        c          <-- OK (because is find in main)
    b      c      lala        d          <-- NOK (because not find in main)
    c      d      3456        e          <-- OK (because find main)
    d      e      jjjj        f          <-- NOK (because not find in main)
    e      f                  g          <-- skip,don't write anything to status column
    

**2.xlsx**

    
    
    name date serial number phone status
    a      b                  c          <-- skip,don't write anything to status column
    b      c      abcd        d          <-- OK (because find main)
    c      d      4533        e          <-- NOK (because not find in main)
    d      e      jjjj        f          <-- NOK (because not find in main)
    e      f                  g          <-- skip,don't write anything to status column
    

Now, I tried doing this in Python, but apparently I couldn't figure how to
write to the status column (tried using `dataFrames`), on the same line where
the `serial number` is find. Any help would be much appreciated. (or at least
some guidance)

My problem it's not finding the duplicates, but rather keeping track of the
rows (to write the status on the correct `serial number`) and writing to the
excel at the specified column (`status` column).

**My try:**

    
    
    import pandas as pd
    
    get_main = pd.ExcelFile('main.xlsx')
    get_1 = pd.ExcelFile('1.xlsx')
    get_2 = pd.ExcelFile('2.xlsx')
    
    sheet1_from_main = get_main.parse(0)
    sheet1_from_1 = get_1.parse(0)
    sheet1_from_2 = get_2.parse(0)
    
    
    column_from_main = sheet1_from_main.iloc[:, 2].real
    column_from_main_py = []
    for x in column_from_main:
        column_from_main_py.append(x)
    
    
    column_from_1 = sheet1_from_1.iloc[:, 2].real
    column_from_1_py = []
    for y in column_from_1:
        column_from_1_py.append(y)
    
    
    column_from_2 = sheet1_from_2.iloc[:, 2].real
    column_2_py = []
    for z in column_from_2:
        column_2_py.append(z)
    

* * *

Suggested edit:

    
    
    import pandas as pd
    
    get_main = pd.read_excel('main.xls', sheetname=0)
    get_1 = pd.read_excel('1.xls', sheetname=0)
    get_2 = pd.read_excel('2.xls', sheetname=0)
    
    
    column_from_main = get_main.ix[:, 'Serial No.'].real
    column_from_main_py = column_from_main.tolist()
    
    
    column_from_1 = get_1.ix[:, 'SERIAL NUMBER'].real
    column_from_1_py = column_from_1.tolist()
    
    
    column_from_2 = get_2.ix[:, 'S/N'].real
    column_from_2_py = column_from_2.tolist()
    
    # Tried to put example data at specific column
    
    df = pd.DataFrame({'Data': [10, 20, 30, 20, 15, 30, 45]})
    writer = pd.ExcelWriter('first.xlsx', engine='xlsxwriter')
    df.to_excel(writer, sheet_name='Sheet1')
    workbook = writer.book
    worksheet = writer.sheets['Sheet1']
    worksheet.set_column('M:M', None, None)
    writer.save()
    


Answer: First off you can skip using excelfile and parse by using
`pd.read_excel(filename, sheetname=0)`.

As far as you columns go, try accessing the columns by name, not by index. And
instead of using a for loop to create a list, use the tolist method. So
instead of `column_from_main = sheet1_from_main.iloc[:, 2].real` you could
say:

    
    
    column_from_main = get_main.ix[:, 'serial number'].real
    column_from_main_py = column_from_main.tolist()
    

Do the same for your other files as well. This will remove any issues with the
serial number column being indexed differently and will operate faster.

As to your comment about not being able to write to 'Status' properly, can you
show your code that you tried? I'd be more than happy to help, but it's nice
to see what you've done to this point.

For checking the values in main against the other two files you will want to
iterate over the lists you created and check if each value in the main list is
in either of the other lists. Within that loop you can then assign the value
of status based on whether the serial number in main is present in one, none,
or both:

    
    
    get_main['status'] = ''
    get_1['status'] = ''
    get_2['status'] = ''
    for num in column_from_main_py:
        if num not in column_from_1_py and not in column_from_2_py:
            get_main.loc[get_main['serial number'] == num, 'status'] = 'NOK'
        elif num in column_from_1_py and not in column_from_2_py:
            get_main.loc[get_main['serial number'] == num, 'status'] = 'OK,1.xlsx'
            get_1.loc[get_1['serial number'] == num, 'status'] = 'OK'
        elif num not in column_from_1_py and in column_from_2_py:
            get_main.loc[get_main['serial number'] == num, 'status'] = 'OK,2.xlsx'
            get_2.loc[get_2['serial number'] == num, 'status'] = 'OK'
    

The lines get_main.loc are where you are setting the OK or NOK value to the
status column. essentially it finds the index where some condition is true and
then lets you change the value of a specific column at that index. Once you
have gone through the main list then you can look through the lists for 1 and
2 to find serial numbers that aren't in main. Similarly:

    
    
    for num in column_from_1_py:
        if num not in column_from_main_py:
            get_1.loc[get_1['serial number'] == num, 'status'] = 'NOK'
    for num in column_from_2_py:
        if num not in column_from_main_py:
            get_2.loc[get_2['serial number'] == num, 'status'] = 'NOK'
    

That will set you NOK values and you should be good to go ahead and export the
dataframes to excel (or csv, hdf, sql, etc...) and that should do it.

There are lots of ways you can index and select data in pandas depending on
what you want to do. I recommend reading the [Indexing and Selecting
Data](http://pandas.pydata.org/pandas-
docs/version/0.17.0/indexing.html#indexing-and-selecting-data) page in the
docs as it has been a great reference for me.

