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
$\endgroup$
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: enter image description here

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)
$\endgroup$
12
  • $\begingroup$ tried this but the NaN values still seem be to present $\endgroup$ – Python Newbie Mar 8 '19 at 16:33
  • $\begingroup$ Sorry for that, you should merge based on Brand only, try that. $\endgroup$ – Victor Oliveira Mar 8 '19 at 16:35
  • $\begingroup$ I edited again the answer $\endgroup$ – Victor Oliveira Mar 8 '19 at 16:50
  • $\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 Mar 8 '19 at 17:00
  • $\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 Mar 8 '19 at 17:02
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.

$\endgroup$
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.