Using Pandas Groupby And Size()/count() To Generate An Aggregated Dataframe

So I currently have a DataFrame called df that goes: date tag 2011-02-18 12:57:00-07:00 A 2011-02-19 12:57:00-07:00 A 2011-03-18 12:57:00-07:00 B 2011-04-0

Solution 1:

you could use unstack() and fillna() methods:

>>> g = df.groupby([["%s-%s" % (d.year, d.month) for d in df.date], df.tag]).size()
>>> g
        tag
2011-2  A      2
2011-3  B      1
2011-4  C      1
2011-5  Z      1
2011-6  A      2
dtype: int64
>>> g.unstack().fillna(0)
tag     A  B  C  Z
2011-2  2  0  0  0
2011-3  0  1  0  0
2011-4  0  0  1  0
2011-5  0  0  0  1
2011-6  2  0  0  0