| col1 | col2 | col3 |
|---|---|---|
| A1 | data 1 | |
| Val B | data 2 | data 6 |
| Val B | data 3 | data |
| A2 | data 4 | data |
| Val B | data 5 | data 7 |
In the first column(col1), if ValB is found, just below the a cell, that starts with ‘A’, replace the only the ValB cell with the above cell element (that starts with A) retaining other values in the row of ValB. And ignore other ‘Val B’ rows if they are not below a cell that starts with A.
| col1 | col2 | col3 |
|---|---|---|
| A1 | data 2 | data 6 |
| A2 | data 5 | data 7 |
Result
I want the result like this. Using python
>Solution :
If need one row after match condition by Series.str.startswith with replace col1 by original DataFrame use:
df = df.shift(-1)[df['col1'].str.startswith('A')].assign(col1 = df['col1'])
print (df)
col1 col2 col3
0 A1 data 2 data 6
3 A2 data 5 data 7
Another idea is shifting only col1 and then filter by condition in boolean indexing:
df['col1'] = df['col1'].shift()
df = df[df['col1'].str.startswith('A', na=False)]
print (df)
col1 col2 col3
1 A1 data 2 data 6
4 A2 data 5 data 7