Find the sum and maximum value of the two column in excel file using Pandas

Here, let us look at how to read data from Excel, run some mathematical operations on it, and save it in a new column in DataFrame.

Let us take an example of demo.xlsx excel spreadsheet as shown below:

Find the sum and maximum value of the two column in excel file using Pandas

Here we add the sum of two columns ‘Age’ and ‘Id’ and get maximum value from it.

Program to Find the Sum and Maximum value of the Two Column in Excel File using Pandas in Python

Step1: Import module and read an Excel File

Approach:

  • Import pandas module as pd using the import keyword.
  • Read the excel file using the read_excel() function and store it as a DataFrame.
  • The Exit of the Program.

Below is the implementation:

# Import pandas module as pd using the import keyword
import pandas as pd
 
# Read the excel file using the read_excel() function and 
# store it as a DataFrame
data_frme = pd.read_excel('demo.xlsx')
data_frme

Output:

Program to Find the Sum and Maximum value of the Two Column in Excel File using Pandas in Python

Step2: Creating Two new columns for storing sum and maximum values

# Creating Two new columns which stores the sum and maximum values
data_frme['sum'] = None
data_frme['Max_values'] = None
data_frme

Output:

Creating Two new columns for storing sum and maximum values

Step3: Create an index to allow access to the needed columns.

Approach:

  • Set the index for required columns in the above dataframe and get their column index using the columns.get_loc() method.
  • Print the index values of the required columns.
  • The Exit of the Program.
# Set the index for required columns in a dataframe and get their column index
# using the columns.get_loc() method
Age_index = data_frme.columns.get_loc('Age')
Id_index = data_frme.columns.get_loc('Id')
sum_index = data_frme.columns.get_loc('sum')
Max_values_index = data_frme.columns.get_loc('Max_values')
# Print the index values of the required columns
print(Age_index, Id_index, sum_index, Max_values_index)

Output:

5 7 8 9

Step4: Selecting each row and perform sum, maximum values operations for both the columns.

for row in range(0, len(data_frme)):
    data_frme.iat[row, sum_index] = data_frme.iat[row,
                                      Age_index] + data_frme.iat[row, Id_index]
     
    if data_frme.iat[row, Age_index] > data_frme.iat[row, Id_index]:
        data_frme.iat[row, Max_values_index] = data_frme.iat[row, Age_index]
    else:
        data_frme.iat[row, Max_values_index] = data_frme.iat[row, Id_index]
data_frme

Output:

Create an index to allow access to the needed columns

Change value in Excel using Python

Here, we will use Python to update a value in an Excel spreadsheet.

1)Opening an Excel File:

openpyxl is a Python package that allows you to read and write Excel xlsx/xlsm/xltx/xltm files. It originated from the lack of an existing library that allowed Python to read and write the Office Open XML format natively. The openpyxl library is required for the following task. You can install the openpyxl module in Python by running the following command.

pip install openpyxl

Openpyxl:

The Openpyxl module in Python is used to work with Excel files without requiring third-party Microsoft application software. We can control Excel without having to open the application by using this module. It is used to perform excel tasks such as reading data from an excel file or writing data to an excel file, drawing charts, accessing an excel sheet, renaming the sheet, modifying (adding and deleting) the sheet, formatting, styling the sheet, and any other task. Openpyxl is a very efficient tool for handling these tasks for you.

Data scientists frequently use Openpyxl to perform various operations such as data copying, data mining, and data analysis.

Functions:

  • load_workbook(): This function reads the Excel spreadsheet
  • workbook.active: directs attention to the active sheet in the excel spreadsheet
  • workbook.save(): This function saves the workbook.

Program to Change value in Excel in Python

Example1

Let us take an example of demo.xlsx excel spreadsheet as shown below:

Change value in Excel using Python import load workbook

Approach:

  • Import load_workbook from openpyxl module using the import keyword.
  • Pass the filename as an argument to the load_workbook() function to load the excel file.
  • Apply active function for the above to open the workbook.
  • Modify the cell name that you want to alter.
  • Pass the filename as an argument to the save() function to save the file.
  • The Exit of the Program.

