from openpyxl import loadworkbook import pandas as pd import datetime 打开Excel文件 wb =…
- User
from openpyxl import load_workbook import pandas as pd import datetime
打开Excel文件
wb = load_workbook(filename=r'C:\Users\LIQI\Documents\民生基金\行业打分\钢铁数据跟踪汇总.xlsx', read_only=True, data_only=True)
选择指定的sheet
sheet = wb['产销数据'] # 这里的Sheet3是你要读取的sheet名称,可以根据实际情况修改
选择指定的列
columns = [0, 11, 12, 13, 14, 15]
读取数据
data = [] for row in sheet.iter_rows(values_only=True): data.append([row[column] for column in columns])
构建DataFrame
headers = ['Column1','Column2', 'Column3', 'Column4', 'Column5', 'Column6'] # 这里是DataFrame的列名,可以根据实际情况修改 df = pd.DataFrame(data, columns=headers) df = df[1:] df.columns = df.iloc[0] df = df[1:]
将第一列作为索引并转换为datetime格式
df.set_index(df.columns[0], inplace=True) df.index = pd.to_datetime(df.index)
def calculate_percentile(df,date, column): date = pd.to_datetime(date) year = date.year month = date.month day = date.day five_years_ago = datetime.datetime.now() - pd.DateOffset(years=5) history_dates = df[(df.index.month == month) & (df.index.day == day) & (df.index.year <= year) & (df.index > five_years_ago)].index history_values = df[df.index.isin(history_dates)][column] target_value = df.loc[date, column] mean_target_value = df.loc[date:date - pd.DateOffset(years=4), column].mean() min_value = history_values.min() max_value = history_values.max() percentile= (target_value - min_value) / (max_value - min_value) * 100 return percentile,history_dates
percentile =calculate_percentile(df,'2023-08-19','螺纹钢') print(percentile) print(history_dates)
percentile =calculate_percentile(df,'2023-08-(0.0, DatetimeIndex(['2020-08-19', '2021-08-19', '2022-08-19', '2023-08-19'], dtype='datetime64[ns]', name=NaT, freq=None))
NameError Traceback (most recent call last) ~\AppData\Local\Temp/ipykernel_8864/724220345.py in 44 percentile =calculate_percentile(df,'2023-08-19','螺纹钢') 45 print(percentile) ---> 46 print(history_dates)
NameError: name 'history_dates' is not defined
- Assistant
In the code you provided, the
history_datesvariable is defined inside thecalculate_percentilefunction. Therefore, it is not accessible outside of the function. If you want to print thehistory_datesvariable, you need to modify your code as follows:percentile, history_dates = calculate_percentile(df,'2023-08-19','螺纹钢') print(percentile) print(history_dates) percentile = calculate_percentile(df,'2023-08-19','螺纹钢') print(percentile[0]) print(percentile[1])This will assign the returned values from the
calculate_percentilefunction to thepercentileandhistory_datesvariables, allowing you to print them separately. - User
好的 如果我要调整mean_target_value 的定义,将其改为target_value 及其往上数1,2,3,4行对应值的平均值 代码怎么改呢
- Assistant
如果你想将
mean_target_value定义为target_value及其往上数1、2、3、4行对应值的平均值,你可以修改calculate_percentile函数中的相关代码如下:def calculate_percentile(df, date, column): date = pd.to_datetime(date) year = date.year month = date.month day = date.day five_years_ago = datetime.datetime.now() - pd.DateOffset(years=5) history_dates = df[(df.index.month == month) & (df.index.day == day) & (df.index.year <= year) & (df.index > five_years_ago)].index history_values = df[df.index.isin(history_dates)][column] target_value = df.loc[date, column] mean_target_value = sum(df.loc[date:date - pd.DateOffset(years=4), column].values) / 5 # 计算目标值及其往上数1、2、3、4行对应值的平均值 min_value = history_values.min() max_value = history_values.max() percentile = (target_value - min_value) / (max_value - min_value) * 100 return percentile, history_dates这样,
mean_target_value的值将为目标值及其往上数1、2、3、4行对应值的平均值。你可以根据实际情况调整计算平均值的行数,这里设置为5行(目标值+往上数4行)。 - User
mean_target_value = sum(df.loc[date:date - pd.DateOffset(years=4), column].values) / 5 这一句话看不太懂 能帮我解释下代码的逻辑吗 谢谢
- Assistant
当我用
df.loc[date:date - pd.DateOffset(years=4), column]来选择数据时,它选择了date日期及其往上数4年的范围。假设date是2023-08-19,那么它选择了从2019-08-19到2023-08-19的这4年的数据。接着,
.values将这个选择的数据转换为一个NumPy数组,这样我们就可以使用NumPy的函数sum()对数组中的值进行求和。最后,将求和的结果除以5,得到
mean_target_value,即目标值及其往上数1、2、3、4行对应值的平均值。这里除以5是因为选择了5行数据(目标值+往上数4行)。
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.