Python读取Excel教程：pandas库使用详解

使用pandas读取Excel文件的核心方法是pd.read_excel()函数，它支持多种参数配置以应对复杂结构。1. 通过sheet_name参数可指定工作表名称或索引，支持读取单个、多个或全部工作表，返回DataFrame或字典；2. header参数设置表头行，index_col指定索引列，usecols控制加载的列范围；3. dtype用于强制指定列数据类型，na_values识别自定义缺失值，parse_dates解析日期列。对于大型文件优化：1. usecols限制加载列；2. dtype选择更节省内存的数据类型；3. 可选engine指定底层解析引擎；4. 必要时转换为CSV并使用分块读取处理超大数据。

用Python读取Excel文件，最直接也最强大的方法就是使用pandas库。它封装了底层文件操作的复杂性，让我们能像操作数据表一样轻松地导入和处理Excel数据。简单来说，就是一行代码的事儿，但背后能玩的花样可不少。

使用pandas.read_excel()函数是核心。这个函数设计得相当灵活，能够处理各种复杂的Excel文件结构。最基本的用法，你只需要传入Excel文件的路径：

import pandas as pd

# 假设你的Excel文件名为 'my_data.xlsx' 并且在当前目录下
try:
    df = pd.read_excel('my_data.xlsx')
    print("成功读取Excel文件！前5行数据：")
    print(df.head())
except FileNotFoundError:
    print("文件未找到，请检查文件路径和名称是否正确。")
except Exception as e:
    print(f"读取Excel时发生错误: {e}")

# 路径示例：
# 绝对路径：df = pd.read_excel('/Users/yourname/Documents/my_data.xlsx')
# 相对路径：df = pd.read_excel('./data/my_data.xlsx')

这只是个开始。实际工作中，Excel文件往往不是那么规规矩矩。比如，数据可能不在第一个工作表，或者表头不在第一行，甚至某些列根本不需要。read_excel函数提供了一系列参数来应对这些场景：

• sheet_name: 这个参数非常有用。默认是读取第一个工作表（索引为0）。你可以指定工作表的名称（字符串），或者索引（整数）。如果你想读多个工作表，可以传入一个列表，甚至传入None来读取所有工作表。
• header: 默认是0，表示将第一行作为列名。如果你的数据没有表头，或者表头在第N行，你可以设置为None或对应的行索引。
• index_col: 指定哪一列作为DataFrame的索引。
• usecols: 如果你只关心Excel中的部分列，这个参数能帮你只加载指定的列，省时省力，尤其是在处理大型文件时。可以传入列的名称列表，或者列的索引列表。
• dtype: 有时候Excel里的数字被当成了字符串，或者日期格式不对劲。dtype允许你强制指定某些列的数据类型，避免导入后的类型混乱。
• na_values: Excel里表示缺失值的方式五花八门，比如“N/A”、“-”、“空”。na_values可以指定一个列表，告诉pandas把这些值识别为NaN（Not a Number）。

举个例子，如果你的数据在名为“销售数据”的工作表里，并且表头在第三行（索引为2），你可能会这么写：

df_sales = pd.read_excel('my_complex_data.xlsx', sheet_name='销售数据', header=2)
print("\n读取指定工作表和表头后的数据：")
print(df_sales.head())

这些参数的组合使用，几乎能满足你所有从Excel读取数据的需求。

如何处理Excel文件中多个工作表（Sheet）的数据？

Excel文件往往不是一个平面结构，它能包含好几个工作表。当我们用Python去读它时，怎么把这些散落在不同“页签”里的数据都抓出来，就成了个挺常见的问题。

read_excel的sheet_name参数是这里的关键。前面提过，它默认是0（第一个工作表），但它远不止如此。

1. 读取特定名称的工作表： 如果你知道工作表的具体名字，比如“2023年销售额”，直接传字符串就行：

   df_2023 = pd.read_excel('yearly_sales.xlsx', sheet_name='2023年销售额')
   print("\n读取 '2023年销售额' 工作表：")
   print(df_2023.head())

   这很直观，也最常用。

2. 读取特定索引的工作表： 如果你更习惯用索引（从0开始），或者工作表名字不固定，可以用整数：

   df_second_sheet = pd.read_excel('yearly_sales.xlsx', sheet_name=1) # 读取第二个工作表
   print("\n读取第二个工作表：")
   print(df_second_sheet.head())

   这在自动化处理时，比如你知道文件里第二个sheet总是汇总数据，会很方便。

