txt文件提取数据到excle文件

Question

txt文件提取数据到excle文件，输出到excle文档操作不熟悉

TXT部分文档：

07-18_12.17.01.550[localplayer][D][14866][face.cc(100)]:ABR Get Next Level: 4
07-18_12.17.01.550[localplayer][D][14866][PlayerStatHelper.cpp(092)]:ABR setAbrs idx:0 bitrate:379203 bwe:8629337 buffer:9951 switchBr:0
07-18_12.17.01.550[localplayer][D][14866][Controller.cpp(741)]:get next level 0
07-18_12.17.02.056[localplayer][D][14866][face.cc(100)]:ABR Get Next Level: 4
07-18_12.17.02.056[localplayer][D][14866][StatHelper.cpp(092)]:ABR setAbrs idx:4 bitrate:1422296 bwe:8770076 buffer:21424 switchBr:0
07-18_12.17.02.056[localplayer][D][14866][Controller.cpp(741)]:get next level 4
07-18_12.17.02.056[localplayer][D][14866][Controller.cpp(1900)]:actual pause download
07-18_12.17.13.887[localplayer][D][14866][face.cc(100)]:ABR Get Next Level: 4
07-18_12.17.13.887[localplayer][D][14866][StatHelper.cpp(092)]:ABR setAbrs idx:4 bitrate:1422296 bwe:9168943 buffer:22838 switchBr:0
07-18_12.17.13.887[localplayer][D][14866][Controller.cpp(741)]:get next level 4
07-18_12.17.13.887[localplayer][D][14866][Controller.cpp(1900)]:actual pause download
07-18_12.17.13.887[localplayer][D][14866][Controller.cpp(1291)]:HlsPlayManager stat change 4->5

从Txt文档中提取对应字段的数据，需要采集的数据大多都是这三行的形式，字段一致，数据不同

需要的结果

问题相关代码，请勿粘贴截图
```python

fi = open("yylocalplayer_jni_2022_07_27_16_48_39_1.txt","r",encoding="utf-8")


line = fi.readlines()    #整个文件读入


key_word = "Asbrs "
datas = line.split(key_word)[1]   #按指定单词分割
items=datas.split(' ')
kvs={}
for item in items:
    if len(item.split(':')) == 2:
        k = item.split(':')[0]
        v = item.split(':')[1]
        kvs[k] = v

fi.close()

Answer 1

import pandas as pd
 
s='''07-18_12.17.01.550[localplayer][D][14866][face.cc(100)]:ABR Get Next Level: 4
07-18_12.17.01.550[localplayer][D][14866][PlayerStatHelper.cpp(092)]:ABR setAbrs idx:0 bitrate:379203 bwe:8629337 buffer:9951 switchBr:0
07-18_12.17.01.550[localplayer][D][14866][Controller.cpp(741)]:get next level 0
07-18_12.17.02.056[localplayer][D][14866][face.cc(100)]:ABR Get Next Level: 4
07-18_12.17.02.056[localplayer][D][14866][StatHelper.cpp(092)]:ABR setAbrs idx:4 bitrate:1422296 bwe:8770076 buffer:21424 switchBr:0
07-18_12.17.02.056[localplayer][D][14866][Controller.cpp(741)]:get next level 4
07-18_12.17.02.056[localplayer][D][14866][Controller.cpp(1900)]:actual pause download
07-18_12.17.13.887[localplayer][D][14866][face.cc(100)]:ABR Get Next Level: 4
07-18_12.17.13.887[localplayer][D][14866][StatHelper.cpp(092)]:ABR setAbrs idx:4 bitrate:1422296 bwe:9168943 buffer:22838 switchBr:0
07-18_12.17.13.887[localplayer][D][14866][Controller.cpp(741)]:get next level 4
07-18_12.17.13.887[localplayer][D][14866][Controller.cpp(1900)]:actual pause download
07-18_12.17.13.887[localplayer][D][14866][Controller.cpp(1291)]:HlsPlayManager stat change 4->5'''
 
df = pd.DataFrame(columns=['firstlevel', 'idx', 'bitrate', 'bwe', 'buffer', 'switchBar', 'nextLevel'])
find_lines = re.findall('Get Next Level.*?idx:.*?get next level.*?\n', s, flags=re.DOTALL)
 
for i in find_lines:
    list_value = re.findall(r':\s{0,1}(\d+)', i)
    list_value.extend(re.search('get next level (\d+)', i)[1])
    df.loc[len(df)] = list_value
 
df.to_excel('result.xlsx', index=False)

您实际代码可用下面的，把filename替换文件对应的路径即可：

import re
import pandas as pd
 
with open('filename', 'r') as f:
    s = f.read()
 
df = pd.DataFrame(columns=['firstlevel', 'idx', 'bitrate', 'bwe', 'buffer', 'switchBar', 'nextLevel'])
find_lines = re.findall('Get Next Level.*?idx:.*?get next level.*?\n', s, flags=re.DOTALL)
 
for i in find_lines:
    list_value = re.findall(r':\s{0,1}(\d+)', i)
    list_value.extend(re.search('get next level (\d+)', i)[1])
    df.loc[len(df)] = list_value
 
df.to_excel('result.xlsx', index=False)