Subset of dataframe where two columns equals two lists

user_1045755 0
Viewed 485

I have multiple data frames with roughly 5 million rows in each. Each data frame has two columns called B and C. Additionally I also have two lists of values where index 0 from the first list corresponds to index 0 in the second list.

What I need to do is get a subset of the full data frame, but where the values in both of the lists are tru for that specific index.

So basically:

df = 

A      B      C
------------------
val1   val2   val3
val4   val5   val6
val7   val8   val9


list1 = ["val2", "val8"]

list2 = ["val3", "val9"]

Then if I use this:

df.loc[(df['B'] == "val2") & (df['C'] == "val3")]

Then this will return:

df_new = 

A      B      C
------------------
val1   val2   val3

But I need it to do it for all the list items in list1 and list2, so the resulting df should be:

df_new = 

A      B      C
------------------
val1   val2   val3 
val7   val8   val9

I was thinking about just creating a new column (new_column) and new list (new_list) where the two list names where joined, and then just run:

df[df['new_column'].isin(new_list)]

But I kind of worry that that is inefficient when taking into account I have to do this for roughly 5 million rows and a lot of different data frames.

So yeah, is there some neat trick to this ?

pandas python
2 Answers

You can create a dataframe with the lists (list1 and list2) and merge the dataframe:

u = pd.DataFrame({"B":list1,"C":list2})
df.merge(u)
#[df.merge(u) for df in df_list] for list

      A     B     C
0  val1  val2  val3
1  val7  val8  val9
user_98406370

The way to use isin , you need pass a tuple , in case the row match the production of two list

df[df[['B','C']].agg(tuple,1).isin(tuple(zip(list1,list2)))]
      A     B     C
0  val1  val2  val3
2  val7  val8  val9
user_79645270
Related