transferring JSON to pandas DataFrame

Question

I am trying to read some data from two json files, and find the users with the same name and then get the values of the specific key from them, however, I had an issue with one of them (data_bvp) that has 100 rows and 1 column in a list. when I saved the data frame as a csv file the, I have got this

The first json file looks like this:

{ "Name": "Alex" :["bvp":[1,2,3], "gsr":[3,2,1] ] } , { "Name": "Laura" :["bvp":[2,3,4], "gsr":[3,2,1] ] }

The secon jason file has the following elements:

{ "Name" : "Alex" : ["calm" :0 , "tension":1] }, { "Name": "Laura" : ["calm":1, "tesnion":0}

I am gonig to get the data that has the same name to get the values of bvp and tension from each json file and save it as a csv file.

it seems that data does not save in the right format like [[1,2,3], [2,3,4]] I can see the list looks like this : [1,2,3 , [2,3,4

actually, my final dataset would look like this :

 name   col1 col2 col3 tension
  Alex   1   2    3    1
  Laura  2   3    4    0

Do you have any idea what part of my code has a problem? Thank you!

with open('raw_data-export.json') as data_file:
    data_bvp = json.loads(data_file.read())
arr_b = list(data_bvp.keys())

with open('v2-1-data-export.json') as data_file_2:
    data_tension = json.loads(data_file_2.read())
arr_tension = list(data_tension.keys())

matches =[]
for i in arr_b:
    for j in arr_tension:
        if i == j:
            matches.append(i)
print("MATCHES", matches)


bvps = []
tension = []

for b in matches:
    bvps.append(data_bvp[b]['bvp'])
       # PROBLEM 

for t in matches:
    tension.append(data_tension[t]['pre']['tense'])



bvp_data_frame = pd.DataFrame(bvps)
bvp_data_frame.to_csv('./bvp.csv')

Edit you question to add a sample. Few lines of each json are enough, just be sure that using you code we can see the problem — Valentino, Jul 03 '19 at 16:35
you can just json_load and use json_normalize() from pandas.io.json. check https://stackoverflow.com/questions/21104592/json-to-pandas-dataframe — Jiss Raphel, Jul 03 '19 at 17:25

Valdi_Bo · Answer 1 · 2019-07-03T19:15:08.477

I created the test DataFrames as follows:

t1 = '''[
    {"Name": "Alex",  "bvp": [1,2,3], "gsr": [3,2,1]},
    {"Name": "Laura", "bvp": [2,3,4], "gsr": [3,2,1]} ]'''
df1 = pd.DataFrame(json.loads(t1))    
t2 = '''[
    {"Name": "Alex",  "calm": 0, "tension": 1},
    {"Name": "Laura", "calm": 1, "tension": 0} ]'''
df2 = pd.DataFrame(json.loads(t2))

(slightly changed, as your original JSON string resulted in an error).

The first step is to create a work DataFrame - a break of bvp column into separate columns, with original index:

wrk = pd.DataFrame(df1.bvp.tolist(), index=df1.index,
    columns=['col1', 'col2', 'col3'])

The result is:

   col1  col2  col3
0     1     2     3
1     2     3     4

And to get the final result, run:

df1.join(wrk).drop(columns=['bvp', 'gsr']).merge(df2, on='Name')

Note the sequence of operations:

First df1 is joined with wrk (on the index).
Then bvp and gsr columns are dropped.
And finally the result is merged with df2 (on Name).

transferring JSON to pandas DataFrame

1 Answers1