Scribd
Upload
16 views6 pages

Python Excel Operations Guide

The document provides a Python program that performs various operations on an Excel spreadsheet using the openpyxl library. It includes functions for reading the first five rows, appending/deleting rows and columns, and performing aggregate functions such as sum, average, max, and min on specified columns. The program also demonstrates these operations and saves the modified workbook as 'modified_excel_file.xlsx'.

Uploaded by

Nomita Chawla
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
16 views6 pages

Python Excel Operations Guide

The document provides a Python program that performs various operations on an Excel spreadsheet using the openpyxl library. It includes functions for reading the first five rows, appending/deleting rows and columns, and performing aggregate functions such as sum, average, max, and min on specified columns. The program also demonstrates these operations and saves the modified workbook as 'modified_excel_file.xlsx'.

Uploaded by

Nomita Chawla
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Introduction to Python Programming

7. Implement a Python program to perform the following


operations on an Excel spreadsheet:
I. Reading the first 5 rows of all columns
II. Appending a new row / new column
III. Delete row/column
IV. To perform aggregate functions
# [Link]
import openpyxl

# Load the workbook and select the sheet


def load_workbook(file_path, sheet_name='Sheet1'):
wb = openpyxl.load_workbook(file_path)
sheet = wb[sheet_name]
return wb, sheet

# I. Reading the first 5 rows of all columns


def read_first_5_rows(sheet):
rows = []
for row in sheet.iter_rows(min_row=1, max_row=5, values_only=True):
[Link](row)
return rows

# II. Appending a new row or column


def append_row(sheet, data):
[Link](data)

def append_column(sheet, data):


max_row = sheet.max_row

Prof. Nomitha Chawla - BIET


Introduction to Python Programming

for i, value in enumerate(data, start=1):


[Link](row=i, column=max_row + 1, value=value)

# III. Deleting a row or column


def delete_row(sheet, row_num):
sheet.delete_rows(row_num)

def delete_column(sheet, col_num):


sheet.delete_cols(col_num)

# IV. Performing aggregate functions


def sum_column(sheet, col_num):
col_sum = 0
for row in sheet.iter_rows(min_row=2, max_row=sheet.max_row,
min_col=col_num, max_col=col_num, values_only=True):
col_sum += row[0] if isinstance(row[0], (int, float)) else 0
return col_sum

def average_column(sheet, col_num):


total = 0
count = 0
for row in sheet.iter_rows(min_row=2, max_row=sheet.max_row,
min_col=col_num, max_col=col_num, values_only=True):
if isinstance(row[0], (int, float)):
total += row[0]
count += 1
return total / count if count != 0 else 0

Prof. Nomitha Chawla - BIET


Introduction to Python Programming

def max_column(sheet, col_num):


max_value = None
for row in sheet.iter_rows(min_row=2, max_row=sheet.max_row,
min_col=col_num, max_col=col_num, values_only=True):
if isinstance(row[0], (int, float)):
if max_value is None or row[0] > max_value:
max_value = row[0]
return max_value

def min_column(sheet, col_num):


min_value = None
for row in sheet.iter_rows(min_row=2, max_row=sheet.max_row,
min_col=col_num, max_col=col_num, values_only=True):
if isinstance(row[0], (int, float)):
if min_value is None or row[0] < min_value:
min_value = row[0]
return min_value

# Main function to demonstrate the operations


def main():
# Load the workbook and the sheet
file_path = 'C:\\Users\\Desktop\\[Link]' # Replace with your Excel
file path
wb, sheet = load_workbook(file_path)

# I. Reading the first 5 rows of all columns


print("First 5 rows of all columns:")
first_5_rows = read_first_5_rows(sheet)
for row in first_5_rows:
print(row)
Prof. Nomitha Chawla - BIET
Introduction to Python Programming

# II. Appending a new row


new_row = ['New', 'Row', 123, 456.78] # Example new row to append
append_row(sheet, new_row)
print("\nAppended new row:", new_row)

# II. Appending a new column


new_column = ['New Data', 10, 20, 30, 40, 50] # Example new column to
append
append_column(sheet, new_column)
print("\nAppended new column:", new_column)

# III. Deleting a row (e.g., delete row 6)


delete_row(sheet, 6)
print("\nDeleted row 6")

# III. Deleting a column (e.g., delete column 5)


delete_column(sheet, 5)
print("\nDeleted column 5")

# IV. Aggregate functions