3. 同时读取多个指定工作表： 这是个很酷的功能。把你想读的那些工作表的名称（或索引）放到一个列表里传给sheet_name。

   # 读取 '2022年销售额' 和 '2023年销售额'
   dfs_selected = pd.read_excel('yearly_sales.xlsx', sheet_name=['2022年销售额', '2023年销售额'])
   print("\n同时读取多个指定工作表（返回字典）：")
   # 这时候返回的不是一个DataFrame，而是一个字典，键是工作表名，值是对应的DataFrame
   for sheet_name, df in dfs_selected.items():
       print(f"\n--- 工作表: {sheet_name} ---")
       print(df.head())

   你会得到一个字典，每个键是工作表的名字，对应的值就是那个工作表的数据框。这种结构很适合后续的循环处理。

4. 读取所有工作表： 如果你想把Excel里所有工作表的数据都导出来，sheet_name=None是你的朋友。

   dfs_all = pd.read_excel('yearly_sales.xlsx', sheet_name=None)
   print("\n读取所有工作表（返回字典）：")
   for sheet_name, df in dfs_all.items():
       print(f"\n--- 工作表: {sheet_name} ---")
       print(df.head())

   同样，这也会返回一个字典，包含所有工作表的数据。这种方法在需要对整个Excel文件进行全面分析时非常有用。

处理多个工作表，关键在于理解sheet_name参数的不同输入类型，以及它返回值的变化（单个DataFrame或字典）。掌握了这些，你就能灵活地从复杂的Excel结构中提取所需数据。

读取Excel时遇到常见的数据类型问题和缺失值如何处理？

数据导入后的类型不对，或者缺失值处理不当，是数据分析中常遇到的“坑”。Excel本身对数据类型不像数据库那么严格，经常会出现数字被存成文本，或者日期格式混乱的情况。pandas提供了几个参数来帮你提前“纠正”这些问题。

1. 数据类型强制转换：dtype参数 想象一下，Excel里某一列全是数字，但因为某个单元格不小心多敲了个空格，或者被格式化成了文本，pandas读进来就成了object（字符串）。这时候你做数值计算就会报错。dtype就能派上用场。 你可以传入一个字典，键是列名，值是你希望的数据类型（比如int, float, str, bool等）。

   # 假设 'my_data.xlsx' 中有一列 '订单号' 实际是数字，但可能被识别为字符串
   # 还有一列 '价格' 应该是浮点数
   df_typed = pd.read_excel('my_data.xlsx', dtype={'订单号': str, '价格': float})
   print("\n指定数据类型后的DataFrame信息：")
   print(df_typed.info())
   # 甚至可以指定更精确的类型，比如 int64, float32 来节省内存
   # df_typed_optimized = pd.read_excel('my_data.xlsx', dtype={'销量': 'int16', '利润率': 'float32'})

   提前指定dtype的好处是，它会在读取阶段就尝试转换，如果转换失败（比如文本无法转为数字），会抛出错误，让你能及时发现问题所在，而不是等到后续计算时才懵圈。

2. 自定义缺失值识别：na_values参数 Excel里表示“空”或者“没有数据”的方式真是五花八门。除了真正的空白单元格，你可能还会看到“N/A”、“-”、“无数据”、“NaN”甚至一些奇怪的字符。na_values参数允许你传入一个列表，告诉pandas这些字符串都应该被识别为NaN。

   # 假设Excel里用 '无' 和 '-' 来表示缺失值
   df_missing = pd.read_excel('my_data.xlsx', na_values=['无', '-'])
   print("\n处理自定义缺失值后的数据：")
   print(df_missing)
   print("\n缺失值统计：")
   print(df_missing.isnull().sum())

   这样，你就不需要手动去替换这些五花八门的缺失值表示了，pandas会帮你统一处理。

