如何从CSV中提取每个月首个可用股票交易日的Date与Close字段

问题背景 #

从yfinance导出的股票CSV数据中，每月首个交易日不一定是1号，需要提取每个月第一条可用记录的Date和Close字段。

示例CSV数据片段：

Date,Open,High,Low,Close,Adj Close,Volume
2021-01-04 00:00:00-05:00,88.0,88.12449645996094,85.35700225830078,86.30650329589844,86.30650329589844,37324000
2021-01-05 00:00:00-05:00,86.25450134277344,87.34149932861328,85.84500122070312,87.00250244140625,87.00250244140625,20360000
2021-02-01 00:00:00-05:00,92.22949981689453,95.7770004272461,92.22949981689453,94.65350341796875,94.65350341796875,40252000
2021-04-30 00:00:00-04:00,118.4010009765625,119.09249877929688,117.3280029296875,117.67500305175781,117.67500305175781,44856000
2021-05-03 00:00:00-04:00,118.24549865722656,119.07749938964844,116.7750015258789,117.15399932861328,117.15399932861328,28242000

现有代码问题 #

当前代码仅筛选日期日部分以0开头的记录，逻辑错误，无法正确获取每月第一条记录：

import csv

with open("quotes.csv", "r") as file:
    data = csv.reader(file)

    for line in data:
        if len(line[0]) > 4 and int(line[0][8]) < 1:
            print([line[0], line[4]])

解决方案 #

方法1：纯CSV模块实现 #

通过跟踪已处理的年月，确保每个月只取第一条记录：

import csv
from datetime import datetime

# 存储已处理过的年月（格式：(年, 月)）
processed_months = set()

with open("quotes.csv", "r") as file:
    reader = csv.reader(file)
    next(reader)  # 跳过表头行
    
    for row in reader:
        date_str = row[0]
        # 解析ISO格式日期字符串，提取年月
        date_obj = datetime.fromisoformat(date_str)
        year_month = (date_obj.year, date_obj.month)
        
        if year_month not in processed_months:
            processed_months.add(year_month)
            print(f"{date_str}, {row[4]}")

方法2：Pandas高效实现 #

适合处理大量数据，代码更简洁：

import pandas as pd

# 读取CSV文件
df = pd.read_csv("quotes.csv")
# 将Date列转换为datetime类型
df['Date'] = pd.to_datetime(df['Date'])
# 按年月分组，取每组第一条记录
monthly_first_quotes = df.groupby([df['Date'].dt.year, df['Date'].dt.month]).first()
# 输出指定字段
print(monthly_first_quotes[['Date', 'Close']].to_csv(sep=',', index=False))

期望输出示例 #

2021-01-04 00:00:00-05:00, 86.30650329589844
2021-02-01 00:00:00-05:00, 94.65350341796875
2021-05-03 00:00:00-04:00, 117.15399932861328

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