CODEWITHSUNDEEP
pandas
scott martin
scott martin

Reputation: 1293

Pandas - Extract value from Dataframe based on certain key value

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

Upvotes: 1

Views: 220

Answers (1)

jezrael
jezrael

Reputation: 863481

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

Upvotes: 2

Related Questions