Skip to content
Related Articles

Related Articles

Improve Article
Save Article
Like Article

How to extract Email column from Excel file and find out the type of mail using Pandas?

  • Last Updated : 02 Sep, 2020

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'] = None
data

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'] = None
data['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.


My Personal Notes arrow_drop_up
Recommended Articles
Page :

Start Your Coding Journey Now!