Summing $ amounts of first time customers in a time series

user_9234450 0
Viewed 79

I have a dataframe that sums the $ amount of money that a customer has in their account during a particular month. If the customer has no money, the amount is simply 0. The dataframe looks like the below:

            A       B       C       D       E        F   
11/30/2015  0       1000    0       0       5000     0   
12/31/2015  2000    1000    0       3000    5000     2000
1/31/2016   2000    0       0       3000    5000     2000
2/29/2016   2000    2000    4000    3000    5000     2000
3/31/2016   2000    2000    4000    0       10000    2000
4/30/2016   0       2000    4000    0       10000    0   
5/31/2016   0       2000    4000    0       10000    0

When a customer first becomes live, they go from having 0 to having a nominal amount for the particular month (or they begin with a nominal amount in Nov). Therefore when a particular customer has their first nominal amount, this is the month in which they are "new".

I want to add a column at the end of the dataframe which sums the amount for "new" customers.

I have been able to count the number of "new" customers (see code below) but I can't alter the code to sum the values.

def new_customer(column):
    return column[-1] and not any(column[:-1])
table['new_loans'] = table.iloc[:, len(table.columns)].expanding().apply(new_customer).sum(axis=1).astype(int)

The resulting dataframe should look like this:

            A       B       C       D       E        F       New_Customers 
11/30/2015  0       1000    0       0       5000     0       6000 
12/31/2015  2000    1000    0       3000    5000     2000    7000
1/31/2016   2000    0       0       3000    5000     2000    0
2/29/2016   2000    2000    4000    3000    5000     2000    4000
3/31/2016   2000    2000    4000    0       10000    2000    0
4/30/2016   0       2000    4000    0       10000    0       0
5/31/2016   0       2000    4000    0       10000    0       0
pandas python
1 Answers

Use:

df['New_Customers'] = df.where(df.ne(0).cumsum().eq(1)).sum(axis=1)
print (df)
               A     B     C     D      E     F  New_Customers
11/30/2015     0  1000     0     0   5000     0         6000.0
12/31/2015  2000  1000     0  3000   5000  2000         7000.0
1/31/2016   2000     0     0  3000   5000  2000            0.0
2/29/2016   2000  2000  4000  3000   5000  2000         4000.0
3/31/2016   2000  2000  4000     0  10000  2000            0.0
4/30/2016      0  2000  4000     0  10000     0            0.0
5/31/2016      0  2000  4000     0  10000     0            0.0

Explanation:

First compare by DataFrame.ne (!=) with 0:

print (df.ne(0))
                A      B      C      D     E      F
11/30/2015  False   True  False  False  True  False
12/31/2015   True   True  False   True  True   True
1/31/2016    True  False  False   True  True   True
2/29/2016    True   True   True   True  True   True
3/31/2016    True   True   True  False  True   True
4/30/2016   False   True   True  False  True  False
5/31/2016   False   True   True  False  True  False

Cumulative sum of boolean mask by DataFrame.cumsum:

print (df.ne(0).cumsum())
            A  B  C  D  E  F
11/30/2015  0  1  0  0  1  0
12/31/2015  1  2  0  1  2  1
1/31/2016   2  2  0  2  3  2
2/29/2016   3  3  1  3  4  3
3/31/2016   4  4  2  3  5  4
4/30/2016   4  5  3  3  6  4
5/31/2016   4  6  4  3  7  4

Compare by 1 with DataFrame.eq (==) - first 1:

print (df.ne(0).cumsum().eq(1))
                A      B      C      D      E      F
11/30/2015  False   True  False  False   True  False
12/31/2015   True  False  False   True  False   True
1/31/2016   False  False  False  False  False  False
2/29/2016   False  False   True  False  False  False
3/31/2016   False  False  False  False  False  False
4/30/2016   False  False  False  False  False  False
5/31/2016   False  False  False  False  False  False

Repalce values to NaNs by DataFrame.where:

print (df.where(df.ne(0).cumsum().eq(1)))
                 A       B       C       D       E       F
11/30/2015     NaN  1000.0     NaN     NaN  5000.0     NaN
12/31/2015  2000.0     NaN     NaN  3000.0     NaN  2000.0
1/31/2016      NaN     NaN     NaN     NaN     NaN     NaN
2/29/2016      NaN     NaN  4000.0     NaN     NaN     NaN
3/31/2016      NaN     NaN     NaN     NaN     NaN     NaN
4/30/2016      NaN     NaN     NaN     NaN     NaN     NaN
5/31/2016      NaN     NaN     NaN     NaN     NaN     NaN

Last sum per columns:

print (df.where(df.ne(0).cumsum().eq(1)).sum(axis=1))
11/30/2015    6000.0
12/31/2015    7000.0
1/31/2016        0.0
2/29/2016     4000.0
3/31/2016        0.0
4/30/2016        0.0
5/31/2016        0.0
dtype: float64
user_29010020
Related