Below is the implementation:

# Import load_workbook from openpyxl module using the import keyword
from openpyxl import load_workbook
 
# Pass the filename as an argument to the load_workbook() function
# to load the excel file
workbook = load_workbook(filename="demo.xlsx")
 
# Apply active function for the above to open the workbook
sheet = workbook.active
 
# Modify the cell name that you want to alter.
sheet["A1"] = "Full Name"
 
# Pass the filename as an argument to the save() function to save the file
workbook.save(filename="demo.xlsx")

Output:

Change value in Excel using Python

Example2: Using xlwt, xlrd, xlutils

This package contains a number of tools for working with Excel files. Because these utilities may require either or both of the xlrd and xlwt packages, they are collected together here, separate from either package.

Installation of xlwt/xlrd/xlutils modules in Python:

pip install xlwt
pip install xlrd
pip install xlutils

Functions:

  • open_workbook(): It is a function that is used to read an Excel spreadsheet.
  • copy(): duplicates the contents of a worksheet.
  • get_sheet(): refers or points to a specific sheet in Excel workbook
  • write(): writes data to a file.
  • save(): This function saves the file.

Approach:

  • Import xlwt module using the import keyword.
  • Import xlrd module using the import keyword.
  • Import copy of xlutils.copy module using the import keyword.
  • Pass the filename as an argument to the open_workbook() function to load the excel file and store it in a variable.
  • Make a dulpicate copy of excel workbook using the copy() function and store it in another variable.
  • Open the sheet that you want to work with of the excel file by passing the number as an argument to the get_sheet() function.
  • Store it in another variable
  • Pass the row number, column number, and some random data as arguments to the write() function.
  • Save the workbook using the save() function.
  • The Exit of the Program.

Below is the implementation:

# Import xlwt module using the import keyword
import xlwt
# Import xlrd module using the import keyword
import xlrd
# Import copy of xlutils.copy module using the import keyword
from xlutils.copy import copy
 
# Pass the filename as an argument to the open_workbook() function
# to load the excel file and store it in a variable
read_book = xlrd.open_workbook('demo.xlsx')
 
# Make a dulpicate copy of excel workbook using the copy() function and store it in another variable
work_book = copy(read_book)
 
# Open the sheet that you want to work with of the excel file by passing 
# the number as an argument to the get_sheet() function
# Store it in another variable
work_sheet = work_book.get_sheet(0)
 
# Pass the row number, column number and some random data as arguments to the
# write() function 
work_sheet.write(0,1,'S.No')
 
# Save the workbook using the save() function
work_book.save('demo.xlsx')

Output:

Change value in Excel using Python using xlwt

How to Import an Excel File into Python using Pandas?

The dataset may not always be available in CSV format. As a result, Pandas includes functions for converting datasets in different formats to Data frames. The format of an excel file is ‘.xlsx’.

Before we begin, we must first install a few libraries as shown below:

pip install pandas
pip install xlrd

We must use the pandas.read_excel() function to import an Excel file into Python using Pandas.

Syntax:

pandas.read_excel(io, sheet_name=0, header=0, names=None,….)

Return Value: It returns a Dataframe or a dictionary of Dataframes.

Let us take an example of demo.xlsx excel spreadsheet as shown below:

How to Import an Excel File into Python using Pandas

How to Import an Excel File into Python using Pandas?

Example1: Reading an Excel file

Approach:

  • Import pandas module as pd using the import keyword.
  • Read the excel file using the read_excel() function and store it as a DataFrame.
  • The Exit of the Program.

Below is the implementation:

# Import pandas module as pd using the import keyword
import pandas as pd
 
# Read the excel file using the read_excel() function and 
# store it as a DataFrame
data_frme = pd.read_excel('demo.xlsx')
data_frme

Output:

Reading an Excel file

Example2: Selecting a specific column

