Pandas averaging by specific row-CodePudding

I have table taht looks like following

A | B | C
__________
na| 1 | 2
3 | 3 | 4
na| 5 | 6
na| 7 | 8
2 | 9 | 10

I would like to average all by column A. All the nan values till the number, final outcome should look like

A | B | C
__________
3 | 2 | 3
2 | 7 | 8

CodePudding user response：

You could backward fill column A and then perform a groupby with mean():

df.assign(A = lambda col:col['A'].bfill()).groupby('A',as_index=False).mean()

prints:

     A    B    C
0  2.0  7.0  8.0
1  3.0  2.0  3.0

CodePudding user response：

df = pd.DataFrame({'A':[pd.NA,3,pd.NA,pd.NA,2], 'B':range(1,10,2), 'C':range(2,11,2)})
df.bfill().groupby('A').mean().reset_index()

Output:

   A    B    C
0  2  7.0  8.0
1  3  2.0  3.0

CodePudding user response：

form a new dataframe missing out rows with NaN in column 'A'

d2 = d1[d1['A'].notnull()]