主页 / 问题 / 1588971
Accepted
Masik
Asked:2024-07-31 14:19:10 +0000 UTC2024-07-31 14:19:10 +0000 UTC

Pandas 迭代 2 个 DataFrame 中的行并更改 2 列

  • 772

我有 2 个数据框：1. 产品，2. 我正在尝试的每日余额：

  1. 按“Artical”列对产品进行排序，从“prod_df”到“sales_df”
  2. 对于找到的每个产品，更改数量“Start_of_Day”和“End_of_Day”
  • 如果日期小于“report_start”减 1 天，则“Start_of_Day”和“End_of_Day”等于“Qty”
  • 如果不是，则“Start_of_Day”等于前一天的“End_of_Day”，并且“End_of_Day”等于“Qty”

结果，我想要得到如图所示的 DataFrame。但我得到一个 DataFrame，其中“Start_of_Day”和“End_of_Day”等于 0

如何替换值？

期望的结果

这是我尝试过的：

mport pandas as pd
from datetime import timedelta

report_start = '01.06.2024'
report_start = pd.to_datetime(report_start, format='%d.%m.%Y',errors='coerce')
report_end = '04.06.2024'
report_end = pd.to_datetime(report_end, format='%d.%m.%Y',errors='coerce')

prod_df = pd.DataFrame({
    'Artical': ['111', '222', '333', '444'],
    'Name': ['name1', 'name2', 'name3', 'name4'],
})

sales_df = pd.DataFrame({
    'Artical': ['111', '111', '111', '111', '111', '222', '222', '222', '222', '222'],
    'Date': ['31.05.2024', '01.06.2024', '02.06.2024', '03.06.2024', '04.06.2024', '31.05.2024', '01.06.2024', '02.06.2024', '03.06.2024', '04.06.2024'],
    'Qty': ['2172', '2172', '2172', '2128', '2128', '0', '2068', '2068', '2056', '2056']    
})
frames = []
for i in prod_df['Artical']:
    data = sales_df[(sales_df['Artical'] == str(i))].reset_index(drop=True)
    data['Start_of_Day'] = 0
    data['End_of_Day'] = 0
    if data.empty == False:
        for index, row in data.iterrows():
            if row['Date'] == (report_start - timedelta(days=1)):
                row['Start_of_Day'] = row['Qty']
                row['End_of_Day'] = row['Qty']
                # row['qtyStart_of_Day'] = row['qtyStart_of_Day'].replace(0, row['Qty'])
                print(row)
            else:
                new_qty = data[(data['Date'] == (report_start - timedelta(days=1)))].reset_index(drop=True)
                row['Start_of_Day'] = new_qty['End_of_Day']
                row['End_of_Day'] = row['Qty']
                
            frames.append(data)
        
final_df = pd.concat(frames, axis=0, ignore_index=True)
print(final_df)
python

1 个回答

  1. Best Answer

    好吧，这是带有注释的“向量”方法的示例

    # преобразование строк в даты
report_start = pd.to_datetime(report_start, dayfirst=True)
sales_df['Date'] = pd.to_datetime(sales_df['Date'], dayfirst=True)
# сортируем, чтобы артикулы и даты были упорядочены
sales_df = sales_df.sort_values(['Artical', 'Date'])
# End_of_Day одинаковый при всех условиях
sales_df['End_of_Day'] = sales_df['Qty']
# строки, где начинается новый артикл
article_change = (sales_df['Artical'] != sales_df.shift()['Artical'])
# маска, по которой будем по-разному заполнять данные
old_mask = ((sales_df['Date'] - report_start).dt.days <= -1) | article_change
# фильтр по маске
sales_df.loc[old_mask, 'Start_of_Day'] = sales_df['Qty']
# фильтр по отрицанию маски
sales_df.loc[~old_mask, 'Start_of_Day'] = sales_df.shift()['End_of_Day']
# колонки переставляем в нужном порядке
sales_df = sales_df[['Artical', 'Date', 'Qty', 'Start_of_Day', 'End_of_Day']]
    • 3

相关问题