count frequency of values by week using pandas then plot

user_14546482 0
Viewed 774

Lets say I have the following Time Series with an item:

df:
date       item
1/6/2021    apples
1/6/2021    apples
1/6/2021    oranges
1/6/2021    oranges
1/7/2021    oranges
1/8/2021    bananas
2/1/2021    apples
2/1/2021    apples
2/1/2021    apples
2/1/2021    apples
2/2/2021    oranges
2/3/2021    bananas
3/1/2021    apples
3/1/2021    apples
3/1/2021    apples
3/1/2021    apples
3/1/2021    oranges
3/2/2021    oranges
3/3/2021    bananas

I would like to make a count of each item and have it broken out by week then plot the results in a bar graph. week is Saturday to Saturday.

week of     item     count

1/9/2021    apples     2
           oranges     3
           bananas     1
        
2/6/2021    apples     4
            oranges    1
            bananas    1
        
3/6/2021    apples     4
            oranges    2
            bananas    1

I tried the following code:

df.groupby(pd.Grouper(key='date',freq='W')
   for name, group in weekly:
    print(group.item.unique(), group.item.count())

That was able to give me the list of items and the sum of all the items, but Im not sure how to get the sum of each item or plot it.

pandas dataframe datetime python
1 Answers

Try groupby size on both pd.Grouper and item (use Anchored Offset to set Saturday to Saturday weekly):

# Convert date to datetime if not already
df['date'] = pd.to_datetime(df['date'])

new_df = (
    df.groupby([pd.Grouper(key='date', freq='W-SAT'), 'item'])
        .size()
        .to_frame(name='count')
)

new_df:

                    count
date       item          
2021-01-09 apples       2
           bananas      1
           oranges      3
2021-02-06 apples       4
           bananas      1
           oranges      1
2021-03-06 apples       4
           bananas      1
           oranges      2

Then to plot use pivot + plot:

from matplotlib import pyplot as plt
import matplotlib.ticker as ticker

# pivot to wide format
plot_df = (
    new_df.reset_index().pivot(index='date', columns='item', values='count')
)
# Plot Bar
ax = plot_df.plot(kind='bar', rot=0, ylabel='count')
# Format X-axis ticks
ax.xaxis.set_major_formatter(
    ticker.FixedFormatter(plot_df.index.strftime('%Y-%m-%d'))
)
plt.show()

Or straight to plot_df without new_df:

plot_df = (
    df.groupby([pd.Grouper(key='date', freq='W-SAT'), 'item'])
        .size()
        .reset_index(name='count')
        .pivot(index='date', columns='item', values='count')
)

# Plot Bar
ax = plot_df.plot(kind='bar', rot=0, ylabel='count')
# Format X-axis ticks
ax.xaxis.set_major_formatter(
    ticker.FixedFormatter(plot_df.index.strftime('%Y-%m-%d'))
)
plt.show()

plot

Complete Working Example:

import matplotlib.ticker as ticker
import pandas as pd
from matplotlib import pyplot as plt

df = pd.DataFrame({
    'date': ['1/6/2021', '1/6/2021', '1/6/2021', '1/6/2021', '1/7/2021',
             '1/8/2021', '2/1/2021', '2/1/2021', '2/1/2021', '2/1/2021',
             '2/2/2021', '2/3/2021', '3/1/2021', '3/1/2021', '3/1/2021',
             '3/1/2021', '3/1/2021', '3/2/2021', '3/3/2021'],
    'item': ['apples', 'apples', 'oranges', 'oranges', 'oranges', 'bananas',
             'apples', 'apples', 'apples', 'apples', 'oranges', 'bananas',
             'apples', 'apples', 'apples', 'apples', 'oranges', 'oranges',
             'bananas']
})
# Convert date to datetime
df['date'] = pd.to_datetime(df['date'])

# Straight to Plot DF
plot_df = (
    df.groupby([pd.Grouper(key='date', freq='W-SAT'), 'item'])
        .size()
        .reset_index(name='count')
        .pivot(index='date', columns='item', values='count')
)
# Plot Bar
ax = plot_df.plot(kind='bar', rot=0, ylabel='count')
# Format X-axis ticks
ax.xaxis.set_major_formatter(
    ticker.FixedFormatter(plot_df.index.strftime('%Y-%m-%d'))
)
plt.show()
user_154978880
Related