Split a datrafame pandas based on raw values interval in string format-CodePudding

I have a little problem that I don't get solutions. I have this dataset as an example: Columns=[A,B,C]

A,B,C
F,Relax,begin
F,,
F,,
H,,
H,,
H,,
G,,
H,,
I,,
G,,
H,Relax,end
H,,
H,,
H,,
F,,
G,,
A,,
O,Cook,begin
Q,,
P,,
I,,
O,,
R,,
P,,
O,Cook,end
G,,
H,,
F,,
G,,
H,Relax,begin
F,,
G,,
I,,
I,,
I,,
I,,
I,,
I,,
I,Relax,end
H,,
I,,
G,,

I want to split this dataframe according to different intervals (begin and end in the C column) in many dataframes, and delete unnecessary raws (raws that are not present in intervals begin and end). For example, expected final dataframes:

dataframe 1
A,B,C
F,Relax,begin
F,,
F,,
H,,
H,,
H,,
G,,
H,,
I,,
G,,
H,Relax,end

dataframe 2
A,B,C
O,Cook,begin
Q,,
P,,
I,,
O,,
R,,
P,,
O,Cook,end

dataframe 3
A,B,C 
H,Relax,begin
F,,
G,,
I,,
I,,
I,,
I,,
I,,
I,,
I,Relax,end

Does everyone know how to solve this problem? Regards.

CodePudding user response：

Find the `'begin'` and `'end'` with Numpy

Keep in mind, this is going to get goofy if your begins and ends don't pair up as expected.

begins = np.flatnonzero(df.C.eq('begin'))
ends = np.flatnonzero(df.C.eq('end'))

dfs = {
    f'dataframe {i}': d
    for i, d in enumerate(
        [df.iloc[b:e 1] for b, e in zip(begins, ends)],
        start=1
    )}

dfs

{'dataframe 1':     A      B      C
 0   F  Relax  begin
 1   F    NaN    NaN
 2   F    NaN    NaN
 3   H    NaN    NaN
 4   H    NaN    NaN
 5   H    NaN    NaN
 6   G    NaN    NaN
 7   H    NaN    NaN
 8   I    NaN    NaN
 9   G    NaN    NaN
 10  H  Relax    end,
 'dataframe 2':     A     B      C
 17  O  Cook  begin
 18  Q   NaN    NaN
 19  P   NaN    NaN
 20  I   NaN    NaN
 21  O   NaN    NaN
 22  R   NaN    NaN
 23  P   NaN    NaN
 24  O  Cook    end,
 'dataframe 3':     A      B      C
 29  H  Relax  begin
 30  F    NaN    NaN
 31  G    NaN    NaN
 32  I    NaN    NaN
 33  I    NaN    NaN
 34  I    NaN    NaN
 35  I    NaN    NaN
 36  I    NaN    NaN
 37  I    NaN    NaN
 38  I  Relax    end}

CodePudding user response：

You can first get rid of the rows between an end and a begin using a mask, then use groupby on the groups starting with "begin" to split into several dataframes:

# filter intermediate rows
m = df['C'].map({'begin':True, 'end':False}).ffill().fillna(False)

# split
grouper = df['C'].eq('begin').cumsum()
dfs = [g for _,g in df[m|m.shift()].groupby(grouper)]

print(dfs)

Output (list of DataFrames):

[    A      B      C
 0   F  Relax  begin
 1   F    NaN    NaN
 2   F    NaN    NaN
 3   H    NaN    NaN
 4   H    NaN    NaN
 5   H    NaN    NaN
 6   G    NaN    NaN
 7   H    NaN    NaN
 8   I    NaN    NaN
 9   G    NaN    NaN
 10  H  Relax    end,
     A     B      C
 17  O  Cook  begin
 18  Q   NaN    NaN
 19  P   NaN    NaN
 20  I   NaN    NaN
 21  O   NaN    NaN
 22  R   NaN    NaN
 23  P   NaN    NaN
 24  O  Cook    end,
     A      B      C
 29  H  Relax  begin
 30  F    NaN    NaN
 31  G    NaN    NaN
 32  I    NaN    NaN
 33  I    NaN    NaN
 34  I    NaN    NaN
 35  I    NaN    NaN
 36  I    NaN    NaN
 37  I    NaN    NaN
 38  I  Relax    end]

Find the 'begin' and 'end' with Numpy

Find the `'begin'` and `'end'` with Numpy