python - Iterate Between Dates Based on Value and Pull forward Quantity from Last Date

Question

I have a data frame as follows:

import pandas as pd

#Data

data = {'Symbol':['MU', 'F', 'F', 'BX', 'BX', 'GE', 'BX', 'MU'], 
        'Date':['2018-08-20', '2018-08-21', '2018-08-22', '2018-08-24', '2018-08-25', '2018-08-27', '2018-08-27', '2018-08-27'],
        'Quantity':[28, 30, 30, 3, 3, 5, 4, -28]} 
  
# Create DataFrame 
df = pd.DataFrame(data) 
  
# Print the output. 
df 

    Symbol  Date    Quantity
0   MU  2018-08-20  28
1   F   2018-08-21  30
2   F   2018-08-22  30
3   BX  2018-08-24  3
4   BX  2018-08-25  3
5   GE  2018-08-27  5
6   BX  2018-08-27  4
7   MU  2018-08-27  -28

I used the following code to create a cumulative cum of the quantity column by symbol on each date:

df1 = df.groupby(by=['Symbol','Date'])['Quantity'].sum().groupby(level='Symbol').cumsum().reset_index(name='Cumsum')
print (df1)

  Symbol        Date  Cumsum
0     BX  2018-08-24       3
1     BX  2018-08-25       6
2     BX  2018-08-27      10
3      F  2018-08-21      30
4      F  2018-08-22      60
5     GE  2018-08-27       5
6     MU  2018-08-20      28
7     MU  2018-08-27       0

Now for each symbol, I want to list all the dates between the start date and the end date (or today if still held) and pull forward the last quantity on each date. The data would then look like this:

Symbol  Date    Quantity
BX  2018-08-24  3
BX  2018-08-25  6
BX  2018-08-26  6
BX  2018-08-27  10
F   2018-08-21  30
F   2018-08-22  60
F   2018-08-23  60
F   2018-08-24  60
F   2018-08-25  60
F   2018-08-26  60
F   2018-08-27  60
GE  2018-08-27  5
MU  2018-08-20  28
MU  2018-08-21  28
MU  2018-08-22  28
MU  2018-08-23  28
MU  2018-08-24  28
MU  2018-08-25  28
MU  2018-08-26  28
MU  2018-08-27  0

How would I go about doing this?

question from:https://stackoverflow.com/questions/65895247/iterate-between-dates-based-on-value-and-pull-forward-quantity-from-last-date

Answer 1

I had already drafted a solution when you decided to delete your question the previous time :).

I think you first need to create combinations and then merge /map it to the original dataframe and populate the values then forward fill the cumsum values.

First convert to Date from string

df['Date'] = pd.to_datetime(df['Date']) #ignore if dtype is already datetime

---

Then:

dates = pd.date_range(df['Date'].min(),df['Date'].max(),freq='D')
comb = pd.MultiIndex.from_product((df['Symbol'].unique(),dates))

out = df.assign(cum_quant=df.groupby("Symbol")['Quantity'].cumsum()).merge(
pd.DataFrame(comb.tolist(),columns=['Symbol','Date']),on=['Symbol','Date'],how='right')

out = out.assign(Quantity=out.sort_values("Date").groupby("Symbol")['cum_quant'].ffill()
          ).dropna(subset=['Quantity']).drop("cum_quant",1)

This will give you the results, however since in the last question your expected output reserved the order of the Symbols, you can use pd.Categorical just to ensure the order. If not required, you can ignore this block.

cat_sym = pd.Categorical(out['Symbol'],categories=df['Symbol'].unique(),ordered=True)
out = out.assign(Symbol=cat_sym).sort_values(['Date','Symbol']).reset_index(drop=True)

---

print(out)

   Symbol       Date  Quantity
0      MU 2018-08-20      28.0
1      MU 2018-08-21      28.0
2       F 2018-08-21      30.0
3      MU 2018-08-22      28.0
4       F 2018-08-22      60.0
5      MU 2018-08-23      28.0
6       F 2018-08-23      60.0
7      MU 2018-08-24      28.0
8       F 2018-08-24      60.0
9      BX 2018-08-24       3.0
10     MU 2018-08-25      28.0
11      F 2018-08-25      60.0
12     BX 2018-08-25       6.0
13     MU 2018-08-26      28.0
14      F 2018-08-26      60.0
15     BX 2018-08-26       6.0
16     MU 2018-08-27       0.0
17      F 2018-08-27      60.0
18     BX 2018-08-27      10.0
19     GE 2018-08-27       5.0