How to extract value from a column with multiple dictionaries

Question

I have a column that was extracted using Pandas. The following column may contain one dictionary or more than one dictionary.

Column B
[{'url': 'mailto:Kim_Do@dmx.com', 'type': 0, 'id': 1021857, 'name': 'KIM Do', 'entryListId': -1}, {'url': 'mailto:Angel_Kong@dmx.com', 'type': 0, 'id': 1023306, 'name': 'Angel Kong', 'entryListId': -1}, {'url': 'mailto:Alex_Do@dmx.com', 'type': 0, 'id': 1023289, 'name': 'Alex Do', 'entryListId': -1}
[{'url': 'mailto:Ray_Chan@dmx.com', 'type': 0, 'id': 1021857, 'name': 'Ray Chan', 'entryListId': -1}, {'url': 'mailto:Paul_Jones@dmx.com', 'type': 0, 'id': 1023306, 'name': 'Paul Jones, 'entryListId': -1}]
nan
nan
[{'url': 'mailto:Ray_Chaudhry@dmx.com', 'type': 0, 'id': 1021857, 'name': 'Ray Chaudhry', 'entryListId': -1}]

What I want back is just the names from the dictionary. So, the output should be as follows:

Column B
Kim Do, Angel Kong, Alex Do, Fred Tome
Ray Chan, Paul Jones
nan
nan
Ray Chaudhry

How can I achieve this. Thank you!

score 0 · Answer 1 · answered Jan 15 '23 at 14:21

0

You can use:

df['New'] = df['Column B'].explode().str['name'].dropna().groupby(level=0).agg(', '.join)

Output (New column only):

0    KIM Do, Angel Kong, Alex Do
1           Ray Chan, Paul Jones
2                            NaN
3                            NaN
4                   Ray Chaudhry

answered Jan 15 '23 at 14:21

mozway

194,879
13
39
75

Thank you mozway. I am getting this error AttributeError: 'dict' object has no attribute 'str' – user14946795 Jan 15 '23 at 14:28
1

can you provide a reproducible input as `DataFrame` constructor? – mozway Jan 15 '23 at 14:36
Hi @mozway, I am not sure you mean as I am new to pythons and pandas. Do you mean df = pd.DataFrame(rows). Thanks you – user14946795 Jan 15 '23 at 14:46
[see here](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) – mozway Jan 15 '23 at 14:53

score 0 · Answer 2 · answered Jan 15 '23 at 15:00

Use this function:

def extract_names(list_data):
    row_names = []
    for n in range(len(list_data)):
        row_names.append(list_data[n]['name'])
    return row_names

store_names = []
col = 0 #column name
for idx, row in df.iterrows():
#     print(idx)
#     print(row[col])
    store_names.append(extract_names(row[col]))
#     print('--')

Now you can store the list as parameter of your choice:

df['Names'] = store_names

How to extract value from a column with multiple dictionaries

2 Answers2