Approach:

  • Import pandas module as pd using the import keyword.
  • Select a specific column of the excel file using the index_col by passing the column index as an argument to it.
  • The Exit of the Program.
# Import pandas module as pd using the import keyword
import pandas as pd
 
# Select a specific column of the excel file using the index_col by passing the 
# column index as an argument to it
data_frme= pd.read_excel("sample.xlsx",
                   index_col = 0)    
data_frme

Output:

Selecting a specific column

Example3: 

If we don’t want the initial heading of the columns, you can change it to indexes by using the “header” argument.

# Import pandas module as pd using the import keyword
import pandas as pd
 
# Read the excel file using the read_excel() function by passing the 
# file name and header= None as the arguments to it.
# (It modifies the header with indexes)
# store it as a DataFrame
data_frme= pd.read_excel('demo.xlsx',
                   header = None)  
data_frme

Output:

Read the excel file using the read excel

Example4: Changing the datatype of columns

If we wish to modify or change the data type of a certain column, use the “dtype” argument.

# Import pandas module as pd using the import keyword
import pandas as pd
 
# Change the datatype of coulums(Gender, Age) using the dtype parameter 
# and store it in a variable
data_frme=  pd.read_excel('demo.xlsx', 
                   dtype = {"Gender": str,
                            "Age":float})
data_frme

Output:

Changing the datatype of columns

Example5: 

If we have unknown values, you can handle them with the “na_values” argument. It will convert the previously specified unknown values to “NaN.”

# Import pandas module as pd using the import keyword
import pandas as pd
 
# Convert the unknown values to NaN using the na_values argument
data_frme= df = pd.read_excel('demo.xlsx', 
                   na_values =['Mara', 
                               'Philip'])
data_frme

Output:

Changing the datatype of columns nv values

Here we converted unknown/unwanted data (Mara,Philip) to NaN values.

How to Import Excel File and Find a Specific Column using Pandas?

In this post, we’ll look at how to import an excel file into a data frame and find a particular column.

Let us take an example of demo.xlsx excel spreadsheet which is shown below:

How to Import Excel File and Find a Specific Column using Pandas?

Approach:

  • Import pandas module as pd using the import keyword.
  • Read the excel file using the read_excel() function and store it as a DataFrame(creating a dataframe).
  • Check the particular column and print it using the head() function. The head() function gives only the first 5 rows of a file.
  • The Exit of the Program.

Step1: Read an Excel File and get the first 5 rows of it.

Below is the implementation:

# Import pandas module as pd using the import keyword
import pandas as pd
# Read the excel file using the read_excel() function and 
# store it as a DataFrame
data_frme = pd.read_excel('demo.xlsx')  
# Get the first 5 rows of the excel file using the head() function
data_frme.head()

Output:

Step2: Check the particular column and display the first 5 values in that column using the head() function.

Below is the implementation:

# Import pandas module as pd using the import keyword
import pandas as pd
# Read the excel file using the read_excel() function and 
# store it as a DataFrame
data_frme= df = pd.read_excel('demo.xlsx')  
# Check the particular column and display the first 5 values in that column using the 
# head() function
# Here check if the Gender is female and display the first 5 values in that column
data_frme[data_frme["Gender"] == 'Female'].head()

Output:

Step3: Do the same for other columns

1)Here check if the Age is greater than 30 and display the first 5 values in that column.

# Import pandas module as pd using the import keyword
import pandas as pd
# Read the excel file using the read_excel() function and 
# store it as a DataFrame
data_frme= df = pd.read_excel('demo.xlsx')  
# Check the particular column and display the first 5 values in that column using the 
# head() function
# Here check if the Age is greater than 30 and display the first 5 values in that column
data_frme[data_frme["Age"]>30].head()

Output:

2)Here check if the Country is ‘United States and display the first 5 values in that column

# Import pandas module as pd using the import keyword
import pandas as pd
# Read the excel file using the read_excel() function and 
# store it as a DataFrame
data_frme= df = pd.read_excel('demo.xlsx')  
# Check the particular column and display the first 5 values in that column using the 
# head() function
# Here check if the Country is 'United States and display the first 5 values in that column
data_frme[data_frme["Country"]=='United States'].head()

