How can I split and convert the output file to columns with column names?

Question

I have a file like below.

{time: "2014-01-01 00:22:01.150000000", scaledAccelX: "0.039298", scaledAccelY: "0.005923", scaledAccelZ: "-0.994687", scaledGyroX: "0.001538", scaledGyroY: "0.000883", scaledGyroZ: "-0.000879", scaledMagX: "0.325602", scaledMagY: "-0.209304", scaledMagZ: "0.145305", scaledAmbientPressure: "1016.627380", deltaThetaX: "0.000141", deltaThetaY: "0.000108", deltaThetaZ: "-0.000096", deltaVelX: "0.003646", deltaVelY: "0.000563", deltaVelZ: "-0.093733", roll: "-0.005220", pitch: "0.042930", yaw: "0.558326", orientQuaternion: "[0.961046,-0.00842277,0.0199133,0.27554]", orientMatrix: "[[0.847362,0.529279,-0.0429168],[-0.52995,0.848013,-0.0052155],[0.0336336,0.0271632,0.999065]]", stabilizedMagX: "0.325882", stabilizedMagY: "-0.208165", stabilizedMagZ: "0.148896", stabilizedAccelX: "0.042812", stabilizedAccelY: "0.005203", stabilizedAccelZ: "-0.996619", gpsCorrelTimestampTow: "309057.015000", gpsCorrelTimestampWeekNum: "2245", gpsCorrelTimestampFlags: "5"}, {time: "2014-01-01 00:22:01.200000000", scaledAccelX: "0.038701", scaledAccelY: "0.007346", scaledAccelZ: "-0.997266", scaledGyroX: "0.001427", scaledGyroY: "0.001284", scaledGyroZ: "-0.001059", scaledMagX: "0.326149", scaledMagY: "-0.211073", scaledMagZ: "0.147229", scaledAmbientPressure: "1016.640015", deltaThetaX: "0.000049", deltaThetaY: "0.000060", deltaThetaZ: "-0.000060", deltaVelX: "0.001957", deltaVelY: "0.000418", deltaVelZ: "-0.049938", roll: "-0.005225", pitch: "0.042927", yaw: "0.558373", orientQuaternion: "[0.96104,-0.00842497,0.0199111,0.275563]", orientMatrix: "[[0.847337,0.529319,-0.042914],[-0.52999,0.847988,-0.00521991],[0.0336276,0.027167,0.999065]]", stabilizedMagX: "0.325892", stabilizedMagY: "-0.208194", stabilizedMagZ: "0.148905", stabilizedAccelX: "0.042810", stabilizedAccelY: "0.005207", stabilizedAccelZ: "-0.996652", gpsCorrelTimestampTow: "309057.065000", gpsCorrelTimestampWeekNum: "2245", gpsCorrelTimestampFlags: "5"},

I want to split the lines and convert each variable to columns with respective column names?

You probably want [`read_json`](https://pandas.pydata.org/docs/reference/api/pandas.read_json.html) — Nick, Mar 12 '23 at 06:12
@Corralien I have the same lines repeating in the single file with different values. — Asish K Sasidharan, Mar 12 '23 at 18:58
All lines start with `{` and close by `}`? And lines end by `,`? — Corralien, Mar 12 '23 at 19:01

Laurent B. · Accepted Answer · 2023-03-12T11:41:32.213

import pandas as pd

d = {'time': "2014-01-01 00:22:01.150000000", 
     'scaledAccelX': "0.039298", 
     'scaledAccelY': "0.005923", 
     'scaledAccelZ': "-0.994687", 
     # .... 
     'gpsCorrelTimestampTow': "309057.015000", 
     'gpsCorrelTimestampWeekNum': "2245", 
     'gpsCorrelTimestampFlags': "5"}

d = str([d]).replace("'", '"')
df = pd.read_json(d, orient='records')

print(df)

                            time  ...  gpsCorrelTimestampFlags
0  2014-01-01 00:22:01.150000000  ...                        5

[1 rows x 31 columns]

Explanation

JSON is a strict format, you have to use " inside strings. So I use replace for that.

The string you provide is under the records format.

It's just missing list brackets

https://stackoverflow.com/questions/36038454/parsing-string-as-json-with-single-quotes — Laurent B., Mar 12 '23 at 11:43

How can I split and convert the output file to columns with column names?

1 Answers1