How to parse json file to DataFrame in Python

Question

I have some json data of phone accelerometer data. Which looks like this:

{u'timestamps': {u'1524771017235': [[u'x',
                                     u'y',
                                     u'z',
                                     u'rotationX',
                                     u'rotationY',
                                     u'rotationZ'],
                                    [-0.02, 0, 0.04, 102.65, 68.15, 108.61],
                                    [-0.03, 0.02, 0.02, 102.63, 68.2, 108.5],
                                    [-0.05, 0.01, 0.1, 102.6, 68.25, 108.4],
                                    [-0.02, 0, 0.09, 102.6, 68.25, 108.4],
                                    [-0.01, 0, 0.03, 102.6, 68.25, 108.4]]}}

What I want is to have a dataFrame with columns of the name of the data (x, y,z rotationX, rotationY, rotationZ) and the row of each data entry. The timestamp information can be stored elsewhere.

When I used d = pd.read_json('data.json'), this is what I get:

timestamps
2018-04-26 19:30:17.235 [[x, y, z, rotationX, rotationY, rotationZ], [...

It seems it takes the timestamps as index. And put all the rest in 1 cell.

I dont have much experience with json so the couldn't really make much sense out of the pandas.read_json api. please help.

My current workaround is to manually skip through the first 2 dictionaries. And create a df with the first column as the headers. It works but it is really not ideal...

dataDf = pd.DataFrame(data = d['timestamps']['1524771017235'][1:], columns = d['timestamps']['1524771017235'][0])

x   y   z   rotationX   rotationY   rotationZ
0   -0.02   0.00    0.04    102.65  68.15   108.61
1   -0.03   0.02    0.02    102.63  68.20   108.50
2   -0.05   0.01    0.10    102.60  68.25   108.40

Thanks

"It works but it is really not ideal..." Why not? It seems fine. — Ami Tavory
– Ami Tavory, Commented Apr 26, 2018 at 21:02
@AmiTavory because the time info (1524771017235 in this case) is different in each file. And with my way, I have to manually choose it for each file. — J_yang
– J_yang, Commented Apr 27, 2018 at 12:00

Ben.T · Accepted Answer · 2018-04-27 13:22:59Z

1

What you need is to get access to the key of the dictionary {u'1524771017235': [[u'x', ... being the value associated to the key timestamps of the dictionary d associated to your json file. Then try:

d['timestamps'].keys()[0]

and it should return your '1524771017235' value, so to create your dataDf just do:

dataDf = pd.DataFrame(data = d['timestamps'][d['timestamps'].keys()[0]][1:], 
                      columns = d['timestamps'][d['timestamps'].keys()[0]][0])

and you get the same result.

answered Apr 27, 2018 at 13:22

Ben.T

29.7k6 gold badges39 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

J_yang Over a year ago

thank you very much. The keys is exactly what I need.

Collectives™ on Stack Overflow

How to parse json file to DataFrame in Python

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related