Merge two lists of dicts of different lengths using a single key in Python

Question

I want to merge two lists of dictionaries on a single key, when the two lists are different lengths (using Python 3.6). For example, if we have a list of dicts called l1:

l1 = [{'pcd_sector': 'ABDC', 'coverage_2014': '100'},
       {'pcd_sector': 'DEFG', 'coverage_2014': '0'}]

and another list of dicts called l2:

l2 = [{'pcd_sector': 'ABDC', 'asset': '3G', 'asset_id': '2gs'},
      {'pcd_sector': 'ABDC', 'asset': '4G', 'asset_id': '7jd'},
      {'pcd_sector': 'DEFG', 'asset': '3G', 'asset_id': '3je'},
      {'pcd_sector': 'DEFG', 'asset': '4G', 'asset_id': '8js'},
      {'pcd_sector': 'CDEF', 'asset': '3G', 'asset_id': '4jd'}]

How would one merge them using pcd_sector to get this(?):

result = [{'pcd_sector': 'ABDC', 'asset': '3G', 'asset_id': '2gs', 'coverage_2014': '100'},
          {'pcd_sector': 'ABDC', 'asset': '4G', 'asset_id': '7jd', 'coverage_2014': '100'},
          {'pcd_sector': 'DEFG', 'asset': '3G', 'asset_id': '3je', 'coverage_2014': '0'},
          {'pcd_sector': 'DEFG', 'asset': '4G', 'asset_id': '8js', 'coverage_2014': '0'},
          {'pcd_sector': 'CDEF', 'asset': '3G', 'asset_id': '4jd'}]

What I have tried so far

I've used the following code to merge the two lists, but I end up with a short version unfortunately, not the desired complete data structure.

import pprint
grouped = {}
for d in l1 + l2:
    grouped.setdefault(d['pcd_sector'], {'asset':0, 'asset_id':0, 'coverage_2014':0}).update(d)
result = [d for d in grouped.values()]
pprint.pprint(result)

So when I run the code, I end up with this short output:

result = [{'pcd_sector': 'ABDC', 'asset': '3G', 'asset_id': '2gs', 'coverage_2014': '100'},
         {'pcd_sector': 'DEFG', 'asset': '4G', 'asset_id': '8js', 'coverage_2014': '0'},
         {'pcd_sector': 'CDEF', 'asset': '3G', 'asset_id': '4jd'}]

Eric Duminil · Accepted Answer · 2017-07-24T10:12:19.483

Problem

The problem in your approach is that your data is put in a grouped dict with 'pcd_sector' as keys but your l2 has multiple dicts with the same 'pcd_sector'. You could use a tuple of 'pcd_sector', 'asset' as key for l2, but it wouldn't work for l1 anymore. So you need to do the processing in two steps instead of iterating on l1 + l2 directly.

Theory

If pcd_sector keys are unique in l1, you can create a big dict instead of a list of small dicts:

>>> d1 = {d['pcd_sector']:d for d in l1}
>>> d1
{'ABDC': {'pcd_sector': 'ABDC', 'coverage_2014': '100'}, 'DEFG': {'pcd_sector': 'DEFG', 'coverage_2014': '0'}}

Then, you simply need to merge the dicts that have the same pcd_sector keys:

>>> [dict(d, **d1.get(d['pcd_sector'], {})) for d in l2]
[{'asset_id': '2gs', 'coverage_2014': '100', 'pcd_sector': 'ABDC', 'asset': '3G'}, {'asset_id': '7jd', 'coverage_2014': '100', 'pcd_sector': 'ABDC', 'asset': '4G'}, {'asset_id': '3je', 'coverage_2014': '0', 'pcd_sector': 'DEFG', 'asset': '3G'}, {'asset_id': '8js', 'coverage_2014': '0', 'pcd_sector': 'DEFG', 'asset': '4G'}, {'asset_id': '4jd', 'pcd_sector': 'CDEF', 'asset': '3G'}]

Complete code

Putting it all together, the code becomes:

l1 = [{'pcd_sector': 'ABDC', 'coverage_2014': '100'},
       {'pcd_sector': 'DEFG', 'coverage_2014': '0'}]

