pandas: best way to select all columns whose names start with X

I have a DataFrame:

    import pandas as pd
    import numpy as np

    df = pd.DataFrame({'foo.aa': [1, 2.1, np.nan, 4.7, 5.6, 6.8],
                       'foo.fighters': [0, 1, np.nan, 0, 0, 0],
                       'foo.bars': [0, 0, 0, 0, 0, 1],
                       'bar.baz': [5, 5, 6, 5, 5.6, 6.8],
                       'foo.fox': [2, 4, 1, 0, 0, 5],
                       'nas.foo': ['NA', 0, 1, 0, 0, 0],
                       'foo.manchu': ['NA', 0, 0, 0, 0, 0],})

I want to select values of 1 in columns starting with foo.. Is there a better way to do it other than:

    df2 = df[(df['foo.aa'] == 1)|
    (df['foo.fighters'] == 1)|
    (df['foo.bars'] == 1)|
    (df['foo.fox'] == 1)|
    (df['foo.manchu'] == 1)
    ]

Something similar to writing something like:

    df2= df[df.STARTS_WITH_FOO == 1]

The answer should print out a DataFrame like this:

       bar.baz  foo.aa  foo.bars  foo.fighters  foo.fox foo.manchu nas.foo
    0      5.0     1.0         0             0        2         NA      NA
    1      5.0     2.1         0             1        4          0       0
    2      6.0     NaN         0           NaN        1          0       1
    5      6.8     6.8         1             0        5          0       0

    [4 rows x 7 columns]

Just perform a list comprehension to create your columns:

    In [28]:

    filter_col = [col for col in df if col.startswith('foo')]
    filter_col
    Out[28]:
    ['foo.aa', 'foo.bars', 'foo.fighters', 'foo.fox', 'foo.manchu']
    In [29]:

    df[filter_col]
    Out[29]:
       foo.aa  foo.bars  foo.fighters  foo.fox foo.manchu
    0     1.0         0             0        2         NA
    1     2.1         0             1        4          0
    2     NaN         0           NaN        1          0
    3     4.7         0             0        0          0
    4     5.6         0             0        0          0
    5     6.8         1             0        5          0

Another method is to create a series from the columns and use the vectorised str method startswith:

    In [33]:

    df[df.columns[pd.Series(df.columns).str.startswith('foo')]]
    Out[33]:
       foo.aa  foo.bars  foo.fighters  foo.fox foo.manchu
    0     1.0         0             0        2         NA
    1     2.1         0             1        4          0
    2     NaN         0           NaN        1          0
    3     4.7         0             0        0          0
    4     5.6         0             0        0          0
    5     6.8         1             0        5          0

In order to achieve what you want you need to add the following to filter the values that don't meet your ==1 criteria:

    In [36]:

    df[df[df.columns[pd.Series(df.columns).str.startswith('foo')]]==1]
    Out[36]:
       bar.baz  foo.aa  foo.bars  foo.fighters  foo.fox foo.manchu nas.foo
    0      NaN       1       NaN           NaN      NaN        NaN     NaN
    1      NaN     NaN       NaN             1      NaN        NaN     NaN
    2      NaN     NaN       NaN           NaN        1        NaN     NaN
    3      NaN     NaN       NaN           NaN      NaN        NaN     NaN
    4      NaN     NaN       NaN           NaN      NaN        NaN     NaN
    5      NaN     NaN         1           NaN      NaN        NaN     NaN

EDIT

OK after seeing what you want the convoluted answer is this:

    In [72]:

    df.loc[df[df[df.columns[pd.Series(df.columns).str.startswith('foo')]] == 1].dropna(how='all', axis=0).index]
    Out[72]:
       bar.baz  foo.aa  foo.bars  foo.fighters  foo.fox foo.manchu nas.foo
    0      5.0     1.0         0             0        2         NA      NA
    1      5.0     2.1         0             1        4          0       0
    2      6.0     NaN         0           NaN        1          0       1
    5      6.8     6.8         1             0        5          0       0

From: stackoverflow.com/q/27275236