Problem with getting values in a row instead of a column from .json file

Question

I have several .json files that I would like to combine into one .csv file. The .json files look like this:

{"version":1.3,"people":[{"person_id":[-1],"pose_keypoints_2d":[346.096,136.31,0.822246,339.237,210.457,0.840786,264.747,203.769,0.807762,149.518,183.631,0.854263,88.6763,82.0749,0.906274,400.122,210.38,0.854152,508.225,183.228,0.898651,555.846,75.2072,0.958429,345.636,345.892,0.111406,291.626,346.153,0.194191,0,0,0,0,0,0,393.258,346.08,0.15249,0,0,0,0,0,0,339.241,136.084,0.804675,352.463,136.006,0.702916,305.539,156.42,0.639209,359.362,156.248,0.325405,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0],
"face_keypoints_2d":[],"hand_left_keypoints_2d":[],"hand_right_keypoints_2d":[],"pose_keypoints_3d":[],
"face_keypoints_3d":[],"hand_left_keypoints_3d":[],"hand_right_keypoints_3d":[]}]}

The code I use to load and combine files:

import json
import os
import pandas as pd

data = []

for file in os.listdir("C:/Users/ciach/PycharmProjects/CSV/"):
    if file.endswith(".json"):
        data2 = json.load(open(os.path.join("C:/Users/ciach/PycharmProjects/CSV/", file)))
        df = pd.json_normalize(data2,record_path=['people','pose_keypoints_2d'])
        data.append(df)

temp = pd.concat(data, ignore_index = True) 
temp.to_csv("keypoints.csv", index=False)

After compilation with two files, I get the following results in .csv file (all values are in one column):

I would like to get the values only from the list 'pose_keypoints_2d' in one row for each .json file instead of one whole column from all files. Something like that:

338.938000 156.226000 0.322833 ...  0.000000  0.000000
346.096000 136.310000 0.822246 ...  0.000000  0.000000

I think it is trivial to solve, but I cannot show you because shown input and output looks unrelated. Where is the sequence 338.938000 156.226000 0.322833 ... 0.000000 0.000000??? — Serge Ballesta
– Serge Ballesta, Commented Dec 20, 2020 at 11:12

IoaTzimas · Accepted Answer · 2020-12-20 11:18:11Z

2

You df is a column of 75 rows when it's added to data2.

Try replacing this:

data.append(df)

by this

data.append(df.T)

Which (df.T) is one row with 75 columns

answered Dec 20, 2020 at 11:18

IoaTzimas

10.7k2 gold badges15 silver badges32 bronze badges

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

2 Comments

Fortides Over a year ago

It wokrs! Thank you very much

IoaTzimas Over a year ago

Great, please accept answer so that it will leave the queue of 'unanswered' questions. Cheers

Collectives™ on Stack Overflow

Problem with getting values in a row instead of a column from .json file

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related