# Python dataframe iterate over rows

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.

• The criteria (or example) you've provided maybe incomplete. For example, you say you want the maximum number of creatures for each Col2, Col3 combination. However, you have 2 output rows for (Z st01 - rows 3 and 6) and (Z st02 - rows 1 and 5). Is there another rule to consider to include 2 rows per group sometimes? – Anandologist Mar 10 '20 at 17:15

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


• Yes @FreedomToWin, I am trying to reduce the records in data, based on Col1, Col2 and Col3. That is, we need to take Col2, Col3 also into consideration. Regarding duplicate values, there wouldn't be any duplicates as my data has few more columns with unique values for each row, which I haven't added here because those columns do not influence the Desired output – omdurg Mar 9 '20 at 11:58