Converting a JSON file to CSV file with multivalue features

user_13835213 0
Viewed 67

I have to do a Machine Learning project and my data set is in the form of JSON files. I have a feature position with 3 values (x,y,z). When I convert the JSON files to CSV files with Python I have the values of feature position in the form of array.

How can I generate from one feature three features pos_x, pos_y and pos_z ??

JSON: "pos":[3838.387671754935,5853.151423739182,1.895]
CSV: "pos": "[3838.387671754935,5853.151423739182,1.895]"

But I must have 3 separated features pos_x : 3838.387671754935, pos_y: 5853.151423739182, pos_z: 1.895

The code I used :

import pandas as pd 
import json

data = []

with open('JSONfile.json') as fh:
    for line in fh:
        data.append(json.loads(line))

df = pd.DataFrame.from_dict(data)
df.to_csv ('csvFile.csv', index = False)
pandas machine-learning csv python json
1 Answers

You can use Miller (mlr) to produce all kind of different output:

mlr --ijson --ocsv cat multivalued.json

which will return the following CSV

pos.1,pos.2,pos.3
3838.387671754935,5853.151423739182,1.895

or for a table formatting

+-------------------+-------------------+-------+
| pos.1             | pos.2             | pos.3 |
+-------------------+-------------------+-------+
| 3838.387671754935 | 5853.151423739182 | 1.895 |
+-------------------+-------------------+-------+

with this alternate

mlr --ijson --opprint --barred cat multivalued.json

and if you don't specify output format (CSV, pretty print...) it will use DKVP (key-value pair):

pos.1=3838.387671754935,pos.2=5853.151423739182,pos.3=1.895

NB: the index is numeric and separator is dot (.1, .2... instead of _a, _b, etc.)

user_95411010
Related