如何合并TDA-API Python库返回的多份DataFrame并统一导出CSV

原有代码问题 #

• 原get_chains函数无返回值，调用后赋值的tmp为None，后续操作直接报错
• 变量名拼写错误：tmp['Symbol'] = Symbol中大写的Symbol未定义，应使用循环变量小写的symbol
• 原函数内置单标的CSV导出逻辑，无需单独保存单标的文件的话可以删除
• pandas 2.0+版本已弃用append方法，推荐使用pd.concat做批量数据合并，效率更高

完整修改后代码 #

from tda import auth, client
import json
import config
import pandas as pd
import datetime

try:
    c = auth.client_from_token_file(config.token_path, config.api_key)
except FileNotFoundError:
    from selenium import webdriver
    with webdriver.Chrome(executable_path='/Users/capnb/Documents/TDAmeritrade/chromedriver') as driver:
        c = auth.client_from_login_flow(
            driver, config.api_key, config.redirect_uri, config.token_path)

def get_chains(symbol):
    r = c.get_option_chain(symbol)
    data = json.loads(r.text)

    ret = []
    for date in data["callExpDateMap"]:
        for strike in data["callExpDateMap"][date]:
            ret.extend(data["callExpDateMap"][date][strike])
    for date in data["putExpDateMap"]:
        for strike in data["putExpDateMap"][date]:
            ret.extend(data["putExpDateMap"][date][strike])
    df = pd.DataFrame(ret)
    for col in ("tradeTimeInLong", "quoteTimeInLong", "expirationDate", "lastTradingDay"):
        df[col] = pd.to_datetime(df[col], unit="ms")
    # 新增标的名列
    df['Symbol'] = symbol
    # 如果需要同时保留单个标的的CSV文件，可取消注释下面两行
    # filename = symbol + '-option_prices-' + datetime.datetime.now().strftime('%Y-%m-%d-%H-%M') + '.csv'
    # path = "c:/Users/capnb/Desktop/"
    # df.to_csv(path+filename)
    return df

symbols = ['AAPL', 'GME', 'TSLA','GDXJ','SPY','UVXY','TLT','NIO','RKT',]
df_list = []
for symbol in symbols:
    tmp = get_chains(symbol)
    tmp.reset_index(drop=True, inplace=True)
    df_list.append(tmp)

# 合并所有标的数据
full_data = pd.concat(df_list, ignore_index=True)

# 统一导出为单个CSV
filename = datetime.datetime.now().strftime('option_chain-%Y-%m-%d-%H-%M.csv')
path = "c:/Users/capnb/Desktop/"
full_data.to_csv(path+filename, index=False)

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