pandas - plot according to groupby index level

Question

I have this simple dataframe df:

City,H
AMS,1.1
AMS,0.8
AMS,0.9
BOS,0.9
BOS,0.7
BOS,0.6
BOS,0.8

I would like to sort the H columns according to each City, and then plot each City index with a different color. So far I started by grouping and sorting:

d = df.groupby('City').apply(lambda x: x.sort('H', ascending=False))

Then, since I would like to plot the H values for each City according to a sort of ranking, I add a column called subindex as:

d['subindex'] = d.groupby(level=0).cumcount() + 1

The resulting dataframe is:

       City    H  subindex
City                      
AMS  0  AMS  1.1         1
     2  AMS  0.9         2
     1  AMS  0.8         3
BOS  3  BOS  0.9         1
     6  BOS  0.8         2
     4  BOS  0.7         3
     5  BOS  0.6         4

The format is what I wanted, but I can't figured out why the column City appears twice. Now the problem is plotting, for each City, the H values according to the subindex. I tried:

for i, group in d:
    group.plot(x='subindex', y='H')

receiving the following ValueError:

for i, group in d:
ValueError: too many values to unpack

the iterable returned from the df are the columns, your df is no longer a groupby but a multi-indexed df, so you want d.index.get_level_values(0).unique() — EdChum
– EdChum, Commented Sep 8, 2015 at 8:57

EdChum · Accepted Answer · 2015-09-08 09:06:58Z

Your d is no longer a groupby object it is a multi-indexed df which is why you get the error:

In [61]:
for col in d:
    print(col)

City
H
subindex

this is what d is now:

Out[52]:
       City    H  subindex
City                      
AMS  0  AMS  1.1         1
     2  AMS  0.9         2
     1  AMS  0.8         3
BOS  3  BOS  0.9         1
     6  BOS  0.8         2
     4  BOS  0.7         3
     5  BOS  0.6         4

If you had not called apply on the groupby object then you could access the groups:

In [69]:
g = df.groupby('City')
g.groups

Out[69]:
{'AMS': [0, 1, 2], 'BOS': [3, 4, 5, 6]}

You could've iterated over the groupby object correctly as before:

In [71]:
for i, group in g:
    print(i)
    print(group)

AMS
  City    H
0  AMS  1.1
1  AMS  0.8
2  AMS  0.9
BOS
  City    H
3  BOS  0.9
4  BOS  0.7
5  BOS  0.6
6  BOS  0.8

As such what you want to do now is to use the index levels to filter your df and plot them:

for city in d.index.get_level_values(0).unique():
    d[d['City']==city].plot(x='subindex', y='H')

yields following plots:

and

Collectives™ on Stack Overflow

pandas - plot according to groupby index level

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related