Pandas dict column to column

Question

I'd like to extract the overall key in the dict ratings column and add it as a separate column . This is what I've tried so far:

def try_literal_eval(e):
    try:
        return ast.literal_eval(e)
    except ValueError:
        return {'overall': 0}

res = pd.DataFrame(df['ratings'].apply(try_literal_eval).tolist())
output = pd.concat((df.drop('ratings', 1), res), axis=1)
output

df

customer_id    rating 
44224         {'overall': 5, 'description': 3}
55243         {'overall': 3, 'description': 2}

desired output_df

customer_id    overall_rating
44224          5
55243          3

`df['rating'].apply(lambda x: x.get('overall'))` this will give you the answer — Joe Ferndz, Jan 25 '21 at 19:22
Does this answer your question? [Extract dictionary value from column in data frame](https://stackoverflow.com/questions/35711059/extract-dictionary-value-from-column-in-data-frame) — Joe Ferndz, Jan 25 '21 at 19:25
@JoeFerndz I get the following error: AttributeError: 'float' object has no attribute 'keys' — user12625679, Jan 25 '21 at 21:43
I added more examples in my response. See details. Let me know what your data looks like so we can address it — Joe Ferndz, Jan 25 '21 at 21:51
works now! I had null values in the column that's why it did not work initially — user12625679, Jan 26 '21 at 09:04

Joe Ferndz · Accepted Answer · 2021-01-25T21:50:13.173

df['overall_rating'] = df['rating'].apply(lambda x: x.get('overall')) should give you the results

c = ['customer_id','rating'] 
d = [[44224,{'overall': 5, 'description': 3}],
[55243,{'overall': 3, 'description': 2}]]
import pandas as pd
df = pd.DataFrame(d,columns=c)
print (df)
df['overall_rating'] = df['rating'].apply(lambda x: x.get('overall'))
print (df)

Output of this is:

Original DataFrame:

   customer_id                            rating
0        44224  {'overall': 5, 'description': 3}
1        55243  {'overall': 3, 'description': 2}

Updated DataFrame:

   customer_id                            rating  overall_rating
0        44224  {'overall': 5, 'description': 3}               5
1        55243  {'overall': 3, 'description': 2}               3

Or you can give:

df['overall_rating'] = pd.DataFrame([x for x in df['rating']])['overall']

The output of this will also be the same:

c = ['customer_id','rating'] 
d = [[44224,{'overall': 5, 'description': 3}],
[55243,{'overall': 3, 'description': 2}]]
import pandas as pd
df = pd.DataFrame(d,columns=c)
print (df)
df['overall_rating'] = pd.DataFrame([x for x in df['rating']])['overall']
#df['overall_rating'] = df['rating'].apply(lambda x: x.get('overall'))
print (df)

Original DataFrame:

   customer_id                            rating
0        44224  {'overall': 5, 'description': 3}
1        55243  {'overall': 3, 'description': 2}

Updated DataFrame:

   customer_id                            rating  overall_rating
0        44224  {'overall': 5, 'description': 3}               5
1        55243  {'overall': 3, 'description': 2}               3

Example with dictionary having float value and dictionary without an entry for 'overall'

c = ['customer_id','rating'] 
d = [[44224,{'overall': 5, 'description': 3}],
[55243,{'overall': 3, 'description': 2}],
[11223,{'overall': 1.5, 'description': 2}],
[12345,{'description':3}]]
import pandas as pd
df = pd.DataFrame(d,columns=c)
print (df)
df['overall_rating'] = df['rating'].apply(lambda x: x.get('overall'))
print (df)

The output of this is:

Input DataFrame

   customer_id                              rating
0        44224    {'overall': 5, 'description': 3}
1        55243    {'overall': 3, 'description': 2}
2        11223  {'overall': 1.5, 'description': 2}
3        12345                  {'description': 3}

The Updated DataFrame is:

   customer_id                              rating  overall_rating
0        44224    {'overall': 5, 'description': 3}             5.0
1        55243    {'overall': 3, 'description': 2}             3.0
2        11223  {'overall': 1.5, 'description': 2}             1.5
3        12345                  {'description': 3}             NaN

I get the following error: AttributeError: 'float' object has no attribute 'get' — user12625679, Jan 25 '21 at 19:55
Is the dataframe like the one I setup? Can you share the dataframe please? I used the one from your question. Isn't it serup that way? — Joe Ferndz, Jan 25 '21 at 20:19
that is the dataframe - i guess the overall value is a float for some reason — user12625679, Jan 25 '21 at 20:35

score 0 · Answer 2 · answered Jan 25 '21 at 19:20

0

Try:

def try_literal_eval(e):
    try:
        return ast.literal_eval(e).get('overall', 0)
    except ValueError:
        return 0

df[['customer_id']].assign(overall_rating=df['ratings'].apply(try_literal_eval))

answered Jan 25 '21 at 19:20

Quang Hoang

146,074
10
56
74

Pandas dict column to column

2 Answers2