Output:

Python Program to Convert any Dates in Spreadsheets

In this tutorial, we’ll look at how to use Python to convert any Dates in Spreadsheets.

Let us take an example of demo.xlsx excel spreadsheet that is shown below:

Python Program to Convert any Dates in Spreadsheets

This file contains a single column called ‘Dates,’ which stores random dates from 2020 in various formats.

to_datetime() Method: To conduct date and time operations, we must first convert the column to a datetime data type.

dt.strftime() Method:

Then, using a value of “%Y-%m-%d” we utilise the dt and strftime methods to tell Python how to format the date. The example we used here is “% Y-% m-% “, where % Y is the whole year, % m is the month with two digits, and % d is the day with two digits.

Program to Convert any Dates in Spreadsheets in Python

Example1

Approach:

  • Import pandas module as pd using the import keyword.
  • Read the file and indicate which column contains the Dates using the read_excel() function and store it in a variable.
  • (here the file has only 1 column, hence we didn’t specify Dates column).
  • Convert the Dates column in the above file to YYYY-MM-DD format using the to_datetime,dt.strftime functions.
  • And store the formated dates output into another Excel file using the to_excel() function.
  • The Exit of the Program.

Below is the implementation:

# Import pandas module as pd using the import keyword 
import pandas as pd
  
# Read the file and indicate which column contains the Dates using the read_excel() function
# and store it in a variable
# (here the file has only 1 column, hence we didn't specify Dates column)
demo_file = pd.read_excel("demo.xlsx")
  
# Convert the Dates column in the above file to YYYY-MM-DD format using the to_datetime,dt.strftime functions
# And store the formated dates output into another Excel file using the to_excel() function.
demo_file["Dates"] = pd.to_datetime(
    demo_file["Dates"]).dt.strftime("%Y-%m-%d")
demo_file.to_excel("demo_format.xlsx")

Output:

Python Program to Convert any Dates in Spreadsheets 1

Different date formats:
Example: Monday, 16 May, 2020, 5:00 PM

“%A, %B %d” -> “Monday, May18”
“%d-%b-%y” -> “16-May-20”
“%d/%m/%Y” -> “16/05/2020”
“%b %d, %Y” -> “May 16, 2020”

      Directive            Description              Example
            %a Weekday as locale’s abbreviated name Sun, Mon,….,Sat(en_US);

So, Mo,……, Sa(de_DE)

            %A Weekday as locale full name Sunday, Monday, Tuesday,….., Saturday
            %w Weekday as a decimal number, where 0 represents Sunday and 6 represents Saturday 0, 1, 2, 3, 4, 5, 6
            %d The month’s day as a zero-padded decimal number 01, 02, 03, …………,31

Example2:

Now let us change the date format as shown below for the same dataset

Format:

 "%d %b, %Y"  --> "16 May, 2020"
# Import pandas module as pd using the import keyword 
import pandas as pd
  
# Read the file and indicate which column contains the Dates using the read_excel() function
# and store it in a variable
# (here the file has only 1 column, hence we didn't specify Dates column)
demo_file = pd.read_excel("demo.xlsx")
  
# Convert the Dates column in the above file to "D MMMM, YYYY" format using the to_datetime,dt.strftime functions
# And store the formated dates output into another Excel file using the to_excel() function.
demo_file["Dates"] = pd.to_datetime(
    demo_file["Dates"]).dt.strftime("%#d %B, %Y")
demo_file.to_excel("demo_format.xlsx")

Output:

Python Program to Convert any Dates in Spreadsheets 2

Example3

Now let us change the date format as shown below for the same dataset:

Format:

"%B %d, %Y" --> "May 16, 2020"
# Import pandas module as pd using the import keyword 
import pandas as pd
  
