How to read Excel files using Pandas

Pandas is a commonly used data processing and analysis tool in Python. It provides a series of convenient methods to read and process Excel files. This article will introduce several common methods for Pandas to read Excel files, and provide specific code examples to help readers better understand and apply them.

1. Use Pandas’ read_excel() function to read Excel files
Pandas provides the read_excel() function, which can directly read Excel files and convert them into DataFrame objects. The basic usage of this function is as follows:

import pandas as pd # 读取Excel文件 df = pd.read_excel('filename.xlsx', sheetname='sheet1')

Where, 'filename.xlsx' is the name of the Excel file to be read, which can be a relative path or an absolute path. The sheetname parameter is used to specify the name of the worksheet to be read, which can be a specific worksheet name or index.

For the convenience of demonstration, we create a sample Excel file nameddata.xlsxwith the following content:
Name Age Gender
Zhang San 25 Male
Li Si 30 Female
王五28 Male

Next, we use the read_excel() function to read and print out the data:

import pandas as pd # 读取Excel文件 df = pd.read_excel('data.xlsx', sheetname='Sheet1') # 打印数据 print(df)

The running results are as follows:
Name Age Gender
0 Zhang San 25 Male
1 Li Si 30 Female
2 Wang Wu 28 Male

After reading the Excel file, various data processing and analysis can be performed on the DataFrame object.

2. Read data from multiple worksheets
If an Excel file contains multiple worksheets, you can read data from the specified worksheet by specifying the sheetname parameter. At this time, the read_excel() function will return a dictionary with the worksheet name as the key and the corresponding DataFrame object as the value. An example is as follows:

import pandas as pd # 读取Excel文件的所有工作表 dfs = pd.read_excel('filename.xlsx', sheetname=None) # 打印所有工作表的数据 for sheetname, df in dfs.items(): print(sheetname, ": ", df)

3. Specify column range to read data
Sometimes, we may only want to read part of the column data in the Excel file. At this time, you can limit the range of columns to be read by specifying the usecols parameter. Examples are as follows:

import pandas as pd # 读取Excel文件的指定列范围 df = pd.read_excel('filename.xlsx', usecols='A:C') # 打印数据 print(df)

4. Handling null values
When reading Excel files, you often encounter situations that contain null values. Pandas provides the fillna() function to handle this situation conveniently. An example is as follows:

import pandas as pd # 读取Excel文件并处理空值 df = pd.read_excel('filename.xlsx') df.fillna(value=0, inplace=True) # 打印数据 print(df)

In the above example, the fillna() function is used to fill the null value with 0, and inplace=True means to modify it directly on the original DataFrame object.

The above are several common methods and sample codes for Pandas to read Excel files. Readers can choose the appropriate method according to their own needs to further explore and apply the data processing and analysis functions of Pandas.

The above is the detailed content of How to read Excel files using Pandas. For more information, please follow other related articles on the PHP Chinese website!