2
$\begingroup$

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!

Improve this question
$\endgroup$
1
  • 1
    $\begingroup$ 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? $\endgroup$
    – Anandologist
    Mar 10, 2020 at 17:15

1 Answer 1

Reset to default
1
$\begingroup$

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.

Improve this answer
$\endgroup$
1
  • $\begingroup$ 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 $\endgroup$
    – omdurg
    Mar 9, 2020 at 11:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.