Split pandas dataframe rows up to searched column value into new dataframes

user_3938025 0
Viewed 26

I have a dataframe that contains multiple header rows (a combination of multiple csvs). Is there a way to split the dataframe back into individual dataframes without using .iloc? iloc works, but will be time consuming for my workflow.

data = {'A':  [1,2,3,'A',4,5,6,'A',7,8,9],
        'B': [9,8,7,'B',6,5,4,'B',3,2,1]}
df = pd.DataFrame(data, columns = ['A','B'])

## My current approach:
df1 = df.iloc[:3,]
df2 = df.iloc[4:7,]
df3 = df.iloc[8:,]

Is there a better way to split the data frame by searching for the values in the columns? i.e. something like df1,df2,df3 = df.split(df['A']=='A')

pandas dataframe python split
1 Answers

One can use eq to check for the header rows, then groupby on the cumsum:

header_rows = df.eq(df.columns).all(1)
dfs = {k:v for k,v in df[~header_rows].groupby(header_rows.cumsum())}

then, for example dfs[0] gives:

   A  B
0  1  9
1  2  8
2  3  7
user_42384080
Related