Python Excel – Manipulating Worksheet Data

by

Building on the skill(s) you learned earlier, Part 3 centers around manipulating the data. This skill is another must-have if you are interested in pursuing a career as a Data Scientist.

Background

After completing Part 2 of this series, you should be comfortable using Python to:

  • access a single row/column value,
  • access a range of row/column values, and
  • access each value on a Worksheet.

The j-greats.xlsx should exist on your system. If you do not have this particular file, click here for instructions.

Add the following code to the top of each code snippet. This snippet will allow the code in this article to run error-free.

from openpyxl import load_workbook

file_name = 'j-greats.xlsx'
w_book    = load_workbook(filename=file_name)
w_sheet   = w_book.active

Append Worksheet Row(s)

Run the code below to append row(s) to the Active Worksheet.

musician = [['Jelly Roll', 'Morton', 1890, 'New Orleans', 'USA', 'piano', 765221],
            ['Freddie', 'Hubbard', 1938, 'Indiana', 'USA', 'Trumpet', 465221]]
for m in musician:
    w_sheet.append(m)
w_book.save(filename=file_name)
w_book.close()
  • Line [1] creates a list of lists containing the missing musicians.
  • Line [2] initializes an iterator to loop through the musicians in the list of lists.
    • Line [3] appends each musician’s information to the end of the Active Worksheet rows.
  • Line [4] saves the Workbook with the relevant changes.
  • Line [5] closes the open Workbook.

After running the above code, the j-greats.xlsx file should look similar to this:

Modify a Single Value in Worksheet

The Worksheet data can be modified either one value at a time or using an iterator.

After reviewing the Worksheet, we noticed a few issues. The first one is on row 12, column F. The instrument for Jelly Roll Morton should have the first character capitalized (piano). Running the code below resolves this issue.

w_sheet[F12] = 'Piano'
w_book.save(filename=file_name)
w_book.close()

Modify Multiple Values in Worksheet

Depending on circumstances, an iterator is needed to modify multiple values at a time on the Active Worksheet.

for cell in w_sheet['F']:
    if cell.value == 'Trumpet':
        cell.value = 'Trumpeter'
w_book.save(filename=file_name)
w_book.close()
  • Line [1] initializes the iteration for column F on the Active Worksheet.
    • Line [2] Checks the cell value for each iteration. If this value equates to Trumpet, line [3] changes the value to Trumpeter.
  • Line [4] saves the changes.
  • Line [5] closes the open Workbook.

Insert Worksheet Column

To insert a new column on the Active Worksheet, run the following code.

Below, this code inserts a blank column between the First and Last columns (position 2). This column contains no data.

w_sheet.insert_cols(2)
w_book.save(file_name)
w_book.close()

When the code below runs, the blank column is updated to reflect the list middle. In addition, the list includes the header row column title of Middle.

middle = ['Middle', 'Dewey', 'Kennedy', 'William', 'Birks', 'Jane',
          'James ', 'Wright', 'Emmanuel', '', '', 'Joseph', 'Dewayne']
idx = 0
for cell in w_sheet['B']:
    if idx >= 0 or idx <= 12:
        cell.value = middle[idx]
    idx += 1
w_book.save(file_name)
w_book.close()

Delete Single Worksheet Row

The following code removes row 9 (Oscar Petersen) from the Active Worksheet and saves the Workbook.

w_sheet.delete_rows(9)
w_book.save(file_name)
w_book.close()

Delete Multiple Worksheet Rows

The following code will remove multiple rows from the Active Worksheet.

We will not run the code for this article to keep the j-greats.xlsx file intact.

w_sheet.delete_rows(2, 4)
w_book.save(file_name)
w_book.close()
  • Line [1] removes rows 2-4 (Miles Petersen – John Williams). The first parameter in the delete_rows() method is the start row position. The second parameter is the stop row position.
  • Line [2] saves the changes.
  • Line [3] closes the open Workbook.

Delete Single Worksheet Column

The following code removes column 8 (Net Worth) from the Active Worksheet.

We will not run the code for this article to keep the j-greats.xlsx file intact.

w_sheet.delete_cols(8)
w_book.save(file_name)
w_book.close()
  • Line [1] passes the parameter of 8 to the delete_cols() method.
  • Line [2] saves the changes.
  • Line [3] closes the open Workbook.

Delete Multiple Worksheet Columns

The following code will remove multiple rows from the Active Worksheet.

We will not run the code for this article to keep the j-greats.xlsx file intact.

w_sheet.delete_cols(2, 4)
w_book.save(file_name)
w_book.close()
  • Line [1] deletes columns 2-4 (Middle, Last, Birth Year). The first parameter in the delete_cols() method is the start row position. The second parameter is the stop row position.
  • Line [2] saves the changes.
  • Line [3] closes the open Workbook.

Summary

In this article, we learned how to:

  • Append Worksheet Row(s)
  • Modify Worksheet Data
  • Insert Worksheet Column
  • Delete Worksheet Row(s)
  • Delete Worksheet Column(s)

Next Up

Part 4 in Working with Excel will focus on how to:

  • Add Worksheet(s)
  • Rename Worksheet(s)
  • Reorder Worksheet(s)
  • Delete Worksheet(s)
  • Determine the Number of Rows/Columns in the Worksheet