Where there is a NaN in a Pandas Column I want to replace with a 1 except for the Totals which need to sum the 1's

user_10380766 0
Viewed 66

In the Month Return Column I need to replace NaN with a 1 except for the ros in the Category Column with the "TOTAL". I need those to sum the 1's up just after the previous "TOTAL" row. Lengths of Grouped rows (by date and Account) can vary by length.

Return Date    Account      Category    Month Return
 7/31/2003      abcdef       BOND        NaN
 7/31/2003      abcdef       CASH        NaN
 7/31/2003      abcdef       EQUITY      NaN
 7/31/2003      abcdef       TOTAL       Nan
 7/31/2003      ghijkl       BOND        0.25
 7/31/2003      ghijkl       CASH        0.25
 7/31/2003      ghijkl       EQUITY      1.25
 7/31/2003      ghijkl       TOTAL       1.75
 7/31/2003      mnopqr       BOND        NaN
 7/31/2003      mnopqr       CASH        NaN
 7/31/2003      mnopqr       EQUITY      NaN
 7/31/2003      mnopqr       REAL        NaN
 7/31/2003      mnopqr       TOTAL       Nan

Want it to look something like this:

Return Date    Account      Category    Month Return
 7/31/2003      abcdef       BOND        1
 7/31/2003      abcdef       CASH        1
 7/31/2003      abcdef       EQUITY      1
 7/31/2003      abcdef       TOTAL       3
 7/31/2003      ghijkl       BOND        0.25
 7/31/2003      ghijkl       CASH        0.25
 7/31/2003      ghijkl       EQUITY      1.25
 7/31/2003      ghijkl       TOTAL       1.75
 7/31/2003      mnopqr       BOND        1
 7/31/2003      mnopqr       CASH        1
 7/31/2003      mnopqr       EQUITY      1
 7/31/2003      mnopqr       REAL        1
 7/31/2003      mnopqr       TOTAL       4
pandas python
2 Answers

You can use DataFrame.fillna with DataFrame.loc:

df=df.replace('Nan',np.nan)
c=df['Category'].ne('TOTAL')
df.loc[c,'Month_Return']=df.loc[c,'Month_Return'].fillna(1)
fill=df.groupby('Account')['Month_Return'].apply(lambda x: x.eq(1).cumsum())
df['Month_Return'].fillna(fill,inplace=True)
print(df)

   Return_Date Account Category Month_Return
0    7/31/2003  abcdef     BOND            1
1    7/31/2003  abcdef     CASH            1
2    7/31/2003  abcdef   EQUITY            1
3    7/31/2003  abcdef    TOTAL            3
4    7/31/2003  ghijkl     BOND         0.25
5    7/31/2003  ghijkl     CASH         0.25
6    7/31/2003  ghijkl   EQUITY         1.25
7    7/31/2003  ghijkl    TOTAL         1.75
8    7/31/2003  mnopqr     BOND            1
9    7/31/2003  mnopqr     CASH            1
10   7/31/2003  mnopqr   EQUITY            1
11   7/31/2003  mnopqr     REAL            1
12   7/31/2003  mnopqr    TOTAL            4
user_118842370

transform mixed in there somewhere

mask = df['Category'].eq('TOTAL')
ones = df['Month Return'].fillna(1).mask(mask)
tots = ones.groupby(df['Account']).transform('sum')
df['Month Return'] = ones.fillna(tots)

df

   Return Date Account Category  Month Return
0    7/31/2003  abcdef     BOND          1.00
1    7/31/2003  abcdef     CASH          1.00
2    7/31/2003  abcdef   EQUITY          1.00
3    7/31/2003  abcdef    TOTAL          3.00
4    7/31/2003  ghijkl     BOND          0.25
5    7/31/2003  ghijkl     CASH          0.25
6    7/31/2003  ghijkl   EQUITY          1.25
7    7/31/2003  ghijkl    TOTAL          1.75
8    7/31/2003  mnopqr     BOND          1.00
9    7/31/2003  mnopqr     CASH          1.00
10   7/31/2003  mnopqr   EQUITY          1.00
11   7/31/2003  mnopqr     REAL          1.00
12   7/31/2003  mnopqr    TOTAL          4.00

Details

mask = df['Category'].eq('TOTAL')

I'm going to use this to blot out values where mask is True but I wanted my code to be prettier.

ones = df['Month Return'].fillna(1) ...

This is where I fill in missing bits with 1

ones = df['Month Return'].fillna(1).mask(mask)

And subsequently blot out the rows where mask is True or Category is TOTAL. Keep in mind that this removes the values where we already had a 'Month Return' in a 'TOTAL' row. But that's ok, I'll recalculate in a bit.

tots = ones.groupby(df['Account']).transform('sum')

This gets me a series whose index matches my dataframe and makes it easier to fillna because Pandas will know which rows to fill.

df['Month Return'] = ones.fillna(tots)

At this point, the only rows in ones that have NaN values are the ones with 'TOTAL' in the 'Category' column. And those are exactly the ones, I'm going to fill with the sum for each 'Account'.

user_23366540
Related