Skip to content
Related Articles

Related Articles

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.

 Attention geek! Strengthen your foundations with the Python Programming Foundation Course and learn the basics.  

To begin with, your interview preparations Enhance your Data Structures concepts with the Python DS Course. And to begin with your Machine Learning Journey, join the Machine Learning – Basic Level Course

My Personal Notes arrow_drop_up
Recommended Articles
Page :