CODEWITHSUNDEEP
pythonpandas
xyhuang
xyhuang

Reputation: 424

How can i fill DataFrame's data by other row's value in a given condition?

I want to fill some rows' values use other rows' value.

let me list an example:

In [7]: df = pd.DataFrame([['a', 'b', 'c', 'aa', 'ba'], [1,2,3,np.nan,np.nan]]).T

In [8]: df
Out[8]: 
    0    1
0   a    1
1   b    2
2   c    3
3  aa  NaN
4  bb  NaN

what i want is to fill df.loc[3, 1] with value of df.loc[0, 1],

df.loc[4, 1] with df.loc[1, 1]

because a given condition ('a' and 'aa'(loc[3,1] and loc[0, 1]) have same

first 'a', 'b' and 'bb' shared 'b')

is there any good methods to do this?

Upvotes: 1

Views: 135

Answers (2)

jezrael
jezrael

Reputation: 862481

If possible combine values by first letter with forward filling use:

df[1] = df.groupby(df[0].str[0])[1].ffill()
print (df)
    0  1
0   a  1
1   b  2
2   c  3
3  aa  1
4  ba  2

If need replace by first non missing value use GroupBy.transform with GroupBy.first:

df = pd.DataFrame([['aa', 'b', 'c', 'a', 'ba'], [np.nan,2,3,1,np.nan]]).T
print (df)
    0    1
0  aa  NaN
1   b    2
2   c    3
3   a    1
4  ba  NaN

df[1] = df.groupby(df[0].str[0])[1].transform('first')
print (df)
    0  1
0  aa  1
1   b  2
2   c  3
3   a  1
4  ba  2

Upvotes: 3

Pygirl
Pygirl

Reputation: 13349

using map I can only think of this:

map_val = df.dropna().set_index(0).to_dict()[1]
df[1] = df[1].fillna(df[0].map(lambda x:map_val[x[0]]))

df

    0   1
0   a   1
1   b   2
2   c   3
3   aa  1
4   ba  2

Upvotes: 0

Related Questions