掌握读取Excel数据的Python技巧

本文还有配套的精品资源，点击获取

简介：本文深入探讨在IT领域常见的任务——读取Excel文件数据。介绍了使用Python中的 pandas 库来读取Excel文件，并将其数据封装到对象中以便进一步处理。文章展示了如何使用 pandas 中的 read_excel 函数读取数据，并演示了如何将读取的数据映射到自定义的 Employee 类实例中。同时，也涵盖了处理多个工作表和数据清洗的高级技巧。通过这些技能，可以有效提升数据分析、报表生成和业务逻辑处理的效率。

1. 读取Excel文件基本方法

在当今数据驱动的行业中，处理Excel文件已经成为日常工作的一部分。无论是进行数据分析还是数据整理，了解如何有效地读取Excel文件是一个重要的技能。本章节将简要介绍几种基础方法来读取Excel文件，以便为后续更高级的数据处理打下良好的基础。

1.1 使用Python内建库读取Excel文件

在Python中，我们可以使用内建库 xlrd 来读取Excel文件。这个库专门用于打开和读取Excel文件，包括 .xls 和 .xlsx 格式。要使用 xlrd ，首先需要通过pip安装这个库：

pip install xlrd

安装完成后，可以使用以下代码读取Excel文件的基本内容：

import xlrd

# 打开Excel文件
workbook = xlrd.open_workbook('example.xls')

# 选择第一个工作表
sheet = workbook.sheet_by_index(0)

# 读取指定行列的数据
data = sheet.cell_value(0, 0)

print(data)

此代码段展示了如何使用 xlrd 库打开一个Excel文件，并读取第一个工作表中位于第一行第一列的数据。

1.2 读取Excel文件为二维数组

有时，将Excel文件读取为一个二维数组会更加方便进行进一步的数据操作。使用 xlrd 的 sheet_values 方法可以做到这一点：

# 读取整个工作表的数据到二维数组
array = sheet.values()

for row in array:
    print(row)

通过这个方法，我们可以将工作表中的每一行数据读取为数组中的一个元素，并且可以轻松遍历整个工作表的内容。

以上便是读取Excel文件的基本方法，虽然简单，但在实际操作中非常实用。在接下来的章节中，我们将探讨如何使用 pandas 库中更为强大的功能来处理Excel数据。

2. pandas库的read_excel函数使用

2.1 pandas库概述

2.1.1 pandas库的安装和导入

在开始使用pandas库之前，我们需要先确保它已经被安装在我们的Python环境中。pandas依赖于NumPy和一些C语言的库，因此安装pandas之前，建议确保Python环境已经安装了这些库。可以通过下面的命令来安装pandas：

pip install pandas

一旦安装完成，就可以在Python脚本中导入pandas库来使用了。导入pandas库的通常做法是使用别名 pd ，这样可以在代码中以更简便的方式来调用pandas提供的功能。

import pandas as pd

2.1.2 pandas库的数据结构简介

pandas库提供了两个主要的数据结构，分别是Series和DataFrame。Series可以看作是一个一维数组，能够存储任意类型的数据，而DataFrame则是一个二维的表格数据结构，可以被看作是一个表格或者说是多个Series的容器。

• Series : 一维数据结构，可以包含整数、浮点数、字符串、Python对象等类型的数据。每个元素都分配了一个标签（通常是一个整数索引）。

• DataFrame : 二维数据结构，是一个由Series组成的字典，每一个Series可以看作是一个列，而列的标签就是数据表的列名。

我们可以通过pandas提供的构造函数来创建这两个数据结构：

# 创建一个Series
data = pd.Series([1, 2, 3, 4, 5])

# 创建一个DataFrame
data = pd.DataFrame({
    'A': [1, 2, 3],
    'B': [4, 5, 6],
    'C': [7, 8, 9]
})

下面，我们将具体探索pandas库中用于读取Excel文件的核心函数 read_excel ，深入理解它的基本用法以及一些高级参数的使用技巧。

2.2 使用read_excel函数读取Excel文件

2.2.1 read_excel函数的基本用法

read_excel 函数允许用户从Excel文件中读取数据，并将这些数据加载到一个DataFrame对象中。这是一个非常实用的工具，因为在数据分析和处理工作中，处理Excel文件是很常见的场景。

基本的 read_excel 函数调用非常简单。你需要提供文件的路径，然后函数就会返回一个包含Excel文件数据的DataFrame。

