Extract values from dictionaries as records in dataframe columns

Question

I have a dataframe which has several columns with values as dictionaries. These columns also contain some None (not sure if it's a string or just missing values).

df = pd.DataFrame([[{0: 300}, {0: 157}, {0: 456}],[{0: 298}, None, {0: 498}],[None, None, {0: 987}]],   columns=['col1', 'col2', 'col3'])

All dictionaries have key = 0, values = number from 100 to 10000.

I need to loop through all columns and rows and extract only the value. Preferably, i will overwrite the columns with only the values

So, end result should look like this:

df = pd.DataFrame([[300, 157, 456],[298, None, 498],[None, None, 987]],   columns=['col1', 'col2', 'col3'])

The number is actually an ID which I will use later on for a "vlookup" into another dataframe.

I tried with lambda functions:

df['col1'] = df['col1'].apply(lambda x: x.values() if x is not None else x)

I did manage to extract the values. Issue is the type of these values is recognized as a dictionary value (they look like this when i print them: (300)).

I need them as integers. I tried chaining an astype(int) but i do get an error (something like, you can't do that on a dictionary's value) Any thoughts?

Can you share a reproducible example as text and a clear expected output ? — Timeless
– Timeless, Commented Dec 6, 2022 at 22:24

jezrael · Accepted Answer · 2022-12-07 08:35:55Z

1

Use DataFrame.applymap for processing all columns with get first values of dict values:

df = df.applymap(lambda x: list(x.values())[0] if isinstance(x, dict) else x)
print (df)
    col1   col2  col3
0  300.0  157.0   456
1  298.0    NaN   498
2    NaN    NaN   987

If need integers convert values to Int64:

df = (df.applymap(lambda x: list(x.values())[0] if isinstance(x, dict) else x)
       .astype('Int64'))
print (df)
   col1  col2  col3
0   300   157   456
1   298  <NA>   498
2  <NA>  <NA>   987

edited Dec 7, 2022 at 8:35

answered Dec 7, 2022 at 7:58

jezrael

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

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

3 Comments

santee Over a year ago

I'm getting this error: AttributeError: 'int' object has no attribute 'values'

jezrael Over a year ago

@santee - Answer was edited.

santee Over a year ago

Nevermind. You solution works. I was just looping through my entire dataframe (which has also columns of other types). I restricted to the ones with dictionaries, and now works. Thank you

Collectives™ on Stack Overflow

Extract values from dictionaries as records in dataframe columns

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related