Filter pandas (python) dataframe based on partial strings in a list

Question

I have a pandas data frame with 99 columns of dx1-dx99 & 99 columns of px1-px99. The contents of these columns are codes of varying length of 4 to 8 characters & digits.

I want to filter only those contents, from these columns, where first three characters of these contents match the three characters in the supplied list. Supplied list has strings that have only three characters.

The length of supplied list I generated dynamically and very length. Therefore I have to pass this whole list not as a separate string.

For example, I have this data frame:

df = pd.DataFrame({'A': 'foo bar one123 bar foo one324 foo 0'.split(),
                   'B': 'one546 one765 twosde three twowef two234 onedfr three'.split(),
                   'C': np.arange(8), 'D': np.arange(8) * 2})
    print(df)

        A       B  C   D
0     foo  one546  0   0
1       0  one765  1   2
2  one123  twosde  2   4
3     bar   three  3   6
4     foo  twowef  4   8
5  one324  two234  5  10
6     foo  onedfr  6  12
7       0   three  7  14

The filled cells are in object type and all zeros were originally NULL which I have filled with zeros by pd.fillna(0).

When I do this:

keep = df.iloc[:,:].isin(['one123','one324','twosde','two234']).values
df.iloc[:,:] = df.iloc[:,:].where(keep, 0)
print(df)

I got this:

        A       B  C  D
0       0       0  0  0
1       0       0  0  0
2  one123  twosde  0  0
3       0       0  0  0
4       0       0  0  0
5  one324  two234  0  0
6       0       0  0  0
7       0       0  0  0

But instead of passing individual strings 'one123','one324','twosde','two234', I want to pass a list containing partial strings like this one:

startstrings = ['one', 'two']

keep = df.iloc[:,:].contains(startstrings)
df.iloc[:,:] = df.iloc[:,:].where(keep, 0)
print(df)

But above will not work. I want to keep all contents which start with 'one' or 'two'.

Any idea how to implement? My data set is huge and hence efficiency matters.

Answer 1

The pandas str.contains accepts regular expressions, which let's you test for any item in a list. Loop through each column and use str.contains:

startstrings = ['one', 'two']
pattern = '|'.join(startstrings)

for col in df:
    if all(df[col].apply(type) == str):
        #Set any values to 0 if they don't contain value
        df.ix[~df[col].str.contains(pattern), col] = 0        
    else:
        #Column is not all strings
        df[col] = 0

Produces:

      A     B  C  D
0     0  one1  0  0
1     0  one1  0  0
2  one1  two1  0  0
3     0     0  0  0
4     0  two1  0  0
5  one1  two1  0  0
6     0  one1  0  0
7     0     0  0  0