If values from two columns in two different data frames match then copy values from another column in df2 to...
up vote
0
down vote
favorite
I have two data frames, and I want to copy values from the column "Result2" in df2 to column "Result1" in df1 and to add the column "Description" from df2 to df1 for values that are in the same row as values from the column "Result2" (that are already copied), if the values from columns "ID" in both data frames match.
Here are the data frames:
df1
ID Result1
Position1 NaN
MRS1 OK
MRS2 Maybe
MRS3 NOK
Position2 NaN
MRS4 OK
MRS5 OK
Position3 NaN
MRS6 OK
Position4 NaN
MRS7 NOK
MRS8 OK
MRS9 NOK
MRS10 Excellent
df2
ID Result2 Description
text NaN NaN
NaN NaN text
text NaN NaN
MRS1 OK New
MRS2 OK Old
NaN NaN NaN
NaN NaN text
MRS3 OK New
MRS4 OK New
MRS5 OK NaN
MRS6 NOK Very old
NaN text NaN
text text NaN
NaN NaN NaN
MRS7 Excellent Very old
MRS8 Excellent Old
text text text
MRS9 Excellent New
NaN NaN NaN
MRS10 NOK New
I want to get:
ID Result1 Result2
Position1 NaN NaN
MRS1 OK New
MRS2 OK Old
MRS3 OK New
Position2 NaN NaN
MRS4 OK New
MRS5 OK NaN
Position3 NaN NaN
MRS6 NOK Very old
Position4 NaN NaN
MRS7 Excellent Very old
MRS8 Excellent Old
MRS9 Excellent New
MRS10 NOK New
and I put NaN on the places where should be an empty cell. How to do this in pandas?
I tried with the merge, but I have the problem because it is required the same length of comparing columns. I just want to compare values from the "ID" columns from both data frames, and those columns do not have the same length.
python pandas dataframe matching
asked Nov 20 at 7:00
slobokv83
427
add a comment |
up vote
0
down vote
favorite
I have two data frames, and I want to copy values from the column "Result2" in df2 to column "Result1" in df1 and to add the column "Description" from df2 to df1 for values that are in the same row as values from the column "Result2" (that are already copied), if the values from columns "ID" in both data frames match.
Here are the data frames:
df1
ID Result1
Position1 NaN
MRS1 OK
MRS2 Maybe
MRS3 NOK
Position2 NaN
MRS4 OK
MRS5 OK
Position3 NaN
MRS6 OK
Position4 NaN
MRS7 NOK
MRS8 OK
MRS9 NOK
MRS10 Excellent
df2
ID Result2 Description
text NaN NaN
NaN NaN text
text NaN NaN
MRS1 OK New
MRS2 OK Old
NaN NaN NaN
NaN NaN text
MRS3 OK New
MRS4 OK New
MRS5 OK NaN
MRS6 NOK Very old
NaN text NaN
text text NaN
NaN NaN NaN
MRS7 Excellent Very old
MRS8 Excellent Old
text text text
MRS9 Excellent New
NaN NaN NaN
MRS10 NOK New
I want to get:
ID Result1 Result2
Position1 NaN NaN
MRS1 OK New
MRS2 OK Old
MRS3 OK New
Position2 NaN NaN
MRS4 OK New
MRS5 OK NaN
Position3 NaN NaN
MRS6 NOK Very old
Position4 NaN NaN
MRS7 Excellent Very old
MRS8 Excellent Old
MRS9 Excellent New
MRS10 NOK New
and I put NaN on the places where should be an empty cell. How to do this in pandas?
I tried with the merge, but I have the problem because it is required the same length of comparing columns. I just want to compare values from the "ID" columns from both data frames, and those columns do not have the same length.
python pandas dataframe matching
asked Nov 20 at 7:00
slobokv83
427
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I have two data frames, and I want to copy values from the column "Result2" in df2 to column "Result1" in df1 and to add the column "Description" from df2 to df1 for values that are in the same row as values from the column "Result2" (that are already copied), if the values from columns "ID" in both data frames match.
Here are the data frames:
df1
ID Result1
Position1 NaN
MRS1 OK
MRS2 Maybe
MRS3 NOK
Position2 NaN
MRS4 OK
MRS5 OK
Position3 NaN
MRS6 OK
Position4 NaN
MRS7 NOK
MRS8 OK
MRS9 NOK
MRS10 Excellent
df2
ID Result2 Description
text NaN NaN
NaN NaN text
text NaN NaN
MRS1 OK New
MRS2 OK Old
NaN NaN NaN
NaN NaN text
MRS3 OK New
MRS4 OK New
MRS5 OK NaN
MRS6 NOK Very old
NaN text NaN
text text NaN
NaN NaN NaN
MRS7 Excellent Very old
MRS8 Excellent Old
text text text
MRS9 Excellent New
NaN NaN NaN
MRS10 NOK New
I want to get:
ID Result1 Result2
Position1 NaN NaN
MRS1 OK New
MRS2 OK Old
MRS3 OK New
Position2 NaN NaN
MRS4 OK New
MRS5 OK NaN
Position3 NaN NaN
MRS6 NOK Very old
Position4 NaN NaN
MRS7 Excellent Very old
MRS8 Excellent Old
MRS9 Excellent New
MRS10 NOK New
and I put NaN on the places where should be an empty cell. How to do this in pandas?
I tried with the merge, but I have the problem because it is required the same length of comparing columns. I just want to compare values from the "ID" columns from both data frames, and those columns do not have the same length.
python pandas dataframe matching
asked Nov 20 at 7:00
slobokv83
427
I have two data frames, and I want to copy values from the column "Result2" in df2 to column "Result1" in df1 and to add the column "Description" from df2 to df1 for values that are in the same row as values from the column "Result2" (that are already copied), if the values from columns "ID" in both data frames match.
Here are the data frames:
df1
ID Result1
Position1 NaN
MRS1 OK
MRS2 Maybe
MRS3 NOK
Position2 NaN
MRS4 OK
MRS5 OK
Position3 NaN
MRS6 OK
Position4 NaN
MRS7 NOK
MRS8 OK
MRS9 NOK
MRS10 Excellent
df2
ID Result2 Description
text NaN NaN
NaN NaN text
text NaN NaN
MRS1 OK New
MRS2 OK Old
NaN NaN NaN
NaN NaN text
MRS3 OK New
MRS4 OK New
MRS5 OK NaN
MRS6 NOK Very old
NaN text NaN
text text NaN
NaN NaN NaN
MRS7 Excellent Very old
MRS8 Excellent Old
text text text
MRS9 Excellent New
NaN NaN NaN
MRS10 NOK New
I want to get:
ID Result1 Result2
Position1 NaN NaN
MRS1 OK New
MRS2 OK Old
MRS3 OK New
Position2 NaN NaN
MRS4 OK New
MRS5 OK NaN
Position3 NaN NaN
MRS6 NOK Very old
Position4 NaN NaN
MRS7 Excellent Very old
MRS8 Excellent Old
MRS9 Excellent New
MRS10 NOK New
and I put NaN on the places where should be an empty cell. How to do this in pandas?
I tried with the merge, but I have the problem because it is required the same length of comparing columns. I just want to compare values from the "ID" columns from both data frames, and those columns do not have the same length.
python pandas dataframe matching
python pandas dataframe matching
asked Nov 20 at 7:00
slobokv83
427
asked Nov 20 at 7:00
slobokv83
427
edited Nov 20 at 7:05
asked Nov 20 at 7:00
slobokv83
427
asked Nov 20 at 7:00
slobokv83
427
asked Nov 20 at 7:00
slobokv83
427
427
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
up vote
1
down vote
accepted
Use merge with only column ID with left join and rename columns:
df = (df1[['ID']].merge(df2, on='ID', how='left')
.rename(columns={'Result2':'Result1','Description':'Result2'}))
print (df)
ID Result1 Result2
0 Position1 NaN NaN
1 MRS1 OK New
2 MRS2 OK Old
3 MRS3 OK New
4 Position2 NaN NaN
5 MRS4 OK New
6 MRS5 OK NaN
7 Position3 NaN NaN
8 MRS6 NOK Very old
9 Position4 NaN NaN
10 MRS7 Excellent Very old
11 MRS8 Excellent Old
12 MRS9 Excellent New
13 MRS10 NOK New
answered Nov 20 at 7:04
jezrael
315k21252330
1
Thank you very much. It works very well. I made one mistake, I wanted to keep the name of the column "Description", and I typed "Result2" instead.
– slobokv83
Nov 20 at 7:32
Could I ask you if I have one more column in df1 which I need to keep in final, what I should do? That column has the same length as "ID" in df1.
– slobokv83
Nov 20 at 9:35
@slobokv83 - only changedf1[['ID']]todf1
– jezrael
Nov 20 at 9:36
1
Thank you very much.
– slobokv83
Nov 20 at 9:37
sorry because I ask you like this, but is it a way to import Excel data to ppt slides? I found the python-pptx library and I use it, but I could not find a way to import data from excel. Also, I could not find a way to keep formatting when I read Excel file, but I could set it afterward.
– slobokv83
Nov 20 at 10:01
|
show 2 more comments
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
1
down vote
accepted
Use merge with only column ID with left join and rename columns:
df = (df1[['ID']].merge(df2, on='ID', how='left')
.rename(columns={'Result2':'Result1','Description':'Result2'}))
print (df)
ID Result1 Result2
0 Position1 NaN NaN
1 MRS1 OK New
2 MRS2 OK Old
3 MRS3 OK New
4 Position2 NaN NaN
5 MRS4 OK New
6 MRS5 OK NaN
7 Position3 NaN NaN
8 MRS6 NOK Very old
9 Position4 NaN NaN
10 MRS7 Excellent Very old
11 MRS8 Excellent Old
12 MRS9 Excellent New
13 MRS10 NOK New
answered Nov 20 at 7:04
jezrael
315k21252330
1
Thank you very much. It works very well. I made one mistake, I wanted to keep the name of the column "Description", and I typed "Result2" instead.
– slobokv83
Nov 20 at 7:32
Could I ask you if I have one more column in df1 which I need to keep in final, what I should do? That column has the same length as "ID" in df1.
– slobokv83
Nov 20 at 9:35
@slobokv83 - only changedf1[['ID']]todf1
– jezrael
Nov 20 at 9:36
1
Thank you very much.
– slobokv83
Nov 20 at 9:37
sorry because I ask you like this, but is it a way to import Excel data to ppt slides? I found the python-pptx library and I use it, but I could not find a way to import data from excel. Also, I could not find a way to keep formatting when I read Excel file, but I could set it afterward.
– slobokv83
Nov 20 at 10:01
|
show 2 more comments
up vote
1
down vote
accepted
Use merge with only column ID with left join and rename columns:
df = (df1[['ID']].merge(df2, on='ID', how='left')
.rename(columns={'Result2':'Result1','Description':'Result2'}))
print (df)
ID Result1 Result2
0 Position1 NaN NaN
1 MRS1 OK New
2 MRS2 OK Old
3 MRS3 OK New
4 Position2 NaN NaN
5 MRS4 OK New
6 MRS5 OK NaN
7 Position3 NaN NaN
8 MRS6 NOK Very old
9 Position4 NaN NaN
10 MRS7 Excellent Very old
11 MRS8 Excellent Old
12 MRS9 Excellent New
13 MRS10 NOK New
answered Nov 20 at 7:04
jezrael
315k21252330
1
Thank you very much. It works very well. I made one mistake, I wanted to keep the name of the column "Description", and I typed "Result2" instead.
– slobokv83
Nov 20 at 7:32
Could I ask you if I have one more column in df1 which I need to keep in final, what I should do? That column has the same length as "ID" in df1.
– slobokv83
Nov 20 at 9:35
@slobokv83 - only changedf1[['ID']]todf1
– jezrael
Nov 20 at 9:36
1
Thank you very much.
– slobokv83
Nov 20 at 9:37
sorry because I ask you like this, but is it a way to import Excel data to ppt slides? I found the python-pptx library and I use it, but I could not find a way to import data from excel. Also, I could not find a way to keep formatting when I read Excel file, but I could set it afterward.
– slobokv83
Nov 20 at 10:01
|
show 2 more comments
up vote
1
down vote
accepted
up vote
1
down vote
accepted
Use merge with only column ID with left join and rename columns:
df = (df1[['ID']].merge(df2, on='ID', how='left')
.rename(columns={'Result2':'Result1','Description':'Result2'}))
print (df)
ID Result1 Result2
0 Position1 NaN NaN
1 MRS1 OK New
2 MRS2 OK Old
3 MRS3 OK New
4 Position2 NaN NaN
5 MRS4 OK New
6 MRS5 OK NaN
7 Position3 NaN NaN
8 MRS6 NOK Very old
9 Position4 NaN NaN
10 MRS7 Excellent Very old
11 MRS8 Excellent Old
12 MRS9 Excellent New
13 MRS10 NOK New
answered Nov 20 at 7:04
jezrael
315k21252330
Use merge with only column ID with left join and rename columns:
df = (df1[['ID']].merge(df2, on='ID', how='left')
.rename(columns={'Result2':'Result1','Description':'Result2'}))
print (df)
ID Result1 Result2
0 Position1 NaN NaN
1 MRS1 OK New
2 MRS2 OK Old
3 MRS3 OK New
4 Position2 NaN NaN
5 MRS4 OK New
6 MRS5 OK NaN
7 Position3 NaN NaN
8 MRS6 NOK Very old
9 Position4 NaN NaN
10 MRS7 Excellent Very old
11 MRS8 Excellent Old
12 MRS9 Excellent New
13 MRS10 NOK New
answered Nov 20 at 7:04
jezrael
315k21252330
answered Nov 20 at 7:04
jezrael
315k21252330
answered Nov 20 at 7:04
jezrael
315k21252330
answered Nov 20 at 7:04
jezrael
315k21252330
315k21252330
1
Thank you very much. It works very well. I made one mistake, I wanted to keep the name of the column "Description", and I typed "Result2" instead.
– slobokv83
Nov 20 at 7:32
Could I ask you if I have one more column in df1 which I need to keep in final, what I should do? That column has the same length as "ID" in df1.
– slobokv83
Nov 20 at 9:35
@slobokv83 - only changedf1[['ID']]todf1
– jezrael
Nov 20 at 9:36
1
Thank you very much.
– slobokv83
Nov 20 at 9:37
sorry because I ask you like this, but is it a way to import Excel data to ppt slides? I found the python-pptx library and I use it, but I could not find a way to import data from excel. Also, I could not find a way to keep formatting when I read Excel file, but I could set it afterward.
– slobokv83
Nov 20 at 10:01
|
show 2 more comments
1
Thank you very much. It works very well. I made one mistake, I wanted to keep the name of the column "Description", and I typed "Result2" instead.
– slobokv83
Nov 20 at 7:32
Could I ask you if I have one more column in df1 which I need to keep in final, what I should do? That column has the same length as "ID" in df1.
– slobokv83
Nov 20 at 9:35
@slobokv83 - only changedf1[['ID']]todf1
– jezrael
Nov 20 at 9:36
1
Thank you very much.
– slobokv83
Nov 20 at 9:37
sorry because I ask you like this, but is it a way to import Excel data to ppt slides? I found the python-pptx library and I use it, but I could not find a way to import data from excel. Also, I could not find a way to keep formatting when I read Excel file, but I could set it afterward.
– slobokv83
Nov 20 at 10:01
1
1
Thank you very much. It works very well. I made one mistake, I wanted to keep the name of the column "Description", and I typed "Result2" instead.
– slobokv83
Nov 20 at 7:32
Thank you very much. It works very well. I made one mistake, I wanted to keep the name of the column "Description", and I typed "Result2" instead.
– slobokv83
Nov 20 at 7:32
Could I ask you if I have one more column in df1 which I need to keep in final, what I should do? That column has the same length as "ID" in df1.
– slobokv83
Nov 20 at 9:35
Could I ask you if I have one more column in df1 which I need to keep in final, what I should do? That column has the same length as "ID" in df1.
– slobokv83
Nov 20 at 9:35
@slobokv83 - only change
df1[['ID']] to df1– jezrael
Nov 20 at 9:36
@slobokv83 - only change
df1[['ID']] to df1– jezrael
Nov 20 at 9:36
1
1
Thank you very much.
– slobokv83
Nov 20 at 9:37
Thank you very much.
– slobokv83
Nov 20 at 9:37
sorry because I ask you like this, but is it a way to import Excel data to ppt slides? I found the python-pptx library and I use it, but I could not find a way to import data from excel. Also, I could not find a way to keep formatting when I read Excel file, but I could set it afterward.
– slobokv83
Nov 20 at 10:01
sorry because I ask you like this, but is it a way to import Excel data to ppt slides? I found the python-pptx library and I use it, but I could not find a way to import data from excel. Also, I could not find a way to keep formatting when I read Excel file, but I could set it afterward.
– slobokv83
Nov 20 at 10:01
|
show 2 more comments
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53387782%2fif-values-from-two-columns-in-two-different-data-frames-match-then-copy-values-f%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
