Pandas: using groupby if values in columns are dictionaries

Question

I have dataframe

category  dictionary
moto   {'motocycle':10, 'buy":8, 'motocompetition':7}
shopping   {'buy':200, 'order':20, 'sale':30}
IT   {'iphone':214, 'phone':1053, 'computer':809}
shopping  {'zara':23, 'sale':18, 'sell':20}
IT   {'lenovo':200, 'iphone':300, 'mac':200}

I need groupby category and as result concatenate dictionaries and choose 3 keys with the biggest values. And next get dataframe, where at the column category I have unique category, and in the column data I have list with keys.

I know, that I can use Counter to concatenate dicts, but I don't know, how do that to categories. Desire output

category   data
moto   ['motocycle', 'buy', 'motocompetition']
shopping   ['buy', 'sale', 'zara']
IT   ['phone', 'computer', 'iphone']

can you give a working example? what have you looked into so far? — JMat, Oct 12 '16 at 08:42
If I have only dicts, without dataframe, I can get this with `a = {1:2, 2:5, 6:9, u'cat':2} b = {1:4, 4:2, 6:1, u'dog':11, u'cat':8} c = {5:2, 7:1, u'dog':19} a = Counter(a) b = Counter(b) c = Counter(c) d = a + b + c result = dict(d.most_common(3)) list = result.keys()` @JMat — Petr Petrov, Oct 12 '16 at 08:46

score 3 · Accepted Answer · answered Oct 12 '16 at 08:56

You can use groupby with custom function with nlargest and Index.tolist:

df = pd.DataFrame({
'category':['moto','shopping','IT','shopping','IT'],
'dictionary':
[{'motocycle':10, 'buy':8, 'motocompetition':7},
{'buy':200, 'order':20, 'sale':30},
{'iphone':214, 'phone':1053, 'computer':809},
{'zara':23, 'sale':18, 'sell':20},
{'lenovo':200, 'iphone':300, 'mac':200}]})

print (df)
   category                                         dictionary
0      moto  {'motocycle': 10, 'buy': 8, 'motocompetition': 7}
1  shopping              {'sale': 30, 'buy': 200, 'order': 20}
2        IT    {'phone': 1053, 'computer': 809, 'iphone': 214}
3  shopping               {'sell': 20, 'zara': 23, 'sale': 18}
4        IT         {'lenovo': 200, 'mac': 200, 'iphone': 300}


import collections
import functools
import operator

def f(x):
    #some possible solution for sum values of dict
    #http://stackoverflow.com/a/3491086/2901002
    return pd.Series(functools.reduce(operator.add, map(collections.Counter, x)))
             .nlargest(3).index.tolist()

print (df.groupby('category')['dictionary'].apply(f).reset_index())
   category                         dictionary
0        IT          [phone, computer, iphone]
1      moto  [motocycle, buy, motocompetition]
2  shopping                  [buy, sale, zara]

I send you email to `gmail`, please check it. – jezrael Dec 12 '16 at 09:13 — jezrael, Dec 12 '16 at 09:13

score 1 · Answer 2 · answered Oct 12 '16 at 08:57

df = pd.DataFrame(dict(category=['moto', 'shopping', 'IT', 'shopping', 'IT'],
                       dictionary=[
                           dict(motorcycle=10, buy=8, motocompetition=7),
                           dict(buy=200, order=20, sale=30),
                           dict(iphone=214, phone=1053, computer=809),
                           dict(zara=23, sale=18, sell=20),
                           dict(lenovo=200, iphone=300, mac=200),
                       ]))

def top3(x):
    return x.dropna().sort_values().tail(3)[::-1].index.tolist()

df.dictionary.apply(pd.Series).groupby(df.category).sum().apply(top3, axis=1)

category
IT                   [phone, computer, iphone]
moto        [motorcycle, buy, motocompetition]
shopping                     [buy, sale, zara]
dtype: object

Pandas: using groupby if values in columns are dictionaries

2 Answers2