# 读取Excel文件
df = pd.read_excel('example.xlsx')

通过这段代码，我们就可以读取位于当前工作目录下名为 example.xlsx 的Excel文件，并将数据存储在变量 df 指向的DataFrame中。

2.2.2 高级参数的使用技巧

read_excel 函数还提供了许多高级参数，允许用户对Excel文件进行更细致的读取控制。以下是一些常见的高级参数：

• sheet_name ：指定要读取的工作表名称或工作表索引。可以是字符串、整数或者字符串列表。

• header ：指定哪一行用作列名。默认情况下，pandas假设第一行是列名。

• skiprows 和 skipfooter ：分别用来跳过文件开头或末尾的若干行，它们可以是整数或整数列表。

• usecols ：指定需要读取的列。可以是列索引的列表，也可以是列名的列表。

• nrows ：读取文件的前n行数据。

下面的例子展示了如何使用 usecols 参数来只读取Excel中的特定列：

# 只读取Excel中的B和C列
df = pd.read_excel('example.xlsx', usecols=['B', 'C'])

为了帮助更好地理解 read_excel 函数的工作原理，让我们通过一个更详细的例子来展示如何使用这些高级参数。

假设我们有一个名为 sales_data.xlsx 的Excel文件，其中包含3个工作表，分别是 January 、 February 和 March 。我们想要读取这三个工作表中的数据，并且只关注 Date 、 Product 和 Total_Sales 这三列数据，同时我们想跳过每个工作表中的第一行。以下是如何使用 read_excel 函数实现这一需求的代码：

# 定义一个字典，映射工作表名称到对应的DataFrame对象
sheet_to_df = {}
for sheet_name in ['January', 'February', 'March']:
    # 使用read_excel函数读取特定列，并跳过第一行
    sheet_to_df[sheet_name] = pd.read_excel(
        'sales_data.xlsx',
        sheet_name=sheet_name,
        usecols=['Date', 'Product', 'Total_Sales'],
        skiprows=1
    )

在这个例子中，我们创建了一个空字典 sheet_to_df 来存储每个工作表对应的DataFrame对象。然后，我们通过循环遍历每个工作表名称，使用 read_excel 函数来读取特定的列，并且跳过每个工作表的第一行。最后，将这些工作表的数据以字典的方式组织起来，便于后续处理和分析。

通过以上内容，我们已经展示了如何使用pandas库的 read_excel 函数来读取Excel文件，并通过高级参数来控制读取过程中的各种细节，使我们能够精确地获取所需的数据。

3. 将Excel数据封装到自定义对象中

3.1 面向对象编程基础

3.1.1 类的定义和对象的创建

在Python中，类可以被理解为一个蓝图，它定义了一组对象共有的方法和属性。类的定义由关键字 class 开始，后接类名和冒号。对象是类的实例，也可以说是一个具体的实体，它被创建自类的定义。我们可以利用类来封装Excel数据，并且可以将Excel中的每一行数据封装为一个对象。在面向对象编程中，数据和操作数据的方法是封装在一起的，这有助于保持代码的组织性和可读性。

下面是一个简单的类定义示例，以及如何创建对象的实例：

class Person:
    def __init__(self, name, age):
        self.name = name
        self.age = age

    def greet(self):
        return f"Hello, my name is {self.name} and I am {self.age} years old."

# 创建一个Person类的对象
person = Person("Alice", 30)

# 调用对象的方法
print(person.greet())  # 输出: Hello, my name is Alice and I am 30 years old.

在上面的例子中， __init__ 方法是一个特殊方法，它在创建类的新实例时自动执行。这个方法用于初始化对象属性，如 name 和 age 。

3.1.2 类的属性和方法

类的属性是指类的特性（即类的对象所拥有的变量），可以理解为对象的状态。而方法则是定义在类中的函数，它描述了对象可以执行的行为。

# 定义一个类，拥有属性和方法
class Book:
    def __init__(self, title, author, pages):
        self.title = title
        self.author = author
        self.pages = pages

    def get_info(self):
        return f"Title: {self.title}, Author: {self.author}, Pages: {self.pages}"

# 创建Book类的对象实例
my_book = Book("1984", "George Orwell", 328)

# 调用对象的方法
print(my_book.get_info())  # 输出: Title: 1984, Author: George Orwell, Pages: 328

在这个 Book 类的例子中， title 、 author 和 pages 是 Book 对象的属性。 get_info 是一个方法，用于输出书的相关信息。

