2

I'm starting to learn about Python Pandas and want to generate a graph with the sum of arbitrary groupings of an ordinal value. It can be better explained with a simple example.

Suppose I have the following table of food consumption data:

enter image description here

And I have two groups of foods defined as two lists:

healthy = ['apple', 'brocolli']
junk = ['cheetos', 'coke']

Now I want to plot a graph with the evolution of consumption of junk and healthy food. I believe I must then process my data to get a DataFrame like:

enter image description here

Suppose the first table is already in a Dataframe called food, how do I transform it to get the second one?

I also welcome suggestions to reword my question to make it clearer, or for different approaches to generate the plot.

neves
  • 33,186
  • 27
  • 159
  • 192
  • Please don't post images of data, for [many reasons](https://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question), among them that it means anyone who wants to show a worked example with your data has to type it in manually. – DSM Apr 05 '17 at 14:51
  • @DSM: lesson learned. Thanks! – neves Apr 06 '17 at 05:44

1 Answers1

4

First create dictinary with lists and then swap keys with values.

Then groupby by mapped column food by dict and year, aggregate sum and last reshape by unstack:

healthy = ['apple', 'brocolli']
junk = ['cheetos', 'coke']

d1 = {'healthy':healthy, 'junk':junk}
##http://stackoverflow.com/a/31674731/2901002
d = {k: oldk for oldk, oldv in d1.items() for k in oldv}
print (d)
{'brocolli': 'healthy', 'cheetos': 'junk', 'apple': 'healthy', 'coke': 'junk'}

df1 = df.groupby([df.food.map(d), 'year'])['amount'].sum().unstack(0)
print (df1)
food  healthy  junk
year               
2010       10    11
2011       17    10
2012       13    24

Another solution with pivot_table:

df1 = df.pivot_table(index='year', columns=df.food.map(d), values='amount', aggfunc='sum')
print (df1)
food  healthy  junk
year               
2010       10    11
2011       17    10
2012       13    24
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252