loading csv file using pandas in python

Question

Here is my sample data:

2017-11-27T00:29:37.698-06:00,,"42,00,00,00,3E,51,1B,D7,42,1C,00,00,40"
2017-11-27T00:29:37.698-06:00,,"42,00,00,00,3E,51,1B,D7,42,1C,00,00,40"
2017-11-27T00:29:37.698-06:00,,"42,00,00,00,3E,51,1B,D7,42,1C,00,00,40"

I tried to load the data using pandas using :

data = pd.read_csv("sample.csv",header = None)

My output is:

                0                 1           2
0  2017-11-27T00:29:37.698-06:00 NaN  42,00,00,00,3E,51,1B,D7,42,1C,00,00,40
1  2017-11-27T00:29:37.698-06:00 NaN  42,00,00,00,3E,51,1B,D7,42,1C,00,00,40
2  2017-11-27T00:29:37.698-06:00 NaN  42,00,00,00,3E,51,1B,D7,42,1C,00,00,40

I wanted to separate each data in second column with first column as time stamp.

My expected output would be:

    0                             1  2  3  4....
0  2017-11-27T00:29:37.698-06:00  42 00 00 00
1  2017-11-27T00:29:37.698-06:00  42 00 00 00
2  2017-11-27T00:29:37.698-06:00  42 00 00 00

Answer 1

You can, if needed, do your own csv parser like:

Code:

def read_my_csv(filename):
    with open(filename, 'rU') as f:

        # build csv reader
        reader = csv.reader(f)

        # for each row, check for footer
        for row in reader:
            yield [row[0]] + row[2].split(',')

Test Code:

import csv
import pandas as pd

df = pd.DataFrame(read_my_csv('csvfile.csv'))
print(df)

Results:

                                  0   1   2   3   4   5   6   7   8   9   10  \
0      2017-11-27T00:29:37.698-06:00  42  00  00  00  3E  51  1B  D7  42  1C   
1      2017-11-27T00:29:37.698-06:00  42  00  00  00  3E  51  1B  D7  42  1C   
2      2017-11-27T00:29:37.698-06:00  42  00  00  00  3E  51  1B  D7  42  1C   

   11  12  13  
0  00  00  40  
1  00  00  40  
2  00  00  40