Pandas: Fill empty values with the first value of same duplicated names/keys group

user_14214287 0
Viewed 214

I have a dataframe of 2 columns: name and id

In the name column I have a couple of duplicates and in the id column I have an empty value for the duplicates of same name.

What I want is to fill that empty value with the value of the first iteration

For example:

df1=pd.DataFrame(columns=["name","id"])
df1["name"]=["a","b","c","a","d","e","b"]
df1["id"]=[1,2,3,"",4,5,""]


  name  id
0    a   1
1    b   2
2    c   3
3    a   
4    d   4
5    e   5
6    b

For the second iteration of "a" and "b" I have an empty value

What I want:

df1["id"]=[1,2,3,1,4,5,2]

How can I fill that empty value with the value of the first iteration ?

pandas python
2 Answers

You can group the name by .groupby() and get the first entry of each group by .transform('first'). Then replace the empty string with these first entries in same name group by .fillna(), as follows:

df1['id'] = df1['id'].replace('', np.nan).fillna(df1.groupby('name')['id'].transform('first')).astype(int)

If your id column already contains some NaN values and you don't want to touch on these values, you can also use .mask() to conditionally replace the empty string values by groupby .transform('first') values, as follows:

df1['id'] = df1['id'].mask(df1['id'] == '', df1.groupby('name')['id'].transform('first'))

Result:

print(df)

  name  id
0    a   1
1    b   2
2    c   3
3    a   1
4    d   4
5    e   5
6    b   2
user_150706970

A few more groupby transform options via

np.where:

df1['id'] = np.where(
    df1['id'].eq(''),
    df1.groupby('name')['id'].transform('first'),
    df1['id']
)

and loc:

df1.loc[df1['id'].eq(''), 'id'] = df1.groupby('name')['id'].transform('first')

df1:

  name id
0    a  1
1    b  2
2    c  3
3    a  1
4    d  4
5    e  5
6    b  2
user_154978880
Related