Is it possible to search multiple columns for multiple values in pandas?

Question

I am a doctor looking at surgical activity in a DataFrame that has 450,000 records and 129 fields or columns. Each row describes a patient admission to hospital, and the columns describe the operation codes and diagnosis codes for the patient.

df.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 452883 entries, 0 to 452882
Columns: 129 entries, fyear to opertn_24Type
dtypes: float64(5), int64(14), object(110)
memory usage: 445.7+ MB

There are 24 operation columns for each row. I want to search in the operation columns (1-24) for the codes for pituitary surgery "B041" and "B012", to identify all patients who have had surgery for a pituitary tumor.

I am a total python beginner and have tried using iloc to describe the range of columns (1-24) which appear starting at position 72 in the list of columns but couldn't get it to work. I am quite happy searching for individual values eg "B041" in a single column using df["OPERTN_01"] == "B041" but would ideally like to search multiple columns (all the surgical columns 1-24) more efficiently. I tried searching the whole dataframe using

test = df[df.isin(["B041", "B012"])]

but that just returns the entire dataframe with null values.

So I have a few questions.

1. How do I identify integer positions (iloc numbers) for columns in a large dataframe of 129 columns? I just listed them and counted them to get the first surgical column ("OPERTN_01") at position 72 — there must be an easier way.
2. What's the best way to slice a dataframe to select records with multiple values from multiple columns?

Answer 1

Let's use .iloc and create a boolean for you to filter by:

import pandas as pd
import numpy as np
np.random.seed(12)
df = pd.DataFrame({"A" : ["John","Deep","Julia","Kate","Sandy"], 
                     "result_1" : np.random.randint(5,15,5),
                    "result_2" : np.random.randint(5,15,5) })
print(df)

       A  result_1  result_2
0   John        11         5
1   Deep         6        11
2  Julia         7         6
3   Kate         8         9
4  Sandy         8        10

Next we need to find your intended values in the selected columns:

df.iloc[:,1:27].isin([11,10])

this returns:

   result_1  result_2
0      True     False
1     False      True
2     False     False
3     False     False
4     False      True

From the above, we need to slice our original dataframe by the rows where any value is true (if I've understood you correctly).

For this we can use np.where() with .loc:

df.loc[np.where(df.iloc[:,1:].isin([11,10])==True)[0]]

       A  result_1  result_2
0   John        11         5
1   Deep         6        11
4  Sandy         8        10

From here it's a simple task to extract your unique IDs.

Answer 2

Answer 1:

Let's say you are looking for eg_col in your dataframe's columns. Then, you can find its index within the columns using:

df.columns.tolist().index('eg_col')

Answer 2:

In your example, if you know the name of the last surgical column (let's say it's called OPERTN_24, you can slice those columns using:

df_op = df.loc[:, 'OPERTN_01':'OPERTN_24']

Continuing from that, we can look for values of 'B041', 'B012' in df_surg as you tried: df_op.isin['B041', 'B012'] which will return the boolean value for all dataframe entries.

To extract, for example, only those rows where at least one of our 'B041' values comes up, we select those rows with:

df.index[df_surg.isin(['B041', 'B012']).any(axis=1)]