# Read the file and indicate which column contains the Dates using the read_excel() function
# and store it in a variable
# (here the file has only 1 column, hence we didn't specify Dates column)
demo_file = pd.read_excel("demo.xlsx")
  
# Convert the Dates column in the above file to "MMMM D, YYY" format using the to_datetime,dt.strftime functions
# And store the formated dates output into another Excel file using the to_excel() function.
demo_file["Dates"] = pd.to_datetime(
    demo_file["Dates"]).dt.strftime("%B %d, %Y")
demo_file.to_excel("demo1_format.xlsx")

Output:

Python Program to Convert any Dates in Spreadsheets 3

How to add timestamp to excel file in Python

Modules Needed:

1) datetime: In Python, this module allows us to work with dates and times.

Installation

pip install datetime

2)openpyxl: This is a Python library that allows you to read and write Excel files.

Installation

pip install openpyxl

3)time: The time module provides a wide variety of time-related functions.

strftime() Method:

Date and time objects are converted to string representations using the strftime() method. It takes one or more formatted code inputs and returns the string representation.

How to Add Timestamp to Excel File in Python

Example

Approach:

  • Import datetime module using the import keyword.
  • Import Workbook from openpyxl module using the import keyword from openpyxl import Workbook.
  • Import time module using the import keyword.
  • Create a workbook object and store it in a variable.
  • Choose the active sheet and store it in another variable.
  • Change the heading of the cell A1(rowno-1 and colno-1) to some random text.
  • Get the present date and time using datetime.now() function and modify the cell A2(rowno-2 and colno-1) value to the Current Date and Time using value.
  • Apply sleep() method by passing some random number as an argument to it to sleep it for the given n seconds.
  • Get the present date and time using datetime.now() function and modify the cell A3(rowno-3 and colno-1) value to the Current Date and Time using value.
  • Apply sleep() method by passing some random number as an argument to it to sleep it for the given n seconds.
  • Get the present date and time using datetime.now() function and modify the cell A4(rowno-4 and colno-1) value to the Current Date and Time using value.
  • Pass some random filename as an argument to the save() function to save the above workbook.
  • Close the workbook using the close() function.
  • The Exit of the Program.

Below is the implementation:

# Import datetime module using the import keyword
import datetime
# Import Workbook from openpyxl module using the import keyword
from openpyxl import Workbook
# Import time module using the import keyword
import time
# Create a workbook object and store it in a variable
work_book = Workbook()
# Choose the active sheet using active and store it in another variable
work_sheet = work_book.active
# Change the heading of the cell A1(rowno-1 and colno-1) to some random text
work_sheet.cell(row=1, column=1).value = "Present Date-Time"
  
# Get the present date and time using datetime.now() function and modify the cell 
# A2(rowno-2 and colno-1) value to the Current Date and Time using value.
work_sheet.cell(row=2, column=1).value = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
  
# Apply sleep() method by passing some random number as an argument to it 
# to sleep it for the given n seconds.
time.sleep(3)
# Get the present date and time using datetime.now() function and modify the cell 
# A3(rowno-3 and colno-1) value to the Current Date and Time using value. 
work_sheet.cell(row=3, column=1).value = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
time.sleep(10)
# Get the present date and time using datetime.now() function and modify the cell 
# A4(rowno-4 and colno-1) value to the Current Date and Time using value.
work_sheet.cell(row=4, column=1).value = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
  
# Pass some random filename as an argument to the save() function 
# to save the above workbook.
work_book.save('demo.xlsx')
# Close the workbook using the close() function
work_book.close()

Output:

How to Add Timestamp to Excel File in Python

Python Script to Automate Refreshing an Excel Spreadsheet

Let us see how to automate refreshing an Excel Spreadsheet with Python

We’ll utilize the pywin32 module to open the Excel application and workbooks.

pywin32 :

