CODEWITHSUNDEEP
pythonpython-3.xpandasdataframe
Sean Nielsen
Sean Nielsen

Reputation: 53

Pandas: Fill column between 2 values if condition is true

I have some data, where I need to count the amount of km a vehicle have driven, while another condition is True.

I might be asking the wrong question, and there might be an obvious way to do this, but I have not been able to find it unfortunately. and there is kinda 2 questions here, hope this is ok, as it is related.

So what I want to do is add a series, that fills the rows with last known state. So if vehicle 1 opens the left front Window, I would then set the series to True, until the window is again closed. Then I would somehow (This I have also not figured out) calculate the amount of km the vehicle 1 have driven while left front window was open.

And then I would do the same for Right window and for all vehicles.

Below is an example of the data, and 

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)

Output

    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

Added a column with the current state of the front left window

    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

And the end calculations of the odometer of all vehicles with the amount of km they have run with open windows on either right side or left side.

   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

Upvotes: 2

Views: 1223

Answers (1)

rafaelc
rafaelc

Reputation: 59274

Use .where and forward filling: 

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

For per-vehicle analysis, you can use groupby and same logic

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

Upvotes: 1

Related Questions