Split pandas data frame columns by row value

user_425024 0
Viewed 675

I have a data frame like this:

>df = pd.DataFrame({'A':['M',2,3],'B':['M',2,3],'AA':['N',20,30],'BB':['N',20,30]})
>df = df.rename(columns={df.columns[2]: 'A'})
>df = df.rename(columns={df.columns[3]: 'B'})
>df

  A  B  A  B
0 M  M  N  N
1 2  2  20 20
2 3  3  30 30

and I have to split the data frame vertically by row index 0 = 'M' and 'N':

  A  B
0 M  M
1 2  2
2 3  3

  A  B
0 N  N
1 20 20
2 30 30

The data in the data frame comes from an Excel sheet and the column names are not unique. Thanks for help!

pandas dataframe python
3 Answers

This should get the job done:

df.loc[:,df.iloc[0, :] == "M"]
df.loc[:,df.iloc[0, :] == "N"]
user_45519840

Use pandas iloc for selecting columns:

=^..^=

import pandas as pd

df = pd.DataFrame({'A':['M',2,3],'B':['M',2,3],'AA':['N',20,30],'BB':['N',20,30]})
df = df.rename(columns={df.columns[2]: 'A'})
df = df.rename(columns={df.columns[3]: 'B'})

df1 = df.iloc[:, :2]
df2 = df.iloc[:, 2:]

Output:

   A  B
0  M  M
1  2  2
2  3  3
    A   B
0   N   N
1  20  20
2  30  30
user_115579740

Use list comprehension with loc as:

dfs = [df.loc[:, df.loc[0,:].eq(s)] for s in ['M','N']]

This gives seperate dataframes in list.

user_83208610
Related