Python dataframe iterate over rows

Question

Dataset

**Col1**                                 **Col2**      **Col3**        
dog                                        Z             st02          
dog,cat                                    Z             st02          
dog,bat,cat                                Z             st02          
bat,cat,elephant                           Y             st02          
dog,bat,cat,elephant                       Y             st02          
tiger                                      Z             st01          
pigeon                                     Z             st01          
pigeon,parrot                              Z             st01          
dove,parrot                                Z             st01          
pigeon,parrot                              Z             st01          
pigeon,parrot,dove                         Z             st01          
lion,leopard,cheetah                       Z             st01          
tiger,lion,leopard,cheetah                 Z             st01          
dog,tiger,cheetah                          Y             st01          
dog,tiger,leopard,cheetah                  Y             st01          
eagle,jaguar,Kangaroo,zebra                Z             st02          
cheetah,eagle,jaguar,Kangaroo,zebra        Z             st02          

The expected output is:

**Col1**                                 **Col2**       **Col3**      
dog,bat,cat                                Z              st02          
dog,bat,cat,elephant                       Y              st02          
tiger,lion,leopard,cheetah                 Z              st01          
dog,tiger,leopard,cheetah                  Y              st01          
cheetah,eagle,jaguar,Kangaroo,zebra        Z              st02          
pigeon,parrot,dove                         Z              st01          

In order to extract the above rows as output, I tried tracing the patterns and using the below logic:

data = pd.read_excel("data.xlsx")
data['Col4'] = data['Col1'].str.count(',')
v1 = []
v2 = []
v1.append(0)
v2.append(0)
for i in range(0,data.shape[0]-1):
    x = data['Col2'][i]
    y = data['Col2'][i+1]
    t1 = data['Col3'][i]
    t2 = data['Col3'][i+1]
    g1 = (x == y) & (t1==t2)
    d1 = data['Col1'][i]
    d2 = data['Col1'][i+1]
    c1 = data['Col4'][i]
    c2 = data['Col4'][i+1]
    flag = 0
    if(all(x in d2 for x in d1)):
      flag = 1
    g2 = (flag == 1)&(c2>c1)
    v1.append(g1)
    v2.append(g2)
    data['new_cond1'] = v1   
    data['new_cond2'] = v2   
    data['Final_flag'] = (data['new_cond1']==True)&(data['new_cond2']==True) 
    data_output = data[data['Final_flag']==True]  

But I didn't end up getting the expected output, rather few additional rows are also present in output. Could someone please help me extracting the rows mentioned in expected output.

---

From the dataset, I am trying to extract 1) Rows which has maximum number of animals separated by commas (or consider birds wherever pigeon/parrot/dove is mentioned). 2) Need not be the case that there should be only one maximum number of animals per Col2 or Col3, there might be even more than one Example as in case of row no. 1 and row no. 5 with same value in Col 2 and Col 3. This is because category of animals is different in row no.1 and row no. 5. Hope it's clear.

Thanks in advance!

Answer 1

It seems like you want to reduce the records in the data if Col1 is a subset of another record in Col1. First you can sort the column alphabetically, and then use the function below.

import pandas as pd
df = pd.DataFrame()

df['Col1']  = ['X','X,Y','Z','Z,W']

def test(x):
    return df.Col1.apply(lambda y: y in x).sum()>1

mask = df.Col1.apply(lambda x: test(x))

df[mask]

Apply is the way to iterate through records in pandas. You can nest apply functions to efficiently solve your task.

Note: if there are duplicates in your dataset, it will also mask those. You will need to deduplicate the dataset first with df.drop_duplcates(subset='Col1')

OR

if you want to keep the variability in the other two columns, then you can pass use the apply function of the whole df, with axis=0. See the docs.