I have a sample data which likes below. Start and End are paired up in the column.
And I don't know how many rows between one Start and End because of the real data is big.
df = pd.DataFrame({'Item':['Item_A','<Start>','A1','A2','<End>','Item_B','<Start>','B1','B2','B3','<End>']})
print (df)
Item
0 Item_A
1 <Start>
2 A1
3 A2
4 <End>
5 Item_B
6 <Start>
7 B1
8 B2
9 B3
10 <End>
How to change it to the format below with Pandas? Thanks.
Solution working if Item value is one row above Start rows:
#compare for Start
m1 = df['Item'].eq('<Start>')
#get Item rows by shift Start mask
m2 = m1.shift(-1, fill_value=False)
#replace non Item values to missing values and forward filling them
df['new'] = df['Item'].where(m2).ffill()
#compare End
m3 = df['Item'].eq('<End>')
#filter no matched rows between Start and End to m4
g = m1.cumsum()
s = m3.shift().cumsum()
m4 = s.groupby(g).transform('min').ne(s)
#filtering with swap columns
df1 = df.loc[~(m4 | m1 | m3), ['new','Item']].copy()
#new columns names
df1.columns = ['Item','Detail']
#replace duplicated to empty string
df1['Item'] = np.where(df1['Item'].duplicated(), '', df1['Item'])
print (df1)
Item Detail
2 Item_A A1
3 A2
7 Item_B B1
8 B2
9 B3