python - How can i find the count of freuency of repeated combination in DataFrame

Question

I have a this data set as sample:

df = pd.DataFrame({'CL1':['A B C','C A N']},
                columns=['CL1','CL2','CL3','CL4']) 
  
     CL1  CL2  CL3  CL4  
0  A B C  NaN  NaN  NaN  
1  C A N  NaN  NaN  NaN  
 
         

My Goal:Finding of most repetition of words combination in data frame with following steps.

• 1. Make a separation of each value with (,) as separator and add in column CL2:

     CL1     CL2     CL3  CL4 
0  'A B C'  'A,B,C'  NaN  NaN 
1  'C A N'  'C,A,N'  NaN  NaN 

• 2. Separation of value in columns CL2 in column CL3:

     CL1     CL2     CL3          CL4 
0  'A B C'  'A,B,C'  'A','B','C'  NaN 
1  'C A N'  'C,A,N'  'C','A','N'  NaN 

     

• 3. Union (set theory from statistic) of column CL4

     CL1     CL2     CL3          CL4 
0  'A B C'  'A,B,C'  'A','B','C'  [ [A],[B],[C],[A,B],[A,C],[B,C],[A,B,C] ] 
1  'C A N'  'C,A,N'  'C','A','N'  [ [C],[A],[N],[A,C],[C,N],[A,N],[C,A,N] ] 
       

• 4. Finding of the repetition of each value of column CL4 in new column CL5 in new data frame and add to Count:

     CL5      Count   
0    [A]       2
1    [B]       1
2    [C]       2
3    [D]       1
4    [N]       1
5    [A,B]     1
etc..

question from:https://stackoverflow.com/questions/66060234/how-can-i-find-the-count-of-freuency-of-repeated-combination-in-dataframe

Answer 1

You can use split by values by spacem then call custom function for all combinations and for counts use Series.explode with Series.value_counts:

df = pd.DataFrame({'CL1':['A B C','C A N','D E F','F X G']},
                         columns=['CL1','CL2','CL3','CL4']) 


#https://stackoverflow.com/a/5898031/2901002
from itertools import chain, combinations
def all_subsets(ss):
    return chain(*map(lambda x: combinations(ss, x), range(1, len(ss)+1)))    

df = (df['CL1'].apply(lambda x: list(all_subsets(x.split())))
               .explode()
               .value_counts()
               .rename_axis('CL5')
               .reset_index(name='count'))
print (df.head(10))
         CL5  count
0       (C,)      2
1       (F,)      2
2       (A,)      2
3     (E, F)      1
4     (F, G)      1
5     (A, B)      1
6     (C, A)      1
7     (A, C)      1
8  (F, X, G)      1
9       (D,)      1

---

df['CL5'] = df['CL5'].apply(list)
print (df.head(10))
         CL5  count
0        [C]      2
1        [F]      2
2        [A]      2
3     [E, F]      1
4     [F, G]      1
5     [A, B]      1
6     [C, A]      1
7     [A, C]      1
8  [F, X, G]      1
9        [D]      1