The pywin32 package has been around for a long time. In fact, Mark Hammond and Andy Robinson published a book on this subject in 2000. Despite being 18 years old (which makes me feel really old:), the underlying technology and concepts are still functional today. Pywin32 is essentially a very thin Python wrapper that allows us to interact with COM objects and automate Windows applications with Python. The strength of this approach is that you can use Python to do almost anything that a Microsoft application can do. The disadvantage is that you must run this on a Windows system with Microsoft Office installed.

Installation of the pywin32 module:

pip install pywin32

Syntax: 

File.Workbooks.open(Filepath)

Parameters

Filepath: It is the path of the specified file

Then, using RefreshAll() function to refresh the file.

Workbook.RefreshAll

Let us take an example of demo.xlsx excel spreadsheet as shown below:

Python Script to Automate Refreshing an Excel Spreadsheet

Python Script to Automate Refreshing an Excel Spreadsheet

Approach:

  • Import the client function from win32com module using the import keyword
  • Pass “Excel.Application” as an argument to the win32com.client.Dispatch() function to open the Excel software.
  • Store it in a variable.
  • We set visibility of excel software on(optional) using visible.
  • Open the workbook by passing the Excel file name as an argument to the Workbooks.open() function and store it in another variable.
  • Refresh all the sheets of the workbook using the RefreshAll() function.
  • Save the above Workbook using the save() function.
  • Close the Excel file using the Quit() function.
  • The Exit of the Program.

Below is the implementation:

# Import the client function from win32 module using the import keyword
import win32com.client
  
# Pass "Excel.Application" as an argument to the win32com.client.Dispatch() 
# function to open the Excel software 
# Store it in a variable.
excelfile = win32com.client.Dispatch("Excel.Application")
  
# We set visibility of excel software on(optional) using visible
excelfile.Visible = 1
  
# Open the workbook by passing the Excelfile name as an argument to the 
# Workbooks.open() function and store it in another variable.
Work_book = excelfile.Workbooks.open("demo.xlsx")
  
# Refresh all the sheets of the workbook using the RefreshAll() function
Work_book.RefreshAll()
  
# Save the above Workbook using the save() function
Work_book.Save()
  
# Close the Excel file using the Quit() function
excelfile.Quit()

Output:

Here it refreshes the excel page

Python – Convert excel serial date to datetime

Let us see how to convert an excel serial date to a DateTime in Python.

The “serial date” format in Excel is actually the number of days since 1900-01-00, or January 1st, 1900. For example, the excel serial date number 38416 symbolizes March 5, 2005, and when converted to a DateTime value, it becomes 2005-03-05.

This can be accomplished by utilising the xlrd.xldate_as_datetime() method. To convert an excel date/time number to a datetime.datetime object, use the xlrd.xldate_as_datetime() function.

Syntax:

xldate_as_datetime (xldate, datemode)

Parameters:

xldate: This is the excel date given that will be converted to datetime.

datemode: This is the given datemode in which the conversion will take place.

Return Value: 

The datetime.datetime object is returned by this xldate_as_datetime  function.

To begin, use the function xlrd.xldate_as_datetime(date, 0) to convert the provided Excel date to a datetime.datetime object. Next call datetime.datetime.date() function on the returned datetime.datetime object to return the date as a datetime.date object.

Finally, call datetime.date.isoformat() to convert the returned datetime.date object to an ISO format date string.

Program to Convert Excel Serial Date to Datetime in Python

Example1: Converting excel serial date to string date in Python

Approach:

  • Importing xlrd module using the import keyword.
  • Take a variable and initialize it with an excel serial date.
  • Pass the above excel serial date, 0 as arguments to the xldate_as_datetime() function to convert the given excel serial date into datetime.datetime object.
  • Store it in a variable.
  • Convert the above datetime.datetime object into datetime.date object by calling the datetime_date.date() function.
  • Store it in another variable.
  • Apply isoformat() function on the above datetime.date object to convert it into the ISO format date string.
  • Print the above ISO format date string.
  • Print the type of the above ISO format date string using the type() function.
  • The Exit of the Program.

Below is the implementation:

# Importing xlrd module using the import keyword
import xlrd
  
