I am new to Python. I want to automate manual Excel tasks. Please help me.
I have one master xlsm file and another xlsx file with multiple sheets.
I am taking the values from multiple sheets in xlsx file and filling required cells in xlsm file.
Master xlsm file contains:
Item no, Item Name, Jan
10, aaa, data_to_be_filled
20, bbb, data_to_be_filled
30, ccc, data_to_be_filled
Source xlsx file:
Item no, Item Name, Amount
10, aaaa 1000
20, bbbb 2000
30, cccc 3000
I want the amount column data to be filled in corresponding cells in Master file 'Jan' column.
Edit, I tried the following:
pd.read_excel. df1 = pd.read_excel(master_file, sheet_name='Sheet1', header=52)
df2 = pd.read_excel(source_file, sheet_name='Sheet1', header=2)
Left_join = pd.merge(df1, df2, on ='Item no.', how ='left')
right_join = pd.merge(df1, df2, on = 'Item no.', how='right')
left_right_join = pd.merge(df1, df2, left_on = 'Item no.', right_on='Item no.', how='right')
inner_join = pd.merge(df1, df2, on = 'Item no.', how='inner')
outer_join = pd.merge(df1, df2, on='Item no.', how='outer')
Is this what you wanted?
df_final = pd.merge(df1, df2[["Item no", "Amount"]], on="Item no", how="left")
df_final["Jan"] = df_final["Amount"]
df_final.drop("Amount", axis=1, inplace=True)
Output:
Item no Item Name Jan
0 10 aaa 1000
1 20 bbb 2000
2 30 ccc 3000