CODEWITHSUNDEEP
pythonpython-3.xpandas
johnnyb
johnnyb

Reputation: 1815

Merging two dataframes with a list inside one of the columns

I am trying to merge multiple dataframes to find users that are in groups they arent supposed to be.

DF1

                  AccountType           Name    SID  disabled     compname       localaccount lockout passwordchangeable passwprdexpires passwordrequired    status
                          512  Administrator   1001     True   mycoolguycompname         True    True               True            NONE             True  Degraded
                          512        coolguy   500     False   mycoolguycompname         True   False               True            NONE             True        OK
                          512        Visitor   501      True   mycoolguycompname         True   False              False            NONE             True  Degraded

DF2

                      groupname  localaccount        GroupSID  sidtype   status                                                                           members
                 Administrators        True    S-1-5-32-544        4       OK     [coolguy, cool_Admins, Inspector_Cool, cool_2, Administrator, Backup Operators]
               Backup Operators        True    S-1-5-32-551        4       OK                                                                   [coolguy, badguy]
Certificate Service DCOM Access        True    S-1-5-32-574        4       OK                                                                            [cool_2]
        Cryptographic Operators        True    S-1-5-32-569        4       OK                                                           [Administrators, Visitor]

How can i merge the two dataframes to show the user names properly correlated to the appropriate groups that they belong like below:

Expected Output:

           usernames                                groups       GroupSID    status           compname
           coolguy                          Administrators   S-1-5-32-544        OK  mycoolguycompname              
           cool_Admins                      Administrators   S-1-5-32-544        OK  mycoolguycompname
           Inspector_Cool                   Administrators   S-1-5-32-544        OK  mycoolguycompname
           cool_2                           Administrators   S-1-5-32-544        OK  mycoolguycompname
           Administrator                    Administrators   S-1-5-32-544        OK  mycoolguycompname
           coolguy                        Backup Operators   S-1-5-32-551        OK  mycoolguycompname
           badguy                         Backup Operators   S-1-5-32-551        OK  mycoolguycompname             
           cool_2          Certificate Service DCOM Access   S-1-5-32-574        OK  mycoolguycompname
           coolguy                 Cryptographic Operators   S-1-5-32-569        OK  mycoolguycompname
           cool_Admins             Cryptographic Operators   S-1-5-32-569        OK  mycoolguycompname
           Inspector_Cool          Cryptographic Operators   S-1-5-32-569        OK  mycoolguycompname
           cool_2                  Cryptographic Operators   S-1-5-32-569        OK  mycoolguycompname
           Administrator           Cryptographic Operators   S-1-5-32-569        OK  mycoolguycompname
           Visitor                 Cryptographic Operators   S-1-5-32-569        OK  mycoolguycompname
           badguy                  Cryptographic Operators   S-1-5-32-569        OK  mycoolguycompname

I am highly worried about the nested group piece. Any help would be greatly appreciated.

Upvotes: 1

Views: 42

Answers (1)

MaxU - stand with Ukraine
MaxU - stand with Ukraine

Reputation: 210832

IIUC you can do it this way:

In [49]: d1 = df1[['Name','compname']]
    ...: d2 = df2[['groupname','GroupSID','members','status']]
    ...: 
    ...: lst_col = 'members'
    ...: 
    ...: pd.DataFrame({
    ...:     col:np.repeat(d2[col].values, d2[lst_col].str.len())
    ...:     for col in d2.columns.difference([lst_col])
    ...: }).assign(**{'Name':np.concatenate(d2[lst_col])}).merge(d1, on='Name', how='left')
    ...:
    ...:
Out[49]:
        GroupSID                        groupname status              Name           compname
0   S-1-5-32-544                   Administrators     OK           coolguy  mycoolguycompname
1   S-1-5-32-544                   Administrators     OK       cool_Admins                NaN
2   S-1-5-32-544                   Administrators     OK    Inspector_Cool                NaN
3   S-1-5-32-544                   Administrators     OK            cool_2                NaN
4   S-1-5-32-544                   Administrators     OK     Administrator  mycoolguycompname
5   S-1-5-32-544                   Administrators     OK  Backup Operators                NaN
6   S-1-5-32-551                 Backup Operators     OK           coolguy  mycoolguycompname
7   S-1-5-32-551                 Backup Operators     OK            badguy                NaN
8   S-1-5-32-574  Certificate Service DCOM Access     OK            cool_2                NaN
9   S-1-5-32-569          Cryptographic Operators     OK    Administrators                NaN
10  S-1-5-32-569          Cryptographic Operators     OK           Visitor  mycoolguycompname

Upvotes: 1

Related Questions