Python自动化办公之使用Pandas玩转Excel数据处理全攻略

pip install pandas openpyxl

import pandas as pd

# 定义excel文件路径
file_path = '员工信息.xlsx'

# 读取整个excel文件的第一个工作表
df = pd.read_excel(file_path)
print("------ 原始数据框 ------")
print(df)

# 读取指定工作表 (如果excel文件有多个工作表，例如：sheet_name='sheet2')
# df_sheet2 = pd.read_excel(file_path, sheet_name='sheet2')
# print("\n------ sheet2 数据框 ------")
# print(df_sheet2)

# 读取时指定某一列作为索引 (例如：员工id作为索引)
# df_indexed = pd.read_excel(file_path, index_col='员工id')
# print("\n------ 以员工id为索引的数据框 ------")
# print(df_indexed)

print("\n------ 查看数据概况 ------")

# 查看前5行数据 (默认值)
print("\n前5行数据:")
print(df.head())

# 查看后3行数据
print("\n后3行数据:")
print(df.tail(3))

# 查看数据框的维度 (行数, 列数)
print("\n数据框维度 (行数, 列数):", df.shape)

# 查看所有列名
print("\n所有列名:", df.columns.tolist())

# 查看数据框的简要信息，包括列名、非空值数量、数据类型和内存占用
print("\n数据框信息:")
df.info()

# 查看数值列的描述性统计信息 (计数、均值、标准差、最小值、25/50/75分位数、最大值)
print("\n数值列描述性统计:")
print(df.describe())

# 查看所有列的描述性统计信息 (包括非数值列)
print("\n所有列描述性统计:")
print(df.describe(include='all'))

print("\n------ 选择数据 ------")

# 选择单列：返回一个series
print("\n选择'姓名'列:")
print(df['姓名'])

# 选择多列：返回一个dataframe
print("\n选择'姓名'和'工资'列:")
print(df[['姓名', '工资']])

# 使用.loc[]按标签选择行和列 (loc[行标签, 列标签])
# 选择第0行到第2行 (包含) 的所有列
print("\n使用.loc[]选择前3行:")
print(df.loc[0:2])

# 选择'张三'和'李四'的'姓名'和'部门' (需要先将姓名设为索引)
# df_indexed_name = df.set_index('姓名')
# print("\n以姓名为索引，选择'张三'和'李四'的'姓名'和'部门':")
# print(df_indexed_name.loc[['张三', '李四'], ['部门', '职位']])

# 使用.iloc[]按位置选择行和列 (iloc[行位置, 列位置])
# 选择第0行到第2行 (不包含) 的所有列
print("\n使用.iloc[]选择前3行:")
print(df.iloc[0:3])

# 选择第0行和第2行，以及第1列和第3列
print("\n使用.iloc[]选择特定行和列 (0,2行；1,3列):")
print(df.iloc[[0, 2], [1, 3]]) # 对应 '姓名', '职位'

print("\n------ 数据清洗 ------")

# 检查缺失值
print("\n各列缺失值数量:")
print(df.isnull().sum())

# 填充缺失值 (例如：职位缺失填充为'待定', 绩效等级缺失填充为'd')
df_filled = df.copy() # 创建副本，避免修改原始dataframe
df_filled['职位'].fillna('待定', inplace=true)
df_filled['绩效等级'].fillna('d', inplace=true)
print("\n填充缺失值后的数据:")
print(df_filled)

# 删除包含缺失值的行
df_dropna_rows = df.dropna()
print("\n删除包含缺失值的行后的数据:")
print(df_dropna_rows)

# 删除包含缺失值的列 (如果某一列的缺失值过多)
# df_dropna_cols = df.dropna(axis=1)
# print("\n删除包含缺失值的列后的数据:")
# print(df_dropna_cols)

# 检查重复行
print("\n重复行数量:", df.duplicated().sum())

# 删除重复行
# 为了演示，我们先创建一个有重复行的dataframe
data_with_duplicates = {
    'a': [1, 2, 2, 3, 4],
    'b': ['x', 'y', 'y', 'z', 'w']
}
df_dup = pd.dataframe(data_with_duplicates)
print("\n原始带重复值数据:\n", df_dup)
df_no_dup = df_dup.drop_duplicates()
print("\n删除重复值后的数据:\n", df_no_dup)

# 数据类型转换 (例如：将'工资'列转换为整数类型)
# 注意：如果列中包含非数字值，转换会报错，需要先处理
df_cleaned = df_filled.copy() # 使用填充过缺失值的df
df_cleaned['工资'] = df_cleaned['工资'].astype(int)
print("\n'工资'列数据类型转换为int后的数据信息:")
df_cleaned.info()

print("\n------ 数据排序 ------")

# 按'工资'列升序排序
df_sorted_salary_asc = df_cleaned.sort_values(by='工资', ascending=true)
print("\n按'工资'升序排序:")
print(df_sorted_salary_asc)

