how to extract database column in json format into multiple columns in dataframe-CodePudding

I have a database column that's been converted to a Pandas dataframe and it looks like below . My actual data has much more columns and rows with different key: value pair.

df["Records"]:{"ID":"1","ID_1":"40309","type":"type1"}

I want to split this into multiple columns in a dataframe.

df1:
ID  ID_1   type
1   40309  type1

I tried this code but data is saving as the index.

json_Str=df["Records"].to_json()
json_dump= json.dumps(json_Str)
df1=pd.read_json(json_Str,lines=True,orient='records')

How do I convert a json column to csv format in pandas?

CodePudding user response：

not entirely sure I understand the question but if youre just trying to take your data out of index, just use

df1.reset_index(drop=False)

or if youre trying to convert rows to columns you could use df1.transpose()

CodePudding user response：

I think this is what you're after

# using the same data repeatedly to highlight effect
df = pd.DataFrame({
    "Records": [
        {"ID":"1","ID_1":"40309","type":"type1"},
        {"ID":"1","ID_1":"40309","type":"type1"},
        {"ID":"1","ID_1":"40309","type":"type1"}
    ]
})

# start data looks like...
print(df)

                                         Records
0  {'ID': '1', 'ID_1': '40309', 'type': 'type1'}
1  {'ID': '1', 'ID_1': '40309', 'type': 'type1'}
2  {'ID': '1', 'ID_1': '40309', 'type': 'type1'}

# "explode" the dictionaries in df.Records
df1 = pd.DataFrame(df.Records.to_list())

print(df1)

  ID   ID_1   type
0  1  40309  type1
1  1  40309  type1
2  1  40309  type1

Reference -- Split a Pandas column of lists into multiple columns