python - 匹配excel单元格中的字符串并将特定值写入新/相邻单元格Python

Question

我正在尝试匹配单元格中的字符串，如果存在，我应该能够在新的单元格/列中写入特定的字符串。例如:
这是我的 xlsx 文件:

|UserPrincipalName         |Contact |Age    |Container                           |
|:-------------------------|:------:|:-----:|:--------------------------:|
|John.doe                  |Email   |23     |\Location\EMEA\France\User |
|Jane.doe                  |Phone   |25     |\Location\EMEA\Germany\User|
|Jane12                    |Phone   |25     |\Location\EMEA\Italy\User  |
|Jane1322                  |Phone   |25     |\Location\EMEA\Belgium\User|
|Feng.Main                 |SMS     |21     |\Location\APAC\China\User  |
|serviceaccount            |Email   |       |\Location\Service Accounts |

预期输出:

|UserPrincipalName         |Contact |Age    |Container                           |Region         |
|:-------------------------|:------:|:-----:|:-----------------:|:--------------|
|John.doe                  |Email   |23     |\Location\EMEA\France\User |EMEA North     |
|Jane.doe                  |Phone   |25     |\Location\EMEA\Belgium\User|EMEA South     |
|Jane12                    |Phone   |25     |\Location\EMEA\Germany\User|EMEA South     |
|Jane1322                  |Phone   |25     |\Location\EMEA\Italy\User  |EMEA North     |
|Feng.Main                 |SMS     |21     |\Location\APAC\China\User  |APAC           |
|serviceaccount            |Email   |       |\Location\Service Accounts |Service Account|

我的代码:

  df = pd.read_excel('C:\\Users\\Desktop\\OpTest.xlsx')
  df['Region'] = df[df['Container'].str.contains(r'\\EMEA\\')]
  df = df.to_excel('C:\\Users\\Desktop\\MFAOpTest.xlsx')

我的输出:
enter image description here

Answer 1

我认为最简单的方法是-

df = pd.read_excel('C:\\Users\\Desktop\\OpTest.xlsx')

def myfunc(x):
    if ('EMEA\France' in x or 'EMEA\Italy'in x):
        return "EMEA North"
    elif ('EMEA\Belgium' in x or '\EMEA\Germany' in x):
        return "EMEA South"
    elif 'APAC\China' in x:
        return "APAC"
    elif 'Service Accounts' in x:
        return "Service Account"

df['Region'] = df['Container'].apply(myfunc)
print(df)

df.to_excel('C:\\Users\\Desktop\\MFAOpTest.xlsx')

这将按预期在 DataFrame 中添加 Region 列。
此外，您可以根据需要自定义功能