Python高效处理Excel文件的12个Pandas函数总结_Python

1.“pd.read_excel()”- 读取excel文件

读取excel文件到dataframe中，支持多种参数控制读取方式。

import pandas as pd

# 基本读取
df = pd.read_excel('data.xlsx')

# 读取特定工作表
df = pd.read_excel('data.xlsx', sheet_name='sheet1')

# 读取特定列
df = pd.read_excel('data.xlsx', usecols=['name', 'age'])

# 读取特定行范围
df = pd.read_excel('data.xlsx', nrows=100)  # 只读取前100行

2.“dataframe.to_excel()”- 写入excel文件

将dataframe写入excel文件，支持多种格式控制。

# 基本写入
df.to_excel('output.xlsx')

# 不写入索引
df.to_excel('output.xlsx', index=false)

# 写入特定工作表
with pd.excelwriter('output.xlsx') as writer:
    df.to_excel(writer, sheet_name='sheet1')

# 格式化输出
with pd.excelwriter('formatted.xlsx', engine='xlsxwriter') as writer:
    df.to_excel(writer, sheet_name='sheet1')
    workbook = writer.book
    worksheet = writer.sheets['sheet1']
    
    # 设置列宽
    worksheet.set_column('a:a', 20)
    
    # 添加格式
    format1 = workbook.add_format({'bold': true, 'font_color': 'red'})
    worksheet.write(0, 0, 'important header', format1)

3. “pd.excelfile”- 处理多个工作表

高效处理包含多个工作表的excel文件。

with pd.excelfile('multi_sheet.xlsx') as xls:
    # 获取所有工作表名
    sheet_names = xls.sheet_names
    
    # 读取特定工作表
    df1 = pd.read_excel(xls, sheet_names[0])
    df2 = pd.read_excel(xls, sheet_names[1])
    
    # 或者使用字典存储所有工作表
    all_sheets = {sheet: pd.read_excel(xls, sheet) for sheet in sheet_names}

4.“dataframe.head()”- 查看前几行

快速查看dataframe的前几行数据。

# 查看前5行（默认）
print(df.head())

# 查看前10行
print(df.head(10))

5.“dataframe.tail()”- 查看后几行

快速查看dataframe的后几行数据。

# 查看后5行（默认）
print(df.tail())

# 查看后3行
print(df.tail(3))

6.“dataframe.info()”- 查看数据概览

获取dataframe的摘要信息，包括列数据类型和内存使用情况。

df.info()

7. “dataframe.describe()”- 统计摘要

生成描述性统计信息，包括计数、均值、标准差、最小值、四分位数和最大值。

# 对所有数值列生成统计信息
print(df.describe())

# 对特定列生成统计信息
print(df['age'].describe())

# 包含非数值列
print(df.describe(include='all'))

8. “dataframe.dropna()”- 处理缺失值

删除包含缺失值的行或列。

# 删除包含任何缺失值的行
df_clean = df.dropna()

# 删除包含任何缺失值的列
df_clean = df.dropna(axis=1)

# 只删除全为缺失值的行
df_clean = df.dropna(how='all')

# 删除在特定列中包含缺失值的行
df_clean = df.dropna(subset=['age', 'salary'])

9. “dataframe.fillna()”- 填充缺失值

用指定值或方法填充缺失值。

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

# 用前一个有效值填充（向前填充）
df_filled = df.fillna(method='ffill')

# 用后一个有效值填充（向后填充）
df_filled = df.fillna(method='bfill')

# 用列均值填充
df_filled = df.fillna(df.mean())

# 对不同列使用不同的填充值
fill_values = {'age': df['age'].median(), 'department': 'unknown'}
df_filled = df.fillna(fill_values)

10. “dataframe.groupby()”- 分组聚合

根据一个或多个键对数据进行分组，然后对每个组应用聚合函数。

# 基本分组
grouped = df.groupby('department')

# 计算每个部门的平均工资
avg_salary = grouped['salary'].mean()

# 多列分组
multi_grouped = df.groupby(['department', 'gender'])

# 多种聚合操作
agg_result = multi_grouped['salary'].agg(['mean', 'median', 'count'])

# 分组后应用多个函数
result = df.groupby('department').agg({'salary': ['mean', 'max'],'age': 'min'})

11.“dataframe.merge()”- 合并数据

基于共同列合并两个dataframe。

# 创建示例dataframe
df1 = pd.dataframe({
    'employeeid': [1, 2, 3],
    'name': ['alice', 'bob', 'charlie']})

df2 = pd.dataframe({
    'employeeid': [1, 2, 4],
    'salary': [70000, 80000, 90000]})

# 内连接（默认）
merged = pd.merge(df1, df2, on='employeeid')

# 左连接
left_merged = pd.merge(df1, df2, on='employeeid', how='left')

# 右连接
right_merged = pd.merge(df1, df2, on='employeeid', how='right')

# 外连接
outer_merged = pd.merge(df1, df2, on='employeeid', how='outer')

# 合并多个键
df3 = pd.dataframe({
    'employeeid': [1, 1, 2],
    'date': ['2022-01-01', '2022-02-01', '2022-01-15'],
    'performance': [4.5, 4.7, 3.9]})

merged_multi = pd.merge(df1, df3, on='employeeid')

12.“dataframe.apply()”- 应用自定义函数

对dataframe的行或列应用自定义函数。

# 创建示例dataframe
df = pd.dataframe({
    'name': ['alice', 'bob', 'charlie'],
    'age': [25, 30, 35],
    'salary': [50000, 60000, 70000]})

# 对列应用函数 - 计算奖金（薪资的10%）
df['bonus'] = df['salary'].apply(lambda x: x * 0.1)

# 对行应用函数 - 创建全名
df['full_name'] = df.apply(lambda row: f"{row['name']} ({row['age']})", axis=1)

# 定义更复杂的函数
def calculate_tax(salary, age):
    if age > 30:
        return salary * 0.2
    else:
        return salary * 0.15

df['tax'] = df.apply(lambda row: calculate_tax(row['salary'], row['age']), axis=1)

# 使用applymap对每个元素应用函数（适用于元素级操作）
df = df.applymap(lambda x: str(x).upper() if isinstance(x, str) else x)

额外技巧：处理大文件

对于大型excel文件，可以使用chunksize参数分块读取：

# 分块读取大文件
chunk_size = 10000  # 每次读取的行数
chunks = pd.read_excel('large_file.xlsx', chunksize=chunk_size)

# 处理每个块
for i, chunk in enumerate(chunks):
    # 处理数据
    processed_chunk = process_data(chunk)
    
    # 写入输出文件（追加模式）
    if i == 0:
        processed_chunk.to_excel('output.xlsx', index=false)
    else:
        with pd.excelwriter('output.xlsx', mode='a', engine='openpyxl') as writer:
            processed_chunk.to_excel(writer, sheet_name=f'chunk_{i}', index=false)

这些函数组合使用可以高效完成excel数据的读取、清洗、分析和输出任务。根据具体需求选择合适的函数和方法，可以显著提高数据处理效率