Pandas - Extract value from Dataframe based on certain key value

Question

I have a Dataframe in the below format:

id, ref
101, [{'id': '74947', 'type': {'id': '104', 'name': 'Sales', 'inward': 'Sales', 'outward': 'PO'}, 'inwardIssue': {'id': '76560', 'key': 'Prod-A'}}]
102, [{'id': '74948', 'type': {'id': '105', 'name': 'Return', 'inward': 'Return Order', 'outward': 'PO'}, 'inwardIssue': {'id': '76560', 'key': 'Prod-C'}}]
103, [{'id': '74949', 'type': {'id': '106', 'name': 'Sales', 'inward': 'Return Order', 'outward': 'PO'}, 'inwardIssue': {'id': '76560', 'key': 'Prod-B'}}]

I am trying to extract rows that have name = Sales and return back the below output:

id, value
101, Prod-A 
103, Prod-B

jezrael · Accepted Answer · 2020-04-17 10:18:52Z

2

Use str[0] for first lists with Series.str.get by values by keys of dicts:

#if necessary convert list/dict repr to list/dict
import ast
df['ref'] = df['ref'].apply(ast.literal_eval)

df['names'] = df['ref'].str[0].str.get('type').str.get('name')
df['value'] = df['ref'].str[0].str.get('inwardIssue').str.get('key')

print (df)

    id                                                ref   names   value
0  101  [{'id': '74947', 'type': {'id': '104', 'name':...   Sales  Prod-A
1  102  [{'id': '74948', 'type': {'id': '105', 'name':...  Return  Prod-C
2  103  [{'id': '74949', 'type': {'id': '106', 'name':...   Sales  Prod-B

And then filter by boolean indexing:

df1 = df.loc[df['names'].eq('Sales'), ['id','value']]
print (df1)
    id   value
0  101  Prod-A
2  103  Prod-B

edited Apr 17, 2020 at 10:18

answered Apr 17, 2020 at 8:03

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

scott martin Over a year ago

thanks for the reply.. I however get this error AttributeError: 'str' object has no attribute 'values'

Collectives™ on Stack Overflow

Pandas - Extract value from Dataframe based on certain key value

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related