Suppose my data look as follows:
import datetime
import pandas as pd
df = pd.DataFrame({'datetime': [datetime.datetime(2024, 11, 27, 0), datetime.datetime(2024, 11, 27, 1), datetime.datetime(2024, 11, 28, 0),
datetime.datetime(2024, 11, 28, 1), datetime.datetime(2024, 11, 28, 2)],
'product': ['Apple', 'Banana', 'Banana', 'Apple', 'Banana']})
datetime product
0 2024-11-27 00:00:00 Apple
1 2024-11-27 01:00:00 Banana
2 2024-11-28 00:00:00 Banana
3 2024-11-28 01:00:00 Apple
4 2024-11-28 02:00:00 Banana
All I want is to plot the relative frequencies of the products sold at each day. In this example 1/2 (50%) of apples and 1/2 of bananas on day 2024-11-27. And 1/3 apples and 2/3 bananas on day 2024-11-28
What I managed to do:
absolute_frequencies = df.groupby([pd.Grouper(key='datetime', freq='D'), 'product']).size().reset_index(name='count')
total_counts = absolute_frequencies.groupby('datetime')['count'].transform('sum')
absolute_frequencies['relative_frequency'] = absolute_frequencies['count'] / total_counts
absolute_frequencies.pivot(index='datetime', columns='product', values='relative_frequency').plot()
I am pretty confident, there is a much less complicated way, since for the absolute frequencies I simply can use:
df.groupby([pd.Grouper(key='datetime', freq='D'), 'product']).size().unstack('product').plot(kind='line')
>Solution :
You can use a crosstab with normalize:
ct = pd.crosstab(df['datetime'].dt.normalize(), df['product'], normalize='index')
Output:
product Apple Banana
datetime
2024-11-27 0.500000 0.500000
2024-11-28 0.333333 0.666667
As a graph:
ct.plot.bar()
Output:
