CODEWITHSUNDEEP
pythonpandaslistdataframedictionary
Zurooch
Zurooch

Reputation: 69

Dataframe extract dictionary from list

I have a Dataframe in the below format:

ID Details
1  [{"Number":"63","ID":"1","Road":"West"}]
2  [{"Number":"97","ID":"2","Road":"North"}]

I am trying to extract the value tagged to Road in each row. Expected output :

ID Details                                    Road
1  [{"Number":"63","ID":"1","Road":"West"}]   West
2  [{"Number":"97","ID":"2","Road":"North"}]  North

In df['Details'].str[0], I get [

In df['Details'].str[0].str.get('Road'), I get NaN only

Upvotes: 0

Views: 62

Answers (2)

sammywemmy
sammywemmy

Reputation: 28729

You could try a list comprehension :

df = pd.DataFrame(
    {
        "ID": [1, 2],
        "Details": [
            [{"Number": "63", "ID": "1", "Road": "West"}],
            [{"Number": "97", "ID": "2", "Road": "North"}],
        ],
    }
)

df

    ID  Details
0   1   [{'Number': '63', 'ID': '1', 'Road': 'West'}]
1   2   [{'Number': '97', 'ID': '2', 'Road': 'North'}]


df["Road"] = [entry["Road"] for entry in df.Details.str[0]]

    ID  Details                                         Road
0   1   [{'Number': '63', 'ID': '1', 'Road': 'West'}]   West
1   2   [{'Number': '97', 'ID': '2', 'Road': 'North'}]  North

Upvotes: 1

jezrael
jezrael

Reputation: 863791

There are not list of dicts, but strings repr of lists. So need create lists before your solution:

import ast, json

df['Details'] = df['Details'].apply(ast.literal_eval)
#alternative
#df['Details'] = df['Details'].apply(json.loads)

df['Details'].str[0].str.get('Road')

For all values use:

df = df.join(pd.DataFrame(df['Details'].str[0].tolist(), index=df.index).add_prefix('det_'))
print (df)
   ID                                         Details det_Number det_ID  \
0   1   [{'Number': '63', 'ID': '1', 'Road': 'West'}]         63      1   
1   2  [{'Number': '97', 'ID': '2', 'Road': 'North'}]         97      2   

  det_Road  
0     West  
1    North

Upvotes: 0

Related Questions