How to copy data from one excel file to second excel file using conditions?

Question

I have two excel files are one.xlxs and two.xlxs. Column names are id, mail, name, gender, age, name same on two excel files but they are jumbled in two.xlxs. Two rows(id and mail) have data on both files. I want to copy the data from one.xlxs to two.xlxs. But the column lineup should not be disturb on two.xlxs. The data will be copied based on Two rows(id and mail). for example: the data should be copied into the respective columns if id and mail matches on two files. Please find the reference pictures are one.xlxs, two.xlxs and result_two.xlxs(As required result). I have searched on internet but i did not get any idea.

I am able to copy data from one.xlxs to two.xlxs using below code. But i don not want to disturb two.xlxs column positions. I want to copy data as shown in the image of two_result.xlxs, If id and mail values matches on both excel files that cell values will be placed on two.xlxs their matched columns. How to do.

import pandas as pd

df1 = pd.read_excel('one.xlsx')
df2 = pd.read_excel('two.xlsx')
df = pd.concat([df1])
df.to_excel('two.xlsx', index=False)

Code Different · Accepted Answer · 2021-09-05 14:23:02Z

1

You can merge them then rearrange the columns:

cols = df2.columns
result = df2[['id', 'mail']].merge(df1, how='left', on=['id', 'mail']).reindex(columns=cols)

result.to_excel('result.xlsx', index=False)

edited Sep 5, 2021 at 14:23

answered Sep 5, 2021 at 13:13

Code Different

93.4k16 gold badges154 silver badges175 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user3030327 Over a year ago

Its working very well. I am agreeing and accepting your answer. But i forgot one thing to add in the question that is, if any extra columns are in two.xlsx file. Its showing raise KeyError(f"{not_found} not in index") KeyError: "['non'] not in index" error 'non' is a extra column. Before this question closing, Can you please rectify this issue also some times some extra columns also will be there.

Code Different Over a year ago

You can use reindex. See my edited answer

user3030327 Over a year ago

But the data will be empty on extra columns After reindex..

Collectives™ on Stack Overflow

How to copy data from one excel file to second excel file using conditions?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related