How to fill an missing values in a column based on another column
0
$begingroup$
I have a dataframe called shoes
Brand Comment
Ugg NaN
Prada NaN
Clarks NaN
Ugg NaN
Clark NaN
Prada Made from horse leather
Prada Made from pig leather
Prada NaN
Ugg Made from Australian cow leather
...
and another dataframe df_mode which was obtained by taking the mode of the comments for each shoe brand in the shoes dataframe for nonnull values
Brand Comment
Ugg Made from sheep
Prada Made from pig leather
Clarks Made from Cow leather
How can I assign the missing values for each shoe brand in the shoes dataframe with its respective mode response shown in the df_mode dataframe.
This is basically what I'm trying to achieve
Brand Comment
Ugg Made from sheep
Prada Made from pig leather
Clarks Made from Cow leather
Ugg Made from sheep
Clark Made from Cow leather
Prada Made from horse leather
Prada Made from pig leather
Prada Made from pig leather
Ugg Made from Australian cow leather
pandas data-cleaning dataframe
asked 2 days ago
Python NewbiePython Newbie
11
New contributor
Python Newbie is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
$endgroup$
add a comment |
0
$begingroup$
I have a dataframe called shoes
Brand Comment
Ugg NaN
Prada NaN
Clarks NaN
Ugg NaN
Clark NaN
Prada Made from horse leather
Prada Made from pig leather
Prada NaN
Ugg Made from Australian cow leather
...
and another dataframe df_mode which was obtained by taking the mode of the comments for each shoe brand in the shoes dataframe for nonnull values
Brand Comment
Ugg Made from sheep
Prada Made from pig leather
Clarks Made from Cow leather
How can I assign the missing values for each shoe brand in the shoes dataframe with its respective mode response shown in the df_mode dataframe.
This is basically what I'm trying to achieve
Brand Comment
Ugg Made from sheep
Prada Made from pig leather
Clarks Made from Cow leather
Ugg Made from sheep
Clark Made from Cow leather
Prada Made from horse leather
Prada Made from pig leather
Prada Made from pig leather
Ugg Made from Australian cow leather
pandas data-cleaning dataframe
asked 2 days ago
Python NewbiePython Newbie
11
New contributor
Python Newbie is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
$endgroup$
add a comment |
0
0
0
$begingroup$
I have a dataframe called shoes
Brand Comment
Ugg NaN
Prada NaN
Clarks NaN
Ugg NaN
Clark NaN
Prada Made from horse leather
Prada Made from pig leather
Prada NaN
Ugg Made from Australian cow leather
...
and another dataframe df_mode which was obtained by taking the mode of the comments for each shoe brand in the shoes dataframe for nonnull values
Brand Comment
Ugg Made from sheep
Prada Made from pig leather
Clarks Made from Cow leather
How can I assign the missing values for each shoe brand in the shoes dataframe with its respective mode response shown in the df_mode dataframe.
This is basically what I'm trying to achieve
Brand Comment
Ugg Made from sheep
Prada Made from pig leather
Clarks Made from Cow leather
Ugg Made from sheep
Clark Made from Cow leather
Prada Made from horse leather
Prada Made from pig leather
Prada Made from pig leather
Ugg Made from Australian cow leather
pandas data-cleaning dataframe
asked 2 days ago
Python NewbiePython Newbie
11
New contributor
Python Newbie is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
$endgroup$
I have a dataframe called shoes
Brand Comment
Ugg NaN
Prada NaN
Clarks NaN
Ugg NaN
Clark NaN
Prada Made from horse leather
Prada Made from pig leather
Prada NaN
Ugg Made from Australian cow leather
...
and another dataframe df_mode which was obtained by taking the mode of the comments for each shoe brand in the shoes dataframe for nonnull values
Brand Comment
Ugg Made from sheep
Prada Made from pig leather
Clarks Made from Cow leather
How can I assign the missing values for each shoe brand in the shoes dataframe with its respective mode response shown in the df_mode dataframe.
This is basically what I'm trying to achieve
Brand Comment
Ugg Made from sheep
Prada Made from pig leather
Clarks Made from Cow leather
Ugg Made from sheep
Clark Made from Cow leather
Prada Made from horse leather
Prada Made from pig leather
Prada Made from pig leather
Ugg Made from Australian cow leather
pandas data-cleaning dataframe
pandas data-cleaning dataframe
asked 2 days ago
Python NewbiePython Newbie
11
New contributor
Python Newbie is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 2 days ago
Python NewbiePython Newbie
11
New contributor
Python Newbie is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited 2 days ago
Python Newbie
asked 2 days ago
Python NewbiePython Newbie
11
New contributor
Python Newbie is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 2 days ago
Python NewbiePython Newbie
11
asked 2 days ago
Python NewbiePython Newbie
11
11
New contributor
Python Newbie is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Python Newbie is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Python Newbie is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
1
$begingroup$
import pandas as pd
import numpy as np
shoes = pd.DataFrame({'Brand':['Ugg', 'Prada', 'Clark', 'Ugg', 'Clark'],
'Comment':[np.NaN, np.NaN, np.NaN, np.NaN, np.NaN]})
df_shoes = pd.DataFrame({('Ugg','Made from sheep'),
('Prada', 'Made from pig leather'),
('Clark', 'Made from Cow leather')}, columns=['Brand', 'Comment'])
shoes.merge(df_shoes, on=['Brand'], how='left', suffixes=('_x', '_y'))
The result will show like:
You can then drop the null columns.
EDIT:
As discussed in the comments, in case for the edited question, you can do that:
shoes[shoes.Comment.isnull()].merge(df_shoes,on=['Brand'], how='left',suffixes=('', '_notnull'))
shoes.Comment.fillna(value=temp.Comment_notnull)
answered 2 days ago
Victor OliveiraVictor Oliveira
1707
$endgroup$
$begingroup$
tried this but the NaN values still seem be to present
$endgroup$
– Python Newbie
2 days ago
$begingroup$
Sorry for that, you should merge based on Brand only, try that.
$endgroup$
– Victor Oliveira
2 days ago
$begingroup$
I edited again the answer
$endgroup$
– Victor Oliveira
2 days ago
$begingroup$
Thanks again for your response. But it suggests all the elements in the comment column are empty which isn't the case. I think that's a mistake on my part. I'll edit the question to reflect that.
$endgroup$
– Python Newbie
2 days ago
$begingroup$
Basically, What I'm trying to do is to assign comments present in the df_mode data frame to missing comments in the shoes dataframe without having to create a new column
$endgroup$
– Python Newbie
2 days ago
|
show 7 more comments
0
$begingroup$
You need a join, check this out, it really helped me understand how to handle situations like the one above. I know this isn't a complete answer, but going through the link is worth the time.
answered 2 days ago
MirMir
132
New contributor
Mir is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
$endgroup$
$begingroup$
Thank you, Mir! I'll check it out
$endgroup$
– Python Newbie
2 days ago
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
return StackExchange.using("mathjaxEditing", function () {
StackExchange.MarkdownEditor.creationCallbacks.add(function (editor, postfix) {
StackExchange.mathjaxEditing.prepareWmdForMathJax(editor, postfix, [["$", "$"], ["\\(","\\)"]]);
});
});
}, "mathjax-editing");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "557"
};
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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
});
}
});
Python Newbie is a new contributor. Be nice, and check out our Code of Conduct.
draft saved
draft discarded
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%2fdatascience.stackexchange.com%2fquestions%2f46939%2fhow-to-fill-an-missing-values-in-a-column-based-on-another-column%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
$begingroup$
import pandas as pd
import numpy as np
shoes = pd.DataFrame({'Brand':['Ugg', 'Prada', 'Clark', 'Ugg', 'Clark'],
'Comment':[np.NaN, np.NaN, np.NaN, np.NaN, np.NaN]})
df_shoes = pd.DataFrame({('Ugg','Made from sheep'),
('Prada', 'Made from pig leather'),
('Clark', 'Made from Cow leather')}, columns=['Brand', 'Comment'])
shoes.merge(df_shoes, on=['Brand'], how='left', suffixes=('_x', '_y'))
The result will show like:
You can then drop the null columns.
EDIT:
As discussed in the comments, in case for the edited question, you can do that:
shoes[shoes.Comment.isnull()].merge(df_shoes,on=['Brand'], how='left',suffixes=('', '_notnull'))
shoes.Comment.fillna(value=temp.Comment_notnull)
answered 2 days ago
Victor OliveiraVictor Oliveira
1707
$endgroup$
$begingroup$
tried this but the NaN values still seem be to present
$endgroup$
– Python Newbie
2 days ago
$begingroup$
Sorry for that, you should merge based on Brand only, try that.
$endgroup$
– Victor Oliveira
2 days ago
$begingroup$
I edited again the answer
$endgroup$
– Victor Oliveira
2 days ago
$begingroup$
Thanks again for your response. But it suggests all the elements in the comment column are empty which isn't the case. I think that's a mistake on my part. I'll edit the question to reflect that.
$endgroup$
– Python Newbie
2 days ago
$begingroup$
Basically, What I'm trying to do is to assign comments present in the df_mode data frame to missing comments in the shoes dataframe without having to create a new column
$endgroup$
– Python Newbie
2 days ago
|
show 7 more comments
1
$begingroup$
import pandas as pd
import numpy as np
shoes = pd.DataFrame({'Brand':['Ugg', 'Prada', 'Clark', 'Ugg', 'Clark'],
'Comment':[np.NaN, np.NaN, np.NaN, np.NaN, np.NaN]})
df_shoes = pd.DataFrame({('Ugg','Made from sheep'),
('Prada', 'Made from pig leather'),
('Clark', 'Made from Cow leather')}, columns=['Brand', 'Comment'])
shoes.merge(df_shoes, on=['Brand'], how='left', suffixes=('_x', '_y'))
The result will show like:
You can then drop the null columns.
EDIT:
As discussed in the comments, in case for the edited question, you can do that:
shoes[shoes.Comment.isnull()].merge(df_shoes,on=['Brand'], how='left',suffixes=('', '_notnull'))
shoes.Comment.fillna(value=temp.Comment_notnull)
answered 2 days ago
Victor OliveiraVictor Oliveira
1707
$endgroup$
$begingroup$
tried this but the NaN values still seem be to present
$endgroup$
– Python Newbie
2 days ago
$begingroup$
Sorry for that, you should merge based on Brand only, try that.
$endgroup$
– Victor Oliveira
2 days ago
$begingroup$
I edited again the answer
$endgroup$
– Victor Oliveira
2 days ago
$begingroup$
Thanks again for your response. But it suggests all the elements in the comment column are empty which isn't the case. I think that's a mistake on my part. I'll edit the question to reflect that.
$endgroup$
– Python Newbie
2 days ago
$begingroup$
Basically, What I'm trying to do is to assign comments present in the df_mode data frame to missing comments in the shoes dataframe without having to create a new column
$endgroup$
– Python Newbie
2 days ago
|
show 7 more comments
1
1
1
$begingroup$
import pandas as pd
import numpy as np
shoes = pd.DataFrame({'Brand':['Ugg', 'Prada', 'Clark', 'Ugg', 'Clark'],
'Comment':[np.NaN, np.NaN, np.NaN, np.NaN, np.NaN]})
df_shoes = pd.DataFrame({('Ugg','Made from sheep'),
('Prada', 'Made from pig leather'),
('Clark', 'Made from Cow leather')}, columns=['Brand', 'Comment'])
shoes.merge(df_shoes, on=['Brand'], how='left', suffixes=('_x', '_y'))
The result will show like:
You can then drop the null columns.
EDIT:
As discussed in the comments, in case for the edited question, you can do that:
shoes[shoes.Comment.isnull()].merge(df_shoes,on=['Brand'], how='left',suffixes=('', '_notnull'))
shoes.Comment.fillna(value=temp.Comment_notnull)
answered 2 days ago
Victor OliveiraVictor Oliveira
1707
$endgroup$
import pandas as pd
import numpy as np
shoes = pd.DataFrame({'Brand':['Ugg', 'Prada', 'Clark', 'Ugg', 'Clark'],
'Comment':[np.NaN, np.NaN, np.NaN, np.NaN, np.NaN]})
df_shoes = pd.DataFrame({('Ugg','Made from sheep'),
('Prada', 'Made from pig leather'),
('Clark', 'Made from Cow leather')}, columns=['Brand', 'Comment'])
shoes.merge(df_shoes, on=['Brand'], how='left', suffixes=('_x', '_y'))
The result will show like:
You can then drop the null columns.
EDIT:
As discussed in the comments, in case for the edited question, you can do that:
shoes[shoes.Comment.isnull()].merge(df_shoes,on=['Brand'], how='left',suffixes=('', '_notnull'))
shoes.Comment.fillna(value=temp.Comment_notnull)
answered 2 days ago
Victor OliveiraVictor Oliveira
1707
edited 14 hours ago
answered 2 days ago
Victor OliveiraVictor Oliveira
1707
answered 2 days ago
Victor OliveiraVictor Oliveira
1707
answered 2 days ago
Victor OliveiraVictor Oliveira
1707
1707
$begingroup$
tried this but the NaN values still seem be to present
$endgroup$
– Python Newbie
2 days ago
$begingroup$
Sorry for that, you should merge based on Brand only, try that.
$endgroup$
– Victor Oliveira
2 days ago
$begingroup$
I edited again the answer
$endgroup$
– Victor Oliveira
2 days ago
$begingroup$
Thanks again for your response. But it suggests all the elements in the comment column are empty which isn't the case. I think that's a mistake on my part. I'll edit the question to reflect that.
$endgroup$
– Python Newbie
2 days ago
$begingroup$
Basically, What I'm trying to do is to assign comments present in the df_mode data frame to missing comments in the shoes dataframe without having to create a new column
$endgroup$
– Python Newbie
2 days ago
|
show 7 more comments
$begingroup$
tried this but the NaN values still seem be to present
$endgroup$
– Python Newbie
2 days ago
$begingroup$
Sorry for that, you should merge based on Brand only, try that.
$endgroup$
– Victor Oliveira
2 days ago
$begingroup$
I edited again the answer
$endgroup$
– Victor Oliveira
2 days ago
$begingroup$
Thanks again for your response. But it suggests all the elements in the comment column are empty which isn't the case. I think that's a mistake on my part. I'll edit the question to reflect that.
$endgroup$
– Python Newbie
2 days ago
$begingroup$
Basically, What I'm trying to do is to assign comments present in the df_mode data frame to missing comments in the shoes dataframe without having to create a new column
$endgroup$
– Python Newbie
2 days ago
$begingroup$
tried this but the NaN values still seem be to present
$endgroup$
– Python Newbie
2 days ago
$begingroup$
tried this but the NaN values still seem be to present
$endgroup$
– Python Newbie
2 days ago
$begingroup$
Sorry for that, you should merge based on Brand only, try that.
$endgroup$
– Victor Oliveira
2 days ago
$begingroup$
Sorry for that, you should merge based on Brand only, try that.
$endgroup$
– Victor Oliveira
2 days ago
$begingroup$
I edited again the answer
$endgroup$
– Victor Oliveira
2 days ago
$begingroup$
I edited again the answer
$endgroup$
– Victor Oliveira
2 days ago
$begingroup$
Thanks again for your response. But it suggests all the elements in the comment column are empty which isn't the case. I think that's a mistake on my part. I'll edit the question to reflect that.
$endgroup$
– Python Newbie
2 days ago
$begingroup$
Thanks again for your response. But it suggests all the elements in the comment column are empty which isn't the case. I think that's a mistake on my part. I'll edit the question to reflect that.
$endgroup$
– Python Newbie
2 days ago
$begingroup$
Basically, What I'm trying to do is to assign comments present in the df_mode data frame to missing comments in the shoes dataframe without having to create a new column
$endgroup$
– Python Newbie
2 days ago
$begingroup$
Basically, What I'm trying to do is to assign comments present in the df_mode data frame to missing comments in the shoes dataframe without having to create a new column
$endgroup$
– Python Newbie
2 days ago
|
show 7 more comments
0
$begingroup$
You need a join, check this out, it really helped me understand how to handle situations like the one above. I know this isn't a complete answer, but going through the link is worth the time.
answered 2 days ago
MirMir
132
New contributor
Mir is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
$endgroup$
$begingroup$
Thank you, Mir! I'll check it out
$endgroup$
– Python Newbie
2 days ago
add a comment |
0
$begingroup$
You need a join, check this out, it really helped me understand how to handle situations like the one above. I know this isn't a complete answer, but going through the link is worth the time.
answered 2 days ago
MirMir
132
New contributor
Mir is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
$endgroup$
$begingroup$
Thank you, Mir! I'll check it out
$endgroup$
– Python Newbie
2 days ago
add a comment |
0
0
0
$begingroup$
You need a join, check this out, it really helped me understand how to handle situations like the one above. I know this isn't a complete answer, but going through the link is worth the time.
answered 2 days ago
MirMir
132
New contributor
Mir is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
$endgroup$
You need a join, check this out, it really helped me understand how to handle situations like the one above. I know this isn't a complete answer, but going through the link is worth the time.
answered 2 days ago
MirMir
132
New contributor
Mir is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
answered 2 days ago
MirMir
132
New contributor
Mir is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
answered 2 days ago
MirMir
132
answered 2 days ago
MirMir
132
132
New contributor
Mir is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Mir is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Mir is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
$begingroup$
Thank you, Mir! I'll check it out
$endgroup$
– Python Newbie
2 days ago
add a comment |
$begingroup$
Thank you, Mir! I'll check it out
$endgroup$
– Python Newbie
2 days ago
$begingroup$
Thank you, Mir! I'll check it out
$endgroup$
– Python Newbie
2 days ago
$begingroup$
Thank you, Mir! I'll check it out
$endgroup$
– Python Newbie
2 days ago
add a comment |
Python Newbie is a new contributor. Be nice, and check out our Code of Conduct.
draft saved
draft discarded
Python Newbie is a new contributor. Be nice, and check out our Code of Conduct.
Python Newbie is a new contributor. Be nice, and check out our Code of Conduct.
Python Newbie is a new contributor. Be nice, and check out our Code of Conduct.
Thanks for contributing an answer to Data Science Stack Exchange!
- 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.
Use MathJax to format equations. MathJax reference.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
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%2fdatascience.stackexchange.com%2fquestions%2f46939%2fhow-to-fill-an-missing-values-in-a-column-based-on-another-column%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
