Extract key value pair from cell into new column

Question

I have a Pandas dataframe where cells in one column typically look like this:

[{'source_name': 'mitre-attack', 'external_id': 'T1156', 'url': 'https://attack.mitre.org/techniques/T1156'}, {'url': 'https://researchcenter.paloaltonetworks.com/2017/04/unit42-new-iotlinux-malware-targets-dvrs-forms-botnet/', 'description': 'Claud Xiao, Cong Zheng, Yanhui Jia. (2017, April 6). New IoT/Linux Malware Targets DVRs, Forms Botnet. Retrieved February 19, 2018.', 'source_name': 'amnesia malware'}]

How do I extract (in this example) the value 'T1156' of the key 'external_id' into a new column called t_id?

`df['t_id'] = df.A.apply(lambda x: x[0].get('external_id'))` This can also work. — Mayank Porwal, Oct 25 '20 at 07:18
@TyngdPunkt - Use `df['t_id'] = df.A.str[0].str.get('external_id')` — jezrael, Oct 25 '20 at 07:55
@Mayank Porwal thanks a million you have solved the problem :) — Tyngd Punkt, Oct 25 '20 at 07:58

score 1 · Answer 1 · answered Oct 25 '20 at 07:13

1

It's easy, try like this:

df['t_id'] = df['external_id']

answered Oct 25 '20 at 07:13

Wasif

14,755
3
14
34

1

No, solution is clearly wrong. – jezrael Oct 25 '20 at 07:14
@jezrael I thought the json was already converted to a df. – Wasif Oct 25 '20 at 07:18
I think issue of OP is convert column to DataFrame. – jezrael Oct 25 '20 at 07:27
This will not work I have no column called external_id. I have a column that contains cells with json and I want to extract the value from the key external_id – Tyngd Punkt Oct 25 '20 at 07:56

Extract key value pair from cell into new column

1 Answers1