osisoft piconfig向普通熊猫出口csv

Question

我有一些问题，试图将csv文件从osisoft piconfig导出到常规熊猫DF。

我是Python的初学者，所以如果你能回答我，我恳请你尽可能详细解释。

原始数据如下所示：

"*> 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...","","",""

理想情况下，我希望将DF中的数据格式化为如下所示：

​

​

我设法用常规格式的csv导入它，但这是一个棘手的问题。任何帮助或指示都将不胜感激。

Answer 1

我会尝试先格式化我的原始数据，然后用过滤后的数据创建一个数据。例如，以下代码从原始数据创建csv文件：

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])

完成后，这样的.csv文件将产生如下结果：

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

在你和熊猫一起读了csv文件之后，你可以将数据格式化成你想要的数据格式。也许需要更多的过滤来删除日期中的“剪切”。