Geek Answers Handbook

Inverted column sum in pandas.DataFrame

I have a pandas DataFrame with a boolean column sorted by another column and you need to calculate the total total, i.e. the number of true values ​​from the current row to the bottom.

Example

In [13]: df = pd.DataFrame({'A': [True] * 3 + [False] * 5, 'B': np.random.rand(8) }) In [15]: df = df.sort_values('B') In [16]: df Out[16]: AB 6 False 0.037710 2 True 0.315414 4 False 0.332480 7 False 0.445505 3 False 0.580156 1 True 0.741551 5 False 0.796944 0 True 0.817563

I need something that will give me a new column with values

 3 3 2 2 2 2 1 1

That is, for each line, it should contain the number of True values ​​in this line and in the lines below.

I tried various methods with .iloc[::-1] , but the result is not the one you need.

Think I missed something obvious. I started using pandas just yesterday.

+7
python pandas reverse dataframe
source share
2 answers

Reverse column A, take cumsum, then again again:

 df['C'] = df.ix[::-1, 'A'].cumsum()[::-1] 
 import pandas as pd df = pd.DataFrame( {'A': [False, True, False, False, False, True, False, True], 'B': [0.03771, 0.315414, 0.33248, 0.445505, 0.580156, 0.741551, 0.796944, 0.817563],}, index=[6, 2, 4, 7, 3, 1, 5, 0]) df['C'] = df.ix[::-1, 'A'].cumsum()[::-1] print(df)

gives

  ABC 6 False 0.037710 3 2 True 0.315414 3 4 False 0.332480 2 7 False 0.445505 2 3 False 0.580156 2 1 True 0.741551 2 5 False 0.796944 1 0 True 0.817563 1

Alternatively, you can count the amount of True in column A and subtract the (offset) cumsum:

 In [113]: df['A'].sum()-df['A'].shift(1).fillna(0).cumsum() Out[113]: 6 3 2 3 4 2 7 2 3 2 1 2 5 1 0 1 Name: A, dtype: object

But it is much slower. Using IPython to run the test:

 In [116]: df = pd.DataFrame({'A':np.random.randint(2, size=10**5).astype(bool)}) In [117]: %timeit df['A'].sum()-df['A'].shift(1).fillna(0).cumsum() 10 loops, best of 3: 19.8 ms per loop In [118]: %timeit df.ix[::-1, 'A'].cumsum()[::-1] 1000 loops, best of 3: 701 µs per loop
+11
source share

This works, but slower ... as @unutbu's answer. True allows 1. False or any other value does not work.

 df[2] = df.groupby('A').cumcount(ascending=False)+1 df[1] = np.where(df['A']==True,df[2],None) df[1] = df[1].fillna(method='bfill').fillna(0) del df[2] AB 1 # 3 False 0.277557 3.0 # 7 False 0.400751 3.0 # 6 False 0.431587 3.0 # 5 False 0.481006 3.0 # 1 True 0.534364 3.0 # 2 True 0.556378 2.0 # 0 True 0.863192 1.0 # 4 False 0.916247 0.0
0
source share

More articles:


All Articles