How to count the frequency of elements for unique combinations of columns and store in another column in Dataframe?

Question

I have a pandas dataframe containing 3 columns id1, id2, call_freq where the data is of below form

Input:

id1 id2 call_frequency
1   1   2
1   1   3
1   1   3
1   1   3
1   1   3
1   2   5
1   2   5
1   2   4
2   1   9
2   2   6
2   2   6
2   2   7
2   2   7
2   2   7
2   2   7

Output:

call_frequency_dict column should contain a dictionary string with elements in call_frequency for the combination of id1 and id2 and their frequencies as values.

I tried searching but found nothing helpful. How can I get the call_frequency_dict?

Answer 1

I will recommend using value_counts rather than push those type of data into a dict

df.groupby(['id1','id2']).call_frequency.value_counts()

To match your output

import collections 
df.groupby(['id1','id2']).call_frequency.agg(collections.Counter).reset_index()
Out[55]: 
   id1  id2 call_frequency
0    1    1   {2: 1, 3: 4}
1    1    2   {5: 2, 4: 1}
2    2    1         {9: 1}
3    2    2   {6: 2, 7: 4}