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:

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

• Python Program to Convert any Dates in Spreadsheets
• How to Import Excel File and Find a Specific Column using Pandas?
• Python Split given List and Insert in Excel File

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:

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:

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: