Given a file like this:
date uselessinfo category uselessinfo2
2011-07-22 02:56:36 banana 1 apple
2011-02-27 17:15:44 banana 4 apple
2010-12-12 00:13:42 banana 1 apple
2010-10-12 00:13:00 banana 2 apple
I am using pandas to build a DataFrame:
data = pd.read_table(pathToFile, "\t")
From this I try to use simple pandas methods to build a crosstab matrix like:
1 2 3 4
2010 1 1 0 0
2011 1 0 0 1
Where columns are categories, rows are dates bins and values are the occurrence of categories in these bins
My problem is that I don't know how to bin the datetimes either by years or month and then build the crosstab matrix. I have seen on stackoverflow that the re-sampling function is the best way to bin datetimes and crosstab to then build the matrix:
data = data.resample('M', on='date').sum()
data = pd.crosstab(data.date,data.category)
Is there a way to combine these two functions to get the desired matrix ? Or am I totally doing wrong ?
The goal to achieve is to use this matrix to plot a seaborn heatmap looking like:
