Compare columns from different excel files and add a column at the beginning of each with the output
<p>I want to start this by saying that I'm not an Excel expert so I kindly need some help.</p>
<p>Let's assume that I have 3 excel files: <code>main.xlsx</code>, <code>1.xlsx</code> and <code>2.xlsx</code>. In all of them I have a column named <code>Serial Numbers</code>. I have to:</p>
<ul>
<li>lookup for all <em>serial numbers</em> in <code>1.xlsx</code> and <code>2.xlsx</code> and verify if they are in <code>main.xlsx</code>.</li>
</ul>
<p>If a serial number is find:</p>
<ul>
<li>on the last column of <code>main.xlsx</code>, on the same row with the <em>serial number</em> that was find write <code>OK + name_of_the_file_in which_it_was_found</code>. Else, write <code>NOK</code>. At the same time, write in <code>1.xlsx</code> and <code>2.xlsx</code> <code>ok</code> or <code>nok</code> on the last column if the <em>serial number</em> was found or not.</li>
</ul>
<p><em>Mention</em>: <code>serial number</code> can be on different columns on <code>1.xlsx</code> and <code>2.xlsx</code></p>
<h3>Example:</h3>
<p><strong>main.xlsx</strong></p>
<pre><code>name date serial number phone status
a      b      abcd        c         &lt;-- ok,2.xlsx
b      c      1234        d         &lt;-- ok,1.xlsx
c      d      3456        e         &lt;-- ok,1.xlsx
d      e      4567        f         &lt;-- NOK
e      f                  g         &lt;-- skip,don't write anything to status column
</code></pre>
<p><strong>1.xlsx</strong></p>
<pre><code>name date serial number phone status
a      b      1234        c          &lt;-- OK (because is find in main)
b      c      lala        d          &lt;-- NOK (because not find in main)
c      d      3456        e          &lt;-- OK (because find main)
d      e      jjjj        f          &lt;-- NOK (because not find in main)
e      f                  g          &lt;-- skip,don't write anything to status column
</code></pre>
<p><strong>2.xlsx</strong></p>
<pre><code>name date serial number phone status
a      b                  c          &lt;-- skip,don't write anything to status column
b      c      abcd        d          &lt;-- OK (because find main)
c      d      4533        e          &lt;-- NOK (because not find in main)
d      e      jjjj        f          &lt;-- NOK (because not find in main)
e      f                  g          &lt;-- skip,don't write anything to status column
</code></pre>
<p>Now, I tried doing this in Python, but apparently I couldn't figure how to write to the status column (tried using <code>dataFrames</code>), on the same line where the <code>serial number</code> is find. Any help would be much appreciated. (or at least some guidance)</p>
<p>My problem it's not finding the duplicates, but rather keeping track of the rows (to write the status on the correct <code>serial number</code>) and writing to the excel at the specified column (<code>status</code> column).</p>
<p><strong>My try:</strong></p>
<pre><code>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)
</code></pre>
<hr />
<p>Suggested edit:</p>
<pre><code>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()
</code></pre>