# Take a variable and initialize it with an excel serial date
excel_date = 38416
  
# Pass the above excel serial date, 0 as arguments to the xldate_as_datetime() function 
# to convert the given excel serial date into datetime.datetime object
# Store it in a variable
datetime_obj = xlrd.xldate_as_datetime(excel_date, 0)
  
# Convert the above datetime.datetime object into datetime.date object 
# by calling the datetime_date.date() function.
# Store it in another variable
dateobj = datetime_obj.date()
  
# Apply isoformat() function on the above datetime.date object to convert the
# it into the ISO format date string
str_date = dateobj.isoformat()
  
# Print the above ISO format date string
print(str_date)
  
# Print the type of above ISO format date string using the type() function
print(type(str_date))

Output:

2005-03-05
<class 'str'>

Example2: Converting excel serial number to DateTime in Python

Approach:

  • Importing xlrd module using the import keyword.
  • Take a variable and initialize it with an excel serial date.
  • Pass the above excel serial date, 0 as arguments to the xldate_as_datetime() function to convert the given excel serial date into datetime.datetime object.
  • Store it in a variable.
  • Convert the above datetime.datetime object into datetime.date object by calling the datetime_date.date() function.
  • Store it in another variable.
  • Print the above date object(converted date).
  • Print the type of the above-obtained date.
  • The Exit of the Program.

Below is the implementation:

# Importing xlrd module using the import keyword
import xlrd
  
# Take a variable and initialize it with an excel serial date
excel_date = 38416
  
# Pass the above excel serial date, 0 as arguments to the xldate_as_datetime() function 
# to convert the given excel serial date into datetime.datetime object
# Store it in a variable
datetime_obj = xlrd.xldate_as_datetime(excel_date, 0)
  
# Convert the above datetime.datetime object into datetime.date object 
# by calling the datetime_date.date() function.
# Store it in another variable
dateobj = datetime_obj.date()
  
# Print the above date object(converted date)
print(dateobj)
  
# Print the type of the above obtained date 
print(type(dateobj))

Output:

2005-03-05
<class 'datetime.date'>

Python Program to Split Given List and Insert in Excel File

Given a list of Currency and Country, the job is to separate these two parts and enter them into Excel one at a time.

Pandas, a well-known data analysis library, can be used. We can easily alter the columns in pandas and simply put the filtered elements into an excel file using the df.to_excel() function.

Program to Split Given List and Insert in Excel File in Python

Method #1: Using Built-in Functions (Static Input)

Approach:

  • Import pandas module as pd using the import keyword.
  • Give the list as static input and store it in a variable.
  • Call the DataFrame() object to create the dataframe and store it in another variable.
  • Create two columns using the above list slicing.
  • Create two columns using the above list slicing.
  • The first column contains the even index values(Currency).
  • The second column contains the odd index values(Country).
  • Convert the above result dataframe columns into an excel file using the to_excel() method by passing the filename and index=False as arguments to it.
  • The Exit of the Program.

Below is the implementation:

# Import pandas module as pd using the import keyword
import pandas as pd
  
# Give the list as static input and store it in a variable.
gvn_list = ['Rupee', 'India',
         'dollar', 'USA', 
         'Yen', 'Japan',
         'Euro', 'France']
  
# Call the DataFrame() object to create the dataframe and store it in another variable.
data_frame = pd.DataFrame()
  
# Create two columns using the above list slicing.
# The first column contains the even index values(Currency)
# The second column contains the odd index values(Country)
data_frame['Currency'] = gvn_list[0::2]
data_frame['Country'] = gvn_list[1::2]
  
# Convert the above result dataframe columns into an excel file using the to_excel() method 
# by passing the filename and index=False as arguments to it.
data_frame.to_excel('demo.xlsx', index = False)

Output:

Python Split given list and insert in excel file

Method #2: Using Built-in Functions (User Input)

