I have a pretty specific problem that I sadly can not work my mind around. The DataFrame I want to transform currently looks like this:
df_larceny
CATEGORY INCIDENTYEAR INCIDENTMONTH
LARCENY 2009 1
LARCENY 2009 1
LARCENY 2009 1
.............................
.............................
LARCENY 2016 11
LARCENY 2016 12
LARCENY 2016 12
LARCENY 2016 12
after the Transformation it should look like this.
COUNT INCIDENTYEAR INCIDENTMONTH
234 2009 1
453 2009 2
847 2009 3
943 2009 4
958 2009 5
.............................
.............................
324 2016 11
372 2016 12
241 2016 12
412 2016 12
basically I want to count how often Larceny occurred for every month of every year.
I tried this tutorial before, sadly without any luck.
I also tried various methods with value_counts() but sadly with no luck.
Out of pure despair at the end I did it manually for another DataFrame which kind of looked like this
jan09 = df["CATEGORY"].loc['2009-01-01':'2009-02-01'].value_counts().sum()
jan10 = df["CATEGORY"].loc['2010-01-01':'2010-02-01'].value_counts().sum()
jan11 = df["CATEGORY"].loc['2011-01-01':'2011-02-01'].value_counts().sum()
jan12 = df["CATEGORY"].loc['2012-01-01':'2012-02-01'].value_counts().sum()
jan13 = df["CATEGORY"].loc['2013-01-01':'2013-02-01'].value_counts().sum()
jan14 = df["CATEGORY"].loc['2014-01-01':'2014-02-01'].value_counts().sum()
jan15 = df["CATEGORY"].loc['2015-01-01':'2015-02-01'].value_counts().sum()
jan16 = df["CATEGORY"].loc['2016-01-01':'2016-02-01'].value_counts().sum()
jan_df = [jan09,jan10,jan11,jan12,jan13,jan14,jan15,jan16]`
I did this for every month and created a new DataFrame at the end which even for an amateur like me looks like way too inefficient. I hope anyone can help me out here.
