Skip to main content
 首页 » 编程设计

python之将新列添加到数据框,其中值取决于同一列的前一行值

2024年12月31日9sharpest

我有一个数据框(df),头部看起来像:

  BB   NEW_DATE     PICKED 
1123 03/10/2018 03/10/2018 
1123 04/10/2018 04/10/2018 
1123 05/10/2018 05/10/2018 
1123 09/10/2018 09/10/2018 
1123 04/01/2013 01/04/2013 
1123 07/01/2013 07/01/2013 
1123 08/01/2013 08/01/2013 

我正在尝试添加一个名为 FINAL 的新列,其值部分取决于 FINAL 的前一行值。

if df['PICKED'] < df['FINAL'].shift(-1): 
    if df['NEW_DATE'].isnumeric(): 
        df['FINAL'] = df['NEW_DATE']  
    else: 
        df['FINAL'] = df['PICKED'] 
    df['FINAL'] = df['PICKED'] 

对于每一行,如果 PICKED 小于前几行的 FINAL 值,则如果 NEW_DATE 是有效日期则当前行的值FINAL 等于 NEW_DATE 的当前行值,否则 FINAL 等于 PICKED。如果 PICKED 大于或等于 FINAL 的前一行值,则 FINAL 等于 PICKED。

所以在上面的数据框中,FINAL 列看起来像;

  BB     NEW_DATE       PICKED       FINAL 
1123   03/10/2018   03/10/2018  03/10/2018 
1123   04/10/2018   04/10/2018  04/10/2018 
1123   05/10/2018   05/10/2018  05/10/2018 
1123   09/10/2018   09/10/2018  09/10/2018 
1123   04/01/2013   01/04/2013  04/01/2013 
1123   07/01/2013   07/01/2013  07/01/2013 
1123   08/01/2013   08/01/2013  08/01/2013 

我尝试过使用以下方法编写此代码,但没有成功:

df['FINAL'] = np.where(df['PICKED'] < df['FINAL'].shift(-1), df.NEW_DATE.fillna(df.DATE), df['PICKED']) 

我也试过:

for row in df.iterrows: 
 
    if index == 0 : 
        row['FINAL'] = row['NEW_DATE'] 
    else: 
 
        if row['PICKED'] < row['FINAL'].shift(-1): 
            if isinstance(row['NEW_DATE'], pd.DatetimeIndex): 
                row['FINAL'] = row['NEW_DATE'] 
            else: 
                row['FINAL'] = row['PICKED'] 
        else: 
            row['FINAL'] = row['PICKED'] 

但我收到错误:TypeError: 'method' object is not iterable

请您参考如下方法:

我想不出没有循环 for 的方法,所以这是一种方法。

# Initalise the first value of FINAL that will be the previous value  
# in the first iteration of the loop 
prev_final = df.loc[0,'PICKED']  
 
#create a list containing the data to create the column FINAL after 
list_final = [prev_final]  
 
# loop over the rows with itertuples, not the first row as it has been take care of before 
for new_date, picked in df.loc[1:,['NEW_DATE','PICKED']].itertuples(index=False): 
 
    # check the two conditions at once as if both are not met, then the value in FINAL is from PICKED 
    if (picked < prev_final) & isinstance(new_date, pd.datetime): 
        # add the value from NEW_DATE 
        list_final.append(new_date)  
        # and update the prev_final for the next iteration of the loop 
        prev_final = new_date  
 
    else: # same idea if conditions not met 
        list_final.append(picked) 
        prev_final = picked 
 
#outside of the loop, create the column with the list 
df['FINAL'] = list_final 
 
print(df) 
     BB   NEW_DATE     PICKED      FINAL 
0  1123 2018-03-10 2018-03-10 2018-03-10 
1  1123 2018-04-10 2018-04-10 2018-04-10 
2  1123 2018-05-10 2018-05-10 2018-05-10 
3  1123 2018-09-10 2018-09-10 2018-09-10 
4  1123 2013-04-01 2013-01-04 2013-04-01 
5  1123 2013-07-01 2013-07-01 2013-07-01 
6  1123 2013-08-01 2013-08-01 2013-08-01