print("\nSum of column 2:", sum_column(sheet, 2))
print("Average of column 2:", average_column(sheet, 2))
print("Max of column 2:", max_column(sheet, 2))
print("Min of column 2:", min_column(sheet, 2))

# Save the modified workbook


[Link]('modified_excel_file.xlsx')

Prof. Nomitha Chawla - BIET


Introduction to Python Programming

print("\nWorkbook saved as 'modified_excel_file.xlsx'")

if __name__ == "__main__":
main()
#SAMPLE OUTPUT

Prof. Nomitha Chawla - BIET


Introduction to Python Programming

Explanation of the Operations:


1. Reading the First 5 Rows:

o read_first_5_rows(sheet) reads the first 5 rows from all columns


using iter_rows with min_row=1 and max_row=5. The
values_only=True ensures we only get cell values (not formatting).

2. Appending New Row or Column:

o append_row(sheet, data) appends a new row of data.

o append_column(sheet, data) appends a new column by iterating


through rows and placing each value in the new column.

3. Deleting Row or Column:

o delete_row(sheet, row_num) deletes a specific row.

o delete_column(sheet, col_num) deletes a specific column.

4. Aggregate Functions:

o sum_column(sheet, col_num) calculates the sum of a specific


column.

o average_column(sheet, col_num) calculates the average of the


values in a specific column.

o max_column(sheet, col_num) finds the maximum value in a


column.

o min_column(sheet, col_num) finds the minimum value in a column.

Notes:
• Replace ' file_path ' variable with the path to your actual Excel file.
• The operations modify the workbook in-place, and after the operations,
the workbook is saved as 'modified_excel_file.xlsx'.

Prof. Nomitha Chawla - BIET

Common questions

Powered by AI

Executing aggregate functions impacts performance based on the number of iterations through data and complexity of the operations. For large datasets, performance may degrade due to increased computational requirements. Reliability is enhanced by type checks preventing errors on non-numeric data, ensuring operations only include valid entries, thus providing accurate results .

Aggregate functions on an Excel column can be performed using dedicated functions: 'sum_column(sheet, col_num)' for summing, 'average_column(sheet, col_num)' for averaging, 'max_column(sheet, col_num)' for finding the maximum, and 'min_column(sheet, col_num)' for finding the minimum. These functions iterate through rows, checking each cell's type, summing or comparing numeric values. Calculations account for the number of elements and data type checks to ensure accuracy .

To delete a specific row or column, use 'delete_row(sheet, row_num)' and 'delete_column(sheet, col_num)', respectively. These methods identify the target by row or column number and remove it from the sheet. This operation alters the data structure, potentially affecting data integrity if not carefully managed, as subsequent rows or columns may shift .

Inline type-checking within the aggregate function loops ensures that only numeric data types (int, float) are processed for summation or averaging, preventing errors from non-numeric data inclusion. This extends the robustness of data processing, leading to reliable outputs. However, it may increase computational load as additional checks are performed per iteration .

To append a new row to an Excel sheet, use the 'append_row(sheet, data)' function, passing the new row data as a list. For a column, use 'append_column(sheet, data)', iterating through the existing rows to place each value in the new column. Ensure the data structure matches the sheet layout, with rows as lists and columns iterated properly to match each cell .

Specifying paths requires accurate file paths relative to the working environment, including handling path differences across operating systems. Sheet names must accurately reflect the target sheet to avoid unintended operations. Failure in specification can lead to operational errors or modifications to unintended files or sheets .

The program saves the modified workbook using 'wb.save('modified_excel_file.xlsx')', overwriting any existing file with the same name. Precautions include ensuring the original data is backed up to prevent data loss, especially if unintended file overwrites occur, and verifying transformations are correctly executed before final save .

The 'openpyxl' library is used to automate Excel file modifications by loading workbooks, selecting sheets, and executing read, append, delete, and aggregate operations programmatically. Advantages include increased efficiency, repeatability, and reduction in human error. However, limitations arise from handling only openpyxl-compatible formats and possible performance issues with large datasets .

Challenges include handling large datasets where performance may degrade, ensuring compatibility with Excel versions, and managing dependencies like openpyxl installation. Ensuring that file paths are dynamically configurable and dealing with unexpected data types or formatting in cells also require robust error handling and validation mechanisms to prevent runtime errors .

To read the first 5 rows of all columns in an Excel spreadsheet using Python, you can use the 'iter_rows' method with parameters 'min_row=1' and 'max_row=5'. The 'values_only=True' parameter ensures that only cell values are obtained, excluding any formatting. This approach efficiently reads and extracts data by iterating only over the specified range .

You might also like