熊猫完美执行两个具有条件的数据框的countifs

Question

我在这里碰到一个街区。我必须翻译这个excel公式IF(COUNTIFS(advisor!$C:$C,$A2)=0,"0 disclosed", "Independent")

  if df.groupby('id').apply(lambda x: x['id'] == df_advisor['company_id']).count() == 0:
        df['auditor_compensation'] = '0 disclosed'
    else:
        df['auditor_compensation'] = 'Independent'

到目前为止，这是我的解决方案，我不断得到 KeyError:（'company\u id'，'发生在索引1'）

任何帮助都将不胜感激。你知道吗

编辑

df样本数据：公司数据

id      ticker       iq_id     company              auditor_compensation
48299   ENXTAM:AALB  IQ881736   Aalberts Industries       ?
48752   ENXTAM:ABN   IQ1090191  ABN AMRO Group            ?
48865   ENXTAM:ACCEL IQ4492981  Accell Group              ?
49226   ENXTAM:AGN   IQ247906   AEGON                     ?
49503   ENXTAM:AD    IQ373545   Koninklijke               ?

下面是df\U advisor示例数据

id    type          company_id  advisor_company_id
1     auditor       48299       60911
17    auditor       48752       165120
6359  auditor       48865       73607
37    auditor       49226       81877
4415  compensation  49226       90258
53    auditor       49503       81877

因此，我们的目标是检查df_advisor中的整个列company_id，并计算发生次数df['id']，以便填充auditor_compensation列。你知道吗

Answer 1

假设您想知道A列中的名称是否在Excel C列的列表中。你知道吗

df['Boolean'] = df['id'].isin(list(df_advisor['company_id'])
df['auditor_compensation'] = ''
df.loc[df['Boolean'] == False, 'auditor_compensation'] = '0 disclosed'
df.loc[df['Boolean'] == True, 'auditor_compensation'] = 'Independent'

Answer 2

使用^{}：

df['auditor_compensation'] = np.where(df['id'].isin(df_advisor['company_id']),
                                      '0 disclosed', 
                                      'Independent')
print (df)
      id        ticker      iq_id              company auditor_compensation
0  48299   ENXTAM:AALB   IQ881736  Aalberts Industries          0 disclosed
1  48752    ENXTAM:ABN  IQ1090191       ABN AMRO Group          0 disclosed
2  48865  ENXTAM:ACCEL  IQ4492981         Accell Group          0 disclosed
3  49226    ENXTAM:AGN   IQ247906                AEGON          0 disclosed
4  49503     ENXTAM:AD   IQ373545          Koninklijke          0 disclosed