pandas updating a dict column value-CodePudding

I have df as below, where "Fee_detail" column value is dict. I need to get the keys and values of 'date' and 'penalty' from "Fee_detail" column and update in "Fee_info" column which will have empty dict by default.

d = {'Fee_detail': [{'date':'datetime.datetime(2022, 5, 5, 7, 31)', 'Fees':1200, 'penalty':0}, {'date':'datetime.datetime(2022, 5, 7, 7, 31)', 'Fees':1000, 'penalty':10}, {'date':'datetime.datetime(2022, 5, 11, 7, 31)', 'Fees':800,'penalty':50}], 'Name': ["Adam", "Laura","Andrew"], 'Fee_info':[{},{},{}]}
df = pd.DataFrame(data=d)

df :

Fee_detail	Name	Fee_info
{'date':'datetime.datetime(2022, 5, 5, 7, 31)', 'Fees':1200, 'penalty':0}	"Adam"	{}
{'date':'datetime.datetime(2022, 5, 7, 7, 31)', 'Fees':1000, 'penalty':10}	"Laura"	{}
{'date':'datetime.datetime(2022, 5, 11, 7, 31)', 'Fees':800,'penalty':50}	"Andrew"	{}

Expected output:

Fee_detail	Name	Fee_info
{'date':'datetime.datetime(2022, 5, 5, 7, 31)', 'Fees':1200, 'penalty':0}	"Adam"	{'date':05-05-2022,'penalty':0}
{'date':'datetime.datetime(2022, 5, 7, 7, 31)', 'Fees':1000, 'penalty':10}	"Laura"	{'date':07-05-2022,'penalty':10}
{'date':'datetime.datetime(2022, 5, 11, 7, 31)', 'Fees':800,'penalty':50}	"Andrew"	{'date':11-05-2022, 'penalty':50}

How can I do this?

CodePudding user response：

Unfortunately, due to the bad input type, you have to loop and evaluate your string:

from ast import literal_eval
from datetime import datetime

df['Fee_info'] = [{'date': datetime(*literal_eval(d['date'][17:]))
                                   .strftime('%d-%m-%Y'),
                   'penalty': d['penalty']}
                  for d in df['Fee_detail']]

output:

                                                                      Fee_detail    Name                               Fee_info
0   {'date': 'datetime.datetime(2022, 5, 5, 7, 31)', 'Fees': 1200, 'penalty': 0}    Adam   {'date': '05-05-2022', 'penalty': 0}
1  {'date': 'datetime.datetime(2022, 5, 7, 7, 31)', 'Fees': 1000, 'penalty': 10}   Laura  {'date': '07-05-2022', 'penalty': 10}
2  {'date': 'datetime.datetime(2022, 5, 11, 7, 31)', 'Fees': 800, 'penalty': 50}  Andrew  {'date': '11-05-2022', 'penalty': 50}

CodePudding user response：

With a slight modification of mozway solution, this could also be used:

import pandas as pd

df['Fee_info'] = [{'date': eval(re.sub(r'datetime\.', '', df.Fee_detail[i]['date'])).strftime('%Y-%m-%d'),
                   'fee_info' : df.Fee_detail[i]['penalty']} 
                  for i in range(0, len(df.index))
                  ]

Output:

                                          Fee_detail  ...                                Fee_info
0  {'date': 'datetime.datetime(2022, 5, 5, 7, 31)...  ...   {'date': '2022-05-05', 'fee_info': 0}
1  {'date': 'datetime.datetime(2022, 5, 7, 7, 31)...  ...  {'date': '2022-05-07', 'fee_info': 10}
2  {'date': 'datetime.datetime(2022, 5, 11, 7, 31...  ...  {'date': '2022-05-11', 'fee_info': 50}