how to use Python to copy data from one excel and print into corresponding row in another excel

Question

I currently have two Excel spreadsheet for example:

Sheet 1:

| Nid | Value | 
|  1  |   15  |
|  2  |   27  |
|  3  |   80  | 
|  4  |   12  |
|  5  |   16  |
|  6  |   25  |
|  7  |   2   |

Sheet 2:

| Nid |
|  1  | 
|  2  |
|  3  | 
|  5  |
|  7  |

Sheet 1 has Nid and Value, but sheet 2 only has Nid and two reports are not the same lenght.

I want to copy the 'Value' data from Sheet 1 to corresponding rows in sheet 2. The result should look like:

| Nid | Value | 
|  1  |   15  |
|  2  |   27  |
|  3  |   80  | 
|  5  |   16  |
|  7  |   2   |

I'm open to any libraries but prefer pandas or openpyxl.

Thanks in advance!

Answer 1

I think you can simply use pandas.DataFrame.merge:

import pandas as pd
df1 = pd.read_excel(r'C:\Users\WhatEver\Map1.xlsx', sheet_name='Sheet1').set_index('Nid')
df2 = pd.read_excel(r'C:\Users\WhatEver\Map1.xlsx', sheet_name='Sheet2').set_index('Nid')
df2 = df1.merge(df2, on='Nid')
with pd.ExcelWriter(r'C:\Users\WhatEver\Map1.xlsx') as writer:
    df1.to_excel(writer, sheet_name='Sheet1')
    df2.to_excel(writer, sheet_name='Sheet2')

Answer 2

Something like this will work -

df1 = pd.read_excel('Sheet1.xlsx')
df2 = pd.read_excel('Sheet2.xlsx')
merged_df = pd.merge(df2, df1, on='Nid', how='left')
merged_df.to_excel('output_sheet.xlsx')