I converted a JSON into DataFrame
and ended up with a column 'Structure_value' having below values as list of dictionary/dictionaries:
Structure_value
[{'Room': 6, 'Length': 7}, {'Room': 6, 'Length': 7}]
[{'Room': 6, 'Length': 22}]
[{'Room': 6, 'Length': 8}, {'Room': 6, 'Length': 9}]
Since it is an object so I guess it ended up in this format.
I need to split it into below four columns:
Structure_value_room_1
Structure_value_length_1
Structure_value_room_2
Structure_value_length_2
All other solutions on StackOverflow only deal with converting Simple JSON into DataFrame and not the nested structure.
P.S.: I know I can do something by explicitly naming fields but I need a generic solution so that in future any JSON of this format can be handled
[Edit]: The output should look like this:
Structure_value_room_1 Structure_value_length_1 Structure_value_room_2 \
0 6 7 6.0
1 6 22 NaN
2 6 8 6.0
Structure_value_length_2
0 7.0
1 NaN
2 9.0
Pandas have a nice inbuilt function called json_normalize() to flatten the simple to moderately semi-structured nested JSON structures to flat tables. Parameters: data – dict or list of dicts. errors – {'raise', 'ignore'}, default 'raise'
Flatten a JSON object: var flatten = (function (isArray, wrapped) { return function (table) { return reduce("", {}, table); }; function reduce(path, accumulator, table) { if (isArray(table)) { var length = table.
Use list comprehension with nested dictionary comprehension with enumerate for deduplicate keys of dicts, last pass list of dictionaries to DataFrame
constructor:
L = [ {f"{k}_{i}": v for i, y in enumerate(x, 1)
for k, v in y.items()}
for x in df["Structure_value"] ]
df = pd.DataFrame(L)
print(df)
Room_1 Length_1 Room_2 Length_2
0 6 7 6.0 7.0
1 6 22 NaN NaN
2 6 8 6.0 9.0
For columns names from question use:
def json_to_df(df, column):
L = [ {f"{column}_{k.lower()}_{i}": v for i, y in enumerate(x, 1)
for k, v in y.items()}
for x in df[column] ]
return pd.DataFrame(L)
df1 = json_to_df(df, 'Structure_value')
print(df1)
Structure_value_room_1 Structure_value_length_1 Structure_value_room_2 \
0 6 7 6.0
1 6 22 NaN
2 6 8 6.0
Structure_value_length_2
0 7.0
1 NaN
2 9.0
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With