Pandas: Select columns based on row values

Question

I have a very large pandas.Dataframe and want to create a new Dataframe by selecting all columns where one row has a specific value.

        A     B     C     D    E 
Region  Nord  Süd   West  Nord Nord
value   2.3   1.2   4.2   0.5  1.3
value2  20    400   30    123  200

Now i want to create a new DataFrame with all columns where the row "Region" has the value "Nord".

How can it be done? The result should look like this:

        A     D    E
Region  Nord  Nord Nord
value   2.3   0.5  1.3
value2  20    123  200

Thanks in advance

jezrael · Accepted Answer · 2021-11-10 07:26:07Z

7

Use first DataFrame.loc for select all rows (:) by mask compred selected row Region by another loc:

df = df.loc[:, df.loc['Region'] == 'Nord']
print (df)
           A     D     E
Region  Nord  Nord  Nord
value    2.3   0.5   1.3
value2    20   123   200

Better is crated MultiIndex by first row with original columns, then is possible select by DataFrame.xs:

df.columns = [df.columns, df.iloc[0]]
df = df.iloc[1:].rename_axis((None, None), axis=1)
print (df)
          A    B    C    D    E
       Nord  Süd West Nord Nord
value   2.3  1.2  4.2  0.5  1.3
value2   20  400   30  123  200

print (df.xs('Nord', axis=1, level=1))
          A    D    E
value   2.3  0.5  1.3
value2   20  123  200


print (df.xs('Nord', axis=1, level=1, drop_level=False))
          A    D    E
       Nord Nord Nord
value   2.3  0.5  1.3
value2   20  123  200

answered Nov 10, 2021 at 7:26

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mayank Porwal · Accepted Answer · 2021-11-10 07:31:06Z

2

You can also use df.T with Series.eq:

In [312]: df.T[df.T.Region.eq('Nord')].T
Out[312]: 
           A     D     E
Region  Nord  Nord  Nord
value    2.3   0.5   1.3
value2    20   123   200

answered Nov 10, 2021 at 7:31

Mayank Porwal

34.2k9 gold badges45 silver badges65 bronze badges

Collectives™ on Stack Overflow

Pandas: Select columns based on row values

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related