3.2 封装Excel数据到自定义对象

3.2.1 设计自定义对象类

为了将Excel数据封装到自定义对象中，我们需要首先设计一个类，这个类的属性将对应于Excel表格中的一行数据的列。假设我们有一个Excel表格，其中包含员工信息，每行包含员工的姓名、年龄和部门等信息。我们可以设计如下的类：

class Employee:
    def __init__(self, name, age, department):
        self.name = name
        self.age = age
        self.department = department

    def __str__(self):
        return f"Employee(Name: {self.name}, Age: {self.age}, Department: {self.department})"

这里定义了 Employee 类，其构造函数接收三个参数： name 、 age 和 department ，分别对应于Excel表格中的三列。 __str__ 方法用于返回对象的字符串表示形式，当打印对象时，会自动调用此方法。

3.2.2 实现数据封装的方法

一旦我们有了设计好的类，接下来就需要编写代码来读取Excel文件并使用这些数据来创建对象实例。这可以通过使用 pandas 库中的 read_excel 函数来实现，并且通过自定义一个函数来封装数据。

下面的代码展示了如何实现这一过程：

import pandas as pd

# 假设已经导入了Employee类

def create_employee_objects(df):
    employee_objects = []
    for index, row in df.iterrows():
        emp = Employee(name=row['Name'], age=row['Age'], department=row['Department'])
        employee_objects.append(emp)
    return employee_objects

# 读取Excel文件
df_employees = pd.read_excel("employees.xlsx")

# 创建Employee对象列表
employees = create_employee_objects(df_employees)

# 打印信息验证结果
for employee in employees:
    print(employee)

这里， create_employee_objects 函数读取一个 pandas DataFrame，并为每一行数据创建一个 Employee 对象，然后将这些对象添加到一个列表中。最后，我们通过遍历这个列表并打印每个对象的 __str__ 方法输出来验证对象是否正确创建。

通过这种方式，我们可以有效地将Excel数据封装到自定义对象中，使数据更加结构化，并且为后续的数据处理和分析提供了极大的便利。

4. 处理Excel中的多个工作表

4.1 工作表基础概念

4.1.1 工作表的结构和作用

在Excel文件中，工作表是组织和展示数据的主要方式，它由一个网格组成，横行被称为“行”，纵列被称为“列”。每个单元格都有一个独特的地址，格式如"A1"。这种结构为数据的录入、分析和可视化提供了极大的便利。工作表可以包含不同的数据类型，如文本、数字、日期等，同时还支持图表、公式和宏等高级功能。

工作表的作用在于其能够将数据以表格形式组织起来，便于进行各种数据操作，如分类汇总、排序筛选、数据分析等。多个工作表通常用于分别存储不同的数据集或数据视图，例如，在财务报告中，不同的工作表可以用于记录收入、支出、资产负债等信息。

4.1.2 多个工作表的访问方法

在Excel文件中，多个工作表可以通过底部的标签切换访问。而当我们使用pandas处理Excel文件时，可以通过 pandas.ExcelFile 对象的 sheet_name 参数来指定需要访问的工作表。例如，可以使用以下代码访问特定的工作表：

import pandas as pd

# 加载Excel文件
xl = pd.ExcelFile('example.xlsx')

# 访问名为'Sheet1'的工作表
df_sheet1 = pd.read_excel(xl, 'Sheet1')

4.2 遍历和处理多个工作表

4.2.1 使用循环遍历工作表

在处理具有多个工作表的Excel文件时，我们经常需要对每个工作表执行相同的数据处理步骤。使用 pandas.ExcelFile 和Python的for循环可以轻松实现这一点。以下是一个示例，展示了如何遍历所有工作表，并将每个工作表的数据转换为DataFrame：

import pandas as pd

# 加载Excel文件
xl = pd.ExcelFile('example.xlsx')

# 遍历所有工作表
for sheet_name in xl.sheet_names:
    df = pd.read_excel(xl, sheet_name=sheet_name)
    print(f"已处理工作表：{sheet_name}")
    # 在此处添加进一步的数据处理代码

4.2.2 工作表数据的筛选和汇总

在处理多个工作表的数据时，经常需要根据某些条件筛选特定的数据，并进行汇总统计。例如，我们可能需要统计每个工作表中特定列的平均值、总和等。以下是使用 groupby 方法对工作表中的数据进行分组汇总的示例代码：

import pandas as pd

# 加载Excel文件
xl = pd.ExcelFile('example.xlsx')

