Python: GroupBy DataFrame and add tally

Asked Sep 12 '22 at 20:28

Active Sep 12 '22 at 20:41

Viewed 41 times

I have a dataframe that looks like this:

df:
date         symbol    constituents
01-01-2022   SPY       AAPL
01-01-2022   SPY       MSFT
01-01-2022   SPY       TSLA
01-01-2022   GLD       AEM
01-01-2022   GLD       ABX
01-01-2022   GLD       KL
01-01-2022   GLD       WPM

I want to apply a tally using the groupby function to the DataFrame to get the following result

df:
ticker              AAPL   MSFT   TSLA   AEM  ABX  KL   WPM
date        symbol
01-01-2022  SPY     1      1      1      0    0    0    0
            GLD     0      0      0      0    0    0    0

I have tried the following df = d=df.groupby(["date", "symbol","constituents"])["constituents"].count() but it is not quite the shape I am looking for.

asked Sep 12 '22 at 20:28

MathMan 99

Why the row `GLD` is all zeros (`0`) ? – Andrej Kesely Sep 12 '22 at 20:33
Note that you can use `sum` instead of `max` in the duplicate to have counts instead of dummies. `pivot_table` would also work. – mozway Sep 12 '22 at 20:40
please look at `df.pivot()` or `df.pivot_table()` – Alex Sep 12 '22 at 20:40
`df.pivot` won't work because of the duplicate values in date. Note that I want the date as index too. – MathMan 99 Sep 12 '22 at 20:46

Python: GroupBy DataFrame and add tally

0 Answers0