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) target_dates = pd.date_range(end=date, periods=5) target_values = df.loc[target_dates, column] target_value_mean = target_values.mean()
five_years_ago = date - pd.DateOffset(years=4) date_range = pd.date_range(start=five_years_ago, end=date) date_range = pd.to_datetime(pd.concat([pd.Series([five_years_ago]), date_range, pd.Series([date])])) history_values = df.loc[date_range, column] # Calculate smoothed values history_means = history_values.rolling(window=5).mean() # Filter out the same month and day values history_values = df[(df.index.month.isin(date_range.month)) & (df.index.day.isin(date_range.day))][column] target_smoothed_value = target_value_mean max_smoothed_value = history_means.max() min_smoothed_value = history_means.min() percentile = (target_smoothed_value - min_smoothed_value) / (max_smoothed_value - min_smoothed_value) * 100 return percentile, history_meanspercentile, history_values =calculate_percentile(df,'2023-08-19','螺纹钢') print(percentile) print(history_values) 我的代码返回了这个错误信息,请你看下问题在哪儿
TypeError Traceback (most recent call last) ~\AppData\Local\Temp/ipykernel_1100/1808308807.py in 52 return percentile, history_means 53 ---> 54 percentile, history_values =calculate_percentile(df,'2023-08-19','螺纹钢') 55 print(percentile) 56 print(history_values)
~\AppData\Local\Temp/ipykernel_1100/1808308807.py in calculate_percentile(df, date, column) 36 five_years_ago = date - pd.DateOffset(years=4) 37 date_range = pd.date_range(start=five_years_ago, end=date) ---> 38 date_range = pd.to_datetime(pd.concat([pd.Series([five_years_ago]), date_range, pd.Series([date])])) 39 history_values = df.loc[date_range, column] 40
~\anaconda3\lib\site-packages\pandas\core\reshape\concat.py in concat(objs, axis, join, ignore_index, keys, levels, names, verify_integrity, sort, copy) 370 copy = False 371 --> 372 op = _Concatenator( 373 objs, 374 axis=axis,
~\anaconda3\lib\site-packages\pandas\core\reshape\concat.py in init(self, objs, axis, join, keys, levels, names, ignore_index, verify_integrity, copy, sort) 460 "only Series and DataFrame objs are valid" 461 ) --> 462 raise TypeError(msg) 463 464 ndims.add(obj.ndim)
TypeError: cannot concatenate object of type '<class 'pandas.core.indexes.datetimes.DatetimeIndex'>'; only Series and DataFrame objs are valid
- Assistant
The issue in your code is with the concatenation of the
date_rangevariable usingpd.concat(). Thedate_rangevariable is already aDatetimeIndex, and you cannot concatenate it with other objects usingpd.concat().To fix this issue, you can directly create the
date_rangeas aDatetimeIndexwithout usingpd.concat(). Here's the modified code:def calculate_percentile(df, date, column): date = pd.to_datetime(date) target_dates = pd.date_range(end=date, periods=5) target_values = df.loc[target_dates, column] target_value_mean = target_values.mean() five_years_ago = date - pd.DateOffset(years=4) date_range = pd.date_range(start=five_years_ago, end=date) history_values = df.loc[date_range, column] # Calculate smoothed values history_means = history_values.rolling(window=5).mean() # Filter out the same month and day values history_values = df[(df.index.month.isin(date_range.month)) & (df.index.day.isin(date_range.day))][column] target_smoothed_value = target_value_mean max_smoothed_value = history_means.max() min_smoothed_value = history_means.min() percentile = (target_smoothed_value - min_smoothed_value) / (max_smoothed_value - min_smoothed_value) * 100 return percentile, history_means percentile, history_values = calculate_percentile(df, '2023-08-19', '螺纹钢') print(percentile) print(history_values)This modified code should resolve the issue and calculate the percentile correctly.
- User
这样的话date_range会包含five_years_ago,和date这两天吗
- Assistant
Apologies for the confusion. You're correct that the modified code I provided would include
five_years_agoanddatein thedate_range. If you want to exclude these two dates, you can modify thedate_rangeusing slicing. Here's the updated code:def calculate_percentile(df, date, column): date = pd.to_datetime(date) target_dates = pd.date_range(end=date, periods=5) target_values = df.loc[target_dates, column] target_value_mean = target_values.mean() five_years_ago = date - pd.DateOffset(years=4) date_range = pd.date_range(start=five_years_ago, end=date) date_range = date_range[1:-1] # Exclude five_years_ago and date history_values = df.loc[date_range, column] # Calculate smoothed values history_means = history_values.rolling(window=5).mean() # Filter out the same month and day values history_values = df[(df.index.month.isin(date_range.month)) & (df.index.day.isin(date_range.day))][column] target_smoothed_value = target_value_mean max_smoothed_value = history_means.max() min_smoothed_value = history_means.min() percentile = (target_smoothed_value - min_smoothed_value) / (max_smoothed_value - min_smoothed_value) * 100 return percentile, history_means percentile, history_values = calculate_percentile(df, '2023-08-19', '螺纹钢') print(percentile) print(history_values)Now, the
date_rangewill excludefive_years_agoanddatewhile retaining the other dates within the specified range.
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.