Read and Write to Excel files in Python Pandas – Lesson 5

Pandas is a popular data analysis library in Python that provides functionality to read and write data from various file formats, including Excel. Here is an example code for reading and writing Excel files using pandas:

First, we need to import the pandas library:

import pandas as pd

Reading Excel files

We can read an Excel file using the read_excel method of pandas:

df = pd.read_excel('filename.xlsx')

The read_excel method reads the data from the Excel file and returns a pandas DataFrame. We can specify the sheet name and other parameters in the method if needed.

Writing Excel files

We can write a pandas DataFrame to an Excel file using the to_excel method:

df.to_excel('filename.xlsx', index=False)

The to_excel method writes the data from the DataFrame to an Excel file. We can specify the sheet name, start row and column, and other parameters in the method if needed.

In the above code, index=False specifies that the index of the DataFrame should not be written to the Excel file.

Example

Here is an example code that reads an Excel file, modifies the data, and writes it back to a new Excel file:

import pandas as pd

# read the Excel file
df = pd.read_excel('input.xlsx')

# modify the data
df['new_column'] = df['column1'] + df['column2']

# write the modified data to a new Excel file
df.to_excel('output.xlsx', index=False)

In the above code, we read an Excel file called input.xlsx using the read_excel method and stored the data in a pandas DataFrame called df. We then added a new column to the DataFrame by adding two existing columns, and wrote the modified data to a new Excel file called output.xlsx using the to_excel method.