CODEWITHSUNDEEP
pandas
JamesHudson81
JamesHudson81

Reputation: 2273

Struggle trying to select values for a dataframe

I have a df those values are a dictionary that looks like this:

                            'kg of valencian tomato (before tomatina)'
        2017-06-09  {'weight': 0.0049385761, 'price': 12.18, 'time':'14:17'}
        2017-06-12  {'weight': 0.0049441361, 'price': 12.1, 'time': '15:21'}
        2017-06-13  {'price': 12.06, 'weight': 0.00491616, 'time': '09:21'}
        2017-06-14  {'weight': 0.0048403923, 'price': 11.77, 'time':'10:12'}

What I am looking forward is for the values of such df to be the price value

                    'kg of valencian tomato (before tomatina)'
        2017-06-09           12.18
        2017-06-12           12.1
        2017-06-13           12.06
        2017-06-14           11.77

What I tried firstly was to:

format = lambda x: list(x.values())[1]
df2=df2.applymap(format)

Until I noticed the different dictionaries didnt followed the same order. How could I obtain desired output?

Another problem I am facing is that I have tons of columns with different names, is there a way to apply it onto the entire df ?

Upvotes: 1

Views: 24

Answers (1)

jezrael
jezrael

Reputation: 862481

You can use apply:

df['price'] = df['kg of valencian tomato (after tomatina)'].apply(lambda x: x['price'])
print (df)
                      kg of valencian tomato (after tomatina)  price
2017-06-09  {'price': 12.18, 'time': '14:17', 'weight': 0....  12.18
2017-06-12  {'price': 12.1, 'time': '15:21', 'weight': 0.0...  12.10
2017-06-13  {'price': 12.06, 'time': '09:21', 'weight': 0....  12.06
2017-06-14  {'price': 11.77, 'time': '10:12', 'weight': 0....  11.77

If have multiple columns with dicts use applymap:

cols = ['kg of valencian tomato (after tomatina)','another col']
df[cols] = df[cols].applymap(lambda x: x['price'])
print (df)
            kg of valencian tomato (after tomatina)
2017-06-09                                    12.18
2017-06-12                                    12.10
2017-06-13                                    12.06
2017-06-14                                    11.77

Upvotes: 1

Related Questions