In this article, Let’s see how to Extract Email column from an Excel file and find out the type of mail using Pandas. Suppose our Excel file looks like below given image, and then we have to store different type of emails in different columns of Dataframe.
For viewing the Excel file Click Here
Approach:
- Import required module.
- Import data from Excel file.
- Make an extra column for each different Email.
- Set Each required Index for searching.
- Define the Pattern of the Email.
- Search the Email and assigning to the respective column in Dataframe.
Let’s see Step-By-Step-Implementation:
Step 1: Import the required module and read data from Excel file.
Python3
# import required module import pandas as pd; import re; # Read excel file and store in to DataFrame data = pd.read_excel("Email_sample.xlsx"); # show the dataframe data |
Output:
Step 2: Make an extra column for each different Email.
Python3
data['Google-mail'] = None data |
Output:
Python3
data['Yahoo-mail'] = Nonedata |
Output :
Step 3: Set Each required Index for searching.
Python3
# set required index index_set = data.columns.get_loc('E-mail') index_gmail = data.columns.get_loc('Google-mail') index_yahoo = data.columns.get_loc('Yahoo-mail') print(index_set, index_gmail, index_yahoo) |
Output:
1 2 3
Step 4: Defining the Pattern of the Email.
Python3
# define pattern of Email google_pattern = r'gmail.com'yahoo_pattern = r'yahoo.com' |
Step 5: Searching the Email and assigning into respective column in Dataframe.
Python3
# Search the Email in DataFrame and store for row in range(0, len(data)): if re.search(google_pattern, data.iat[row, index_set]) == None : data.iat[row,index_gmail] = 'Account not belongs to Google' else: gmail = re.search(google_pattern, data.iat[row, index_set]).group() data.iat[row,index_gmail] = "Google-Mail" if re.search(yahoo_pattern, data.iat[row, index_set]) == None : data.iat[row,index_yahoo] = 'Account not belongs to Yahoo' else: yahoo = re.search(yahoo_pattern, data.iat[row, index_set]).group() data.iat[row,index_yahoo] = "Yahoo-Mail" data |
Output:
Complete Code:
Python3
# importing required module import pandas as pd import re # Creating df # Reading data from Excel data = pd.read_excel("Email_sample.xlsx") print("Original DataFrame") print(data) # Create column for # each type of Email data['Google-mail'] = Nonedata['Yahoo-mail'] = None # set index index_set = data.columns.get_loc('E-mail') index_gmail = data.columns.get_loc('Google-mail') index_yahoo = data.columns.get_loc('Yahoo-mail') # define Email pattern google_pattern = r'gmail.com'yahoo_pattern = r'yahoo.com' # Searching the email # Store into DataFrame for row in range(0, len(data)): if re.search(google_pattern, data.iat[row, index_set]) == None: data.iat[row, index_gmail] = 'Account not belongs to Google' else: gmail = re.search(google_pattern, data.iat[row, index_set]).group() data.iat[row, index_gmail] = "Google-Mail" if re.search(yahoo_pattern, data.iat[row, index_set]) == None: data.iat[row, index_yahoo] = 'Account not belongs to Yahoo' else: yahoo = re.search(yahoo_pattern, data.iat[row, index_set]).group() data.iat[row, index_yahoo] = "Yahoo-Mail" data |
Output :
Note: Before running this program, make sure you have already installed xlrd library in your Python environment.
