Using itertools, melt and groupby correctly to count pairs of event per attribute value using Pandas

user_11305480 0
Viewed 108

I have a table on the following format

  Id   |   Sequence   |   Attribute A  |  Attribute B |
  ID1       [A,B,C,D]         A1              B1        
  ID2       [A,B,F,G]         A2              B3            
  ID3       [A,B,C,D]         A1              B1

I want to calculate, for each event combination and attribute value, count the number of unique ID's.

The final table should look like

  Pair    |  Attribute Type | Attribute Value   | ID Count
  (A,B)        Attribute A          A1              2        #Event A happens before event B in 2 unique ID's where A1 is the value of Attribute A.
  (A,C)        Attribute A          A1              2
  (A,D)        Attribute A          A1              2
  (B,C)        Attribute A          A1              2
  (B,D)        Attribute A          A1              2
  (C,D)        Attribute A          A1              2
  (A,B)        Attribute A          A2              1
  (A,F)        Attribute A          A2              1 
  (A,G)        Attribute A          A2              1 
  (B,F)        Attribute A          A2              1
  (B,G)        Attribute A          A2              1
  (F,G)        Attribute A          A2              1
  (A,B)        Attribute B          B1              2
  (A,C)        Attribute B          B1              2
  (A,D)        Attribute B          B1              2
  (B,C)        Attribute B          B1              2
  (B,D)        Attribute B          B1              2
  (C,D)        Attribute B          B1              2
  (A,B)        Attribute B          B3              1
  (A,F)        Attribute B          B3              1 
  (A,G)        Attribute B          B3              1 
  (B,F)        Attribute B          B3              1
  (B,G)        Attribute B          B3              1
  (F,G)        Attribute B          B3              1

What would be the correct way of doing this? In reality I will have more than just 2 Attributes.

This is how far I have come

 df['Sequence Combs'] = df['Sequence'].apply(lambda x: list(itertools.combinations(x,2)))
 

  Id   |   Sequence   |          Event Combs                   |   Attribute A  |  Attribute B |
  ID1       [A,B,C,D]   [(A,B),(A,C),(A,D),(B,C),(B,D),(C,D)]           A1              B1        
  ID2       [A,B,F,G]   [(A,B),(A,F),(A,G),(B,F),(B,G),(F,G)]           A2              B3              
  ID3       [A,B,C,D]   [(A,B),(A,C),(A,D),(B,C),(B,D),(C,D)]           A1              B1

And after doing explode

df = df.explode('Sequence Combs')

I get the following

  Id   |   Sequence   |  Event Combs |  Attribute A  |  Attribute B |
  ID1       [A,B,C,D]       (A,B)           A1              B1        
  ID1       [A,B,C,D]       (A,C)           A1              B1        
  ID1       [A,B,C,D]       (A,D)           A1              B1        
  ID1       [A,B,C,D]       (B,C)           A1              B1        
  ID1       [A,B,C,D]       (B,D)           A1              B1        
  ID1       [A,B,C,D]       (C,D)           A1              B1        
  ...          ...           ..             ..              ..

But I am unsure on how to proceed from here, any ideas?

pandas python
2 Answers

You could do:

from itertools import combinations

# create function for creating a list the 2-combinations
combs = lambda x: list(combinations(x, r=2))

# create new DataFrame with now the Sequence column is the list of the 2-combinations
res = df.assign(seq=df['Sequence'].apply(combs)).drop('Sequence', axis=1).rename(columns={'seq' : 'Sequence'})

# explode, then melt
res = res.explode('Sequence').melt(id_vars=['Id', 'Sequence'], var_name='Attribute Type', value_name='Attribute Value')

# finally group by all the columns but Id, and count
res = res.groupby(['Sequence', 'Attribute Type', 'Attribute Value'])['Id'].count()

print(res)

Output

Sequence  Attribute Type  Attribute Value
(A, B)    Attribute A     A1                 2
                          A2                 1
          Attribute B     B1                 2
                          B3                 1
(A, C)    Attribute A     A1                 2
          Attribute B     B1                 2
(A, D)    Attribute A     A1                 2
          Attribute B     B1                 2
(A, F)    Attribute A     A2                 1
          Attribute B     B3                 1
(A, G)    Attribute A     A2                 1
          Attribute B     B3                 1
(B, C)    Attribute A     A1                 2
          Attribute B     B1                 2
(B, D)    Attribute A     A1                 2
          Attribute B     B1                 2
(B, F)    Attribute A     A2                 1
          Attribute B     B3                 1
(B, G)    Attribute A     A2                 1
          Attribute B     B3                 1
(C, D)    Attribute A     A1                 2
          Attribute B     B1                 2
(F, G)    Attribute A     A2                 1
          Attribute B     B3                 1
Name: Id, dtype: int64

If you want to truly match the expected output, do:

# finally group by all the columns but Id, and count
res = res.groupby(['Sequence', 'Attribute Type', 'Attribute Value'], as_index=False)['Id'].count().rename({'Id' : 'Id Count'}).sort_values('Attribute Type')

print(res)

Output

   Sequence Attribute Type Attribute Value  Id
0    (A, B)    Attribute A              A1   2
1    (A, B)    Attribute A              A2   1
20   (C, D)    Attribute A              A1   2
4    (A, C)    Attribute A              A1   2
6    (A, D)    Attribute A              A1   2
18   (B, G)    Attribute A              A2   1
8    (A, F)    Attribute A              A2   1
10   (A, G)    Attribute A              A2   1
22   (F, G)    Attribute A              A2   1
12   (B, C)    Attribute A              A1   2
16   (B, F)    Attribute A              A2   1
14   (B, D)    Attribute A              A1   2
21   (C, D)    Attribute B              B1   2
19   (B, G)    Attribute B              B3   1
17   (B, F)    Attribute B              B3   1
11   (A, G)    Attribute B              B3   1
13   (B, C)    Attribute B              B1   2
9    (A, F)    Attribute B              B3   1
7    (A, D)    Attribute B              B1   2
5    (A, C)    Attribute B              B1   2
3    (A, B)    Attribute B              B3   1
2    (A, B)    Attribute B              B1   2
15   (B, D)    Attribute B              B1   2
23   (F, G)    Attribute B              B3   1
user_40015920

Here it goes a solution using pivot_table.

# df after "df.explode('Sequence Combs')"

attributes = ['Attribute A', 'Attribute B']

my_pivots = [df.pivot_table(index='Sequence Combs',
                            columns=attr,
                            aggfunc='count')['Sequence'] for attr in attributes]

final_df = (pd.concat(my_pivots, axis=1).melt(ignore_index=False)
                                        .dropna()
                                        .rename({'variable': 'Attribute Value',
                                                 'value': 'ID Count'},
                                                 axis=1))

This gives us a dataframe like this

               Attribute Value  ID Count
Sequence Combs                          
(A, B)                      A1       2.0
(A, C)                      A1       2.0
(A, D)                      A1       2.0
(B, C)                      A1       2.0
(B, D)                      A1       2.0

Note that it doesn't have the column 'Attribute Type'. To fix this, you can simply run:

final_df['Attribute Type'] = 'Attribute ' + final_df['Attribute Value'].str[0]
user_142988800
Related