from openpyxl import loadworkbook import pandas as pd 打开Excel文件 wb =…
- User
from openpyxl import load_workbook import pandas as pd
打开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)
calculate_percentile(df,'2013-08-19','螺纹钢')
def calculate_percentile(df,date, column): date = pd.to_datetime(date) year = date.year month = date.month day = date.day history_dates = df[(df.index.month == month) & (df.index.day == day) & (df.index.year <= year)].index history_values = df[df.index.isin(history_dates)][column] target_value = df.loc[date, column] min_value = history_values.min() max_value = history_values.max() percentile= (target_value - min_value) / (max_value - min_value) * 100
KeyError Traceback (most recent call last) ~\anaconda3\lib\site-packages\pandas_libs\index.pyx in pandas._libs.index.DatetimeEngine.get_loc()
pandas_libs\hashtable_class_helper.pxi in pandas._libs.hashtable.Int64HashTable.get_item()
pandas_libs\hashtable_class_helper.pxi in pandas._libs.hashtable.Int64HashTable.get_item()
KeyError: 1376870400000000000
During handling of the above exception, another exception occurred:
KeyError Traceback (most recent call last) ~\anaconda3\lib\site-packages\pandas\core\indexes\base.py in get_loc(self, key) 3652 try: -> 3653 return self._engine.get_loc(casted_key) 3654 except KeyError as err:
~\anaconda3\lib\site-packages\pandas_libs\index.pyx in pandas._libs.index.DatetimeEngine.get_loc()
~\anaconda3\lib\site-packages\pandas_libs\index.pyx in pandas._libs.index.DatetimeEngine.get_loc()
KeyError: Timestamp('2013-08-19 00:00:00')
The above exception was the direct cause of the following exception:
KeyError Traceback (most recent call last) ~\anaconda3\lib\site-packages\pandas\core\indexes\datetimes.py in get_loc(self, key) 583 try: --> 584 return Index.get_loc(self, key) 585 except KeyError as err:
~\anaconda3\lib\site-packages\pandas\core\indexes\base.py in get_loc(self, key) 3654 except KeyError as err: -> 3655 raise KeyError(key) from err 3656 except TypeError:
KeyError: Timestamp('2013-08-19 00:00:00')
The above exception was the direct cause of the following exception:
KeyError Traceback (most recent call last) ~\AppData\Local\Temp/ipykernel_8864/2648162365.py in 27 df.index = pd.to_datetime(df.index) 28 ---> 29 calculate_percentile(df,'2013-08-19','螺纹钢') 30 31 def calculate_percentile(df,date, column):
~\AppData\Local\Temp/ipykernel_8864/2397934083.py in calculate_percentile(df, date, column) 6 history_dates = df[(df.index.month == month) & (df.index.day == day) & (df.index.year <= year)].index 7 history_values = df[df.index.isin(history_dates)][column] ----> 8 target_value = df.loc[date, column] 9 min_value = history_values.min() 10 max_value = history_values.max()
~\anaconda3\lib\site-packages\pandas\core\indexing.py in getitem(self, key) 1094 key = tuple(com.apply_if_callable(x, self.obj) for x in key) 1095 if self._is_scalar_access(key): -> 1096 return self.obj._get_value(*key, takeable=self._takeable) 1097 return self._getitem_tuple(key) 1098 else:
~\anaconda3\lib\site-packages\pandas\core\frame.py in _get_value(self, index, col, takeable) 3875 # results if our categories are integers that dont match our codes 3876 # IntervalIndex: IntervalTree has no get_loc -> 3877 row = self.index.get_loc(index) 3878 return series._values[row] 3879
~\anaconda3\lib\site-packages\pandas\core\indexes\datetimes.py in get_loc(self, key) 584 return Index.get_loc(self, key) 585 except KeyError as err: --> 586 raise KeyError(orig_key) from err 587 588 @doc(DatetimeTimedeltaMixin._maybe_cast_slice_bound)
KeyError: Timestamp('2013-08-19 00:00:00') 错误信息如下 帮我看看怎么改?
- Assistant
在调用
calculate_percentile函数之前,你还需要将该函数的定义移动到函数调用之前,这样才能正确调用函数。另外,你可能需要检查数据中是否有名为"螺纹钢"的列,因为该列名在函数调用中是作为参数传递的。以下是修改后的代码:from openpyxl import load_workbook import pandas as pd def calculate_percentile(df,date, column): date = pd.to_datetime(date) year = date.year month = date.month day = date.day history_dates = df[(df.index.month == month) & (df.index.day == day) & (df.index.year <= year)].index history_values = df[df.index.isin(history_dates)][column] target_value = df.loc[date, column] min_value = history_values.min() max_value = history_values.max() percentile= (target_value - min_value) / (max_value - min_value) * 100 return percentile # 打开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) percentile = calculate_percentile(df,'2013-08-19','螺纹钢') print(percentile)请注意,我在
calculate_percentile函数中使用了return percentile语句,以便在函数调用后返回计算的百分位数值。我还添加了一行代码print(percentile)以打印结果。 - User
KeyError Traceback (most recent call last) ~\anaconda3\lib\site-packages\pandas_libs\index.pyx in pandas._libs.index.DatetimeEngine.get_loc()
pandas_libs\hashtable_class_helper.pxi in pandas._libs.hashtable.Int64HashTable.get_item()
pandas_libs\hashtable_class_helper.pxi in pandas._libs.hashtable.Int64HashTable.get_item()
KeyError: 1376870400000000000
During handling of the above exception, another exception occurred:
KeyError Traceback (most recent call last) ~\anaconda3\lib\site-packages\pandas\core\indexes\base.py in get_loc(self, key) 3652 try: -> 3653 return self._engine.get_loc(casted_key) 3654 except KeyError as err:
~\anaconda3\lib\site-packages\pandas_libs\index.pyx in pandas._libs.index.DatetimeEngine.get_loc()
~\anaconda3\lib\site-packages\pandas_libs\index.pyx in pandas._libs.index.DatetimeEngine.get_loc()
KeyError: Timestamp('2013-08-19 00:00:00')
The above exception was the direct cause of the following exception:
KeyError Traceback (most recent call last) ~\anaconda3\lib\site-packages\pandas\core\indexes\datetimes.py in get_loc(self, key) 583 try: --> 584 return Index.get_loc(self, key) 585 except KeyError as err:
~\anaconda3\lib\site-packages\pandas\core\indexes\base.py in get_loc(self, key) 3654 except KeyError as err: -> 3655 raise KeyError(key) from err 3656 except TypeError:
KeyError: Timestamp('2013-08-19 00:00:00')
The above exception was the direct cause of the following exception:
KeyError Traceback (most recent call last) ~\AppData\Local\Temp/ipykernel_8864/1957037896.py in 40 41 ---> 42 percentile =calculate_percentile(df,'2013-08-19','螺纹钢') 43 print(percentile)
~\AppData\Local\Temp/ipykernel_8864/1957037896.py in calculate_percentile(df, date, column) 34 history_dates = df[(df.index.month == month) & (df.index.day == day) & (df.index.year <= year)].index 35 history_values = df[df.index.isin(history_dates)][column] ---> 36 target_value = df.loc[date, column] 37 min_value = history_values.min() 38 max_value = history_values.max()
~\anaconda3\lib\site-packages\pandas\core\indexing.py in getitem(self, key) 1094 key = tuple(com.apply_if_callable(x, self.obj) for x in key) 1095 if self._is_scalar_access(key): -> 1096 return self.obj._get_value(*key, takeable=self._takeable) 1097 return self._getitem_tuple(key) 1098 else:
~\anaconda3\lib\site-packages\pandas\core\frame.py in _get_value(self, index, col, takeable) 3875 # results if our categories are integers that dont match our codes 3876 # IntervalIndex: IntervalTree has no get_loc -> 3877 row = self.index.get_loc(index) 3878 return series._values[row] 3879
~\anaconda3\lib\site-packages\pandas\core\indexes\datetimes.py in get_loc(self, key) 584 return Index.get_loc(self, key) 585 except KeyError as err: --> 586 raise KeyError(orig_key) from err 587 588 @doc(DatetimeTimedeltaMixin._maybe_cast_slice_bound)
KeyError: Timestamp('2013-08-19 00:00:00') 错误信息没有变化吧
- Assistant
对不起,我之前犯了一个错误。根据之前的代码,我们需要确认日期字符串是否与数据框索引中的日期格式完全匹配。在使用
pd.to_datetime函数将日期字符串转换为datetime格式之后,它将具有00:00:00的时间戳,但是你的数据框中的日期索引可能仅包含日期信息。因此,在计算百分位数之前,你需要使用date.date()将日期截断为仅包含日期信息,而不包含时间信息。以下是修改后的代码:from openpyxl import load_workbook import pandas as pd def calculate_percentile(df, date, column): # 将日期截断为仅包含日期信息 date = pd.to_datetime(date).date() year = date.year month = date.month day = date.day history_dates = df[(df.index.month == month) & (df.index.day == day) & (df.index.year <= year)].index history_values = df[df.index.isin(history_dates)][column] target_value = df.loc[date, column] min_value = history_values.min() max_value = history_values.max() percentile = (target_value - min_value) / (max_value - min_value) * 100 return percentile # 打开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) percentile = calculate_percentile(df, '2013-08-19', '螺纹钢') print(percentile)这次修改应该可以解决问题。请注意,我将函数定义放在函数调用之前,并且在
calculate_percentile函数中将date截断为仅包含日期信息。