Skip to main content
 首页 » 编程设计

python之Pandas :如果条件为真,则在 2 个值之间填充列

2024年11月01日15freeliver54

我有一些数据,我需要计算车辆行驶的公里数,而另一个条件为真。

我可能问错了问题,可能有一个明显的方法可以做到这一点,但不幸的是我没能找到它。 这里有两个问题,希望没问题,因为它是相关的。

所以我想做的是添加一个系列,用最后已知的状态填充行。 因此,如果车辆 1 打开左前窗,我会将系列设置为 True,直到 window 再次关闭。 然后我会以某种方式(我也没有弄清楚)计算车辆 1 在左前窗打开时行驶的公里数。

然后我会对右窗和所有车辆执行相同的操作。

下面是数据的例子,

import pandas as pd 
 
matrix = [(1, 'Front Left Window Open', True), 
         (2, 'Engine Started', True), 
         (3, 'Engine Started', True), 
         (4, 'Front Left Window Open', True), 
         (1, 'Engine Started', True), 
         (2, 'Engine Started', True), 
         (3, 'Engine Started', True), 
         (1, 'Odometer', 254), 
         (1, 'Engine Started', True), 
         (2, 'Engine Started', True), 
         (3, 'Engine Started', True), 
         (1, 'Front Right Window Open', True), 
         (1, 'Engine Started', True), 
         (1, 'Odometer', 257), 
         (3, 'Engine Started', True), 
         (1, 'Odometer', 259), 
         (1, 'Front Left Window Open', False), 
         (1, 'Engine Started', True), 
         (1, 'Odometer', 261), 
         (3, 'Engine Started', True), 
         (1, 'Front Left Window Open', True), 
         (1, 'Odometer', 265), 
         ] 
 
# Create a DataFrame object 
dfObj = pd.DataFrame(matrix, columns=['Vehicle', 'Event', 'State']) 
 
print (dfObj) 
 
state = [True,True,True,True,True,True, True, True, True, True, True, True, True, True, True,True, False, False, False, False, True, True] 
 
dfObj["FrontLeftWindowOpen"] = state 
 
print ("\n\n\n") 
 
print (dfObj[dfObj.Vehicle == 1]) 
 
print ("\n\n\n") 
 
matrix = [(1, 'Front Left Window Open', 5), 
         (1, 'Front Right Window Open', 2), 
         (2, 'Front Left Window Open', 15), 
         (2, 'Front Right Window Open', 12) 
         ] 
 
# Create a DataFrame object 
dfObj = pd.DataFrame(matrix, columns=['Vehicle', 'state', 'km']) 
 
print (dfObj) 

输出

    Vehicle                    Event  State 
0         1   Front Left Window Open   True 
1         2           Engine Started   True 
2         3           Engine Started   True 
3         4   Front Left Window Open   True 
4         1           Engine Started   True 
5         2           Engine Started   True 
6         3           Engine Started   True 
7         1                 Odometer    254 
8         1           Engine Started   True 
9         2           Engine Started   True 
10        3           Engine Started   True 
11        1  Front Right Window Open   True 
12        1           Engine Started   True 
13        1                 Odometer    257 
14        3           Engine Started   True 
15        1                 Odometer    259 
16        1   Front Left Window Open  False 
17        1           Engine Started   True 
18        1                 Odometer    261 
19        3           Engine Started   True 
20        1   Front Left Window Open   True 
21        1                 Odometer    265 

添加了一个包含左前窗口当前状态的列

    Vehicle                    Event  State  FrontLeftWindowOpen 
0         1   Front Left Window Open   True                 True 
4         1           Engine Started   True                 True 
7         1                 Odometer    254                 True 
8         1           Engine Started   True                 True 
11        1  Front Right Window Open   True                 True 
12        1           Engine Started   True                 True 
13        1                 Odometer    257                 True 
15        1                 Odometer    259                 True 
16        1   Front Left Window Open  False                False 
17        1           Engine Started   True                False 
18        1                 Odometer    261                False 
20        1   Front Left Window Open   True                 True 
21        1                 Odometer    265                 True 
 

最后计算所有车辆的里程表以及它们在右侧或左侧打开 window 时行驶的公里数。

   Vehicle                    state  km 
0        1   Front Left Window Open   5 
1        1  Front Right Window Open   2 
2        2   Front Left Window Open  15 
3        2  Front Right Window Open  12 

请您参考如下方法:

使用.where 和正向填充:

df['LeftWindowOpen'] = df.State.where(df.Event.eq('Front Left Window Open')).ffill() 

    Vehicle                    Event  State  FrontLeftWindowOpen 
0         1   Front Left Window Open   True                 True 
1         2           Engine Started   True                 True 
2         3           Engine Started   True                 True 
3         4   Front Left Window Open   True                 True 
4         1           Engine Started   True                 True 
5         2           Engine Started   True                 True 
6         3           Engine Started   True                 True 
7         1                 Odometer    254                 True 
8         1           Engine Started   True                 True 
9         2           Engine Started   True                 True 
10        3           Engine Started   True                 True 
11        1  Front Right Window Open   True                 True 
12        1           Engine Started   True                 True 
13        1                 Odometer    257                 True 
14        3           Engine Started   True                 True 
15        1                 Odometer    259                 True 
16        1   Front Left Window Open  False                False 
17        1           Engine Started   True                False 
18        1                 Odometer    261                False 
19        3           Engine Started   True                False 
20        1   Front Left Window Open   True                 True 
21        1                 Odometer    265                 True 

对于每辆车的分析,您可以使用 groupby 和相同的逻辑

df.groupby('Vehicle')[['Event', 'State']]\ 
  .apply(lambda s: s['State'].where(s['Event'].eq('Front Left Window Open'))\ 
                             .reindex(df.index)\ 
                             .ffill() 
         ) 

State      0     1     2     3     4   ...     17     18     19    20    21 
Vehicle                                ...                                  
1        True  True  True  True  True  ...  False  False  False  True  True 
2         NaN   NaN   NaN   NaN   NaN  ...    NaN    NaN    NaN   NaN   NaN 
3         NaN   NaN   NaN   NaN   NaN  ...    NaN    NaN    NaN   NaN   NaN 
4         NaN   NaN   NaN  True  True  ...   True   True   True  True  True