Compare the two column in different data frame in pandas

user_8901845 0
Viewed 32

I have two table as shown below

user table:

user_id     courses        attended_modules             
1           [A]            {A:[1,2,3,4,5,6]}            
2           [A,B,C]        {A:[8], B:[5], C:[6]}       
3           [A,B]          {A:[2,3,9], B:[10]}           
4           [A]            {A:[3]}                     
5           [B]            {B:[5]}                     
6           [A]            {A:[3]}                      
7           [B]            {B:[5]}                     
8           [A]            {A:[4]}

Course table:

course_id         modules                         
A                 [1,2,3,4,5,6,8,9]                   
B                 [5,8]                              
C                 [6,10]

From the above compare the attended_module in user table with modules in course table. Create a new column in user table Remaining_module as explained below.

Example: user_id = 1, attended the course A, and attended 6 modules, there are 8 modules in course so Remaining_module = {A:2}

Similarly for user_id = 2, Remaining_module = {A:7, B:1, C:1} And So on...

Expected Output:

user_id     attended_modules                #Remaining_modules
1           {A:[1,2,3,4,5,6]}               {A:2}
2           {A:[8], B:[5], C:[6]}           {A:7, B:1, C:1}
3           {A:[2,3,9], B:[8]}              {A:5, B:1}
4           {A:[3]}                         {A:7}
5           {B:[5]}                         {B:1}
6           {A:[3]}                         {A:7}
7           {B:[5]}                         {B:1}
8           {A:[4]}                         {A:7}
pandas-groupby pandas
1 Answers

Idea is compare matched values of generator and sum True values:

df2 = df2.set_index('course_id')
mo = df2['modules'].to_dict()
#print (mo)

def f(x):
    return {k: sum(i not in v for i in mo[k]) for k, v in x.items()}

df1['Remaining_modules'] = df1['attended_modules'].apply(f)
print (df1)
   user_id  courses                attended_modules         Remaining_modules
0        1      [A]       {'A': [1, 2, 3, 4, 5, 6]}                  {'A': 2}
1        2  [A,B,C]  {'A': [8], 'B': [5], 'C': [6]}  {'A': 7, 'B': 1, 'C': 1}
2        3    [A,B]     {'A': [2, 3, 9], 'B': [10]}          {'A': 5, 'B': 2}
3        4      [A]                      {'A': [3]}                  {'A': 7}
4        5      [B]                      {'B': [5]}                  {'B': 1}
5        6      [A]                      {'A': [3]}                  {'A': 7}
6        7      [B]                      {'B': [5]}                  {'B': 1}
7        8      [A]                      {'A': [4]}                  {'A': 7}
user_29010020
Related