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?

score 1 · Answer 1 · answered Feb 25 '16 at 11:03

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

score 1 · Answer 2 · edited May 23 '17 at 12:15

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

score 1 · Accepted Answer · answered Feb 25 '16 at 11:08

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

Loading Json into Pandas dataframe

3 Answers3