osisoft piconfig export csv into regular pandas DF

Question

I have some issues trying to import csv files from osisoft piconfig export into regular pandas DF.

I am a beginner in Python, so if you are kind enough to answer me, I kindly ask you to explain in details if possible.

The raw data looks like this:

"*> sensor_1"," 09-aug-22 10:36"," 09-aug-22 10:36"," 100000000000"
"sensor_1","9-Aug-22 10:36:00.0000 CUT","-31.10765",""
"sensor_1","9-Aug-22 10:36:01.0000 CUT","-30.58978",""
"sensor_1","9-Aug-22 10:36:02.0000 CUT","-30.14589",""
"sensor_1","9-Aug-22 10:36:03.0000 CUT","-30.14589",""
"sensor_1","9-Aug-22 10:36:04.0000 CUT","-29.70199",""
"* End Repeat...","","",""
"*> sensor_2"," 09-aug-22 10:36"," 09-aug-22 10:36"," 100000000000"
"sensor_2","9-Aug-22 10:36:00.5000 CUT","-29.25809",""
"sensor_2","9-Aug-22 10:36:01.5000 CUT","-32.17654",""
"sensor_2","9-Aug-22 10:36:02.5000 CUT","-28.54631",""
"sensor_2","9-Aug-22 10:36:03.5000 CUT","-27.51562",""
"sensor_2","9-Aug-22 10:36:04.5000 CUT","-32.42654",""
"* End Repeat...","","",""
"*> sensor_3"," 09-aug-22 10:36"," 09-aug-22 10:36"," 100000000000"
"sensor_3","9-Aug-22 10:36:00.39900 CUT","-26.35991",""
"sensor_3","9-Aug-22 10:36:01.45001 CUT","-26.35991",""
"sensor_3","9-Aug-22 10:36:02.5448 CUT","-28.20952",""
"sensor_3","9-Aug-22 10:36:03.80101 CUT","-32.17654",""
"sensor_3","9-Aug-22 10:36:04.75401 CUT","-32.17654",""
"* End Repeat...","","",""

Ideally I would like to format the data in the DF to be as shown below:

I manage to import it with regular formatted csv, but this is a tricky one. Any help or pointers would be greatly appreciated.

Answer 1

I would try to first format my raw data, then create a dataframe with the filtered data. For example the following code creates a csv file from your raw data:

import csv

f = open('output.csv', 'w')
writer = csv.writer(f)
with open('test.csv', 'r') as file:
    data = file.read().rstrip()
    split_data = data.split('"')
    filtered_data = [x for x in split_data if len(x) >= 2]
    for ide, element in enumerate(filtered_data):
        if 'sensor' in element:
            normal_offset = [1, 2]
            double_ts_offset = [2, 3]
            if filtered_data[ide + 1] == filtered_data[ide + 2]:
                offset = double_ts_offset
            else:
                offset = normal_offset
            if '>' in element:
                name = element[2:]
            else:
                name = element
            date = filtered_data[ide + offset[0]]
            value = filtered_data[ide + offset[1]]
            writer.writerow([name, date, value])

When finished a .csv file like that will result:

sensor_1, 09-aug-22 10:36, 100000000000
sensor_1,9-Aug-22 10:36:00.0000 CUT,-31.10765
sensor_1,9-Aug-22 10:36:01.0000 CUT,-30.58978
sensor_1,9-Aug-22 10:36:02.0000 CUT,-30.14589
sensor_1,9-Aug-22 10:36:03.0000 CUT,-30.14589
sensor_1,9-Aug-22 10:36:04.0000 CUT,-29.70199
sensor_2, 09-aug-22 10:36, 100000000000
sensor_2,9-Aug-22 10:36:00.5000 CUT,-29.25809
sensor_2,9-Aug-22 10:36:01.5000 CUT,-32.17654
sensor_2,9-Aug-22 10:36:02.5000 CUT,-28.54631
sensor_2,9-Aug-22 10:36:03.5000 CUT,-27.51562
sensor_2,9-Aug-22 10:36:04.5000 CUT,-32.42654
sensor_3, 09-aug-22 10:36, 100000000000
sensor_3,9-Aug-22 10:36:00.39900 CUT,-26.35991
sensor_3,9-Aug-22 10:36:01.45001 CUT,-26.35991
sensor_3,9-Aug-22 10:36:02.5448 CUT,-28.20952
sensor_3,9-Aug-22 10:36:03.80101 CUT,-32.17654
sensor_3,9-Aug-22 10:36:04.75401 CUT,-32.17654

After u read the csv file with pandas you can format the data as u'd like to in your dataframe. Maybe there is a little more filtering required to remove the 'CUT' in the dates.