如何将日志解析和聚合为数据帧（3行到1行）？

Question

这是我的数据集

Month  Date Time        Log                                                                                      Command    
Apr    4    20:30:33    200.200.200.254 dns,packet person: --- got query from 10.10.10.243:30648:                Query
Apr    4    20:30:33    200.200.200.254 dns,packet person: id:78b1 rd:1 tc:0 aa:0 qr:0 ra:0 QUERY 'no error'     Not Command
Apr    4    20:30:33    200.200.200.254 dns,packet person: question: home.twitter.com:a:IN                       Not Command
Apr    4    20:30:34    200.200.200.254 dns,packet person: --- sending udp query to 200.10.10.10:53              Sending
Apr    4    20:30:34    200.200.200.254 dns,packet person: id:99a1 rd:1 tc:0 aa:0 qr:0 ra:0 QUERY 'no error'     Not Command
Apr    4    20:30:34    200.200.200.254 dns,packet person: question: home.twitter.com:a:IN                       Not Command

在这个数据集中，我想让每3行变成一行，但实际上我想让它变成一行，约束总是3行变成1行，是的，命令是3行的第一行，因为我需要机器学习的目的

低于预期结果：

Month  Date  Time        Command        IP1                    IP2                           user      id      url                 message        
Apr    4     20:30:33    Query          200.200.200.254        10.10.10.243:30648            person    78b1    home.twitter.com    no error
Apr    4     20:30:34    Sending        200.200.200.254        200.10.10.53                  person    99a1    home.twitter.com    no error

Answer 1

我尝试将str.extract与regex一起使用。希望我没有对你的数据做太多假设

df

Month   Date        Time                                                 Log     Command
  Apr      4    20:30:33    200.200.200.254 dns,packet person:  - got que...   Query
  Apr      4    20:30:33    200.200.200.254 dns,packet person: id:78b1 rd:...   Not Command
  Apr      4    20:30:33    200.200.200.254 dns,packet person: question: h...   Not Command
  Apr      4    20:30:34    200.200.200.254 dns,packet person:  - sending...   Sending
  Apr      4    20:30:34    200.200.200.254 dns,packet person: id:99a1 rd:...   Not Command
  Apr      4    20:30:34    200.200.200.254 dns,packet person: question: h...   Not Command

data = df.reset_index()
data.columns = ["month_name"] + list(data.columns)[1:]

new_df = pd.DataFrame()
new_df = data[data.index % 3 == 0]

new_df['IP2'] = data[data.index % 3 == 0].Log.str.extract(r'(\d*.\d*.\d*.\d*:\d*)?:*$').values

new_df['IP1'] = data[data.index % 3 == 0].Log.str.extract(r'(\d*.\d*.\d*.\d*)\s').values

new_df['user'] = data[data.index % 3 == 1].Log.str.extract(r'(\w*):\s-*').values

new_df['id'] = data[data.index % 3 == 1].Log.str.extract(r'id:(\w*)\s').values

new_df['message'] = data[data.index % 3 == 1].Log.str.extract(r"'(\w*\s*\w*)'").values

new_df['url'] = data[data.index % 3 == 2].Log.str.extract(r'question:\s*(\w*.+):\w*:').values

new_df = new_df.drop(columns=["Log"]).set_index("month_name", drop=True)
new_df.columns.name = "Month"
new_df.index.name = None
new_df

Month   Date        Time    Command           IP2                  IP1        user  id      message           url
Apr        4    20:30:33    Query      10.10.10.243:30648   200.200.200.254 person  78b1    no error    home.twitter.com
Apr        4    20:30:34    Sending    200.10.10.10:53      200.200.200.254 person  99a1    no error    home.twitter.com