Skip to main content
 首页 » 编程设计

python之在 Python 中根据列值减去行的最佳方法

2025年02月15日6emanlee

我需要根据不同列的值减去行的值。我的数据框如下所示:

Id |    col1    |  col2    | col3 | 
1  | 2016-01-02 |  7:00:00 |  Yes | 
1  | 2016-01-02 |  7:05:00 |  No  | 
1  | 2016-01-02 |  7:10:00 |  Yes | 
1  | 2016-01-02 |  8:00:00 |  No  | 
2  | 2016-01-02 |  7:10:00 |  Yes |  
2  | 2016-01-02 |  7:50:00 |  No  |  
2  | 2016-01-02 |  9:00:00 |  No  |  
2  | 2016-01-02 |  9:10:00 |  No  |  
2  | 2016-01-02 |  9:15:00 |  No  |  
3  | 2016-01-02 |  6:05:00 |  Yes |  
3  | 2016-01-02 |  6:10:00 |  Yes |  
3  | 2016-01-02 |  6:20:00 |  Yes |  
3  | 2016-01-02 |  6:45:00 |  No  |  

我需要根据 col3 的值计算 col1 和 col2 组合的平均时间差。规则如下:

Whenever there is a Yes in col3 do row-next row

到目前为止我所做的一个简化版本是遍历数据框中的所有值并执行此操作:

for i in range(len(df)): 
    if df['col3'][i] == 'Yes': 
        date1 = datetime.combine(df['col1'][i], df['col2'][i]) 
        date2 = datetime.combine(df['col1'][i+1], df['col2'][i+1]) 
        dict[df['Id'][i]] = date1-date2 

变量 dict 只是一个字典,其中包含每个不同 Id 的结果。

由于我有超过 6MM 的行,循环需要很多时间才能完成,所以我想知道是否有人可以想出更高效、更优雅的解决方案。

谢谢!

请您参考如下方法:

我认为你可以使用:

#datetime column - add time to dates 
df['datetime'] = pd.to_datetime(df['col1']) + pd.to_timedelta(df['col2']) 
#get difference of all values, filter by multiple mask only if `Yes` 
#convert to ns for numeric for aggregate 
df['dif']=df['datetime'].diff(-1).mul(df['col3'] == 'Yes').fillna(0).values.astype(np.int64) 
print (df) 
    Id        col1     col2 col3            datetime            dif 
0    1  2016-01-02  7:00:00  Yes 2016-01-02 07:00:00  -300000000000 
1    1  2016-01-02  7:05:00   No 2016-01-02 07:05:00              0 
2    1  2016-01-02  7:10:00  Yes 2016-01-02 07:10:00 -3000000000000 
3    1  2016-01-02  8:00:00   No 2016-01-02 08:00:00              0 
4    2  2016-01-02  7:10:00  Yes 2016-01-02 07:10:00 -2400000000000 
5    2  2016-01-02  7:50:00   No 2016-01-02 07:50:00              0 
6    2  2016-01-02  9:00:00   No 2016-01-02 09:00:00              0 
7    2  2016-01-02  9:10:00   No 2016-01-02 09:10:00              0 
8    2  2016-01-02  9:15:00   No 2016-01-02 09:15:00              0 
9    3  2016-01-02  6:05:00  Yes 2016-01-02 06:05:00  -300000000000 
10   3  2016-01-02  6:10:00  Yes 2016-01-02 06:10:00  -600000000000 
11   3  2016-01-02  6:20:00  Yes 2016-01-02 06:20:00 -1500000000000 
12   3  2016-01-02  6:45:00   No 2016-01-02 06:45:00              0 
 
d = pd.to_timedelta(df.groupby('Id')['dif'].mean()).to_dict() 
print (d) 
{1: Timedelta('-1 days +23:46:15'),  
 2: Timedelta('-1 days +23:52:00'),  
 3: Timedelta('-1 days +23:50:00')} 

什么是相同的:

datetime = pd.to_datetime(df['col1']) + pd.to_timedelta(df['col2']) 
diff = datetime.diff(-1).mul(df['col3'] == 'Yes').fillna(0).values.astype(np.int64) 
d = pd.to_timedelta(pd.Series(diff, index=df.index).groupby(df['Id']).mean()).to_dict() 
print (d) 
{1: Timedelta('-1 days +23:46:15'),  
 2: Timedelta('-1 days +23:52:00'),  
 3: Timedelta('-1 days +23:50:00')} 

但如果需要绝对值来移除负时间增量,请添加 numpy.abs:

df['datetime'] = pd.to_datetime(df['col1']) + pd.to_timedelta(df['col2']) 
df['dif'] = np.abs(df['datetime'].diff(-1) 
                                 .mul(df['col3'] == 'Yes') 
                                 .fillna(0) 
                                 .values 
                                 .astype(np.int64)) 
print (df) 
    Id        col1     col2 col3            datetime            dif 
0    1  2016-01-02  7:00:00  Yes 2016-01-02 07:00:00   300000000000 
1    1  2016-01-02  7:05:00   No 2016-01-02 07:05:00              0 
2    1  2016-01-02  7:10:00  Yes 2016-01-02 07:10:00  3000000000000 
3    1  2016-01-02  8:00:00   No 2016-01-02 08:00:00              0 
4    2  2016-01-02  7:10:00  Yes 2016-01-02 07:10:00  2400000000000 
5    2  2016-01-02  7:50:00   No 2016-01-02 07:50:00              0 
6    2  2016-01-02  9:00:00   No 2016-01-02 09:00:00              0 
7    2  2016-01-02  9:10:00   No 2016-01-02 09:10:00              0 
8    2  2016-01-02  9:15:00   No 2016-01-02 09:15:00              0 
9    3  2016-01-02  6:05:00  Yes 2016-01-02 06:05:00   300000000000 
10   3  2016-01-02  6:10:00  Yes 2016-01-02 06:10:00   600000000000 
11   3  2016-01-02  6:20:00  Yes 2016-01-02 06:20:00  1500000000000 
12   3  2016-01-02  6:45:00   No 2016-01-02 06:45:00              0 
 
d = pd.to_timedelta(df.groupby('Id')['dif'].mean()).to_dict() 
print (d) 
{1: Timedelta('0 days 00:13:45'),  
 2: Timedelta('0 days 00:08:00'),  
 3: Timedelta('0 days 00:10:00')}