Reading and Writing Excel (XLSX) Files in Python

Programmers can use the Pandas library to read and write excel files using Python. Like other types of files, programmers can read and write files in Python.

They can also learn to write multiple DataFrames using Pandas library, read particular rows and columns from a spreadsheet, and change or modify the name of single and multiple spreadsheets inside an excel file before accomplishing anything.

Also, programmers can manipulate the data by changing it through the Pandas DataFrames. This Python article will cover all the topics for programmers who want to learn how to read and write Excel (XLSX) Files in Python.

Read and write CSV files using Pandas

In the very first step, users install the Pandas library before implementing it. Run the command on Windows:

$ python pip install pandas

Write Excel Files Using Pandas

Programmers can store the data as they do in other files in a DataFrame. Here, they can use the built-in to_excel() function that extracts the given content into an Excel file.

The following code snippet will make users understand more clearly:

import pandas as pd
df = pd.DataFrame({'Students':['A', 'B', 'C', 'D', 'E', 'F'],
    'Stream':['Science', 'Commerce', 'Science', 'Arts', 'Commerce', 'Arts'],
    'Population':['234523', '234567', '327655', '345645', '235654', '227032']})
df.to_excel('./Sample.xlsx')
df.to_excel('./Sample.xlsx', sheet_name='States')
df.to_excel('./Sample.xlsx', sheet_name='States', index=False)

Output:

Explanation:

We have added the data using the Pandas library and written it into an excel sheet using the to_excel() function. We used the keys in the dictionary that will set the column names. Similarly, we set the values as the rows that include the information. The to_excel() function will write the contents to the excel sheet.

Here, we also used a single argument, the file path, that set the path of our excel file with the given name (here, we used the file name "Sample.xlsx"). Users can change their file name or the name of the excel sheet by using the sheet_name parameter to the to_excel() call. In addition to this process, we can add the index parameter and set it to false, which changes the index column from the main spreadsheet.

Note Programmers must not use any parameters in their Python example. Therefore, it will set the default name of the spreadsheet inside the file as "Sheet 1". In the output, we can see that the Excel file contains an additional column that has numbers. These numbers represent the index values for each row, which come straight from the Pandas DataFrame.

Read an Excel file using Pandas

As programmers can write an excel file in Python, they can do the opposite, i.e., read the data from that excel spreadsheet and extract it into DataFrames. The easiest way to pack the contents of the excel file into a DataFrame is by calling the read_excel() function. This function will print the data from the excel sheet into a DataFrame. We take an example of the following code snippet to understand how it works:

import pandas as pd
students_grades = pd.read_excel('./sample.xlsx')
students_grades.head()
print(students_grades)

Output:

Explanation:

Here, we only used the path argument that allows us to add the file path of the required excel file. The read_excel() function reads and packs the data into a DataFrame, which we can then display using the head() function.

Also, programmers can read a specific column from an excel file. In many cases, programmers might want to read a part of the Excel content.

It is always useful to display the entire content in the traditional approach, but if programmers want to extract some specific contents from the excel sheet or to access a particular element, they can do this by passing the column names into the usecols parameter.

The following code snippet makes the concept clear

import pandas as pd
a = [0, 1, 3]
students_grades = pd.read_excel('./sample.xlsx', usecols=a)
students_grades.head()
print(students_grades)

Output:

Explanation:

In this code example, we have specified the columns by passing the list of columns into the usecols parameter. It will allow users to read the element's value and retrieve the columns defined in the cols list.

Conclusion:

In this article, we have shown two general functions of the Pandas library in Python, the read_excel() and to_excel(), that allow programmers to read and write Excel files into DataFrames. It extracts the existing excel file to read and helps modify the content by writing into the DataFrame.

Also, we can change the behavior of these functions by adding parameters as required. It allows programmers to create customized files instead of just dumping all the contents from the DataFrame.