Pandas: Accumulated Shares Holdings per Ticker per Day from a List of Trades

Question

I have a pd.DataFrame (pandas.core.frame.DataFrame) with some stock trades.

data = {'Date': ['2021-01-15', '2021-01-21', '2021-02-28', '2021-01-30', '2021-02-16', '2021-03-22', '2021-01-08', '2021-03-02', '2021-02-25', '2021-04-04', '2021-03-15', '2021-04-08'], 'Ticker': ['MFST', 'AMZN', 'GOOG', 'AAPL','MFST', 'AMZN', 'GOOG', 'AAPL','MFST', 'AMZN', 'GOOG', 'AAPL'], 'Quantity': [2,3,7,2,6,4,-3,8,-2,9,11,1]}  
df = pd.DataFrame(data)

          Date Ticker  Quantity
0   2021-01-15   MFST         2
1   2021-01-21   AMZN         3
2   2021-02-28   GOOG         7
3   2021-01-30   AAPL         2
4   2021-02-16   MFST         6
5   2021-03-22   AMZN         4
6   2021-01-08   GOOG        -3
7   2021-03-02   AAPL         8
8   2021-02-25   MFST        -2
9   2021-04-04   AMZN         9
10  2021-03-15   GOOG        11
11  2021-04-08   AAPL         1

Quantity refers to the number of shares bought.

I am looking for an efficient way to create a new df which contains the number of shares for each Ticker per day.

The first trade was on 2021-01-08 and the last on 2021-04-08. I want a new dataframe that contains all days between those to dates as rows and the tickers as columns. Values shall be the number of shares I hold at a specific day. Hence, if I buy 4 shares of a stock at 2021-03-15 (assuming no further buying or selling) I will have them from 2021-03-15 till 2021-04-08 which should be represented as a 4 in every row for this specific ticker. If I decide to buy more shares this number will change on that day and all following days.

Could be something like this:

         Date   MFST  AMZN    GOOG    APPL
  2021-01-08      2     3       1       0
  2021-01-09      2     3       1       0
  2021-01-10      2     3       1       0

       ...

  2021-04-08      2     3       1       7

My first guess was to create an empty DataFrame and then iterate with two for loops over all its Dates and Tickers. However, I think that is not the most efficient way. I am thankful for any recommendation!

user_15960341 · Answer

Use df.groupby

df.groupby(['Date']).agg('sum')

user_15070697 · Answer

You can use df.pivot() to transform your data into a tabular form, as shown on the expected output layout, as follows:

df.pivot(index='Date', columns='Ticker', values='Quantity').rename_axis(columns=None).reset_index().fillna(0, downcast='infer')

If you need to aggregate Quantity for same date for each stock, you can use df.pivot_table() with parameter aggfunc='sum', as follows:

df.pivot_table(index='Date', columns='Ticker', values='Quantity', aggfunc='sum').rename_axis(columns=None).reset_index().fillna(0, downcast='infer')

Result:

         Date  AAPL  AMZN  GOOG  MFST
0  2021-01-21     0     3     0     0
1  2021-02-28     0     0     1     0
2  2021-03-15     0     0     0     2
3  2021-04-30     7     0     0     0

Additional Test Case:

To showcase the aggregation function of df.pivot_table(), I have added some data as follows:

data = {'Date': ['2021-03-15',
  '2021-01-21',
  '2021-01-21',
  '2021-02-28',
  '2021-02-28',
  '2021-04-30',
  '2021-04-30'],
 'Ticker': ['MFST', 'AMZN', 'AMZN', 'GOOG', 'GOOG', 'AAPL', 'AAPL'],
 'Quantity': [2, 3, 4, 1, 2, 7, 2]}

 df = pd.DataFrame(data)


         Date Ticker  Quantity
0  2021-03-15   MFST         2
1  2021-01-21   AMZN         3
2  2021-01-21   AMZN         4
3  2021-02-28   GOOG         1
4  2021-02-28   GOOG         2
5  2021-04-30   AAPL         7
6  2021-04-30   AAPL         2


df.pivot_table(index='Date', columns='Ticker', values='Quantity', aggfunc='sum').rename_axis(columns=None).reset_index().fillna(0, downcast='infer')


         Date  AAPL  AMZN  GOOG  MFST
0  2021-01-21     0     7     0     0
1  2021-02-28     0     0     3     0
2  2021-03-15     0     0     0     2
3  2021-04-30     9     0     0     0

Edit

Based on latest requirement:

The first trade was on 2021-03-15 and the last on 2021-04-30. I want a new dataframe that contains all days between those to dates as rows and the tickers as columns. Values shall be the number of shares I hold at a specific day. Hence, if I buy 4 shares of a stock at 2021-03-15 (assuming no further buying or selling) I will have them from 2021-03-15 till 2021-04-30 which should be represented as a 4 in every row for this specific ticker. If I decide to buy more shares this number will change on that day and all following days.

Here is the enhanced solution:

