Tabulate data frame with groupby and size methods

Question

I have a Pandas dataframe, out, that I am computing counts on by the columns, city and raingarden using the following series:

out.groupby(['city','raingarden']).size() with the output

city raingarden 55405 True 3 Edina True 7 MInneapolis True 8 Minneapolis False 2482 True 847 Minneapolis False 2 True 1 Minneapolis, True 1 Minneapolis, False 2 Minneapolsi False 5 True 3 Minnepolis False 4 Minnespolis False 4 Minnetonka False 1 True 2 Minnneapolis False 5 Mpla True 3 Mpls False 22 True 20 Mpls. False 8 True 17 NE Mpls True 6 Richfield True 1 SLP True 3 St Louis Park True 2 St. Louis Park False 1 Victoria False 1 Wayzata False 2 True 1 minneapolis False 3 mpls True 2 dtype: int64

I want to take this and output it to a tabulate table.

To do this, I did the following:

headers = ['city','has rain garden', 'n']
print tabulate(out.groupby(['city','raingarden']).size().to_frame(), headers, tablefmt="simple")

Issue 1: I need to get a column name on the counts, but have not had any luck;

Issue 2 (which is probably related to issue 1), the output looks like this:

city                          has rain garden
--------------------------  -----------------
(u'55405', True)                            3
(u'Edina', True)                            7
(u'MInneapolis', True)                      8
(u'Minneapolis', False)                  2482
(u'Minneapolis', True)                    847
(u'Minneapolis ', False)                    2
(u'Minneapolis ', True)                     1
(u'Minneapolis,', True)                     1
(u'Minneapolis, ', False)                   2
(u'Minneapolsi', False)                     5
(u'Minneapolsi', True)                      3
(u'Minnepolis', False)                      4
(u'Minnespolis', False)                     4
(u'Minnetonka', False)                      1
(u'Minnetonka', True)                       2
(u'Minnneapolis', False)                    5
(u'Mpla', True)                             3
(u'Mpls', False)                           22
(u'Mpls', True)                            20
(u'Mpls.', False)                           8
(u'Mpls.', True)                           17
(u'NE Mpls', True)                          6
(u'Richfield', True)                        1
(u'SLP', True)                              3
(u'St Louis Park', True)                    2
(u'St. Louis Park', False)                  1
(u'Victoria', False)                        1
(u'Wayzata', False)                         2
(u'Wayzata', True)                          1
(u'minneapolis', False)                     3
(u'mpls', True)                             2

The first two columns are given as a tuple? Thus, how do I split these into separate columns, and how do I add a label for my counts? I am sure what I am trying to achieve should be much simpler than I tried.

Perhaps ``pivot_table`` is what you need. See https://stackoverflow.com/questions/34315837/groupby-and-pivot-pandas-table — as133, Mar 02 '16 at 03:39
Can you add `out.head()` to the question so we know what your original data looks like? — Gustavo Bezerra, Mar 02 '16 at 05:40
Pivoting the data is definitely not what I need, but I will need it for some other analysis I am doing, so thanks for the link. — horcle_buzz, Mar 02 '16 at 15:10

score 2 · Accepted Answer · answered Mar 02 '16 at 05:39

By groping by two columns, you are creating a multi-level index Series, which I believe is not what you want. I am not sure how to original data looks like (would be nice providing out.head() in the question), but I believe what you are looking for is:

out.groupby('city').sum()['raingarden']

Here's an example with some randomly generated data:

import random
import string
import pandas as pd
import numpy as np

city = random.sample(string.lowercase*500,100)
raingarden = np.random.randint(0,10,100)

out = pd.DataFrame({'city':city, 'raingarden':raingarden})

Output:

In [30]: out.groupby('city').sum()['raingarden']
Out[30]:
city
a    17
b     7
c    16
d     8
e    24
f    28
g    16
h    49
i    29
j    24
k     4
l     5
m    17
n    29
p    22
q    14
r    19
s     6
t    21
u     8
v    18
w    25
x    11
y     9
z    40
Name: raingarden, dtype: int64

Yep, that did it... not sure why I was grouping by the two columns. — horcle_buzz, Mar 02 '16 at 14:41
I had to use the count() method, instead, since 'raingarden' was a Boolean. But, this did it. — horcle_buzz, Mar 02 '16 at 15:08

Tabulate data frame with groupby and size methods

1 Answers1