Pandas : Making Decision on groupby size()
<p>I am trying to do a 'Change Data Capture' using two spreadsheet.
I have grouped my resulting dataframe and stuck with a strange problem.
Requirement:</p>

<p>Case 1) size of a group == 2, do certain tasks</p>

<p>Case 2) size of a group == 1 , do certain tasks</p>

<p>Case 3) size_of_a_group > 2, do certain tasks</p>

<p>Problem is no matter how I try I can not break down result of groupby as per its size and then loop through it</p>

<p>I would like to do something like :</p>

<pre><code>if(group_by_1.filter(lambda x : len(x) ==2):
         for grp,rows in sub(??)group:
             for j in range(len(rows)-1):
                  #check rows[j,'column1'] != rows[j+1,'column1']:
                            do something
</code></pre>

<p>here is my code snippet. Any help is much appreciated.</p>

<pre><code>import pandas as pd
import numpy as np

pd.set_option('display.height', 1000)
pd.set_option('display.max_rows', 500)
pd.set_option('display.max_columns', 500)
pd.set_option('display.width', 1000)
print("reading wolverine xlxs")


# defining metadata

df_header = ['DisplayName','StoreLanguage','Territory','WorkType','EntryType','TitleInternalAlias',
         'TitleDisplayUnlimited','LocalizationType','LicenseType','LicenseRightsDescription',
         'FormatProfile','Start','End','PriceType','PriceValue','SRP','Description',
         'OtherTerms','OtherInstructions','ContentID','ProductID','EncodeID','AvailID',
         'Metadata', 'AltID', 'SuppressionLiftDate','SpecialPreOrderFulfillDate','ReleaseYear','ReleaseHistoryOriginal','ReleaseHistoryPhysicalHV',
          'ExceptionFlag','RatingSystem','RatingValue','RatingReason','RentalDuration','WatchDuration','CaptionIncluded','CaptionExemption','Any','ContractID',
          'ServiceProvider','TotalRunTime','HoldbackLanguage','HoldbackExclusionLanguage']
df_w01 = pd.read_excel("wolverine_1.xlsx", names = df_header)

df_w02 = pd.read_excel("wolverine_2.xlsx", names = df_header)





df_w01['version'] = 'OLD'
df_w02['version'] = 'NEW'

#print(df_w01)
df_m_d = pd.concat([df_w01, df_w02], ignore_index = True).reset_index()

#print(df_m_d)

first_pass_get_duplicates = df_m_d[df_m_d.duplicated(['StoreLanguage','Territory','TitleInternalAlias','LocalizationType','LicenseType',
                                   'LicenseRightsDescription','FormatProfile','Start','End','PriceType','PriceValue','ContentID','ProductID',
                                   'AltID','ReleaseHistoryPhysicalHV','RatingSystem','RatingValue','CaptionIncluded'], keep='first')] # This datframe has records which are DUPES on NEW and OLD
#print(first_pass_get_duplicates) 

first_pass_drop_duplicate = df_m_d.drop_duplicates(['StoreLanguage','Territory','TitleInternalAlias','LocalizationType','LicenseType',
                                   'LicenseRightsDescription','FormatProfile','Start','End','PriceType','PriceValue','ContentID','ProductID',
                                   'AltID','ReleaseHistoryPhysicalHV','RatingSystem','RatingValue','CaptionIncluded'], keep=False) # This datframe has records which are unique on desired values evn for first time

#print(first_pass_drop_duplicate)


group_by_1 = first_pass_drop_duplicate.groupby(['StoreLanguage','Territory','TitleInternalAlias','LocalizationType','LicenseType','FormatProfile'],as_index=False)
#Best Case group_by has 2 elements on big key and at least one row is 'new'
#print(group_by_1.grouper.group_info[0])
  #for i,rows in group_by_1:

  #if(.transform(lambda x : len(x)==2)):
      #print(group_by_1.grouper.group_info[0])

#print(group_by_1.describe())

'''for i,rows in group_by_1:
     temp_rows = rows.reset_index()
     temp_rows.reindex(index=range(0,len(rows)))
     print("group has: ", len(temp_rows))
     for j in range(len(rows)-1):
     print(j)
     print("this iteration: ", temp_rows.loc[j,'Start'])
     print("next iteration: ", temp_rows.loc[j+1,'Start'])
     if(temp_rows.loc[j+1,'Start'] == temp_rows.loc[j,'Start']):
        print("Match")
    else:
        print("no_match")
        print(temp_rows.loc[j,'Start'])
    print("++++-----++++")'''
</code></pre>

<p>Any Help is much appreciated.</p>

<p>Use <code>groupby</code> with a <code>transformation</code> of <code>df</code> with <code>np.size</code>  </p>

<p>Consider the dataframe <code>df</code></p>

<pre><code>df = pd.DataFrame([
        [1, 2, 3],
        [1, 2, 3],
        [2, 3, 4],
        [2, 3, 4],
        [2, 3, 4],
        [3, 4, 5],
    ], columns=list('abc'))
</code></pre>

<p>and the function <code>my_function</code></p>

<pre><code>def my_function(df):
    if df.name == 1:
        return 'blue'
    elif df.name == 2:
        return 'red'
    else:
        return 'green'
</code></pre>

<p>The thing to group by is <code>grouper</code></p>

<pre><code>grouper = df.groupby('a').b.transform(np.size)
grouper

0    2
1    2
2    3
3    3
4    3
5    1
Name: b, dtype: int64
</code></pre>

<hr>

<pre><code>df.groupby(grouper).apply(my_function)

b
1     blue
2      red
3    green
dtype: object
</code></pre>

<hr>

<p>You should be able to piece this together to get what you want.</p>

python pandas