将数据拆分为3列数据

Question

我很难将一个数据文件解析成一个数据框架。当我用熊猫阅读数据时，我会得到一个包含所有信息的列数据框架。

Server    
7.14.182.917 - - [20/Dec/2018:08:30:21 -0500] "GET /tools/performance/log/lib/ui-bootstrap-tpls-0.23.5.min.js HTTP/1.1" 235 89583
7.18.134.196 - - [20/Dec/2018:07:40:13 -0500] "HEAD / HTTP/1.0" 502 -
...

我想在三列中解析数据。我尝试使用df[['Server', 'Date', 'Address']] = pd.DataFrame([ x.split() for x in df['Server'].tolist() ])，但我得到了一个错误，ValueError: Columns must be same length as key有一种方法可以解析数据，使其有3列，如下所示

Server        Date                          Address                               
7.14.182.917  20/Dec/2018:08:30:21 -0500.   "GET /tools/performance/log/lib/ui-bootstrap-tpls-0.23.5.min.js HTTP/1.1" 235 89583

Answer 1

这里可以根据输入文件类型和格式采取多种方法。如果文件是有效的字符串路径，请尝试以下方法(请参阅更多)

import pandas as pd
# approach 1
df = pd.read_fwf('inputfile.txt')

# approach 2
df = pd.read_csv("inputfile.txt", sep = "\t") # check the delimiter

# then select the columns you want
df_subset = df[['Server', 'Date', 'Address']]

全面解决办法：

import pandas as pd

# read in text file
df = pd.read_csv("test_input.txt", sep=" ", error_bad_lines=False)

# convert df to string
df = df.astype(str)

# get num rows
num_rows = df.shape[0]

# get IP from index, then reset index
df['IP'] = df.index

# reset index to proper index
new_index = pd.Series(list(range(num_rows)))
df = df.set_index([new_index])

# rename columns and drop old cols
df = df.rename(columns={'Server': 'Date', 'IP': "Server"})

# create Date col, drop old col
df['Date'] = df.Date.str.cat(df['Unnamed: 1'])
df = df.drop(["Unnamed: 1"], axis=1)

# Create address col, drop old col
df['Address'] = df['Unnamed: 2'] + df['Unnamed: 3'] + df['Unnamed: 4']
df = df.drop(["Unnamed: 2","Unnamed: 3","Unnamed: 4"], axis=1)

# Strip brackets, other chars
df['Date'] = df['Date'].str.strip("[]")
df['Server'] = df["Server"].astype(str)
df['Server'] = df['Server'].str.strip("()-'', '-',")

返回：

​

​