3. 日期时间解析：parse_dates参数 日期列也是个老大难。Excel里的日期格式千变万化，pandas默认可能无法正确识别。parse_dates参数可以接收一个列表，指定哪些列应该被解析为日期时间类型。

   # 假设 'my_data.xlsx' 中有一列 '交易日期'
   df_dates = pd.read_excel('my_data.xlsx', parse_dates=['交易日期'])
   print("\n日期列解析后的DataFrame信息：")
   print(df_dates.info())
   print("\n日期列数据：")
   print(df_dates['交易日期'].head())

   如果日期格式特别复杂，read_excel可能还是会有点吃力，但对于大部分标准格式，parse_dates都能很好地工作。实在不行，你也可以先按字符串读入，再用pd.to_datetime()进行更精细的转换。

这些参数的灵活运用，能极大提升你数据导入的质量和效率，减少后续数据清洗的麻烦。

读取大型Excel文件时，有哪些性能优化和内存管理技巧？

处理动辄几十万、上百万行的大型Excel文件，直接用read_excel可能会遇到内存不足或者读取速度奇慢的问题。这时候，一些优化策略就显得尤为重要。

1. 只读取必要的列：usecols参数 这是最直接也最有效的优化手段。如果你的Excel文件有几十上百列，但你实际分析只需要其中几列，那么只加载这几列能显著减少内存占用和读取时间。

   # 假设 'large_data.xlsx' 有很多列，但你只需要 'ID', '名称', '数值' 这三列
   df_partial = pd.read_excel('large_data.xlsx', usecols=['ID', '名称', '数值'])
   print("\n仅读取部分列后的DataFrame信息：")
   print(df_partial.info())
   # 或者通过列索引：usecols=[0, 2, 5]

   减少加载的数据量，是优化大型文件处理的首要原则。

2. 精确控制数据类型：dtype参数 前面提过dtype用于纠正数据类型，它同时也是内存优化的利器。pandas默认会为数值列分配int64或float64这样的数据类型，它们占用的内存较大。如果你知道某列的数值范围，可以指定更小的类型，比如：

   • 整数：int8 (-128到127), int16 (-32768到32767), int32 (约±20亿)
   • 浮点数：float32 (精度较低，但占用内存少一半)
   • 字符串：如果某一列的字符串种类有限（比如性别、省份），可以转换为category类型，能大幅节省内存。

     df_optimized_mem = pd.read_excel(
     'large_data.xlsx',
     dtype={
         '用户ID': 'int32',
         '订单金额': 'float32',
         '产品类别': 'category', # 适合重复值多的字符串列
         '是否完成': 'bool'
     }
     )
     print("\n优化数据类型后的DataFrame信息：")
     print(df_optimized_mem.info(memory_usage='deep')) # 查看详细内存使用

     这需要你对数据有一定的预判，但效果通常非常显著。

3. 选择合适的引擎：engine参数read_excel在底层会使用不同的库来解析Excel文件。默认情况下，对于.xlsx文件会使用openpyxl，对于.xls文件会使用xlrd。通常情况下，你不需要手动指定。但如果遇到特定问题或者想尝试不同的性能表现，可以手动切换：

   # 强制使用 openpyxl 引擎（通常用于 .xlsx 文件）
   df_engine_openpyxl = pd.read_excel('large_data.xlsx', engine='openpyxl')
   
   # 强制使用 xlrd 引擎（通常用于 .xls 文件，但需要单独安装，且对 .xlsx 支持有限）
   # df_engine_xlrd = pd.read_excel('old_data.xls', engine='xlrd')

   在大多数情况下，默认引擎表现良好，除非你遇到非常规的Excel文件或特定错误，才需要考虑调整这个参数。

4. 分块读取（针对CSV/文本文件更常见，Excel有限） 虽然read_excel不像read_csv那样直接支持chunksize参数进行分块读取，但对于特别巨大的Excel文件，你可以考虑将其转换为CSV格式（Excel本身就可以另存为CSV），然后使用pd.read_csv的chunksize参数进行迭代处理。这在处理内存无法一次性加载的数据时非常有效。

   如果必须处理巨型Excel，且无法转CSV，可以考虑使用openpyxl或xlrd库的底层API，它们允许你逐行或逐单元格读取，但这样会丢失pandas的便利性，代码复杂度也会大大增加。通常，前面提到的usecols和dtype已经能解决大部分性能问题了。

优化大型文件读取，本质上就是减少不必要的数据加载，并以最高效的方式存储必要的数据。这些技巧能让你在处理大数据时更加游刃有余。