Match string in excel cell and write specific value to new/adjacent cell Python

user_16596573 0
Viewed 183

I am trying to match a string in a cell and if it is present I should be able to write a specific string in new cell/column. For example:

This is my xlsx file:

|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 |

Expected Output:

|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|

My Code:

  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')

My Output: enter image description here

excel python
2 Answers

I think the simplest way would be -

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')

This will add the Region column in DataFrame as expected. Also, you can customize the function as per your requirements

user_165235860

Apologies for not being active here. I found the solution for my question.

#Importing the file in which the changes mst be done import_file_path = filedialog.askopenfilename(filetypes=(("Excel files", ".xlsx"),("All files", ".xlsx") )) df = pd.read_excel (import_file_path)

#Under the 'Container' column if the string contains 'France' then the 'Region' column must be written with the value EMEA North df.loc[df['Container'].astype(str).str.contains("France"), 'Region'] = 'EMEA North' df.loc[df['Container'].astype(str).str.contains("Germany"), 'Region'] = 'EMEA South'

This worked for me! Appreciate your help Vinay :)

user_165965730
Related