Pandas：如果条件为tru，则填充两个值之间的列

Question

我有一些数据，我需要计算车辆行驶的公里数，而另一个条件是真的。你知道吗

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

所以我要做的是添加一个序列，用最后一个已知的状态填充行。 因此，如果vehicle 1打开左前车窗，我会将序列设置为True，直到车窗再次关闭。 然后我会不知何故（这个我也没有弄清楚）计算车辆在左前车窗打开时行驶的公里数。你知道吗

然后我会对右车窗和所有车辆做同样的处理。你知道吗

以下是数据示例，以及

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

以及所有车辆在右侧或左侧车窗打开时行驶的公里数的里程表的最终计算。你知道吗

   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

Answer 1

使用.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