how to join two dataframe with a key and duplicate the matching value to fill in
how can I join two data frames by column "ID" and fill the blanks with the matching value. Since it is complicated to explain, here is my code to show what I want for the result.
import pandas as pd
df = pd.DataFrame({'id': [1, 1, 1, 2, 2, 3, 4, 4, 4], 'col1': [3, 0, -1, 3.4, 4, 5, 6, 7, 8]})
df2 = pd.DataFrame({'id': [1, 2, 3, 4, 5, 6, 7, 8, 9], 'col2': ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I']})
Now, I want to join these two dataframes with "id" and duplicate the values in col2 to fill in the blank col2 column after join.
please help me. Thanks
python pandas dataframe join
asked Nov 13 at 7:32
Yun Tae Hwang
15711
add a comment |
how can I join two data frames by column "ID" and fill the blanks with the matching value. Since it is complicated to explain, here is my code to show what I want for the result.
import pandas as pd
df = pd.DataFrame({'id': [1, 1, 1, 2, 2, 3, 4, 4, 4], 'col1': [3, 0, -1, 3.4, 4, 5, 6, 7, 8]})
df2 = pd.DataFrame({'id': [1, 2, 3, 4, 5, 6, 7, 8, 9], 'col2': ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I']})
Now, I want to join these two dataframes with "id" and duplicate the values in col2 to fill in the blank col2 column after join.
please help me. Thanks
python pandas dataframe join
asked Nov 13 at 7:32
Yun Tae Hwang
15711
What is expected output?
– jezrael
Nov 13 at 7:33
add a comment |
how can I join two data frames by column "ID" and fill the blanks with the matching value. Since it is complicated to explain, here is my code to show what I want for the result.
import pandas as pd
df = pd.DataFrame({'id': [1, 1, 1, 2, 2, 3, 4, 4, 4], 'col1': [3, 0, -1, 3.4, 4, 5, 6, 7, 8]})
df2 = pd.DataFrame({'id': [1, 2, 3, 4, 5, 6, 7, 8, 9], 'col2': ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I']})
Now, I want to join these two dataframes with "id" and duplicate the values in col2 to fill in the blank col2 column after join.
please help me. Thanks
python pandas dataframe join
asked Nov 13 at 7:32
Yun Tae Hwang
15711
how can I join two data frames by column "ID" and fill the blanks with the matching value. Since it is complicated to explain, here is my code to show what I want for the result.
import pandas as pd
df = pd.DataFrame({'id': [1, 1, 1, 2, 2, 3, 4, 4, 4], 'col1': [3, 0, -1, 3.4, 4, 5, 6, 7, 8]})
df2 = pd.DataFrame({'id': [1, 2, 3, 4, 5, 6, 7, 8, 9], 'col2': ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I']})
Now, I want to join these two dataframes with "id" and duplicate the values in col2 to fill in the blank col2 column after join.
please help me. Thanks
python pandas dataframe join
python pandas dataframe join
asked Nov 13 at 7:32
Yun Tae Hwang
15711
asked Nov 13 at 7:32
Yun Tae Hwang
15711
edited Nov 21 at 13:38
asked Nov 13 at 7:32
Yun Tae Hwang
15711
asked Nov 13 at 7:32
Yun Tae Hwang
15711
asked Nov 13 at 7:32
Yun Tae Hwang
15711
15711
What is expected output?
– jezrael
Nov 13 at 7:33
add a comment |
What is expected output?
– jezrael
Nov 13 at 7:33
What is expected output?
– jezrael
Nov 13 at 7:33
What is expected output?
– jezrael
Nov 13 at 7:33
add a comment |
3 Answers
3
active
oldest
votes
Are you looking for merge?
df.merge(df2, on='id')
id col1 col2
0 1 3.0 A
1 1 0.0 A
2 1 -1.0 A
3 2 3.4 B
4 2 4.0 B
5 3 5.0 C
6 4 6.0 D
7 4 7.0 D
8 4 8.0 D
answered Nov 13 at 7:37
user7374610
6981422
Simplest and easiest way to do merging
– id101112
Nov 21 at 15:05
add a comment |
I believe you need map:
df['col2'] = df['id'].map(df2.set_index('id')['col2'])
Or left join with merge:
df = df.merge(df2, on='id', how='left')
print (df)
id col1 col2
0 1 3.0 A
1 1 0.0 A
2 1 -1.0 A
3 2 3.4 B
4 2 4.0 B
5 3 5.0 C
6 4 6.0 D
7 4 7.0 D
8 4 8.0 D
answered Nov 13 at 7:35
jezrael
319k22258337
add a comment |
If you outer join them:
df3 = pd.merge(df, df2, on='id', how='outer')
Then you can replace NaN values in col1 by corresponding values in col2:
df3.loc[df3.col1.isnull(), 'col1'] = df3.loc[df3.col1.isnull(), 'col2']
print(df3)
Output:
id col1 col2
0 1 3 A
1 1 0 A
2 1 -1 A
3 2 3.4 B
4 2 4 B
5 3 5 C
6 4 6 D
7 4 7 D
8 4 8 D
9 5 E E
10 6 F F
11 7 G G
12 8 H H
13 9 I I
answered Nov 13 at 7:43
Alla Tarighati
594210
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
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%2f53275955%2fhow-to-join-two-dataframe-with-a-key-and-duplicate-the-matching-value-to-fill-in%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
Are you looking for merge?
df.merge(df2, on='id')
id col1 col2
0 1 3.0 A
1 1 0.0 A
2 1 -1.0 A
3 2 3.4 B
4 2 4.0 B
5 3 5.0 C
6 4 6.0 D
7 4 7.0 D
8 4 8.0 D
answered Nov 13 at 7:37
user7374610
6981422
Simplest and easiest way to do merging
– id101112
Nov 21 at 15:05
add a comment |
Are you looking for merge?
df.merge(df2, on='id')
id col1 col2
0 1 3.0 A
1 1 0.0 A
2 1 -1.0 A
3 2 3.4 B
4 2 4.0 B
5 3 5.0 C
6 4 6.0 D
7 4 7.0 D
8 4 8.0 D
answered Nov 13 at 7:37
user7374610
6981422
Simplest and easiest way to do merging
– id101112
Nov 21 at 15:05
add a comment |
Are you looking for merge?
df.merge(df2, on='id')
id col1 col2
0 1 3.0 A
1 1 0.0 A
2 1 -1.0 A
3 2 3.4 B
4 2 4.0 B
5 3 5.0 C
6 4 6.0 D
7 4 7.0 D
8 4 8.0 D
answered Nov 13 at 7:37
user7374610
6981422
Are you looking for merge?
df.merge(df2, on='id')
id col1 col2
0 1 3.0 A
1 1 0.0 A
2 1 -1.0 A
3 2 3.4 B
4 2 4.0 B
5 3 5.0 C
6 4 6.0 D
7 4 7.0 D
8 4 8.0 D
answered Nov 13 at 7:37
user7374610
6981422
answered Nov 13 at 7:37
user7374610
6981422
answered Nov 13 at 7:37
user7374610
6981422
answered Nov 13 at 7:37
user7374610
6981422
6981422
Simplest and easiest way to do merging
– id101112
Nov 21 at 15:05
add a comment |
Simplest and easiest way to do merging
– id101112
Nov 21 at 15:05
Simplest and easiest way to do merging
– id101112
Nov 21 at 15:05
Simplest and easiest way to do merging
– id101112
Nov 21 at 15:05
add a comment |
I believe you need map:
df['col2'] = df['id'].map(df2.set_index('id')['col2'])
Or left join with merge:
df = df.merge(df2, on='id', how='left')
print (df)
id col1 col2
0 1 3.0 A
1 1 0.0 A
2 1 -1.0 A
3 2 3.4 B
4 2 4.0 B
5 3 5.0 C
6 4 6.0 D
7 4 7.0 D
8 4 8.0 D
answered Nov 13 at 7:35
jezrael
319k22258337
add a comment |
I believe you need map:
df['col2'] = df['id'].map(df2.set_index('id')['col2'])
Or left join with merge:
df = df.merge(df2, on='id', how='left')
print (df)
id col1 col2
0 1 3.0 A
1 1 0.0 A
2 1 -1.0 A
3 2 3.4 B
4 2 4.0 B
5 3 5.0 C
6 4 6.0 D
7 4 7.0 D
8 4 8.0 D
answered Nov 13 at 7:35
jezrael
319k22258337
add a comment |
I believe you need map:
df['col2'] = df['id'].map(df2.set_index('id')['col2'])
Or left join with merge:
df = df.merge(df2, on='id', how='left')
print (df)
id col1 col2
0 1 3.0 A
1 1 0.0 A
2 1 -1.0 A
3 2 3.4 B
4 2 4.0 B
5 3 5.0 C
6 4 6.0 D
7 4 7.0 D
8 4 8.0 D
answered Nov 13 at 7:35
jezrael
319k22258337
I believe you need map:
df['col2'] = df['id'].map(df2.set_index('id')['col2'])
Or left join with merge:
df = df.merge(df2, on='id', how='left')
print (df)
id col1 col2
0 1 3.0 A
1 1 0.0 A
2 1 -1.0 A
3 2 3.4 B
4 2 4.0 B
5 3 5.0 C
6 4 6.0 D
7 4 7.0 D
8 4 8.0 D
answered Nov 13 at 7:35
jezrael
319k22258337
answered Nov 13 at 7:35
jezrael
319k22258337
answered Nov 13 at 7:35
jezrael
319k22258337
answered Nov 13 at 7:35
jezrael
319k22258337
319k22258337
add a comment |
add a comment |
If you outer join them:
df3 = pd.merge(df, df2, on='id', how='outer')
Then you can replace NaN values in col1 by corresponding values in col2:
df3.loc[df3.col1.isnull(), 'col1'] = df3.loc[df3.col1.isnull(), 'col2']
print(df3)
Output:
id col1 col2
0 1 3 A
1 1 0 A
2 1 -1 A
3 2 3.4 B
4 2 4 B
5 3 5 C
6 4 6 D
7 4 7 D
8 4 8 D
9 5 E E
10 6 F F
11 7 G G
12 8 H H
13 9 I I
answered Nov 13 at 7:43
Alla Tarighati
594210
add a comment |
If you outer join them:
df3 = pd.merge(df, df2, on='id', how='outer')
Then you can replace NaN values in col1 by corresponding values in col2:
df3.loc[df3.col1.isnull(), 'col1'] = df3.loc[df3.col1.isnull(), 'col2']
print(df3)
Output:
id col1 col2
0 1 3 A
1 1 0 A
2 1 -1 A
3 2 3.4 B
4 2 4 B
5 3 5 C
6 4 6 D
7 4 7 D
8 4 8 D
9 5 E E
10 6 F F
11 7 G G
12 8 H H
13 9 I I
answered Nov 13 at 7:43
Alla Tarighati
594210
add a comment |
If you outer join them:
df3 = pd.merge(df, df2, on='id', how='outer')
Then you can replace NaN values in col1 by corresponding values in col2:
df3.loc[df3.col1.isnull(), 'col1'] = df3.loc[df3.col1.isnull(), 'col2']
print(df3)
Output:
id col1 col2
0 1 3 A
1 1 0 A
2 1 -1 A
3 2 3.4 B
4 2 4 B
5 3 5 C
6 4 6 D
7 4 7 D
8 4 8 D
9 5 E E
10 6 F F
11 7 G G
12 8 H H
13 9 I I
answered Nov 13 at 7:43
Alla Tarighati
594210
If you outer join them:
df3 = pd.merge(df, df2, on='id', how='outer')
Then you can replace NaN values in col1 by corresponding values in col2:
df3.loc[df3.col1.isnull(), 'col1'] = df3.loc[df3.col1.isnull(), 'col2']
print(df3)
Output:
id col1 col2
0 1 3 A
1 1 0 A
2 1 -1 A
3 2 3.4 B
4 2 4 B
5 3 5 C
6 4 6 D
7 4 7 D
8 4 8 D
9 5 E E
10 6 F F
11 7 G G
12 8 H H
13 9 I I
answered Nov 13 at 7:43
Alla Tarighati
594210
answered Nov 13 at 7:43
Alla Tarighati
594210
answered Nov 13 at 7:43
Alla Tarighati
594210
answered Nov 13 at 7:43
Alla Tarighati
594210
594210
add a comment |
add a comment |
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%2f53275955%2fhow-to-join-two-dataframe-with-a-key-and-duplicate-the-matching-value-to-fill-in%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
What is expected output?
– jezrael
Nov 13 at 7:33