将两个数据帧与其中一列中的列表合并

Question

我正在尝试合并多个数据帧，以查找不属于他们的组中的用户

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]

如何合并这两个数据帧以显示与它们所属的适当组正确相关的用户名，如下所示：

预期产出：

           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  

我非常担心嵌套的组块。任何帮助都将不胜感激

Answer 1

IIUC您可以这样做：

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