Python: Can you check how many times a unique combination of two column values appears in another dataframe?

user_11312041 0
Viewed 106

I am trying to see how many times a unique combination of two column values appears in another dataframe and add it as a new column with one line. I have a reference table looking at unique combinations of the ID and Desc fields. I also have a table that has all active occurrences of those combinations

     ref_table                               active_data
   ID      Desc                         ID         Desc
0   1     Windows                    0   1        Windows
1   1     Linux                      1   1        Windows
2   2     Linux                      2   1        Linux
3   3     Network                    3   2        Linux
4   4     Automation                 4   3        Network
                                     5   3        Network
                                     6   3        Network
                                     7   4        Automation

I'd like to add to the ref_table the count of the unique combinations of the ID and Desc fields that appears in active_data like so:

         ref_table                              
   ID      Desc        Count                  
0   1     Windows        2   
1   1     Linux          1              
2   2     Linux          1            
3   3     Network        3          
4   4     Automation     1

I recognize this can be accomplished by performing pd.merge or join. However, if possible, I would like to do it with one line, and if I was just concerned with the count of one column like ID, I know it can be done with:

ref_table['Count'] = ref_table['ID'].map(active_data['ID'].value_counts()).

Trying to extend this to look at both the ID AND Desc columns using:

ref_table['Count'] = ref_table[['ID', 'Desc']].apply(active_data[['ID', 'Desc']].value_counts()) produces an error, KeyError: "None of [Index([3, 'Network'], dtype='object')] are in the [index]". Ideally I would like to use the value_counts solution, but cannot figure it out with two columns.

pandas apply dataframe merge python
1 Answers

You can do a merge on groupby:

ref_table.merge(active_data.groupby(['ID','Desc'], as_index=False)['ID'].count(),
                on=['ID','Desc'], how='left')

Or you can merge, then groupby:

(ref_table.merge(active_data, on=['ID','Desc'], how='left')
     .groupby(['ID','Desc'])['ID'].count()
     .reset_index('Count')
)
user_42384080
Related