Python - Join two lists of dictionaries on a contains, and leave out any that do not match?

Question

Im trying to join two list of dictionaries where one of the fields contains a section of another field, and if there isn't a match to lave it out.

list_1 = [{
    "bytes_in": 0,
    "rsrq": -50,
    "hostname": "AB9472"
},
{
    "bytes_in": 0,
    "rsrq": -90,
    "hostname": "DF4845"
},
{
    "bytes_in": 0,
    "rsrq": "None",
    "hostname": "FC4848"
}
]

list_2 = [{
    "id": 249,
    "ref_no": "AB9472 - 015632584",
    "rssi": "-75.0",
    "circuit_type": "4G"
},
{
    "id": 17,
    "ref_no": "DF4845 - 8984494",
    "rssi": "-20.0",
    "circuit_type": "4G"
}]

so in the above example list_1 hostname field would be contained inside list_2 ref_no and would match for the first two records and then be created in a new list_3

I feel I can do this in iterrtools but I'm not sure how? Thanks

desired output:

list_3 = [{
    "id": 249,
    "ref_no": "AB9472 - 015632584",
    "rssi": "-75.0",
    "circuit_type": "4G",
    "bytes_in": 0,
    "rsrq": -50,
    "hostname": "AB9472"
},
{
    "id": 17,
    "ref_no": "DF4845 - 8984494",
    "rssi": "-20.0",
    "circuit_type": "4G",
    "bytes_in": 0,
    "rsrq": -90,
    "hostname": "DF4845"
}]

Do you know ahead the names of the fields in question? i..e what if the `id` accidentally appears in the `hostname`? — Itamar Mushkin, Aug 05 '19 at 10:07

score 1 · Accepted Answer · answered Aug 05 '19 at 10:08

1

Try this :

list_3 = []
for i in list_1:
    for j in list_2:
        if i['hostname'] == j['ref_no'].split("-")[0].strip():
            list_3.append({**i,**j})

print(list_3)

answered Aug 05 '19 at 10:08

Vikas Periyadath

3,088
1
21
33

Lê Tư Thành · Answer 2 · 2019-08-05T13:01:31.527

1

Your question basically is extend a dictionary by another dictionary.

Following this answer, I suggest that you can use dict1.update(dict2).

This is an example code for your issue when each element of the first list matched with each one of the second list.

# Both lists have matched order
len2 = len(list_2)   # Use length of list_2 because it shorter
list_3=list_1[:len2] # list_3 contains 2 first elements of list_1
for i in range(len2):
    list_3[i].update(list_2[i])  # Each element is a dict, so use .update() for join 2 dicts

edited Aug 05 '19 at 13:01

answered Aug 05 '19 at 10:08

Lê Tư Thành

1,063
2
10
19

1

@FZs, I updated some description. Thanks for remind. – Lê Tư Thành Aug 05 '19 at 13:02

Jeremy H · Answer 3 · 2019-08-05T10:23:01.967

Working off the assumption that you're basically doing a 'join' on hostname and the first portion of ref_no (with the same format for ref_no) I would propose the following procedure (using Pandas to do heavy lifting):

# Convert to pandas dataframes to abstract the merging
import pandas as pd
df_1 = pd.DataFrame(list_1)
df_2 = pd.DataFrame(list_2)

# Extract the hostname for merging
df_2['hostname'] = df_2['ref_no'].apply(lambda x: x.split(' - ')[0])

# Merge the dataframes - using outer-join to keep all information
# User inner to remove those that have no matches
df_3 = pd.merge(df_1, df_2, how='outer', on='hostname')

# Convert back to a list
list_3 = df_3.to_dict('records')

score 0 · Answer 4 · answered Aug 05 '19 at 10:17

iterating through list_2 and getting values from list_1 when hostname matched:

list_3 = list_2
for dataDict1 in list_3:
    refNo = dataDict1["ref_no"].split()[0]
    print(refNo)
    for dataDict2 in list_1:
        if refNo == dataDict2["hostname"]:
            dataDict1["hostname"] = refNo
            dataDict1["rsrq"] = dataDict2["rsrq"]
            dataDict1["bytes_in"] = dataDict2["bytes_in"]
            break
print(list_3)

output:

[{'bytes_in': 0,
  'circuit_type': '4G',
  'hostname': 'AB9472',
  'id': 249,
  'ref_no': 'AB9472 - 015632584',
  'rsrq': -50,
  'rssi': '-75.0'},
 {'bytes_in': 0,
  'circuit_type': '4G',
  'hostname': 'DF4845',
  'id': 17,
  'ref_no': 'DF4845 - 8984494',
  'rsrq': -90,
  'rssi': '-20.0'}]

score 0 · Answer 5 · answered Aug 05 '19 at 10:22

list_1 = [{
    "bytes_in": 0,
    "rsrq": -50,
    "hostname": "AB9472"
},
{
    "bytes_in": 0,
    "rsrq": -90,
    "hostname": "DF4845"
},
{
    "bytes_in": 0,
    "rsrq": "None",
    "hostname": "FC4848"
}
]

list_2 = [{
    "id": 249,
    "ref_no": "AB9472 - 015632584",
    "rssi": "-75.0",
    "circuit_type": "4G"
},
{
    "id": 17,
    "ref_no": "DF4845 - 8984494",
    "rssi": "-20.0",
    "circuit_type": "4G"
}]

list_3 = []

for fs in list_1:
    for sl_fs in list_2:
        if sl_fs['ref_no'].split()[0] == fs['hostname']:
            list_3.append({**sl_fs, **fs})

Python - Join two lists of dictionaries on a contains, and leave out any that do not match?

5 Answers5