How to sum by > date for a range of dates and append them to a new column in a new dataframe in pandas?

user_9621377 0
Viewed 83

I have a dataset that resembles the following:

abbrev    amount    maturity
acct1     100       6/2/2019
acct1     500       6/3/2019
acct1     1100      6/5/2019
acct2     200       6/3/2019
acct3     1000      6/4/2019
acct4     2500      6/5/2019

What I am trying to do is have a list or date range. I tried the following:

accountDates = pd.date_range(start='6/1/2019', end='6/5/2019')

Then I want to loop through the date range one-by-one and get sums when the maturity is > than the date. For instance, if maturity > '6/1/2019', acct1 would sum up to 1200. Ideally then I'd like to have this information stored in a new dataframe that looks like this:

abbrev    6/1/2019    6/2/2019    6/3/2019    6/4/2019    6/5/2019
acct1     1700        1600        1100        1100        0
acct2     200         200         0           0           0
acct3     1000        1000        1000        0           0
acct4     2500        2500        2500        2500        0

But I am struggling with how the mechanics of the loop would work and if I'd just be able to use a for loop with the aggregate sum function in it and have it go through the dates, but then how would it store a single dates results and append a new column to the new dataframe?

Edit: Added some more information to the dataset to make clearer (hopefully) the use case (see: acct1) where I want it to always sum everything in a given account that has line items with maturity greater than the date it's testing from the date range list.

pandas dataframe python loops
3 Answers

you can use pivot_table, then reindex with your list of dates reversed and use cumsum plus shift to get the sum shifted of one day. Then reindex again with the list of dates in the correct order.

print (df.pivot_table(values='amount', index='abbrev', 
                      columns='maturity', aggfunc=sum, fill_value=0)
          .reindex(columns=accountDates[::-1])
          .cumsum(axis=1)
          .shift(axis=1)
          .reindex(columns=accountDates)
          .fillna(0))
       2019-06-01  2019-06-02  2019-06-03  2019-06-04  2019-06-05
abbrev                                                            
acct1       1700.0      1600.0      1100.0      1100.0         0.0
acct2        200.0       200.0         0.0         0.0         0.0
acct3       1000.0      1000.0      1000.0         0.0         0.0
acct4       2500.0      2500.0      2500.0      2500.0         0.0
user_92747320

You need groupby and then pivot_table

df['maturity'] = df['maturity'] - pd.Timedelta(days=1)
mdata = df.groupby(['maturity', 'abbrev']).sum().reset_index(1).reindex(accountDates)
pdf = pd.pivot_table(mdata.dropna(), index='abbrev', columns=mdata.dropna().index).bfill(1).fillna(0)

Output:

       amount                                 
       2019-06-01 2019-06-02 2019-06-03 2019-06-04
abbrev                                            
acct1      1200.0        0.0        0.0        0.0
acct2       200.0      200.0        0.0        0.0
acct3      1000.0     1000.0     1000.0        0.0
acct4      2500.0     2500.0     2500.0     2500.0
user_96094470

Another take using join and pivot:

(df[['abbrev','maturity']].join(df.sort_values(['abbrev','maturity'], ascending=False)
                                  .groupby(['abbrev'])
                                  .cumsum())
                          .pivot(index='abbrev', columns='maturity', values='amount')
                          .reindex(columns=accountDates)
                          .shift(-1, axis=1)
                          .bfill(1)
                          .fillna(0))
user_85610570
Related