how to extract data from a cell with df into a new column with dict format pandas-CodePudding

so, how to extract values of LastPriceChangeDateTime and CashPrice as a key:value pair in to a new column of the main df for DIESEL only(df['diesel_price_change'])?

eventually, i want to append in that column dict with LastPriceChangeDateTime: CashPrice every time it's changed

i tried to loop with bunch of parameters but seems like somthing is messed up

for index, row in df.iterrows():
    dfnew = pd.DataFrame(df['FuelPrices'][index])
    dfnew['price_change'] = dfnew.apply(lambda row: {row['LastPriceChangeDateTime']: row['CashPrice']}, axis=1)
    df['diesel_price_change'][index] = dfnew.apply(lambda x: y['price_change'] for y in x if y['ProductName'] == 'DIESEL')

i receive "'int' object is not iterable"

CodePudding user response：

Unfortunately, The only way I found is to loop through it, but I still hope that i'll find pandas solution for it.

for index, row in df.iterrows():
    for row in df['FuelPrices'][index]:
        if row['ProductName'] == 'DIESEL':
            df['diesel_price_change'][index] = {row['LastPriceChangeDateTime']:row['CashPrice']}

CodePudding user response：

can you try this:

df['test_v1']=df['FuelPrices'].apply(lambda x: {x[0]['LastPriceChangeDateTime']:x[0]['CashPrice']})

if you are getting TypeError: string indices must be integers use:

import ast
df['FuelPrices']=df['FuelPrices'].apply(ast.literal_eval)
df['test_v1']=df['FuelPrices'].apply(lambda x: {x[0]['LastPriceChangeDateTime']:x[0]['CashPrice']})