# 遍历所有工作表
all_data = []
for sheet_name in xl.sheet_names:
    df = pd.read_excel(xl, sheet_name=sheet_name)
    # 筛选和汇总数据
    df_summary = df.groupby('Category').agg({'Value': ['mean', 'sum']})
    df_summary['Sheet'] = sheet_name  # 添加工作表名称作为新列
    all_data.append(df_summary)

# 合并所有工作表的数据
summary = pd.concat(all_data).reset_index()
print(summary)

在上述代码中，我们创建了一个空列表 all_data 用于存储每个工作表的汇总结果，然后使用 groupby 方法根据类别进行分组，并计算每个类别的平均值和总和。最后，我们将工作表名称作为新列添加到汇总结果中，并使用 concat 方法将所有工作表的汇总结果合并为一个单一的DataFrame。

通过本章节的介绍，我们了解了如何使用pandas库处理Excel文件中的多个工作表，并通过实际的代码示例展示了遍历工作表和数据筛选汇总的基本方法。在实际工作中，根据具体需求对这些基础方法进行扩展和优化将大大提升数据处理的效率和准确性。

5. 数据清洗技巧（处理缺失值、类型转换）

5.1 数据清洗的重要性

5.1.1 数据清洗的目的和意义

数据清洗是数据分析中一个至关重要的步骤，其目的在于提高数据质量，为后续的数据分析、数据挖掘和数据建模打下坚实的基础。未经清洗的数据常常包含错误、重复、缺失值、异常值和不一致性，这些都会严重影响数据分析的结果，甚至可能导致错误的结论。

数据清洗的过程包括但不限于处理缺失值、纠正错误、删除或填充异常值、类型转换、以及去除重复记录等。良好的数据清洗可以减少数据处理和分析过程中的错误，提高数据处理的效率，确保分析结果的准确性和可靠性。

5.1.2 常见的数据质量问题

在数据收集和存储的过程中，数据质量问题几乎是不可避免的。常见的数据质量问题包括：

• 缺失值：数据集中某些字段的值可能是空的，这可能是由于数据未被输入、数据丢失或数据提取错误导致的。
• 异常值：数据集中可能存在一些极端值，它们与其他数据相比显得非常不同，可能是由于错误记录或自然变异导致的。
• 数据不一致：相同的信息可能在不同地方以不同的格式或拼写出现，如日期格式不统一、大小写不一致等。
• 数据类型错误：数据类型可能与字段定义不匹配，如字符串代替日期或数字等。
• 重复记录：同一个实体的信息可能被多次记录，导致数据重复。

5.2 缺失值和类型转换的处理

5.2.1 缺失值的识别和处理方法

在Excel和pandas中，缺失值通常用NaN（Not a Number）表示。识别缺失值的方法包括：

在pandas中，可以使用 isnull() 和 notnull() 方法来识别缺失值：

import pandas as pd

# 读取Excel文件
df = pd.read_excel('data.xlsx')

# 识别缺失值
missing_values = df.isnull()

处理缺失值的方法有很多，可以根据实际情况选择：

• 删除含有缺失值的行或列
• 用某个固定值填充缺失值，例如0或者众数
• 用某一字段的均值或中位数填充该字段的缺失值
• 使用插值法填充缺失值

pandas提供了一个非常方便的函数 fillna() 来进行缺失值的填充：

# 删除含有缺失值的行
df_cleaned = df.dropna()

# 用0填充所有缺失值
df_filled = df.fillna(0)

# 用某列的均值填充缺失值
mean_value = df['column_name'].mean()
df_filled = df.fillna(mean_value)

5.2.2 数据类型的转换和规范

数据类型转换是确保数据能够正确进行后续处理的关键步骤。在pandas中，常见的数据类型包括 int64 （整数）、 float64 （浮点数）、 bool （布尔值）、 datetime64 （日期时间）和 object （字符串等）。

识别数据类型可以使用 dtypes 属性：

# 识别每列的数据类型
data_types = df.dtypes

将数据转换为特定类型可以使用不同的转换函数，例如：

# 将字符串转换为日期时间格式
df['date_column'] = pd.to_datetime(df['date_column'])

# 将某列转换为数值类型
df['numeric_column'] = pd.to_numeric(df['numeric_column'])

规范数据类型可能需要结合业务逻辑，例如：