Approach:

  • Import pandas module as pd using the import keyword.
  • Give the list as user input using the list(), input() functions and store it in a variable.
  • Call the DataFrame() object to create the dataframe and store it in another variable.
  • Create two columns using the above list slicing.
  • Create two columns using the above list slicing.
  • The first column contains the even index values(Fruits).
  • The second column contains the odd index values(Vegetables).
  • Convert the above result dataframe columns into an excel file using the to_excel() method by passing the filename and index=False as arguments to it.
  • The Exit of the Program.

Below is the implementation:

# Import pandas module as pd using the import keyword
import pandas as pd
  
# Give the list as user input using the list(), input() functions,
# and store the list in a variable.
gvn_list = list(
   input('Enter some random string elements for the list = ').split())

  
# Call the DataFrame() object to create the dataframe and store it in another variable.
data_frame = pd.DataFrame()
  
# Create two columns using the above list slicing.
# The first column contains the even index values(Fruits)
# The second column contains the odd index values(Vegetables)
data_frame['Fruits'] = gvn_list[0::2]
data_frame['Vegetables'] = gvn_list[1::2]
  
# Convert the above result dataframe columns into excel file using the to_excel() method 
# by passing the filename and index=False as arguments to it.
data_frame.to_excel('demo1.xlsx', index = False)

Output:

Enter some random string elements for the list = apple brinjal mango tomato banana beetroot

Python Split given list and insert in excel file user input

Python Program to Convert Excel to PDF

Python is a high-level, general-purpose programming language that is widely used. Python programming language is utilized in web development, machine learning applications, and all cutting-edge software technologies.

Now let us see how to convert Excel file to PDF.

pywin32 Module:

The pywin32 package has been available for quite some time. In fact, Mark Hammond and Andy Robinson published a book on the subject in 2000. Despite the fact that it is 18 years old (which makes me feel really old:), the underlying technology and concepts are still functional today. Pywin32 is a very thin Python wrapper that allows us to interact with COM objects and automate Windows applications using Python. The power of this approach is that you can use Python to do almost anything that a Microsoft application can do. The disadvantage is that this must be run on a Windows system with Microsoft Office installed.

Installation

pip install pywin32

ExportAsFixedFormat() Function:

To publish a workbook in either PDF or XPS format, use the ExportAsFixedFormat() function.

Syntax:

ExportAsFixedFormat (Type, File_name, Quality, IncludeDocProperties, IgnorePrintAreas, 
From, To, OpenAfterPublish, FixedFormatExtClassPtr)

Program to Convert Excel to PDF in Python

Approach:

  • Import client that belongs to win32com module using the import keyword.
  • Pass “Excel.Application” as an argument client.Dispatch() function to the Create COM object(Opening Microsoft Excel).
  • Open the Excel file by Passing the Excel file path as an argument to the Open() method.
  • Open the first worksheet using the Worksheets[0] and store it in another variable.
  • Convert the above Excel file to PDF File using the ExportAsFixedFormat() function by passing 0, PDF file path as arguments to it.
  • The Exit of the Program.

Below is the implementation:

# Import client that belongs to win32com module using the import keyword
from win32com import client
  
# Pass "Excel.Application" as an argument client.Dispatch() function 
# to the Create COM object(Opening Microsoft Excel)
# Store it in a variable.
excel_file = client.Dispatch("Excel.Application")
  
# Open the Excel file by Passing the Excel file path as an argument to the Open() method
xl_sheets = excel_file.Workbooks.Open(r'C:\Users\vicky\Downloads\demo.xlsx')
# Open the first worksheet using the Worksheets[0]
# Store it in another variable.
worksheets = xl_sheets.Worksheets[0]
  
# Convert the above Excel file to PDF File using the ExportAsFixedFormat() 
# function by passing 0, PDF file path as arguments to it.
worksheets.ExportAsFixedFormat(0, r'C:\Users\vicky\Downloads\demo.pdf')

Outputs:

Excel File Image:

Program to Convert Excel to PDF in Python

Folder Image:

Program to Convert Excel to PDF in Python - Folder

Pdf Screenshot:

Convert Excel to PDF using Python Code