Visualize lineplot with count of a column

May 23, 2022

I have a problem. I have two columns toDate and fromDate. I want to visualize the difference of them in a linechart. The x-axis should be the month e.g. (1,2,3,4, etc.) and the y-axis should be the count of the month. And finally this should be hued by what. Unfortunately I did not get the wished output.

Dataframe

    id  toDate  fromDate
0   1   2021-03-22T18:59:59Z    2021-02-22
1   2   None    2021-03-18
2   3   2021-04-22T18:59:59Z    2021-03-22
3   4   2021-02-15T18:59:59Z    2021-02-10
4   5   2021-09-15T18:59:59Z    2021-09-07
5   6   2020-01-12T18:59:59Z    None
6   7   2022-02-22T18:59:59Z    2022-01-18

Code

import pandas as pd
import seaborn as sns
d = {'id': [1, 2, 3, 4, 5, 6, 7],
     'toDate': ['2021-03-22T18:59:59Z', None, '2021-04-22T18:59:59Z', 
'2021-02-15T18:59:59Z', '2021-09-15T18:59:59Z', '2020-01-12T18:59:59Z', '2022-02-22T18:59:59Z'],
     'fromDate': ['2021-02-22', '2021-03-18', '2021-03-22', 
'2021-02-10', '2021-09-07', None, '2022-01-18']
    }
df = pd.DataFrame(data=d)
display(df)
df_new = pd.DataFrame()
df_new['toDate_month']  = pd.to_datetime(df['toDate'], errors='coerce').dt.month
df_new['fromDate_month']  = pd.to_datetime(df['fromDate'], errors='coerce').dt.month

df_new.melt(var_name='what', value_name='month')

What I want

sns.lineplot(data=df_new, x="month", y="month".value_counts(), hue="what")

>Solution :

IIUC, you can pass a pandas.crosstab DataFrame to sns.lineplot which will handle a wide form as "x" for the index and "hue" for the columns:

sns.lineplot(data=pd.crosstab(df_new['month'], df_new['what']))

output:

crosstab:

what   fromDate_month  toDate_month
month                              
1.0                 1             1
2.0                 2             2
3.0                 2             1
4.0                 0             1
9.0                 1             1