getting unusual line chart on weekly time series data in matplotlib

Question

I have weekly car sales data and I made line charts by different car producers. However, I got little unusual line charts because the days one margin like 12-31-xxxx and 01-01-xxxx still stays in the same weeks, which gave me quite an unexpected plot. How should I make line charts if week numbers sit on the margins? Is there any better ideas to make this right? Can anyone point me out how to correct this? Thanks

reproducible data & my attempt

here is reproducible data on this gist that I used for making line chart

my attempt

import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns

url = 'https://gist.githubusercontent.com/adamFlyn/8a6378ff7c77b1af76d1cb33a018b329/raw/3b38168bd528ef9e7e2bfa1246b91bba1c1dc287/car_sales.csv'
df = pd.read_csv(url, parse_dates=['date'])
# df = df.rename({'Unnamed: 0' : 'date'}, axis=1)
df.drop('Unnamed: 0', axis=1, inplace=True)
df['date']= pd.to_datetime(df['date'])

dfww = df.groupby(['company', 'week'])['product_sales_cnt'].agg(['max', 'min', 'mean']).stack().reset_index(level=[2]).rename(columns={'level_2': 'mm', 0: 'vals'}).reset_index()

for g, d in dfww.groupby('company'):
    fig, ax = plt.subplots(figsize=(7, 4), dpi=144)
    for year in df['year'].unique():
        data = df[(df.date.dt.year == year) & (df.company == g)]
        sns.lineplot(x='week', y='product_sales_cnt', ci=None, data=data, label=year)
plt.ylabel('product_sales_cnt')
plt.margins(x=0)
plt.tight_layout()
plt.grid(True)
plt.show()
plt.close()

current output

here is the one of the current output that I got from the above code snippet:

since the data I used, it is going to be updated weekly base, so the plot also going to be consistent with the data. But now, because of the wrong week's number, resulted in a line chart goes wrong. I think because 12-31 and 01-01 shares the same week number, that causes the problem. How should I fix this up? Any idea?

user_6366770 · Answer

You can use:

df['week'] = np.select([(df['week'] == 53) & (df['date'].dt.month == 1),
                        (df['week'] == 53) & (df['date'].dt.month == 12)],
                        [1, 52], df['week'])

This will ensure that week is either 1 or 52. As shown in the image, 2016 no longer has it's own "Week 53". Full code:

import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns

url = 'https://gist.githubusercontent.com/adamFlyn/8a6378ff7c77b1af76d1cb33a018b329/raw/3b38168bd528ef9e7e2bfa1246b91bba1c1dc287/car_sales.csv'
df = pd.read_csv(url, parse_dates=['date'])
# df = df.rename({'Unnamed: 0' : 'date'}, axis=1)
df.drop('Unnamed: 0', axis=1, inplace=True)
df['date']= pd.to_datetime(df['date'])
df['week'] = np.select([(df['week'] == 53) & (df['date'].dt.month == 1),
                        (df['week'] == 53) & (df['date'].dt.month == 12)],
                        [1, 52], df['week'])
df
dfww = df.groupby(['company', 'week'])['product_sales_cnt'].agg(['max', 'min', 'mean']).stack().reset_index(level=[2]).rename(columns={'level_2': 'mm', 0: 'vals'}).reset_index()
for g, d in dfww.groupby('company'):
    fig, ax = plt.subplots(figsize=(7, 4), dpi=144)
    for year in df['year'].unique():
        data = df[(df.date.dt.year == year) & (df.company == g)]
        sns.lineplot(x='week', y='product_sales_cnt', ci=None, data=data, label=year)
plt.ylabel('product_sales_cnt')
plt.margins(x=0)
plt.tight_layout()
plt.grid(True)
plt.show()
plt.close()

Per Comments:

user_3809172 · Answer

Might not be the perfect solution, but you can convert week number so the first part of the week that continues into new year is 0 by doing this: df['week']=df['date'].dt.strftime('%W').astype('uint8') or '%U' for week start as Sunday

getting unusual line chart on weekly time series data in matplotlib

2 Answers