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

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

  1. 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_means
    

    percentile, 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

  2. Assistant

    The issue in your code is with the concatenation of the date_range variable using pd.concat(). The date_range variable is already a DatetimeIndex, and you cannot concatenate it with other objects using pd.concat().

    To fix this issue, you can directly create the date_range as a DatetimeIndex without using pd.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.

  3. User

    这样的话date_range会包含five_years_ago,和date这两天吗

  4. Assistant

    Apologies for the confusion. You're correct that the modified code I provided would include five_years_ago and date in the date_range. If you want to exclude these two dates, you can modify the date_range using 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_range will exclude five_years_ago and date while retaining the other dates within the specified range.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.