Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0613· OpenAIAi2 WildChat-4.8M (AI2)

from openpyxl import loadworkbook import pandas as pd 打开Excel文件 wb =…

  1. 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') 错误信息如下 帮我看看怎么改?

  2. 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)以打印结果。

  3. 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') 错误信息没有变化吧

  4. 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截断为仅包含日期信息。