我有一个CSV文件,基本如下所示(我将其缩短为一个显示结构的最小示例):
ID1#First_Name
TIME_BIN,COUNT,AVG
09:00-12:00,100,50
15:00-18:00,24,14
21:00-23:00,69,47
ID2#Second_Name
TIME_BIN,COUNT,AVG
09:00-12:00,36,5
15:00-18:00,74,68
21:00-23:00,22,76
ID3#Third_Name
TIME_BIN,COUNT,AVG
09:00-12:00,15,10
15:00-18:00,77,36
21:00-23:00,55,18
可以看到,数据被分成多个块。每个块都有一个标题(例如ID1#First_Name
),其中包含两个信息和平(IDx
和{#
分隔。在
每个标题后面都是列标题(TIME_BIN, COUNT, AVG
),对于所有块都是相同的。在
然后跟随属于列标题的一些数据行(例如TIME_BIN=09:00-12:00
,COUNT=100
,AVG=50
)。在
我想将此文件解析为Pandas数据帧,如下所示:
^{pr2}$这意味着标题不能被跳过,但必须被#
分割,然后链接到它所属块中的数据。此外,列标题只需要一次,因为它们以后不会更改。在
不知怎么的,我用下面的代码实现了我的目标。然而,这种方法看起来有点过于复杂,对我来说并不健壮,我相信有更好的方法来实现这一点。欢迎提出任何建议!在
import pandas as pd
from io import StringIO (<- Python 3, for Python 2 use from StringIO import StringIO)
pathToFile = 'mydata.txt'
# read the textfile into a StringIO object and skip the repeating column header rows
s = StringIO()
with open(pathToFile) as file:
for line in file:
if not line.startswith('TIME_BIN'):
s.write(line)
# reset buffer to the beginning of the StringIO object
s.seek(0)
# create new dataframe with desired column names
df = pd.read_csv(s, names=['TIME_BIN', 'COUNT', 'AVG'])
# split the headline string which is currently found in the TIME_BIN column and insert both parts as new dataframe columns.
# the headline is identified by its start which is 'ID'
df['ID'] = df[df.TIME_BIN.str.startswith('ID')].TIME_BIN.str.split('#').str.get(0)
df['Name'] = df[df.TIME_BIN.str.startswith('ID')].TIME_BIN.str.split('#').str.get(1)
# fill the NaN values in the ID and Name columns by propagating the last valid observation
df['ID'] = df['ID'].fillna(method='ffill')
df['Name'] = df['Name'].fillna(method='ffill')
# remove all rows where TIME_BIN starts with 'ID'
df['TIME_BIN'] = df['TIME_BIN'].drop(df[df.TIME_BIN.str.startswith('ID')].index)
df = df.dropna(subset=['TIME_BIN'])
# reorder columns to bring ID and Name to the front
cols = list(df)
cols.insert(0, cols.pop(cols.index('Name')))
cols.insert(0, cols.pop(cols.index('ID')))
df = df.ix[:, cols]
相关问题 更多 >
编程相关推荐