CODEWITHSUNDEEP
pythonpandascsvdictionary
Morten Nissov
Morten Nissov

Reputation: 452

Reading csv file formatted as dictioary into pandas

I have a csv file containing sensor data where one row is of the following format

1616580317.0733, {'Roll': 0.563820598084682, 'Pitch': 0.29817540218781163, 'Yaw': 60.18415650363684, 'gyroX': 0.006687641609460116, 'gyroY': -0.012394784949719908, 'gyroZ': -0.0027120113372802734, 'accX': -0.12778355181217196, 'accY': 0.24647256731987, 'accZ': 9.763526916503906}

Where the first column is a timestamp and the remainder is a dictionary like object containing various measured quantities.

I want to read this into a pandas array wit the columns ["Timestamp","Roll","Pitch","Yaw","gyroX","gyroY","gyroZ","accX","accY","accZ"]. What would be an efficient way of doing this? The file is 600MB so it's not a trivial number of lines which need to be parsed.

Upvotes: 1

Views: 104

Answers (2)

GuillemB
GuillemB

Reputation: 621

I'm not sure where you are getting the seconds column from.

The code below parses each row into a timestamp and dict. Then adds the timestamp to the dictionary that will eventually become a row in the dataframe.

import json
import pandas as pd


def read_file(filename):
    

    chunk_size = 20000
    entries = []
    counter = 0
    
    df = pd.DataFrame()

    with open(filename, "r") as fh:
        for line in fh:
            timestamp, data_dict = line.split(",", 1)
            data_dict = json.loads(data_dict.replace("'", '"'))
            data_dict["timestamp"] = float(timestamp)
            entries.append(data_dict)
            counter += 1
            
            if counter == chunk_size:
                df = df.append(entries, ignore_index=True)
                entries = []
                counter = 0
                
        if counter != 0:
            df = df.append(entries, ignore_index=True)

                
    return df

read_file("sample.txt")

Upvotes: 1

Toinou
Toinou

Reputation: 3

I think you should convert your csv file to json format and then look at this site on how to transform the dictionary into a pandas dataframe : https://www.delftstack.com/fr/howto/python-pandas/how-to-convert-python-dictionary-to-pandas-dataframe/#:~:text=2%20banana%2012-,M%C3%A9thode%20pandas.,le%20nom%20de%20la%20colonne.

Upvotes: 0

Related Questions