<p>在将这些数据读入熊猫之前,可能更容易进行一些清理。假设您的数据是CSV,这不是最漂亮的代码,但这应该可以做到:</p>
<pre><code>import numpy as np
import pandas as pd
import re
filename = "<path to file>.csv"
new_file = "<path to where fixed csv should go>.csv"
with open(filename, "r") as infile:
text = infile.read()
# get rid of existing new line characters
text = text.replace("\n", ",")
# put a new line before every number
out = re.sub("([0-9]+)", "\n\\1", text)
# write out
with open(new_file, "w+") as outfile:
outfile.write(out)
# read in the fixed csv need to provide a number of columns
# greater than you'll need (using 50 here), and then cut the excess
df = pd.read_csv(new_file, header=None, names=range(50)).dropna(how="all", axis=1)
# jam as many columns into column1 as necessary to get just 3 after ID
df["cols_to_jam"] = df[df.columns[1:]].notnull().sum(axis=1) - 3
def jam(row):
if row["cols_to_jam"] > 0:
new = ""
for col in range(1, row["cols_to_jam"] + 2):
new += str(row[col])
else:
new = row[1]
return new
idx = df[0]
col1 = df.apply(jam, axis=1)
# blank out jammed values
for i, row in df.iterrows():
if row["cols_to_jam"] > 0:
for col in range(1, row["cols_to_jam"] + 2):
df.ix[i, col] = np.nan
else:
df.ix[i, 1] = np.nan
del df["cols_to_jam"], df[0]
remaining_cols = df.apply(lambda x: list(x.dropna().tail(2).values), axis=1).apply(pd.Series)
remaining_cols.columns = ["col2", "col3"]
# put it all together
output = idx.to_frame("id").join(col1.to_frame("col1")).join(remaining_cols)
</code></pre>