在python中对列表进行转置

Question

输入文件。csv

['NE,PORT,EVENT,TIME,VALUE',
'NODE,13,MAX,2021-08-30 09:15:00+01:00 DST,-10.9', 
'NODE,13,MIN,2021-08-30 09:15:00+01:00 DST,-11.0', 
'NODE,13,CUR,2021-08-30 09:15:00+01:00 DST,-10.9', 
'NODE,13,MAX,2021-08-30 10:30:00+01:00 DST,-12.9', 
'NODE,13,MIN,2021-08-30 10:30:00+01:00 DST,-10.0', 
'NODE,13,CUR,2021-08-30 10:30:00+01:00 DST,-12.9']

python代码:

intext=open('file.csv', 'r')
check=intext.readlines()
for lista in check:
lista_split=lista.split(",")
lista_split.extend(['MAX','MIN','CUR'])
lista_index=[0,1,2,3,4]
lista_index.extend([5,6,7])
contents=list(lista_split[i] for i in lista_index)
if contents[2]==('MAX'):
contents[5] = contents[4])
elif contents[2]==('MIN'):
contents[6] = contents[4])
elif contents[2]==('CUR'):
contents[7] = contents[4])
contents.remove(contents[2])
contents.remove(contents[4])
print(contents)

step1事件作为列和相应的值,步骤2清洁列(删除事件和价值),完成了!

['NE', 'PORT', 'TIME', 'MAX', 'MIN', 'CUR']
['NODE', '13', '2021-08-30 09:15:00+01:00 DST', '-10.9', 'MIN', 'CUR']
['NODE', '13', '2021-08-30 09:15:00+01:00 DST', 'MAX', '-11.0', 'CUR']
['NODE', '13', '2021-08-30 09:15:00+01:00 DST', 'MAX', 'MIN', '-10.9']
['NODE', '13', '2021-08-30 10:30:00+01:00 DST', '-12.9', 'MIN', 'CUR']
['NODE', '13', '2021-08-30 10:30:00+01:00 DST', 'MAX', '-10.0', 'CUR']
['NODE', '13', '2021-08-30 10:30:00+01:00 DST', 'MAX', 'MIN', '-12.9']

目标:

['NE', 'PORT', 'TIME', 'MAX', 'MIN', 'CUR']
['NODE', '13', '2021-08-30 09:15:00+01:00 DST', '-10.9', '-11.0', '-10.9']
['NODE', '13', '2021-08-30 10:30:00+01:00 DST', '-12.9', '-10.0', '-12.9']

Answer 1

方法之一是创建数据透视表。

csv = ['NE,PORT,EVENT,TIME,VALUE',
'NODE,13,MAX,2021-08-30 09:15:00+01:00 DST,-10.9', 
'NODE,13,MIN,2021-08-30 09:15:00+01:00 DST,-11.0', 
'NODE,13,CUR,2021-08-30 09:15:00+01:00 DST,-10.9', 
'NODE,13,MAX,2021-08-30 10:30:00+01:00 DST,-12.9', 
'NODE,13,MIN,2021-08-30 10:30:00+01:00 DST,-10.0', 
'NODE,13,CUR,2021-08-30 10:30:00+01:00 DST,-12.9']
csv = [x.split(",") for x in csv]
df = pd.DataFrame(csv[1:], columns=csv[0])
df["VALUE"] = df["VALUE"].astype(float)
df = df.pivot_table("VALUE", ["NE", "PORT", "TIME"], "EVENT")
df = df.reset_index().rename_axis(None, axis=1)

结果

Answer 2

我会纠正你的第一步。你的逻辑很好，但是由于列表太多，导致很多混乱

for lista in check:
lista=lista.split(",")
lista.extend(['MAX','MIN','CUR'])
if lista[2]==('MAX'):
lista[5] = lista[4]
elif lista[2]==('MIN'):
lista[6] = lista[4]
elif lista[2]==('CUR'):
lista[7] = lista[4]
lista.remove(lista[2])
lista.remove(lista[3]) #you removed an index before so it's not 4 anymore
print(lista)