Python--按指定符号分割列、行

数据内容截取如下：
61042901722;40001;运行;6104;1号;℃;100;0;0;0;0;2023-07-31~610422;40001;运行;610401;1号;℃;100;0;0;0;0;2023-07-31~6104222263;40001;运行;610401;1号度;℃;100;0;0;0;0;2023-07-31~6104226;40001;运行;610429;1号度;℃;100;0;0;0;0;2023-07-31~6104290;40001;运行;61042;1号;℃;100;0;0;0;0;2023-07-31~6104290172;40001;运行;6104290;1高;KPa;100;0;0;0;0;2023-07-31

其中“~”为分行符，“;”为分列符


import pandas as pd

with open(r'20230731091122.txt', 'r',encoding="utf-8") as f:
    data = f.read()
#按指定符号分成行
data = data.split('~')
#按指定符号分成列
data = [i.split(';') for i in data]

df = pd.DataFrame(data)

with open(r'WSCFSS_6301_20230731091122.txt', 'r',encoding="utf-8") as f:
    data2 = f.read()
#按指定符号分成行
data2 = data2.split('~')
#按指定符号分成列
data2 = [i.split(';') for i in data2]

df2 = pd.DataFrame(data2)

# 获取列名
cols = df.columns.values
cols

#指定列为索引
df.set_index(0,inplace=True)

# 获取列名
cols = df2.columns.values
cols

# 重命名列名为 D、E、F
df2.rename(columns={0: "A",1: "B", 2: "C", 3: "D", 4: "E", 4: "F", 5: "G", 6: "H", 7: "I"}, inplace=True)

# 获取列名
cols = df2.columns.values
cols

#指定列为索引
df2.set_index('A',inplace=True)

#表连接
df3 = df.join(df2,how="inner")

#导出为excel
df3.to_excel(r'data.xlsx')