# 按'入职日期'降序排序
df_sorted_date_desc = df_cleaned.sort_values(by='入职日期', ascending=false)
print("\n按'入职日期'降序排序:")
print(df_sorted_date_desc)

# 多列排序：先按'部门'升序，再按'工资'降序
df_sorted_multi = df_cleaned.sort_values(by=['部门', '工资'], ascending=[true, false])
print("\n按'部门'升序，'工资'降序排序:")
print(df_sorted_multi)

print("\n------ 数据筛选 ------")

# 筛选'部门'为'销售部'的员工
sales_employees = df_cleaned[df_cleaned['部门'] == '销售部']
print("\n'销售部'的员工:")
print(sales_employees)

# 筛选'工资'大于等于8000的员工
high_salary_employees = df_cleaned[df_cleaned['工资'] >= 8000]
print("\n工资大于等于8000的员工:")
print(high_salary_employees)

# 多条件筛选：'部门'为'市场部' 且 '绩效等级'为'a'的员工
market_a_employees = df_cleaned[(df_cleaned['部门'] == '市场部') & (df_cleaned['绩效等级'] == 'a')]
print("\n'市场部'且'绩效等级'为'a'的员工:")
print(market_a_employees)

# 多条件筛选：'部门'为'销售部' 或 '部门'为'研发部'的员工
sales_or_rd_employees = df_cleaned[(df_cleaned['部门'] == '销售部') | (df_cleaned['部门'] == '研发部')]
print("\n'销售部'或'研发部'的员工:")
print(sales_or_rd_employees)

# 使用.isin()筛选：'部门'在列表['销售部', '市场部']中的员工
selected_departments = df_cleaned[df_cleaned['部门'].isin(['销售部', '市场部'])]
print("\n部门在['销售部', '市场部']中的员工:")
print(selected_departments)

# 筛选包含特定字符串的行 (例如：职位包含'经理')
# 注意：使用.str访问字符串方法
manager_employees = df_cleaned[df_cleaned['职位'].str.contains('经理', na=false)] # na=false 处理 nan 值
print("\n职位包含'经理'的员工:")
print(manager_employees)

print("\n------ 应用函数 ------")

# 对单列应用函数：例如，计算年终奖 (假设为月工资的2倍)
df_processed = df_cleaned.copy()
df_processed['年终奖'] = df_processed['工资'] * 2
print("\n添加'年终奖'列:")
print(df_processed[['姓名', '工资', '年终奖']])

# 使用apply()对单列应用自定义函数 (例如：将绩效等级转换为数值)
def convert_performance(grade):
    if grade == 'a':
        return 3
    elif grade == 'b':
        return 2
    elif grade == 'c':
        return 1
    else: # d或其他
        return 0

df_processed['绩效分数'] = df_processed['绩效等级'].apply(convert_performance)
print("\n添加'绩效分数'列:")
print(df_processed[['姓名', '绩效等级', '绩效分数']])

# 使用lambda函数进行快速计算 (例如：计算税前总收入)
df_processed['税前总收入'] = df_processed.apply(lambda row: row['工资'] + row['年终奖'], axis=1)
print("\n添加'税前总收入'列:")
print(df_processed[['姓名', '工资', '年终奖', '税前总收入']])

# 对多列进行批量操作 (例如：所有数值列乘以100)
# numeric_cols = df_processed.select_dtypes(include=['int64', 'float64']).columns
# df_processed[numeric_cols] = df_processed[numeric_cols].apply(lambda x: x * 100)
# print("\n数值列乘以100后的数据 (部分):")
# print(df_processed[numeric_cols].head())

print("\n------ 写入excel文件 ------")

output_file_path = '处理后的员工信息.xlsx'

# 将处理后的dataframe写入新的excel文件
# index=false 意味着不将dataframe的索引写入excel的第一列
df_processed.to_excel(output_file_path, index=false)
print(f"\n数据已成功写入 '{output_file_path}'")

# 将多个dataframe写入同一个excel文件的不同工作表
output_multi_sheet_file = '多工作表员工信息.xlsx'
with pd.excelwriter(output_multi_sheet_file) as writer:
    df_processed.to_excel(writer, sheet_name='所有员工', index=false)
    sales_employees.to_excel(writer, sheet_name='销售部员工', index=false)
    high_salary_employees.to_excel(writer, sheet_name='高薪员工', index=false)
print(f"\n多个dataframe已成功写入 '{output_multi_sheet_file}' 的不同工作表")

# for chunk in pd.read_excel(file_path, chunksize=1000):
#     # 对每个chunk进行处理
#     print(f"处理了 {len(chunk)} 行数据")

# df_partial = pd.read_excel(file_path, usecols=['姓名', '工资', '部门'])

# df['入职日期'] = pd.to_datetime(df['入职日期'])