如何用Pandas导入含多日期记录的文本文件为DataFrame

问题背景 #

目标文件结构如下：

Time,Weight,Change,BMI,Body Fat,Skeletal Muscle Mass,Bone Mass,Body
Water,
" Oct 10, 2020",
5:15 PM,82.7 kg,1.1 kg,22.4,17.4 %,35.6 kg,5.8kg,60.3 %,
" Nov 2, 2020",
11:40 PM,84.0 kg,0.1 kg,22.8,17.5 %,35.9 kg,6.0 kg,60.3 %,
10:20 AM,83.9 kg,0.5 kg,22.8,18.1 %,35.9 kg,5.9 kg,59.8 %,
13:40 AM,84.4 kg,0.8 kg,22.9,17.3 %,36.0 kg,6.0 kg,60.4 %,

常规导入无法识别日期与记录的关联：日期单独成行且其余字段为NaN，记录行的日期字段被错误填充为时间值。尝试的代码如下：

data = pd.read_csv('test.txt', sep=',')
data.columns = ["Date","Time", "Weight", "Change", "BMI", "Body Fat", "Skeletal Muscle Mass", "Bone Mass", "Body Water"]

错误输出示例：

解决方案 #

方法一：预处理文本后构建DataFrame #

直接读取文件内容，手动关联日期与对应记录：

import pandas as pd

# 读取并清理文件行
with open('test.txt', 'r') as f:
    lines = [line.strip() for line in f if line.strip() and not line.startswith('Time')]

records = []
current_date = None
for line in lines:
    # 识别日期行（带引号的日期字符串）
    if line.startswith('"'):
        current_date = line.strip('", ')
    else:
        # 分割记录字段并插入当前日期
        fields = [field.strip() for field in line.strip(',').split(',')]
        fields.insert(0, current_date)
        records.append(fields)

# 转换为DataFrame
columns = ["Date","Time", "Weight", "Change", "BMI", "Body Fat", "Skeletal Muscle Mass", "Bone Mass", "Body Water"]
df = pd.DataFrame(records, columns=columns)

方法二：导入后用向前填充补全日期 #

先常规导入，再通过ffill填充日期并过滤无效行：

import pandas as pd

# 跳过首行标题，直接指定列名导入
df = pd.read_csv(
    'test.txt', 
    skiprows=1, 
    names=["Date","Time", "Weight", "Change", "BMI", "Body Fat", "Skeletal Muscle Mass", "Bone Mass", "Body Water"]
)

# 清理日期列的引号和空格
df['Date'] = df['Date'].str.strip('" ')

# 向前填充空日期值
df['Date'] = df['Date'].ffill()

# 过滤掉无时间的日期行
df = df.dropna(subset=['Time']).reset_index(drop=True)

处理后得到的正确DataFrame示例：

内容的提问来源于stack exchange，提问作者Lila D