Styjun

I know there are quite similar questions so far, but I just don't get a right idea sorry.

I would like to convert a complex json to a clean pandas dataframe.

My Code so far:

with open('JSON_Input.json', 'r') as json_file:
 json_data = json.load(json_file)
 json_data = json.loads(json_data)

This creates the following complex, nested json object:

json_data 

'time': 0,
 'day1': ['time': 0,
 'coordinates': ['x': 1202.5, 'y': 486, 'time': 3276,
 'x': 1162.5, 'y': 484, 'time': 3331,
 'x': 742.5, 'y': 492.5, 'time': 3487,
 'x': 673.5, 'y': 501.5, 'time': 3514,
 'x': 636, 'y': 508.5, 'time': 3539],
 'path': 'path1',
 'time': 3558,
 'coordinates': ['x': 1237, 'y': 173, 'time': 5437,
 'x': 1240, 'y': 182, 'time': 5601,
 'x': 1260, 'y': 161, 'time': 7289,
 'x': 1263, 'y': 165, 'time': 7465,
 'x': 1482, 'y': 114.5, 'time': 8072,
 'x': 1482, 'y': 114, 'time': 8197,
 'x': 1482, 'y': 126.5, 'time': 9539],
 'path': 'path2',
 'time': 23620,
 'coordinates': ['x': 227.5, 'y': 420, 'time': 25228,
 'x': 235, 'y': 418, 'time': 25426],
 'path': 'path3',
 'time': 35891,
 'coordinates': ['x': 681.5, 'y': 431, 'time': 36648,
 'x': 704.5, 'y': 427.5, 'time': 36661,
 'x': 874.5, 'y': 420.5, 'time': 36714,
 'x': 909.5, 'y': 422, 'time': 36734]],
 'day2': 'path': 'path4',
 'time': 36743,
 'coordinates': ['x': 600, 'y': 622.5, 'time': 37390,
 'x': 603, 'y': 594.5, 'time': 37448,
 'x': 605, 'y': 541.5, 'time': 37478,
 'x': 608.5, 'y': 481.5, 'time': 37495,
 'x': 620, 'y': 369, 'time': 37530,
 'x': 624.5, 'y': 329, 'time': 37547,
 'x': 636, 'y': 366, 'time': 38043]

Now how can get a clean dataframe out of this json file?

This is tricky. You're going to end up with lots of nulls, and I also don't know exactly how you want the end datframe to look like. But maybe this can get you going in the right direction:

jsonObj = 'time': 0,
 'day1': ['time': 0,
 'coordinates': ['x': 1202.5, 'y': 486, 'time': 3276,
 'x': 1162.5, 'y': 484, 'time': 3331,
 'x': 742.5, 'y': 492.5, 'time': 3487,
 'x': 673.5, 'y': 501.5, 'time': 3514,
 'x': 636, 'y': 508.5, 'time': 3539],
 'path': 'path1',
 'time': 3558,
 'coordinates': ['x': 1237, 'y': 173, 'time': 5437,
 'x': 1240, 'y': 182, 'time': 5601,
 'x': 1260, 'y': 161, 'time': 7289,
 'x': 1263, 'y': 165, 'time': 7465,
 'x': 1482, 'y': 114.5, 'time': 8072,
 'x': 1482, 'y': 114, 'time': 8197,
 'x': 1482, 'y': 126.5, 'time': 9539],
 'path': 'path2',
 'time': 23620,
 'coordinates': ['x': 227.5, 'y': 420, 'time': 25228,
 'x': 235, 'y': 418, 'time': 25426],
 'path': 'path3',
 'time': 35891,
 'coordinates': ['x': 681.5, 'y': 431, 'time': 36648,
 'x': 704.5, 'y': 427.5, 'time': 36661,
 'x': 874.5, 'y': 420.5, 'time': 36714,
 'x': 909.5, 'y': 422, 'time': 36734]],
 'day2': 'path': 'path4',
 'time': 36743,
 'coordinates': ['x': 600, 'y': 622.5, 'time': 37390,
 'x': 603, 'y': 594.5, 'time': 37448,
 'x': 605, 'y': 541.5, 'time': 37478,
 'x': 608.5, 'y': 481.5, 'time': 37495,
 'x': 620, 'y': 369, 'time': 37530,
 'x': 624.5, 'y': 329, 'time': 37547,
 'x': 636, 'y': 366, 'time': 38043]










import pandas as pd
import re


def flatten_json(y):
 out = 

 def flatten(x, name=''):
 if type(x) is dict:
 for a in x:
 flatten(x[a], name + a + '_')
 elif type(x) is list:
 i = 0
 for a in x:
 flatten(a, name + str(i) + '_')
 i += 1
 else:
 out[name[:-1]] = x

 flatten(y)
 return out

results = pd.DataFrame()
for k in jsonObj:

 flat = flatten_json(jsonObj[k]) 


 temp_df = pd.DataFrame()
 special_cols = []

 columns_list = list(flat.keys())
 for item in columns_list:
 try:
 row_idx = re.findall(r'_(d+)_', item )[0]
 except:
 special_cols.append(item)
 continue
 column = re.findall(r'_d+_(.*)', item )[0]
 column = column.replace('_', '')

 row_idx = int(row_idx)
 value = flat[item]

 temp_df.loc[row_idx, column] = value

 for item in special_cols:
 temp_df[item] = flat[item]

 if 'day' in k:
 temp_df['day'] = k
 results = results.append(temp_df).reset_index(drop=True)

results = results.dropna(axis=1, how='all')