Loading Json into Pandas dataframe

Question

I have a valid json file with the following format that I am trying to load into pandas.

{
    "testvalues": [
        [1424754000000, 0.7413],
        [1424840400000, 0.7375],
        [1424926800000, 0.7344],
        [1425013200000, 0.7375],
        [1425272400000, 0.7422],
        [1425358800000, 0.7427]
    ]
}

There is a Pandas function called read_json() that takes in json files/buffers and spits out the dataframe but I have not been able to get it to load correctly, which is to show two columns rather than a single column with elements looking like [1424754000000, 0.7413]. I have tried different 'orient' and 'typ' to no avail. What options should I pass into the function to get it to spit out a two column dataframe corresponding the timestamp and the value?

jezrael · Accepted Answer · 2016-02-25 11:08:33Z

1

You can use list comprehension with DataFrame contructor:

import pandas as pd

df = pd.read_json('file.json')
print df
                testvalues
0  [1424754000000, 0.7413]
1  [1424840400000, 0.7375]
2  [1424926800000, 0.7344]
3  [1425013200000, 0.7375]
4  [1425272400000, 0.7422]
5  [1425358800000, 0.7427]

print pd.DataFrame([x for x in df['testvalues']], columns=['a','b'])
               a       b
0  1424754000000  0.7413
1  1424840400000  0.7375
2  1424926800000  0.7344
3  1425013200000  0.7375
4  1425272400000  0.7422
5  1425358800000  0.7427

answered Feb 25, 2016 at 11:08

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

Comments

Anton Protopopov · Accepted Answer · 2016-02-25 11:03:21Z

I'm not sure about pandas read_json but IIUC you could do that with astype(str), str.split, str.strip:

d = {
    "testvalues": [
        [1424754000000, 0.7413],
        [1424840400000, 0.7375],
        [1424926800000, 0.7344],
        [1425013200000, 0.7375],
        [1425272400000, 0.7422],
        [1425358800000, 0.7427]
    ]
}

df = pd.DataFrame(d)
res = df.testvalues.astype(str).str.strip('[]').str.split(', ', expand=True)


In [112]: df
Out[112]:
                testvalues
0  [1424754000000, 0.7413]
1  [1424840400000, 0.7375]
2  [1424926800000, 0.7344]
3  [1425013200000, 0.7375]
4  [1425272400000, 0.7422]
5  [1425358800000, 0.7427]

In [113]: res
Out[113]:
               0       1
0  1424754000000  0.7413
1  1424840400000  0.7375
2  1424926800000  0.7344
3  1425013200000  0.7375
4  1425272400000  0.7422
5  1425358800000  0.7427

Community · Accepted Answer · 2017-05-23 12:15:41Z

1

You can apply a function that splits it into a pd.Series.

Say you start with

df = pd.read_json(s)

Then just apply a splitting function:

>>>     df.apply(
    lambda r: pd.Series({'l': r[0][0], 'r': r[0][1]}),
    axis=1)
    l   r
0   1.424754e+12    0.7413
1   1.424840e+12    0.7375
2   1.424927e+12    0.7344
3   1.425013e+12    0.7375
4   1.425272e+12    0.7422
5   1.425359e+12    0.7427

edited May 23, 2017 at 12:15

CommunityBot

11 silver badge

answered Feb 25, 2016 at 11:07

Ami Tavory

76.7k13 gold badges152 silver badges196 bronze badges

Collectives™ on Stack Overflow

Loading Json into Pandas dataframe

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related