Filter a Pandas DataFrame on a text column using a concatenation of other column values

user_15118051 0
Viewed 53

I have a big dataframe with columns like this:

data = [
        [0,'His score is 15/30',15,30],
        [1, 'She scored 34/50 in math', np.NaN, np.NaN],
        [2,'avg score is 32.5/50 for english',32,50],
        [3,'He scored last @ 15/50',25,50]
        ]

df = pd.DataFrame(data, columns=['id','text','num','denom'])

|   id | text                             |   num |   denom |
|-----:|:---------------------------------|------:|--------:|
|    0 | His score is 15/30               |    15 |      30 |
|    1 | She scored 34/50 in math         |   nan |     nan |
|    2 | avg score is 32.5/50 for english |    32 |      50 |
|    3 | He scored last @ 15/50           |    25 |      50 |

I want to select all rows where df.num + '/' + df.denom in df.text

I tried the following code but it returns an empty DataFrame:

df.loc[df.apply(lambda row: (str(row.num)+'/' + str(row.denom)) in row.text, axis=1) ]
pandas dataframe filter python
3 Answers

Does something like this work?

You’re trying to match a float converted to a string, with an int as text in the string.

This wont work well if you’re trying to also match floats.



df = df.fillna(0)
df= df.loc[df.apply(lambda x: (str(int(x.num))+ '/' + str(int(x.denom))) in str(x.text), axis=1)]
user_148986970

The problem is that, because of NaNs, the last two columns become float, so your numbers become floats.

This should work:

>>> df[['num', 'denom']] = df[['num', 'denom']].fillna(0).astype(int)
>>> df.loc[df.apply(lambda row: (str(row.num) + '/' + str(row.denom)) in row.text, axis=1)]
   id                text  num  denom
0   0  His score is 15/30   15     30
user_116470250 
df[df.apply(lambda row: (str(int(row.num))+'/' + str(int(row.denom))) in row.text if not np.isnan(row.num) else False, axis=1) ]

Actually you are right. The only thing you could do is to cast row.num and row.demon to int. This is because pandas interpret values as float, thus producing a False condition (str(row.num) for the first row returns 15.0, not 15). Look also at nan values (it cannot be casted to int).

user_135366730
Related