How to group raw sensor data from csv file using python

Question

I am getting three types of sensor data from mobile(Accelerometer,Gyroscope and Magnetic field) with the following code (pretty bad as I am just beginning with python, but they are working anyhow :) ).

import socket, traceback
host = ''
port = 5555

s = socket.socket(socket.AF_INET, socket.SOCK_DGRAM)
s.setsockopt(socket.SOL_SOCKET, socket.SO_REUSEADDR, 1)
s.setsockopt(socket.SOL_SOCKET, socket.SO_BROADCAST, 1)
s.bind((host, port))

#used for debugging
count=0
print("Success binding")
while count<10:
    message, address = s.recvfrom(5555)
    messageString = message.decode("utf-8")
    print(messageString)
    count=count+1
    with open('yourfile.csv','a') as f:
     f.write(messageString)
     f.close()

However the output I am getting in the csv file is like this--

205729.68706, 3,   0.464, -0.425,  9.903, 4,  -0.000,  0.001, -0.000, 5,  36.290,  4.707,-24.930205729.88997, 3,   0.443, -0.433,  9.885, 4,  -0.000,  0.001,  0.000205730.09288, 3,   0.465, -0.446,  9.894, 4,  -0.000, -0.000, -0.000, 5,  37.206,  5.412,-24.298205730.29576, 3,   0.468, -0.432,  9.897, 4,  -0.000, -0.000, -0.000, 5,  36.308,  5.411,-25.554205730.49867, 3,   0.442, -0.424,  9.883, 4,   0.000,  0.000, -0.000, 5,  36.868,  4.611,-26.465205730.70155, 3,   0.467, -0.444,  9.888, 4,  -0.000,  0.000, -0.000, 5,  37.498,  5.637,-25.841205730.90446, 3,   0.464, -0.435,  9.904, 4,  -0.001,  0.000, -0.000, 5,  36.301,  5.049,-24.931205731.10734, 3,   0.440, -0.432,  9.876, 4,  -0.000, -0.000, -0.000, 5,  36.308,  5.605,-26.483205731.31026, 3,   0.460, -0.440,  9.906, 4,  -0.000,  0.000,  0.000, 5,  35.706,  5.025,-24.944205731.51317, 3,   0.458, -0.434,  9.909, 4,   0.000,  0.000, -0.000, 5,  35.374,  4.218,-26.492

Where the first big number is the timestamp, 3,4,5 are the sensor ids followed by their x,y,z co-ordinate values. Now this raw data need to be grouped in tabular format by their sensor ids for each time stamp. A meaningful column header will be excellent if it does not require complex coding. I am trying to give a sample below--

Expected output

I have installed veryprettytable from Github but the code or implementation is beyond my python knowledge. Please help. I am using python 3.27 latest (June 2018) on windows 10.

Idk whats going on here, you're using socket for something, and theres a csv invovled, and you give an html example. — SuperStew, Jul 12 '18 at 15:12
Yeah, and besides that you should really try to reduce the length of your example. 5 or 6 rows of data are fully sufficient for an example. — JE_Muc, Jul 12 '18 at 15:13
please run the html code snippet to get the output format.This is the only quick way I found to express what I want to mean properly. — Bukaida, Jul 12 '18 at 15:17

score 0 · Answer 1 · answered Jul 12 '18 at 15:53

0

this should work in this scenario:

while count<10:
    message, address = s.recvfrom(5555)
    messageString = message.decode("utf-8")
    print(messageString)
    count=count+1
    filename = yourfile.xlsx

    workbook = Workbook(filename + '.xlsx')
    worksheet = workbook.add_worksheet()
    with open(filename , 'rt', encoding='utf8') as f:

        for r, row in enumerate(reader):
        print(r)
        for c, col in enumerate(messageString):
            worksheet.write(r, c, col)
    workbook.close()

answered Jul 12 '18 at 15:53

DeshDeep Singh

1,817
2
23
43

The code need to be modified slightly to avoid syntax and alignment error. A new package 'xlsxwriter' needed to be installed . The code is now: count=count+1 filename = "yourfile.xlsx" workbook = xlsxwriter.Workbook(filename + '.xlsx') worksheet = workbook.add_worksheet() with open(filename , 'a', encoding='utf8') as f: for r, row in enumerate(messageString): print(r) for c, col in enumerate(messageString): worksheet.write(r, c, col) workbook.close() Now running in infinite loop produce blank excel sheet – Bukaida Jul 15 '18 at 07:46

score 0 · Answer 2 · answered Jul 12 '18 at 19:21

To get your desired output, you need to

add the desired header information
strip off unwanted spaces in messageString
append newline to messageString before writing

To do header information you need something like

fid = open('yourfile.csv', 'w')
fid.write('timestamp,,accelerometer,,gyroscope,,magnetic field\n')
fid.write(',x,y,z,x,y,z,x,y,z\n')
fid.close()

From the original post it's really hard to tell how messageString is generated. But it's clear you have spaces between fields. You need to strip those somehow. To strip them from the existing messageString use str.replace:

messageString = messageString.replace(' ', '') # replace space with empty string

Finally, tacking on a newline to messageString is pretty straightforward:

messageString += '\n'

or

messageString = messageString + '\n'

Output file is repeating the header for every record. Also alignment problem for 2nd and third (Gyro & mag) when the output file is opened in excel. The file mode has to be changed to 'a' otherwise only last record is being written. — Bukaida, Jul 15 '18 at 08:07

How to group raw sensor data from csv file using python

2 Answers2