How to redistribude the the value equaly between NaN values in pandas?

Question

I have the following dataframe:

                'B'         'C'
1/1/2017    'A' 
            BTC NaN       0.367392
            ETH NaN       0.367392
            XRP 0.164735  0.164735
            LTC 0.100481  0.100481
1/2/2017    BTC NaN       0.315265
            XRP NaN       0.315265
            ETH NaN       0.315265
            LTC 0.054204  0.054204

I want to redistribute (1 - df['B'].groupby(level=0).sum()) equally between NaN values. Column 'C' is an example of an expected output.

@jezrael I apologize, I gave a wrong example of the formula. I need (1 - df['B'].groupby(level=0).sum()) Thank you. Sorry for the confusion. — rakamakafo, Mar 11 '19 at 07:46

f.wue · Answer 1 · 2019-03-11T08:59:27.800

This should do it. You can get more info on how to count non-nan values from here and onisnull()from here.

Regardless of the answer you use, always catch the case where a column has no NaN value. In this case both answers will cause a division by zero error.

import pandas as pd
import numpy as np
test = [np.NAN,np.NAN,np.NAN,np.NAN,0.123128,0.1238123]
df = pd.DataFrame({"B":test})
df["C"] = df["B"].copy()
df["C"][df["C"].isnull()] = (1-df["B"].dropna().sum())/(len(df["B"]) - df["B"].count())
print(df)

Output:

          B         C
0       NaN  0.188265
1       NaN  0.188265
2       NaN  0.188265
3       NaN  0.188265
4  0.123128  0.123128
5  0.123812  0.123812

jezrael · Accepted Answer · 2019-03-11T07:48:46.763

You can use GroupBy.transform by first level of MultiIndex with sum, for second count number of NaNs by check missing values by Series.isna with sum, divide and replace missing values:

print (df)
                     C         D
A        B                      
1/1/2017 BTC       NaN  0.367392
         ETH       NaN  0.367392
         XRP  0.164735  0.164735
         LTC  0.100481  0.100481
1/2/2017 BTC       NaN  0.315265
         XRP       NaN  0.315265
         ETH       NaN  0.315265
         LTC  0.054204  0.054204

sum1 = 1 - df['C'].groupby(level=0).transform('sum')
len1 = df['C'].isna().groupby(level=0).transform('sum')

df['E'] = df['C'].fillna(sum1 / len1)
print (df)
                     C         D         E
A        B                                
1/1/2017 BTC       NaN  0.367392  0.367392
         ETH       NaN  0.367392  0.367392
         XRP  0.164735  0.164735  0.164735
         LTC  0.100481  0.100481  0.100481
1/2/2017 BTC       NaN  0.315265  0.315265
         XRP       NaN  0.315265  0.315265
         ETH       NaN  0.315265  0.315265
         LTC  0.054204  0.054204  0.054204

Thank you so much for your solution! Worked perfectly! – rakamakafo Mar 11 '19 at 09:02 — rakamakafo, Mar 11 '19 at 09:02

How to redistribude the the value equaly between NaN values in pandas?

2 Answers2