I have a dataframe like this generated from the below code: df1
import pandas as pd
import numpy as np
df = pd.DataFrame({'Start': [True, '-', '-', '-', True, '-', '-', '-', True, '-', '-', '-'],
'End': ['-', '-', '-', True, '-', '-', '-', True, '-', '-', '-', True],
'Value': ['-', 15, '-', '-', 109, '-', '-', '-', '-', '-', 13, '-']})
df
| | Start | End | Value |
|-------|-------|-------|-------|
| 0 | True | - | - |
| 1 | - | - | 15 |
| 2 | - | - | - |
| 3 | - | True | - |
| 4 | True | - | 109 |
| 5 | - | - | - |
| 6 | - | - | - |
| 7 | - | True | - |
| 8 | True | - | - |
| 9 | - | - | - |
| 10 | - | - | 13 |
| 11 | - | True | - |
and I would like to forward fill the 'Value' column till the point where 'End' column == True like this: df2
| | Start | End | Value |
|-------|-------|-------|-------|
| 0 | True | - | - |
| 1 | - | - | 15 |
| 2 | - | - | 15 |
| 3 | - | True | 15 |
| 4 | True | - | 109 |
| 5 | - | - | 109 |
| 6 | - | - | 109 |
| 7 | - | True | 109 |
| 8 | True | - | - |
| 9 | - | - | - |
| 10 | - | - | 13 |
| 11 | - | True | 13 |
Any help will be highly appreaciated!
PS. Sorry my reputation is not high enough to post images directly...
First, always avoid to have mixed types in a column. Apparently you have strings and boolean values - that's not recommended.
First, make your data frame workable:
df = df.replace('-',np.nan).astype(float)
Then, simple groupby+ffill
s = df.start.eq(1).cumsum()
df['value'] = df.groupby(s).value.ffill()
start end value
0 1.0 NaN NaN
1 NaN NaN 15.0
2 NaN NaN 15.0
3 NaN 1.0 15.0
4 1.0 NaN 109.0
5 NaN NaN 109.0
6 NaN NaN 109.0
7 NaN 1.0 109.0
8 1.0 NaN NaN
9 NaN NaN NaN
10 NaN NaN 13.0
11 NaN 1.0 13.0