5帮我写个python代码。我有一个accdb数据库,里面有5列,其中一列是tradingdate 一列是stock…
- User
5帮我写个python代码。我有一个accdb数据库,里面有5列,其中一列是tradingdate 一列是stock ticker,另三列是给定日期与股票的因子A、B、C。现在我想用Python读取这个数据库,然后遍历每一个不同的tradingdate,给出每一个tradingdate里所有stock ticker对应的因子值的一些统计,比如因子A的平均值、中位数10%分位数、25%分位数、75%分位数、90%分位数,诸如此类,然后针对每一个因子,画一张横坐标为日期,纵坐标为不同分位数的时间序列折线图。
- Assistant
以下是实现您需求的Python代码:
import pandas as pd import matplotlib.pyplot as plt # 读取数据库 df = pd.read_excel('your_database.accdb') # 统计不同tradingdate对应的每个stock ticker的因子值 statistics = df.groupby('tradingdate').agg({'factorA': ['mean', 'median', lambda x: x.quantile(0.1), lambda x: x.quantile(0.25), lambda x: x.quantile(0.75), lambda x: x.quantile(0.9)], 'factorB': ['mean', 'median', lambda x: x.quantile(0.1), lambda x: x.quantile(0.25), lambda x: x.quantile(0.75), lambda x: x.quantile(0.9)], 'factorC': ['mean', 'median', lambda x: x.quantile(0.1), lambda x: x.quantile(0.25), lambda x: x.quantile(0.75), lambda x: x.quantile(0.9)]}) # 绘制时间序列折线图 dates = statistics.index.tolist() plt.figure(figsize=(12, 6)) # 绘制因子A的时间序列折线图 plt.subplot(3, 1, 1) plt.plot(dates, statistics[('factorA', 'mean')], label='Mean') plt.plot(dates, statistics[('factorA', 'median')], label='Median') plt.plot(dates, statistics[('factorA', '<lambda_0>')], label='10% Quantile') plt.plot(dates, statistics[('factorA', '<lambda_1>')], label='25% Quantile') plt.plot(dates, statistics[('factorA', '<lambda_2>')], label='75% Quantile') plt.plot(dates, statistics[('factorA', '<lambda_3>')], label='90% Quantile') plt.ylabel('Factor A') plt.legend() # 绘制因子B的时间序列折线图 plt.subplot(3, 1, 2) plt.plot(dates, statistics[('factorB', 'mean')], label='Mean') plt.plot(dates, statistics[('factorB', 'median')], label='Median') plt.plot(dates, statistics[('factorB', '<lambda_0>')], label='10% Quantile') plt.plot(dates, statistics[('factorB', '<lambda_1>')], label='25% Quantile') plt.plot(dates, statistics[('factorB', '<lambda_2>')], label='75% Quantile') plt.plot(dates, statistics[('factorB', '<lambda_3>')], label='90% Quantile') plt.ylabel('Factor B') plt.legend() # 绘制因子C的时间序列折线图 plt.subplot(3, 1, 3) plt.plot(dates, statistics[('factorC', 'mean')], label='Mean') plt.plot(dates, statistics[('factorC', 'median')], label='Median') plt.plot(dates, statistics[('factorC', '<lambda_0>')], label='10% Quantile') plt.plot(dates, statistics[('factorC', '<lambda_1>')], label='25% Quantile') plt.plot(dates, statistics[('factorC', '<lambda_2>')], label='75% Quantile') plt.plot(dates, statistics[('factorC', '<lambda_3>')], label='90% Quantile') plt.ylabel('Factor C') plt.legend() plt.xlabel('Trading Date') plt.tight_layout() plt.show()请将代码中的
your_database.accdb替换为实际的数据库文件路径,并确保您已安装pandas和matplotlib库。运行代码后,将会生成三张子图,分别为每个因子的时间序列折线图,其中横坐标为日期,纵坐标为不同分位数的因子值。 - User
虽然我不太专业 但怎么会死pd.read_Excel然后参数是一个accdb文件呢? 这里应该是 pd.read_sql(portfolio_query, conn) 只是我不会写这个query
- Assistant
对不起,给出的是错误的代码。对于ACCDB数据库文件,我们可以使用
pyodbc库来读取。以下是修正后的代码:import pandas as pd import matplotlib.pyplot as plt import pyodbc # 连接到数据库 conn_str = r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=path_to_your_database.accdb;' conn = pyodbc.connect(conn_str) # 执行查询 query = "SELECT tradingdate, ticker, factorA, factorB, factorC FROM your_table" df = pd.read_sql(query, conn) # 关闭连接 conn.close() # 统计不同tradingdate对应的每个stock ticker的因子值 statistics = df.groupby('tradingdate').agg({'factorA': ['mean', 'median', lambda x: x.quantile(0.1), lambda x: x.quantile(0.25), lambda x: x.quantile(0.75), lambda x: x.quantile(0.9)], 'factorB': ['mean', 'median', lambda x: x.quantile(0.1), lambda x: x.quantile(0.25), lambda x: x.quantile(0.75), lambda x: x.quantile(0.9)], 'factorC': ['mean', 'median', lambda x: x.quantile(0.1), lambda x: x.quantile(0.25), lambda x: x.quantile(0.75), lambda x: x.quantile(0.9)]}) # 绘制时间序列折线图 dates = statistics.index.tolist() plt.figure(figsize=(12, 6)) # 绘制因子A的时间序列折线图 plt.subplot(3, 1, 1) plt.plot(dates, statistics[('factorA', 'mean')], label='Mean') plt.plot(dates, statistics[('factorA', 'median')], label='Median') plt.plot(dates, statistics[('factorA', '<lambda_0>')], label='10% Quantile') plt.plot(dates, statistics[('factorA', '<lambda_1>')], label='25% Quantile') plt.plot(dates, statistics[('factorA', '<lambda_2>')], label='75% Quantile') plt.plot(dates, statistics[('factorA', '<lambda_3>')], label='90% Quantile') plt.ylabel('Factor A') plt.legend() # 绘制因子B的时间序列折线图 plt.subplot(3, 1, 2) plt.plot(dates, statistics[('factorB', 'mean')], label='Mean') plt.plot(dates, statistics[('factorB', 'median')], label='Median') plt.plot(dates, statistics[('factorB', '<lambda_0>')], label='10% Quantile') plt.plot(dates, statistics[('factorB', '<lambda_1>')], label='25% Quantile') plt.plot(dates, statistics[('factorB', '<lambda_2>')], label='75% Quantile') plt.plot(dates, statistics[('factorB', '<lambda_3>')], label='90% Quantile') plt.ylabel('Factor B') plt.legend() # 绘制因子C的时间序列折线图 plt.subplot(3, 1, 3) plt.plot(dates, statistics[('factorC', 'mean')], label='Mean') plt.plot(dates, statistics[('factorC', 'median')], label='Median') plt.plot(dates, statistics[('factorC', '<lambda_0>')], label='10% Quantile') plt.plot(dates, statistics[('factorC', '<lambda_1>')], label='25% Quantile') plt.plot(dates, statistics[('factorC', '<lambda_2>')], label='75% Quantile') plt.plot(dates, statistics[('factorC', '<lambda_3>')], label='90% Quantile') plt.ylabel('Factor C') plt.legend() plt.xlabel('Trading Date') plt.tight_layout() plt.show()请将
path_to_your_database.accdb替换为实际的数据库文件路径,将your_table替换为包含数据的表名。运行代码后,将会生成三张子图,分别为每个因子的时间序列折线图,其中横坐标为日期,纵坐标为不同分位数的因子值。确保您已安装pandas,matplotlib和pyodbc库。 - User
<lambda_0>、<lambda_1>、<lambda_2>、<lambda_3>在plot参数里,这几个列名在之前的代码中有定义过吗?好像没有吧?
- Assistant
非常抱歉,我之前的代码中确实没有定义lambda函数的列名。下面是更新后的代码,将lambda函数的列名从
<lambda_0>到<lambda_3>分别改为'10% Quantile','25% Quantile','75% Quantile'和'90% Quantile':import pandas as pd import matplotlib.pyplot as plt import pyodbc # 连接到数据库 conn_str = r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=path_to_your_database.accdb;' conn = pyodbc.connect(conn_str) # 执行查询 query = "SELECT tradingdate, ticker, factorA, factorB, factorC FROM your_table" df = pd.read_sql(query, conn) # 关闭连接 conn.close() # 统计不同tradingdate对应的每个stock ticker的因子值 statistics = df.groupby('tradingdate').agg({'factorA': ['mean', 'median', lambda x: x.quantile(0.1), lambda x: x.quantile(0.25), lambda x: x.quantile(0.75), lambda x: x.quantile(0.9)], 'factorB': ['mean', 'median', lambda x: x.quantile(0.1), lambda x: x.quantile(0.25), lambda x: x.quantile(0.75), lambda x: x.quantile(0.9)], 'factorC': ['mean', 'median', lambda x: x.quantile(0.1), lambda x: x.quantile(0.25), lambda x: x.quantile(0.75), lambda x: x.quantile(0.9)]}) # 绘制时间序列折线图 dates = statistics.index.tolist() plt.figure(figsize=(12, 6)) # 绘制因子A的时间序列折线图 plt.subplot(3, 1, 1) plt.plot(dates, statistics[('factorA', 'mean')], label='Mean') plt.plot(dates, statistics[('factorA', 'median')], label='Median') plt.plot(dates, statistics[('factorA', '10% Quantile')], label='10% Quantile') plt.plot(dates, statistics[('factorA', '25% Quantile')], label='25% Quantile') plt.plot(dates, statistics[('factorA', '75% Quantile')], label='75% Quantile') plt.plot(dates, statistics[('factorA', '90% Quantile')], label='90% Quantile') plt.ylabel('Factor A') plt.legend() # 绘制因子B的时间序列折线图 plt.subplot(3, 1, 2) plt.plot(dates, statistics[('factorB', 'mean')], label='Mean') plt.plot(dates, statistics[('factorB', 'median')], label='Median') plt.plot(dates, statistics[('factorB', '10% Quantile')], label='10% Quantile') plt.plot(dates, statistics[('factorB', '25% Quantile')], label='25% Quantile') plt.plot(dates, statistics[('factorB', '75% Quantile')], label='75% Quantile') plt.plot(dates, statistics[('factorB', '90% Quantile')], label='90% Quantile') plt.ylabel('Factor B') plt.legend() # 绘制因子C的时间序列折线图 plt.subplot(3, 1, 3) plt.plot(dates, statistics[('factorC', 'mean')], label='Mean') plt.plot(dates, statistics[('factorC', 'median')], label='Median') plt.plot(dates, statistics[('factorC', '10% Quantile')], label='10% Quantile') plt.plot(dates, statistics[('factorC', '25% Quantile')], label='25% Quantile') plt.plot(dates, statistics[('factorC', '75% Quantile')], label='75% Quantile') plt.plot(dates, statistics[('factorC', '90% Quantile')], label='90% Quantile') plt.ylabel('Factor C') plt.legend() plt.xlabel('Trading Date') plt.tight_layout() plt.show()请将
path_to_your_database.accdb替换为实际的数据库文件路径,your_table替换为包含数据的表名。运行代码后,将会生成三张子图,分别为每个因子的时间序列折线图,其中横坐标为日期,纵坐标为不同分位数的因子值。确保您已安装pandas,matplotlib和pyodbc库。 - User
plt.plot(dates, statistics[(‘factorC’, ‘10% Quantile’)], label=‘10% Quantile’) 这里statistics[(‘factorC’, ‘10% Quantile’)]里的‘10% Quantile’还是没定义吧?
- Assistant
非常抱歉,之前的代码再次有错误。对于使用lambda函数定义的列,需要使用
['']的方式访问它们。修改以下代码:plt.figure(figsize=(12, 6)) # 绘制因子A的时间序列折线图 plt.subplot(3, 1, 1) plt.plot(dates, statistics[('factorA', 'mean')], label='Mean') plt.plot(dates, statistics[('factorA', 'median')], label='Median') plt.plot(dates, statistics[('factorA', '<lambda_0>')], label='10% Quantile') plt.plot(dates, statistics[('factorA', '<lambda_1>')], label='25% Quantile') plt.plot(dates, statistics[('factorA', '<lambda_2>')], label='75% Quantile') plt.plot(dates, statistics[('factorA', '<lambda_3>')], label='90% Quantile') plt.ylabel('Factor A') plt.legend() # 绘制因子B的时间序列折线图 plt.subplot(3, 1, 2) plt.plot(dates, statistics[('factorB', 'mean')], label='Mean') plt.plot(dates, statistics[('factorB', 'median')], label='Median') plt.plot(dates, statistics[('factorB', '<lambda_0>')], label='10% Quantile') plt.plot(dates, statistics[('factorB', '<lambda_1>')], label='25% Quantile') plt.plot(dates, statistics[('factorB', '<lambda_2>')], label='75% Quantile') plt.plot(dates, statistics[('factorB', '<lambda_3>')], label='90% Quantile') plt.ylabel('Factor B') plt.legend() # 绘制因子C的时间序列折线图 plt.subplot(3, 1, 3) plt.plot(dates, statistics[('factorC', 'mean')], label='Mean') plt.plot(dates, statistics[('factorC', 'median')], label='Median') plt.plot(dates, statistics[('factorC', '<lambda_0>')], label='10% Quantile') plt.plot(dates, statistics[('factorC', '<lambda_1>')], label='25% Quantile') plt.plot(dates, statistics[('factorC', '<lambda_2>')], label='75% Quantile') plt.plot(dates, statistics[('factorC', '<lambda_3>')], label='90% Quantile') plt.ylabel('Factor C') plt.legend() plt.xlabel('Trading Date') plt.tight_layout() plt.show()非常抱歉给您带来了困惑和混淆。请重新运行上述更新后的代码,现在会正确引用lambda函数生成的列名。