• 确保日期时间格式的统一，比如YYYY-MM-DD。
• 保证字符串格式的一致性，比如所有邮箱地址都应是小写字母。
• 将字符串类型转换为枚举类型（分类变量），便于数据分析。

正确处理缺失值和数据类型转换不仅能提升数据质量，而且能为数据分析提供更加准确和高效的基础。在实际操作中，根据数据集的规模和复杂度，可能还需要采取其他数据清洗的策略和方法。

6. pandas数据结构DataFrame的应用

数据处理是数据分析的核心部分，而pandas库中的DataFrame是处理表格数据的强大工具。本章将深入探讨DataFrame的结构和特性，并展示其在数据处理中的实际应用。

6.1 DataFrame的结构和特性

6.1.1 DataFrame的创建和属性

DataFrame是由行和列组成的二维数据结构，类似于Excel表。创建DataFrame之前，我们需要导入pandas库。

import pandas as pd

# 创建一个简单的DataFrame
data = {
    'Name': ['John', 'Anna', 'Peter', 'Linda'],
    'Location': ['New York', 'Paris', 'Berlin', 'London'],
    'Age': [24, 13, 53, 33]
}
df = pd.DataFrame(data)
print(df)

DataFrame的创建非常直观，可以由字典、列表或NumPy数组等多种形式的数据源生成。它具有多个属性，例如：

• shape : 返回DataFrame的维度（行数和列数）
• dtypes : 返回每列的数据类型
• head() : 返回前几行数据，默认为前5行

6.1.2 DataFrame的操作和函数应用

一旦创建了DataFrame，就可以对其执行各种操作。例如，对数据进行排序、选择特定列、过滤数据等。一些有用的函数包括：

• df.sort_values(by='列名') : 根据某列对数据进行排序
• df['列名'] : 选择DataFrame中的某一列
• df.loc : 通过标签选择数据

# 按年龄排序
sorted_df = df.sort_values(by='Age')
print(sorted_df)

# 选择Location列
locations = df['Location']
print(locations)

# 使用loc选择特定行和列
subset = df.loc[1:3, 'Name':'Age']
print(subset)

6.2 DataFrame在数据处理中的应用

6.2.1 数据选择和过滤技巧

在数据分析过程中，经常需要从大量数据中选择或过滤出需要的信息。DataFrame提供了多种方法来实现这一点。

• 使用条件表达式进行过滤：

# 过滤出年龄大于30岁的人员
filtered_df = df[df['Age'] > 30]
print(filtered_df)

• 使用 .query() 方法：

# 使用query方法过滤数据
filtered_query = df.query('Age > 30')
print(filtered_query)

6.2.2 数据聚合和分组操作

聚合函数可以对数据集执行数学计算。DataFrame的 groupby 方法可以按照一列或多列对数据进行分组，然后应用聚合函数。

# 计算每个不同地点的平均年龄
grouped = df.groupby('Location')['Age'].mean()
print(grouped)

6.2.3 数据合并和连接技术

在数据分析中，经常需要将多个数据集合并在一起。DataFrame提供了多种合并和连接的方法。

• 使用 merge 函数合并数据：

# 假设有一个额外的DataFrame包含工作信息
work_data = {
    'Name': ['John', 'Anna', 'Peter', 'Linda'],
    'Job': ['Engineer', 'Teacher', 'Architect', 'Doctor']
}
work_df = pd.DataFrame(work_data)

# 将df和work_df按照Name列合并
merged_df = pd.merge(df, work_df, on='Name')
print(merged_df)

• 使用 concat 函数连接数据：

# 将df与自身连接
concatenated_df = pd.concat([df, df], ignore_index=True)
print(concatenated_df)

通过这些方法，我们能够对数据进行复杂的处理和分析，从而揭示数据之间的关系和趋势。

本章展示了DataFrame的基础知识和应用技巧，为数据分析和处理提供了强大的工具。在下一章节中，我们将继续深入探讨数据处理的其他高级主题。

本文还有配套的精品资源，点击获取

简介：本文深入探讨在IT领域常见的任务——读取Excel文件数据。介绍了使用Python中的 pandas 库来读取Excel文件，并将其数据封装到对象中以便进一步处理。文章展示了如何使用 pandas 中的 read_excel 函数读取数据，并演示了如何将读取的数据映射到自定义的 Employee 类实例中。同时，也涵盖了处理多个工作表和数据清洗的高级技巧。通过这些技能，可以有效提升数据分析、报表生成和业务逻辑处理的效率。

本文还有配套的精品资源，点击获取