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 '18 at 7:32
Yun Tae HwangYun 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 '18 at 7:32
Yun Tae HwangYun Tae Hwang
15711
What is expected output?
– jezrael
Nov 13 '18 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 '18 at 7:32
Yun Tae HwangYun 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 '18 at 7:32
Yun Tae HwangYun Tae Hwang
15711
asked Nov 13 '18 at 7:32
Yun Tae HwangYun Tae Hwang
15711
edited Nov 21 '18 at 13:38
Yun Tae Hwang
asked Nov 13 '18 at 7:32
Yun Tae HwangYun Tae Hwang
15711
asked Nov 13 '18 at 7:32
Yun Tae HwangYun Tae Hwang
15711
asked Nov 13 '18 at 7:32
Yun Tae HwangYun Tae Hwang
15711
15711
What is expected output?
– jezrael
Nov 13 '18 at 7:33
add a comment |
What is expected output?
– jezrael
Nov 13 '18 at 7:33
What is expected output?
– jezrael
Nov 13 '18 at 7:33
What is expected output?
– jezrael
Nov 13 '18 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 '18 at 7:37
user7374610user7374610
6981522
Simplest and easiest way to do merging
– id101112
Nov 21 '18 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 '18 at 7:35
jezraeljezrael
326k23268344
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 '18 at 7:43
Alla TarighatiAlla Tarighati
596210
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 '18 at 7:37
user7374610user7374610
6981522
Simplest and easiest way to do merging
– id101112
Nov 21 '18 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 '18 at 7:37
user7374610user7374610
6981522
Simplest and easiest way to do merging
– id101112
Nov 21 '18 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 '18 at 7:37
user7374610user7374610
6981522
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 '18 at 7:37
user7374610user7374610
6981522
answered Nov 13 '18 at 7:37
user7374610user7374610
6981522
answered Nov 13 '18 at 7:37
user7374610user7374610
6981522
answered Nov 13 '18 at 7:37
user7374610user7374610
6981522
6981522
Simplest and easiest way to do merging
– id101112
Nov 21 '18 at 15:05
add a comment |
Simplest and easiest way to do merging
– id101112
Nov 21 '18 at 15:05
Simplest and easiest way to do merging
– id101112
Nov 21 '18 at 15:05
Simplest and easiest way to do merging
– id101112
Nov 21 '18 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 '18 at 7:35
jezraeljezrael
326k23268344
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 '18 at 7:35
jezraeljezrael
326k23268344
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 '18 at 7:35
jezraeljezrael
326k23268344
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 '18 at 7:35
jezraeljezrael
326k23268344
answered Nov 13 '18 at 7:35
jezraeljezrael
326k23268344
answered Nov 13 '18 at 7:35
jezraeljezrael
326k23268344
answered Nov 13 '18 at 7:35
jezraeljezrael
326k23268344
326k23268344
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 '18 at 7:43
Alla TarighatiAlla Tarighati
596210
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 '18 at 7:43
Alla TarighatiAlla Tarighati
596210
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 '18 at 7:43
Alla TarighatiAlla Tarighati
596210
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 '18 at 7:43
Alla TarighatiAlla Tarighati
596210
answered Nov 13 '18 at 7:43
Alla TarighatiAlla Tarighati
596210
answered Nov 13 '18 at 7:43
Alla TarighatiAlla Tarighati
596210
answered Nov 13 '18 at 7:43
Alla TarighatiAlla Tarighati
596210
596210
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.
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 '18 at 7:33