convert df column into json df column-CodePudding

I have a df

clearance_info
4431,by category 2,2022-02-03
4231,by category 1,2022-02-03
4331,by category 3,2022-02-03
4431,by category 4,2022-02-03

How to convert it into

clearance_info
{"price": 4431 ,"category" : "by category 2","timestamp" : "2022-02-03"}
{"price": 4231 ,"category" : "by category 1","timestamp" : "2022-02-03"}
{"price": 4331 ,"category" : "by category 3","timestamp" : "2022-02-03"}
{"price": 4431 ,"category" : "by category 4","timestamp" : "2022-02-03"}

Tried with orient split but that didnt work as expected. Suggestions please.

Orient help only if there is columns seperately present, i want these as fixed ones like mentioned.

CodePudding user response：

to_dict with orient="records"

cols = dict(enumerate(['price', 'category', 'timestamp']))

df.assign(
    clearance_info=
    df.clearance_info.str
      .split(',', expand=True)
      .rename(columns=cols)
      .to_dict('records')
)
    
                                      clearance_info
0  {'price': '4431', 'category': 'by category 2',...
1  {'price': '4231', 'category': 'by category 1',...
2  {'price': '4331', 'category': 'by category 3',...
3  {'price': '4431', 'category': 'by category 4',...

If you want this to persist in df

df = df.assign(
    clearance_info=
    df.clearance_info.str
      .split(',', expand=True)
      .rename(columns=cols)
      .to_dict('records')
)

CodePudding user response：

You could try:

df[['price', 'category','timestamp']] = df['clearance_info'].str.split(',',expand=True,n=3)
df['price'] = df['price'].astype(int)
df['clearance_info'] = df[['price', 'category','timestamp']].apply(lambda row: row.to_json(), axis=1)
df.drop(['price', 'category','timestamp'], axis=1, inplace=True)

Output:

    clearance_info
0   {"price":4431,"category":"by category 2","time...
1   {"price":4231,"category":"by category 1","time...
2   {"price":4331,"category":"by category 3","time...
3   {"price":4431,"category":"by category 4","time...