Line plot for time series of grouped data in pandas

I have monthly data for customers belonging to separate categories. I would like to display line plot of the time series grouped by customer category

Here is a snapshot containing data ("volume") over 4 consecutive months for 3 customers belonging to 2 categories

df = pd.DataFrame({'cust_id': [1, 1, 1, 1, 2, 2, 2, 2, 3, 3, 3, 3],
                   'month': [200101, 200102, 200103, 200104, 200101, 200102, 200103, 200104, 200101, 200102, 200103, 200104],
                   'volume': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12],
                   'category': [1, 1, 1, 1, 2, 2, 2, 2, 2, 2, 2, 2]})

How to plot a line plot (time period on the x axe) displaying the average monthly volume for each of these 2 groups?

What about using a groupby.mean and seaborn.lineplot:

import seaborn as sns
import matplotlib.dates as mdates

# optional: to ensure having a categorical palette
df['category'] = df['category'].astype('category')

tmp = (df.groupby(['category', pd.to_datetime(df['month'], format='%Y%m')])
       ['volume'].mean().reset_index(name='average volume')
      )

ax = sns.lineplot(data=tmp, x='month', y='average volume', hue='category')

# change labels
ax.tick_params(axis='x', rotation=45)
ax.xaxis.set_major_formatter(mdates.DateFormatter('%Y-%m'))

Output:

Changing the plot width:

import matplotlib.pyplot as plt

# ...

fig, ax = plt.subplots(figsize=(15, 8))
sns.lineplot(data=tmp, x='month', y='average volume', hue='category', ax=ax)

# ...

Output:

pivot_table offers great control in very few words over the desired aggregate shape.

Your requested time series:

import pandas as pd
import numpy as np

TS = pd.pivot_table(data    = df,
                    values  = ['volume'],
                    index   = ['month'],
                    columns = ['category'],
                    aggfunc = np.mean)

Output:

         volume    
category      1   2
month              
200101        1   7
200102        2   8
200103        3   9
200104        4  10

Indeed, prior conversion to datetime always preferrable, as suggested by other respondants. Upvoted mozway for this:

df['month'] = pd.to_datetime(df['month'], format='%Y%m')

Plot is as expected; add any ornaments you like.

TS.plot(figsize=(24,8), 
        ylabel = 'Monthly average volume')

This code will create a line plot with the months on the x-axis, the volume on the y-axis, and each line corresponding to a customer category. The legend will show the customer category associated with each line.

<!-- begin snippet: js hide: false console: true babel: false -->

<!-- language: lang-python -->

import pandas as pd
import matplotlib.pyplot as plt

# Your DataFrame
df = pd.DataFrame({'cust_id': [1, 1, 1, 1, 2, 2, 2, 2, 3, 3, 3, 3],
                   'month': [200101, 200102, 200103, 200104, 200101, 200102, 200103, 200104, 200101, 200102, 200103, 200104],
                   'volume': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12],
                   'category': [1, 1, 1, 1, 2, 2, 2, 2, 2, 2, 2, 2]})

# Convert the 'month' column to a pandas datetime format for proper sorting
df['month'] = pd.to_datetime(df['month'], format='%y%m%d')

# Group the data by 'category' and 'cust_id' and then pivot it to have 'category' as columns
grouped_data = df.groupby(['category', 'cust_id', 'month'])['volume'].sum().unstack(level=0)

# Plotting the data
plt.figure(figsize=(10, 6))
for category in grouped_data.columns:
    plt.plot(grouped_data.index, grouped_data[category], label=f'Category {category}')

plt.xlabel('Month')
plt.ylabel('Volume')
plt.title('Time Series of Volume Grouped by Customer Category')
plt.legend()
plt.show()

<!-- end snippet -->