How to transform json format into string column for python dataframe?-CodePudding

I got this dataframe:

Dataframe: df_case_1

   Id                                       RecordType
0  1234  {'attributes': {'type': 'RecordType', 'url': '/services/data/v55.0/sobjects/RecordType/1234', 'name', 'XYZ'}}
1  4321  {'attributes': {'type': 'RecordType', 'url': '/services/data/v55.0/sobjects/RecordType/4321', 'name', 'ABC'}}

I want to have this dataframe:

Dataframe: df_case_final

   Id   RecordType
0  1234   'XYZ'
1  4321   'ABC'

At the moment I use this statemane but it gives me the name on position 0 for every case object. df_case_1['RecordType'] = df_case_1.RecordType[0]['Name']

How to build the statement, that I give me the correct name for every id, like in df_case_final?

Thanks

CodePudding user response：

There are 3 Ways you can convert JSON to Pandas Dataframe

# 1. Use json_normalize() to convert JSON to DataFrame
dict= json.loads(data)
df = json_normalize(dict['technologies']) 

# 2. Convert JSON to DataFrame Using read_json()
df2 = pd.read_json(jsonStr, orient ='index')

# 3. Use pandas.DataFrame.from_dict() to Convert JSON to DataFrame
dict= json.loads(data)
df2 = pd.DataFrame.from_dict(dict, orient="index")

Now, after converting Json to df take the last column and append it to your original dataframe

CodePudding user response：

split your df by coma & trim un-neccessary cols

import pandas as pd

df=pd.read_csv(r"Hansmuff.csv")

df[['1', '2','3','required']]=df['RecordType'].str.split(',', expand=True)

df = df.drop(columns=['RecordType', '1','2','3'])

df['required'] = df['required'].str.strip('{}')

print(df)

output

  Id required
0   1234    'XYZ'
1   4321   'ABC'