l2 = [{'pcd_sector': 'ABDC', 'asset': '3G', 'asset_id': '2gs'},
      {'pcd_sector': 'ABDC', 'asset': '4G', 'asset_id': '7jd'},
      {'pcd_sector': 'DEFG', 'asset': '3G', 'asset_id': '3je'},
      {'pcd_sector': 'DEFG', 'asset': '4G', 'asset_id': '8js'},
      {'pcd_sector': 'CDEF', 'asset': '3G', 'asset_id': '4jd'}]

d1 = {d['pcd_sector']:d for d in l1}
result = [dict(d, **d1.get(d['pcd_sector'], {})) for d in l2]

import pprint
pprint.pprint(result)
#   [{'asset': '3G',
#     'asset_id': '2gs',
#     'coverage_2014': '100',
#     'pcd_sector': 'ABDC'},
#    {'asset': '4G',
#     'asset_id': '7jd',
#     'coverage_2014': '100',
#     'pcd_sector': 'ABDC'},
#    {'asset': '3G',
#     'asset_id': '3je',
#     'coverage_2014': '0',
#     'pcd_sector': 'DEFG'},
#    {'asset': '4G',
#     'asset_id': '8js',
#     'coverage_2014': '0',
#     'pcd_sector': 'DEFG'},
#    {'asset': '3G', 'asset_id': '4jd', 'pcd_sector': 'CDEF'}]

This workede great, thanks for the combined theory and complete code answer. — Thirst for Knowledge, Jul 24 '17 at 11:13

cs95 · Answer 2 · 2017-07-24T10:02:04.053

You can create a lookup dictionary based on pcd_sector and just update your original list of dicts based on that:

>>> import copy
>>> lookup = { x['pcd_sector'] : x for x in l1 }
>>> result = copy.deepcopy(l2)
>>> for d in result:
...     d.update(lookup.get(d['pcd_sector'], {})) # golfed courtesy Ashwini Chaudhary
... 
>>> result
[{'pcd_sector': 'ABDC', 'asset': '3G', 'asset_id': '2gs', 'coverage_2014': '100'}, 
{'pcd_sector': 'ABDC', 'asset': '4G', 'asset_id': '7jd', 'coverage_2014': '100'}, 
{'pcd_sector': 'DEFG', 'asset': '3G', 'asset_id': '3je', 'coverage_2014': '0'}, 
{'pcd_sector': 'DEFG', 'asset': '4G', 'asset_id': '8js', 'coverage_2014': '0'},
{'pcd_sector': 'CDEF', 'asset': '3G', 'asset_id': '4jd'}]

Thanks for the suggested answer Coldspeed. In the end I opted for Eric's answer as it provided both theory and complete code sections. — Thirst for Knowledge, Jul 24 '17 at 11:14

score 0 · Answer 3 · answered Jul 24 '17 at 09:54

A solution using pandas:

import pandas as pd

df1 = pd.DataFrame(l1)
df2 = pd.DataFrame(l2)
dfr = df1.join(df2, how='outer')
print(dfr)

Output:

  coverage_2014 pcd_sector asset asset_id
0           100       ABDC    3G      2gs
1           100       ABDC    4G      7jd
2             0       DEFG    3G      3je
3             0       DEFG    4G      8js
4           NaN       CDEF    3G      4jd

If you want it as a dictionary again:

result = dfr.to_dict('records')
print(result)

Output (with linebreaks added):

[{'coverage_2014': '100', 'pcd_sector': 'ABDC', 'asset': '3G', 'asset_id': '2gs'},
 {'coverage_2014': '100', 'pcd_sector': 'ABDC', 'asset': '4G', 'asset_id': '7jd'},
 {'coverage_2014': '0', 'pcd_sector': 'DEFG', 'asset': '3G', 'asset_id': '3je'},
 {'coverage_2014': '0', 'pcd_sector': 'DEFG', 'asset': '4G', 'asset_id': '8js'},
 {'coverage_2014': nan, 'pcd_sector': 'CDEF', 'asset': '3G', 'asset_id': '4jd'}]

Nice. Don't you need to specify which columns you use for lookup? — Eric Duminil, Jul 24 '17 at 10:20
You can pass columns explicitly to the `on` keyword argument, but I was under the impression that not doing so would automatically find matching column names between the dataframes, and that is how the code seems to behave. However, [the documentation](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.join.html) seems to indicate otherwise (namely using the index), so I'm not sure now... — , Jul 24 '17 at 10:48

Merge two lists of dicts of different lengths using a single key in Python

3 Answers3

Problem

Theory

Complete code