Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0613· openaiHugging Face Suzhen/CodeChat

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=3)    
    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
    

    创建一个空的列表,用于存储每一天的percentile值

    percentiles = []

    设置起始日期

    start_date = pd.to_datetime('2023-01-01')

    循环遍历DataFrame的每一行

    for index, row in df.iterrows(): # 检查是否满足起始日期的条件,如果不满足,则跳过计算 if index < start_date: percentiles.append(None) continue

    # 获取当前行的日期和列名
    date = index
    column = '螺纹钢'  # 要计算percentile的列名,请根据实际情况修改
    
    # 调用calculate_percentile函数计算percentile值
    percentile = calculate_percentile(df, date, column)
    
    # 将percentile值添加到列表中
    percentiles.append(percentile)
    

    将percentiles列表添加为新的一列到DataFrame中

    df['Percentile'] = percentiles

    现在我要在df增加一列df['Percentile YOY'],对每一个date,'Percentile YOY'就是这个date上的Percentile列结果减去上年同期的Percentile列结果。这个代码怎么写。

  2. Assistant

    可以使用shift函数和日期偏移来计算上一年同期的日期,并在循环中进行计算。以下是修改后的代码:

    # 创建一个空的列表,用于存储每一天的Percentile YOY值
    percentile_yoy = []
    
    # 循环遍历DataFrame的每一行
    for index, row in df.iterrows():
        # 检查是否满足起始日期的条件,如果不满足,则跳过计算
        if index < start_date:
            percentile_yoy.append(None)
            continue
        
        # 获取当前行的日期和列名
        date = index
        column = '螺纹钢'  # 要计算Percentile YOY的列名,请根据实际情况修改
    
        # 获取上一年同期的日期
        last_year_date = date - pd.DateOffset(years=1)
    
        # 检查上一年同期的日期是否在DataFrame中存在
        if last_year_date in df.index:
            # 计算上一年同期的Percentile值
            last_year_percentile = df.loc[last_year_date, 'Percentile']
            # 计算Percentile YOY值
            percentile_yoy_value = row['Percentile'] - last_year_percentile
            percentile_yoy.append(percentile_yoy_value)
        else:
            percentile_yoy.append(None)
    
    # 将percentile_yoy列表添加为新的一列到DataFrame中
    df['Percentile YOY'] = percentile_yoy
    

    请根据实际情况修改column变量的值以及你想要的起始日期start_date。

  3. User

    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:] 这几句代码麻烦给我注释一下

  4. Assistant

    Sure! Here are the explanations of the code snippets:

    1. headers = ['Column1','Column2', 'Column3', 'Column4', 'Column5', 'Column6']: This line defines a list called headers which contains the column names for the DataFrame. You can modify this list according to your actual column names.

    2. df = pd.DataFrame(data, columns=headers): This line creates a DataFrame called df using the pd.DataFrame() function. It takes data as the data parameter and headers as the columns parameter. The result is a DataFrame with the specified column names.

    3. df = df[1:]: This line slices the DataFrame df to exclude the first row. Usually, the first row in an Excel file is the header row, so it is removed to ensure that the DataFrame only contains the data rows.

    4. df.columns = df.iloc[0]: This line assigns the first row of the DataFrame df as the new column names. The df.iloc[0] is used to select the first row in the DataFrame.

    5. df = df[1:]: This line slices the DataFrame df again to exclude the first row. This is necessary because the first row is used as the new column names, so it is no longer needed in the data.

    Overall, these code snippets read the data from the Excel file into a DataFrame, set the column names, and perform some slicing to remove unnecessary rows.

  5. User

    选择指定的sheet

    sheet = wb['吨钢毛利'] # 这里的Sheet3是你要读取的sheet名称,可以根据实际情况修改

    选择指定的列

    columns = [0, 9, 10, 11, 12]

    读取数据

    data = [] for row in sheet.iter_rows(values_only=True): data.append([row[column] for column in columns]) df = pd.DataFrame(data) df.set_index(df.columns[0], inplace=True) df.index = pd.to_datetime(df.index) 这段代码是用来读取数据的。但是我检查发现,excel中有数据的行数只有10行,但是df却有20行,打印出来后面Index都是NaT 列值都是None 这是为啥?

  6. Assistant

    The issue you're facing might be caused by some empty rows in the Excel sheet after the actual data. When reading the data using sheet.iter_rows(), it will still iterate over those empty rows, resulting in a larger number of rows in the DataFrame with None values.

    To handle this issue, you can modify the data reading process to stop reading rows once a row with empty values is encountered. Here's an updated version of the code snippet:

    # 读取数据
    data = []
    for row in sheet.iter_rows(values_only=True):
        if all(value is None for value in row):
            break
        data.append([row[column] for column in columns])
    
    df = pd.DataFrame(data)
    df.set_index(df.columns[0], inplace=True)
    df.index = pd.to_datetime(df.index)
    

    In this updated code, it checks if all the values in a row are None, and if so, it breaks the loop to stop reading further rows. This will ensure that the DataFrame only includes the actual data rows and excludes any empty rows.

    Please give it a try and let me know if it resolves the issue!

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