LearningSlowly LearningSlowly - 3 months ago 15
Python Question

Counting matching dictionaries

I have a list containing dictionaries:

[{'x': u'osgb32', 'y': u'osgb4000'},
{'x': u'osgb4340', 'y': u'osgb4000'},
{'x': u'osgb4020', 'y': u'osgb4000'},
{'x': u'osgb32', 'y': u'osgb4000'},
{'x': u'osgb32', 'y': u'osgb4000'}]


I wish to count the incidents of each dict and create a new field
count


The desired outcome looks like this:

[{'x': u'osgb32', 'y': u'osgb4000', 'count': 3},
{'x': u'osgb4340', 'y': u'osgb4000', 'count': 1},
{'x': u'osgb4020', 'y': u'osgb4000', 'count': 1}]


I am unsure how to match
dict
s.

Answer

You can pass your list of dicts as the data arg to DataFrame ctor:

In [74]:
import pandas as pd
data = [{'x': u'osgb32', 'y': u'osgb4000'},
 {'x': u'osgb4340', 'y': u'osgb4000'},
 {'x': u'osgb4020', 'y': u'osgb4000'},
 {'x': u'osgb32', 'y': u'osgb4000'},
 {'x': u'osgb32', 'y': u'osgb4000'}]
df = pd.DataFrame(data)
df

Out[74]:
          x         y
0    osgb32  osgb4000
1  osgb4340  osgb4000
2  osgb4020  osgb4000
3    osgb32  osgb4000
4    osgb32  osgb4000

you can then groubpy on the cols and call size to get a count:

In [76]:    
df.groupby(['x','y']).size()

Out[76]:
x         y       
osgb32    osgb4000    3
osgb4020  osgb4000    1
osgb4340  osgb4000    1
dtype: int64

and then call to_dict:

In [77]:    
df.groupby(['x','y']).size().to_dict()

Out[77]:
{('osgb32', 'osgb4000'): 3,
 ('osgb4020', 'osgb4000'): 1,
 ('osgb4340', 'osgb4000'): 1}

You can wrap the above into a list:

In [79]:
[df.groupby(['x','y']).size().to_dict()]

Out[79]:
[{('osgb32', 'osgb4000'): 3,
  ('osgb4020', 'osgb4000'): 1,
  ('osgb4340', 'osgb4000'): 1}]

You can reset_index, rename the column and pass arg orient='records':

In [94]:
df.groupby(['x','y']).size().reset_index().rename(columns={0:'count'}).to_dict(orient='records')

Out[94]:
[{'count': 3, 'x': 'osgb32', 'y': 'osgb4000'},
 {'count': 1, 'x': 'osgb4020', 'y': 'osgb4000'},
 {'count': 1, 'x': 'osgb4340', 'y': 'osgb4000'}]