Pandas DataFrame to Seaborn

Question

I am trying to make marine thermal insulation using pandas DataFrame. The format of my data is below

visit_table

   yyyymm  visit_cnt
0  201101      91252
1  201102     140571
2  201103     141457
3  201104     147680
4  201105     154066
...

68  201609     591242
69  201610     650174
70  201611     507579
71  201612     465218

How can I change a DataFrame to a sea data format as shown below

  2011       2012     2013   2015

1     91252
2     14057
3     147680
4     154066
...
11    123455
12    1234456

Answer 1

You can use to_datetimeto convert a column yyyymmand then create a new one Series(columns) with dt.monthand dt.year. Latest update pivotand replace NaNon 0to fillnaif necessary.

df['yyyymm'] = pd.to_datetime(df['yyyymm'], format='%Y%m')
df1 = pd.pivot(index=df['yyyymm'].dt.month, columns=df['yyyymm'].dt.year, values=df.visit_cnt)
       .fillna(0)
print (df1)
yyyymm      2011      2016
yyyymm                    
1        91252.0       0.0
2       140571.0       0.0
3       141457.0       0.0
4       147680.0       0.0
5       154066.0       0.0
9            0.0  591242.0
10           0.0  650174.0
11           0.0  507579.0
12           0.0  465218.0

Another solution seems to be just a change set_indexand unstack:

df['yyyymm'] = pd.to_datetime(df['yyyymm'], format='%Y%m')
df['year'] = df['yyyymm'].dt.year
df['month'] = df['yyyymm'].dt.month
df1 = df.set_index(['month','year'])['visit_cnt'].unstack(fill_value=0)
print (df1)
year     2011    2016
month                
1       91252       0
2      140571       0
3      141457       0
4      147680       0
5      154066       0
9           0  591242
10          0  650174
11          0  507579
12          0  465218

Finally use seaborn.heatmap:

import seaborn as sns
ax = sns.heatmap(df1)