Plotting row based data

I have some data that looks something like this:

df =

id      period    15-nov    16-nov    17-nov    18-nov
------------------------------------------------------
vodka   night     1         2         3         1
vodka   day       0         1         2         0
vodka   evening   1         2         2         0
rum     night     1         2         3         3
rum     day       1         2         2         0
rum     evening   1         3         2         1

What I would like to do is to plot the values of the header dates, but grouped by id and period. So essentially this would become 6 line plots, with the x-axis given as the dates.

However, maybe I’m just tired, but this data set is weirdly put together imo, so my usual groupby stuff doesn’t work because only some of the headers are the x-axis values. I was thinking about transforming the date columns into one single column, and then adding the date headers as another column giving me 4 total columns – but more rows, and then use the groupby function. But I can’t think of any clever way to do this, accept maybe doing it kind of manually.

So is that the way for me to go, or am I missing an easy transformation trick ?

Answer

You could unpivot your dataframe using melt and then use seaborn to plot the lines:

import seaborn as sns

df_melted = df.melt(value_vars=['15-nov', '16-nov', '17-nov', '18-nov'], 
                    id_vars=['id', 'period'], 
                    var_name='date'
                   )

sns.lineplot(data=df_melted.sort_values('date'), 
             x='date', y='value', 
             hue=df_melted['id'] + '_' + df_melted['period']
            )

Output: