I have an excel file that looks something like this:

Header3 contains JSON strings that look like this

[
{"key1":"123","key2":"Value1"},
{"key1":"123","key2":"Value2"}, 
{"key1":"123","key2":"Value3"}
]

I would like to parse the JSON Header3 column and for each key create a column with the name of the key appended with the value of key2, the keys are always the same throughout the file.

The end data frame should look something like this:

Actual example:

Needs to become:

Before getting into fancy stuff I tried to at least normalize the data, but I'm returned with an empty series.

df = pd.read_excel('test.xlsx')
pd.json_normalize(df.JSON)

Output:
0
1
2
3
4
5
...
188 rows x 0 columns

CodePudding user response：

You can't normalize it because it's loaded as string from Excel. Try this:

import json

s = df["JSON"].apply(json.loads).explode()
tmp = (
    pd.DataFrame(s.to_list(), index=s.index)
    .set_index("card", append=True)
    .unstack()
)
tmp.columns = [".".join(col) for col in tmp.columns]

pd.concat([df[["Company"]], tmp], axis=1)

The card.* columns look kinda weird. If you know what column you are referring to, you already know its value so why include it in the output dataframe?