CODEWITHSUNDEEP
pythonpython-3.xpandasdata-science
Shadab Ansari
Shadab Ansari

Reputation: 7070

Pandas:How to fill in value from the same column but different row

I have a requirement to fill out a column with values from same column but from different rows.

Example: Below is the data structure.

    fname   lname
0    bob    andy
1  manny  dorson
2    bob     NaN

Now for all "lname" values which are NaN, I want to fill those with "lname" values from the rows which share the common "fname"

So 3rd row which does not have "lname" I want to pick the "lname" from 1st row since "fname" for both the rows are same. The result which I expect :

    fname   lname
0    bob    andy
1  manny  dorson
2    bob     andy

This is just a simple minified example. And let's say, if there are multiple rows with matching first name, we can pick up the first one. I tried a lot of things but not getting it to work. Any help is appreciated. Thanks.

Upvotes: 0

Views: 858

Answers (1)

Space Impact
Space Impact

Reputation: 13255

Use groupby and ffill:

#To fill the NaN's with last value of group
df['lname'] = df.groupby('fname', as_index=False)['lname'].ffill()

#To fill the NaN's with first value of group
df['lname'] = (df['lname'].fillna(df.groupby('fname')['lname']
                                    .transform('first')))

Upvotes: 1

Related Questions