data = {'Date': ['2021-01-15', '2021-01-21', '2021-02-28', '2021-01-30', '2021-02-16', '2021-03-22', '2021-01-08', '2021-03-02', '2021-02-25', '2021-04-04', '2021-03-15', '2021-04-08'], 'Ticker': ['MFST', 'AMZN', 'GOOG', 'AAPL','MFST', 'AMZN', 'GOOG', 'AAPL','MFST', 'AMZN', 'GOOG', 'AAPL'], 'Quantity': [2,3,7,2,6,4,-3,8,-2,9,11,1]}  
df = pd.DataFrame(data)


df['Date'] = pd.to_datetime(df['Date'])
df = df.sort_values('Date')

df1 = df.set_index('Date').asfreq('D')
df1['Ticker'] = df1['Ticker'].ffill().bfill()
df1['Quantity'] = df1['Quantity'].fillna(0)

df2 = df1.pivot_table(index='Date', columns='Ticker', values='Quantity', aggfunc='sum').rename_axis(columns=None).reset_index().fillna(0, downcast='infer')
df3 = df2[['Date']].join(df2.iloc[:,1:].cumsum())

Result:

print(df3)


         Date  AAPL  AMZN  GOOG  MFST
0  2021-01-08     0     0    -3     0
1  2021-01-09     0     0    -3     0
2  2021-01-10     0     0    -3     0
3  2021-01-11     0     0    -3     0
4  2021-01-12     0     0    -3     0
5  2021-01-13     0     0    -3     0
6  2021-01-14     0     0    -3     0
7  2021-01-15     0     0    -3     2
8  2021-01-16     0     0    -3     2
9  2021-01-17     0     0    -3     2
10 2021-01-18     0     0    -3     2
11 2021-01-19     0     0    -3     2
12 2021-01-20     0     0    -3     2
13 2021-01-21     0     3    -3     2
14 2021-01-22     0     3    -3     2
15 2021-01-23     0     3    -3     2
16 2021-01-24     0     3    -3     2
17 2021-01-25     0     3    -3     2
18 2021-01-26     0     3    -3     2
19 2021-01-27     0     3    -3     2
20 2021-01-28     0     3    -3     2
21 2021-01-29     0     3    -3     2
22 2021-01-30     2     3    -3     2
23 2021-01-31     2     3    -3     2
24 2021-02-01     2     3    -3     2
25 2021-02-02     2     3    -3     2
26 2021-02-03     2     3    -3     2
27 2021-02-04     2     3    -3     2
28 2021-02-05     2     3    -3     2
29 2021-02-06     2     3    -3     2
30 2021-02-07     2     3    -3     2
31 2021-02-08     2     3    -3     2
32 2021-02-09     2     3    -3     2
33 2021-02-10     2     3    -3     2
34 2021-02-11     2     3    -3     2
35 2021-02-12     2     3    -3     2
36 2021-02-13     2     3    -3     2
37 2021-02-14     2     3    -3     2
38 2021-02-15     2     3    -3     2
39 2021-02-16     2     3    -3     8
40 2021-02-17     2     3    -3     8
41 2021-02-18     2     3    -3     8
42 2021-02-19     2     3    -3     8
43 2021-02-20     2     3    -3     8
44 2021-02-21     2     3    -3     8
45 2021-02-22     2     3    -3     8
46 2021-02-23     2     3    -3     8
47 2021-02-24     2     3    -3     8
48 2021-02-25     2     3    -3     6
49 2021-02-26     2     3    -3     6
50 2021-02-27     2     3    -3     6
51 2021-02-28     2     3     4     6
52 2021-03-01     2     3     4     6
53 2021-03-02    10     3     4     6
54 2021-03-03    10     3     4     6
55 2021-03-04    10     3     4     6
56 2021-03-05    10     3     4     6
57 2021-03-06    10     3     4     6
58 2021-03-07    10     3     4     6
59 2021-03-08    10     3     4     6
60 2021-03-09    10     3     4     6
61 2021-03-10    10     3     4     6
62 2021-03-11    10     3     4     6
63 2021-03-12    10     3     4     6
64 2021-03-13    10     3     4     6
65 2021-03-14    10     3     4     6
66 2021-03-15    10     3    15     6
67 2021-03-16    10     3    15     6
68 2021-03-17    10     3    15     6
69 2021-03-18    10     3    15     6
70 2021-03-19    10     3    15     6
71 2021-03-20    10     3    15     6
72 2021-03-21    10     3    15     6
73 2021-03-22    10     7    15     6
74 2021-03-23    10     7    15     6
75 2021-03-24    10     7    15     6
76 2021-03-25    10     7    15     6
77 2021-03-26    10     7    15     6
78 2021-03-27    10     7    15     6
79 2021-03-28    10     7    15     6
80 2021-03-29    10     7    15     6
81 2021-03-30    10     7    15     6
82 2021-03-31    10     7    15     6
83 2021-04-01    10     7    15     6
84 2021-04-02    10     7    15     6
85 2021-04-03    10     7    15     6
86 2021-04-04    10    16    15     6
87 2021-04-05    10    16    15     6
88 2021-04-06    10    16    15     6
89 2021-04-07    10    16    15     6
90 2021-04-08    11    16    15     6

Pandas: Accumulated Shares Holdings per Ticker per Day from a List of Trades

2 Answers

Edit