Python-14项目实战之使用pandas进行数据清洗与分析
·
Python项目实战：使用pandas进行数据清洗与分析
学习目标
- 掌握pandas的基本数据结构
- 学会数据读取和写入
- 掌握数据清洗技巧
- 学会数据筛选和排序
- 掌握数据分组和聚合
- 学会处理缺失值和异常值
- 掌握数据合并和连接
1. Pandas基础
1.1 安装和导入
# 安装pandas
# pip install pandas
# 导入pandas
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
# 设置显示选项
pd.set_option('display.max_columns', None)
pd.set_option('display.max_rows', 100)
pd.set_option('display.width', None)
1.2 核心数据结构
# Series：一维数组
s = pd.Series([1, 3, 5, np.nan, 6, 8])
print("Series:")
print(s)
# DataFrame：二维表格
dates = pd.date_range('20230101', periods=6)
df = pd.DataFrame(np.random.randn(6, 4), index=dates, columns=list('ABCD'))
print("\nDataFrame:")
print(df)
# 从字典创建DataFrame
data = {
'Name': ['Alice', 'Bob', 'Charlie', 'David'],
'Age': [25, 30, 35, 28],
'City': ['New York', 'London', 'Tokyo', 'Paris']
}
df2 = pd.DataFrame(data)
print("\nDataFrame from dict:")
print(df2)
1.3 查看数据
# 创建示例数据
df = pd.DataFrame({
'Name': ['Alice', 'Bob', 'Charlie', 'David', 'Eve'],
'Age': [25, 30, 35, 28, 32],
'Salary': [50000, 60000, 70000, 55000, 65000],
'Department': ['IT', 'HR', 'IT', 'Finance', 'HR']
})
# 查看数据
print("DataFrame head:")
print(df.head(3))
print("\nDataFrame tail:")
print(df.tail(2))
print("\nDataFrame info:")
print(df.info())
print("\nDataFrame describe:")
print(df.describe())
print("\nDataFrame columns:")
print(df.columns)
print("\nDataFrame index:")
print(df.index)
print("\nDataFrame shape:")
print(df.shape)
2. 数据读取和写入
2.1 读取CSV文件
# 创建示例CSV文件
import csv
# 创建示例数据
data = [
['Name', 'Age', 'City', 'Salary', 'Department'],
['Alice', '25', 'New York', '50000', 'IT'],
['Bob', '30', 'London', '60000', 'HR'],
['Charlie', '35', 'Tokyo', '70000', 'IT'],
['David', '28', 'Paris', '55000', 'Finance'],
['Eve', '32', 'Sydney', '65000', 'HR']
]
# 写入CSV文件
with open('employees.csv', 'w', newline='', encoding='utf-8') as f:
writer = csv.writer(f)
writer.writerows(data)
# 读取CSV文件
df = pd.read_csv('employees.csv')
print("CSV Data:")
print(df)
# 读取CSV的高级选项
df2 = pd.read_csv('employees.csv',
index_col=0, # 使用第一列作为索引
dtype={'Age': int, 'Salary': float}, # 指定数据类型
na_values=['N/A', 'null']) # 指定缺失值表示
print("\nCSV with options:")
print(df2)
# 处理大文件
# 分块读取
chunk_size = 2
for chunk in pd.read_csv('employees.csv', chunksize=chunk_size):
print(f"\nProcessing chunk with {len(chunk)} rows:")
print(chunk)
2.2 读取Excel文件
# 安装openpyxl: pip install openpyxl
# 创建示例Excel文件
import openpyxl
# 创建工作簿和工作表
wb = openpyxl.Workbook()
ws = wb.active
ws.title = "Employees"
# 添加数据
headers = ['Name', 'Age', 'City', 'Salary', 'Department']
data = [
['Alice', 25, 'New York', 50000, 'IT'],
['Bob', 30, 'London', 60000, 'HR'],
['Charlie', 35, 'Tokyo', 70000, 'IT'],
['David', 28, 'Paris', 55000, 'Finance'],
['Eve', 32, 'Sydney', 65000, 'HR']
]
# 写入数据
for col, header in enumerate(headers, 1):
ws.cell(row=1, column=col, value=header)
for row_idx, row_data in enumerate(data, 2):
for col_idx, value in enumerate(row_data, 1):
ws.cell(row=row_idx, column=col_idx, value=value)
# 保存Excel文件
wb.save('employees.xlsx')
# 读取Excel文件
df = pd.read_excel('employees.xlsx')
print("Excel Data:")
print(df)
# 读取特定工作表
df2 = pd.read_excel('employees.xlsx', sheet_name='Employees')
print("\nSpecific sheet:")
print(df2)
# 读取多个工作表
# 创建第二个工作表
ws2 = wb.create_sheet("Sales")
sales_data = [
['Product', 'Quantity', 'Price'],
['Laptop', 10, 1200],
['Mouse', 50, 25],
['Keyboard', 30, 80]
]
for col, header in enumerate(sales_data[0], 1):
ws2.cell(row=1, column=col, value=header)
for row_idx, row_data in enumerate(sales_data[1:], 2):
for col_idx, value in enumerate(row_data, 1):
ws2.cell(row=row_idx, column=col_idx, value=value)
wb.save('employees.xlsx')
# 读取所有工作表
all_sheets = pd.read_excel('employees.xlsx', sheet_name=None)
print("\nAll sheets:")
for sheet_name, df_sheet in all_sheets.items():
print(f"\nSheet: {sheet_name}")
print(df_sheet)
2.3 写入文件
# 创建示例数据
df = pd.DataFrame({
'Name': ['Alice', 'Bob', 'Charlie'],
'Age': [25, 30, 35],
'Salary': [50000, 60000, 70000]
})
# 写入CSV文件
df.to_csv('output.csv', index=False)
print("Data written to output.csv")
# 写入CSV的高级选项
df.to_csv('output_formatted.csv',
index=False,
sep=';', # 分隔符
header=True,
columns=['Name', 'Salary'], # 选择列
encoding='utf-8',
line_terminator='\n')
# 写入Excel文件
df.to_excel('output.xlsx', sheet_name='Employees', index=False)
# 写入多个工作表
with pd.ExcelWriter('multiple_sheets.xlsx') as writer:
df.to_excel(writer, sheet_name='Employees', index=False)
# 创建另一个DataFrame
df2 = pd.DataFrame({
'Product': ['A', 'B', 'C'],
'Sales': [100, 200, 150]
})
df2.to_excel(writer, sheet_name='Sales', index=False)
# 写入JSON
df.to_json('output.json', orient='records', indent=2)
# 写入SQL数据库（需要安装SQLAlchemy）
# from sqlalchemy import create_engine
# engine = create_engine('sqlite:///data.db')
# df.to_sql('employees', engine, if_exists='replace', index=False)
3. 数据选择和索引
3.1 列选择
# 创建示例数据
df = pd.DataFrame({
'Name': ['Alice', 'Bob', 'Charlie', 'David', 'Eve'],
'Age': [25, 30, 35, 28, 32],
'Salary': [50000, 60000, 70000, 55000, 65000],
'Department': ['IT', 'HR', 'IT', 'Finance', 'HR'],
'Experience': [2, 5, 8, 3, 6]
})
# 选择单列
names = df['Name']
print("Names column:")
print(names)
print(f"Type: {type(names)}")
# 选择多列
subset = df[['Name', 'Salary']]
print("\nName and Salary:")
print(subset)
# 使用loc（基于标签）
print("\nUsing loc:")
print(df.loc[0:2, ['Name', 'Salary']])
# 使用iloc（基于位置）
print("\nUsing iloc:")
print(df.iloc[0:3, [0, 2]])
3.2 行选择
# 基于标签的行选择
print("Row 0:")
print(df.loc[0])
print("\nRows 0 to 2:")
print(df.loc[0:2])
# 基于位置的行选择
print("\nFirst 3 rows:")
print(df.iloc[0:3])
# 基于条件的行选择
print("\nEmployees with salary > 60000:")
high_salary = df[df['Salary'] > 60000]
print(high_salary)
print("\nIT department employees:")
it_employees = df[df['Department'] == 'IT']
print(it_employees)
# 复杂条件
print("\nSenior IT employees (Age > 30):")
senior_it = df[(df['Department'] == 'IT') & (df['Age'] > 30)]
print(senior_it)
# 使用query方法
print("\nUsing query:")
young_employees = df.query('Age < 30')
print(young_employees)
high_salary_it = df.query('Department == "IT" and Salary > 60000')
print("\nHigh salary IT employees:")
print(high_salary_it)
3.3 设置和重置索引
# 设置新索引
df_indexed = df.set_index('Name')
print("DataFrame with Name as index:")
print(df_indexed)
# 重置索引
df_reset = df_indexed.reset_index()
print("\nDataFrame with reset index:")
print(df_reset)
# 创建多级索引
df_multi = df.set_index(['Department', 'Name'])
print("\nMulti-level index:")
print(df_multi)
# 访问多级索引
print("\nIT department:")
print(df_multi.loc['IT'])
4. 数据清洗
4.1 处理缺失值
# 创建包含缺失值的数据
df_missing = pd.DataFrame({
'Name': ['Alice', 'Bob', 'Charlie', 'David', 'Eve'],
'Age': [25, None, 35, 28, 32],
'Salary': [50000, 60000, None, 55000, 65000],
'Department': ['IT', 'HR', 'IT', None, 'HR']
})
print("DataFrame with missing values:")
print(df_missing)
# 检查缺失值
print("\nMissing values:")
print(df_missing.isnull())
print("\nMissing values count:")
print(df_missing.isnull().sum())
# 删除缺失值
print("\nDrop rows with any missing values:")
df_dropped = df_missing.dropna()
print(df_dropped)
print("\nDrop rows with all missing values:")
df_dropped_all = df_missing.dropna(how='all')
print(df_dropped_all)
print("\nDrop columns with missing values:")
df_dropped_cols = df_missing.dropna(axis=1)
print(df_dropped_cols)
# 填充缺失值
print("\nFill missing values with 0:")
df_filled = df_missing.fillna(0)
print(df_filled)
print("\nFill with different values:")
df_filled_dict = df_missing.fillna({
'Age': df_missing['Age'].mean(),
'Salary': df_missing['Salary'].median(),
'Department': 'Unknown'
})
print(df_filled_dict)
# 前向填充和后向填充
print("\nForward fill:")
df_ffill = df_missing.fillna(method='ffill')
print(df_ffill)
print("\nBackward fill:")
df_bfill = df_missing.fillna(method='bfill')
print(df_bfill)
4.2 处理重复值
# 创建包含重复值的数据
df_dup = pd.DataFrame({
'Name': ['Alice', 'Bob', 'Alice', 'Charlie', 'Bob'],
'Age': [25, 30, 25, 35, 30],
'City': ['New York', 'London', 'New York', 'Tokyo', 'London']
})
print("DataFrame with duplicates:")
print(df_dup)
# 检查重复值
print("\nDuplicate rows:")
print(df_dup.duplicated())
print("\nDuplicate rows (keep=False):")
print(df_dup.duplicated(keep=False))
# 删除重复值
print("\nDrop duplicates:")
df_no_dup = df_dup.drop_duplicates()
print(df_no_dup)
print("\nDrop duplicates based on specific columns:")
df_no_name_dup = df_dup.drop_duplicates(subset=['Name'])
print(df_no_name_dup)
# 保留最后一个重复项
print("\nKeep last duplicate:")
df_keep_last = df_dup.drop_duplicates(keep='last')
print(df_keep_last)
4.3 数据类型转换
# 创建混合类型数据
df_mixed = pd.DataFrame({
'ID': ['001', '002', '003'],
'Price': ['25.5', '30.0', '45.75'],
'Quantity': ['10', '20', '15'],
'Date': ['2023-01-01', '2023-01-02', '2023-01-03']
})
print("Original DataFrame:")
print(df_mixed)
print("\nData types:")
print(df_mixed.dtypes)
# 转换数据类型
df_converted = df_mixed.copy()
df_converted['ID'] = df_converted['ID'].astype('int64')
df_converted['Price'] = df_converted['Price'].astype('float64')
df_converted['Quantity'] = df_converted['Quantity'].astype('int32')
df_converted['Date'] = pd.to_datetime(df_converted['Date'])
print("\nConverted DataFrame:")
print(df_converted)
print("\nNew data types:")
print(df_converted.dtypes)
# 处理转换错误
df_error = pd.DataFrame({
'Numbers': ['1', '2', 'three', '4']
})
print("\nData with conversion error:")
print(df_error)
# 使用errors参数
df_coerced = df_error.copy()
df_coerced['Numbers'] = pd.to_numeric(df_coerced['Numbers'], errors='coerce')
print("\nAfter coercion:")
print(df_coerced)
4.4 字符串处理
# 创建包含字符串的数据
df_strings = pd.DataFrame({
'Name': [' alice smith ', 'BOB JONES', 'charlie brown'],
'Email': ['Alice@Example.com', 'BOB@EXAMPLE.COM', 'Charlie@Example.Com'],
'Phone': ['123-456-7890', '987.654.3210', '(555) 123-4567']
})
print("Original DataFrame:")
print(df_strings)
# 字符串方法
print("\nName column operations:")
print("Uppercase:", df_strings['Name'].str.upper())
print("Lowercase:", df_strings['Name'].str.lower())
print("Title case:", df_strings['Name'].str.title())
print("Strip whitespace:", df_strings['Name'].str.strip())
# 字符串替换
print("\nReplace in Phone:")
df_strings['Phone_Clean'] = df_strings['Phone'].str.replace('-', '').str.replace('.', '').str.replace('(', '').str.replace(')', '').str.replace(' ', '')
print(df_strings['Phone_Clean'])
# 字符串包含
print("\nEmail contains 'example':")
print(df_strings['Email'].str.contains('example', case=False))
# 字符串分割
print("\nSplit Name:")
print(df_strings['Name'].str.split())
# 提取子字符串
print("\nExtract first name:")
df_strings['First_Name'] = df_strings['Name'].str.split().str[0].str.title()
print(df_strings['First_Name'])
5. 数据筛选和排序
5.1 高级筛选
# 创建示例数据
df = pd.DataFrame({
'Name': ['Alice', 'Bob', 'Charlie', 'David', 'Eve', 'Frank'],
'Age': [25, 30, 35, 28, 32, 40],
'Salary': [50000, 60000, 70000, 55000, 65000, 75000],
'Department': ['IT', 'HR', 'IT', 'Finance', 'HR', 'IT'],
'Experience': [2, 5, 8, 3, 6, 10]
})
# 使用isin进行筛选
departments = ['IT', 'HR']
it_hr_employees = df[df['Department'].isin(departments)]
print("IT and HR employees:")
print(it_hr_employees)
# 使用between进行范围筛选
age_range = df[df['Age'].between(25, 35)]
print("\nEmployees aged 25-35:")
print(age_range)
# 使用str方法筛选字符串
name_with_a = df[df['Name'].str.contains('a', case=False)]
print("\nNames containing 'a':")
print(name_with_a)
# 复杂条件筛选
complex_filter = df[
(df['Department'] == 'IT') &
(df['Salary'] > 60000) &
(df['Experience'] >= 5)
]
print("\nSenior IT employees:")
print(complex_filter)
# 使用query进行复杂筛选
query_result = df.query('Department == "IT" and Salary > 60000 and Experience >= 5')
print("\nSame result using query:")
print(query_result)
# 筛选后赋值
df_filtered = df.copy()
df_filtered.loc[df_filtered['Department'] == 'IT', 'Bonus'] = df_filtered['Salary'] * 0.1
print("\nWith bonus for IT employees:")
print(df_filtered)
5.2 数据排序
# 单列排序
print("Sorted by Age (ascending):")
print(df.sort_values('Age'))
print("\nSorted by Salary (descending):")
print(df.sort_values('Salary', ascending=False))
# 多列排序
print("\nSorted by Department and Salary:")
print(df.sort_values(['Department', 'Salary'], ascending=[True, False]))
# 按索引排序
df_indexed = df.set_index('Name')
print("\nSorted by index:")
print(df_indexed.sort_index())
# 按值排序并获取排名
df_sorted = df.sort_values('Salary')
df_sorted['Salary_Rank'] = df_sorted['Salary'].rank(ascending=False)
print("\nWith salary ranking:")
print(df_sorted[['Name', 'Salary', 'Salary_Rank']])
# nlargest和nsmallest
print("\nTop 3 highest paid employees:")
print(df.nlargest(3, 'Salary'))
print("\nBottom 2 youngest employees:")
print(df.nsmallest(2, 'Age'))
6. 数据分组和聚合
6.1 基本分组操作
# 按部门分组
grouped = df.groupby('Department')
print("Groups:")
for name, group in grouped:
print(f"\n{name}:")
print(group)
# 分组统计
print("\nDepartment statistics:")
print(grouped['Salary'].mean())
print("\nMultiple statistics:")
print(grouped['Salary'].agg(['mean', 'min', 'max', 'count']))
# 多列分组统计
print("\nDepartment age statistics:")
print(grouped['Age'].agg(['mean', 'min', 'max']))
# 对不同列应用不同函数
print("\nDepartment summary:")
summary = grouped.agg({
'Salary': ['mean', 'min', 'max'],
'Age': ['mean', 'min'],
'Experience': 'sum'
})
print(summary)
6.2 高级分组操作
# 创建更复杂的数据
df_complex = pd.DataFrame({
'Department': ['IT', 'IT', 'HR', 'HR', 'Finance', 'Finance'],
'Team': ['Backend', 'Frontend', 'Recruitment', 'Training', 'Accounting', 'Audit'],
'Employee': ['Alice', 'Bob', 'Charlie', 'David', 'Eve', 'Frank'],
'Salary': [70000, 65000, 60000, 55000, 75000, 70000],
'Years': [5, 3, 4, 2, 6, 4]
})
# 多级分组
multi_grouped = df_complex.groupby(['Department', 'Team'])
print("Multi-level grouping:")
for (dept, team), group in multi_grouped:
print(f"\n{dept} - {team}:")
print(group)
# 分组转换
df_complex['Salary_Rank'] = df_complex.groupby('Department')['Salary'].rank(ascending=False)
print("\nWith salary rank within department:")
print(df_complex)
# 分组过滤
high_paying_depts = df_complex.groupby('Department').filter(
lambda x: x['Salary'].mean() > 65000
)
print("\nHigh paying departments:")
print(high_paying_depts)
# 分组应用自定义函数
def salary_stats(group):
return pd.Series({
'mean_salary': group['Salary'].mean(),
'median_salary': group['Salary'].median(),
'salary_range': group['Salary'].max() - group['Salary'].min(),
'employee_count': len(group)
})
dept_stats = df_complex.groupby('Department').apply(salary_stats)
print("\nDepartment salary statistics:")
print(dept_stats)
6.3 透视表和交叉表
# 创建销售数据
sales_data = pd.DataFrame({
'Date': pd.date_range('2023-01-01', periods=20, freq='D'),
'Region': ['North'] * 5 + ['South'] * 5 + ['East'] * 5 + ['West'] * 5,
'Product': ['A', 'B', 'C', 'D', 'E'] * 4,
'Sales': np.random.randint(100, 1000, 20),
'Quantity': np.random.randint(10, 100, 20)
})
# 创建透视表
pivot_table = pd.pivot_table(sales_data,
values='Sales',
index='Region',
columns='Product',
aggfunc='sum')
print("Sales pivot table:")
print(pivot_table)
# 多个值字段
pivot_multi = pd.pivot_table(sales_data,
values=['Sales', 'Quantity'],
index='Region',
columns='Product',
aggfunc={'Sales': 'sum', 'Quantity': 'mean'})
print("\nMulti-value pivot table:")
print(pivot_multi)
# 交叉表
cross_tab = pd.crosstab(sales_data['Region'], sales_data['Product'])
print("\nCross tabulation:")
print(cross_tab)
# 带聚合函数的交叉表
cross_tab_values = pd.crosstab(sales_data['Region'],
sales_data['Product'],
values=sales_data['Sales'],
aggfunc='sum')
print("\nCross tab with values:")
print(cross_tab_values)
7. 数据合并和连接
7.1 数据连接
# 创建示例数据
df1 = pd.DataFrame({
'ID': [1, 2, 3, 4],
'Name': ['Alice', 'Bob', 'Charlie', 'David'],
'Department': ['IT', 'HR', 'IT', 'Finance']
})
df2 = pd.DataFrame({
'ID': [3, 4, 5, 6],
'Salary': [70000, 55000, 65000, 60000],
'Experience': [8, 3, 6, 4]
})
# 内连接（默认）
inner_join = pd.merge(df1, df2, on='ID', how='inner')
print("Inner join:")
print(inner_join)
# 左连接
left_join = pd.merge(df1, df2, on='ID', how='left')
print("\nLeft join:")
print(left_join)
# 右连接
right_join = pd.merge(df1, df2, on='ID', how='right')
print("\nRight join:")
print(right_join)
# 外连接
outer_join = pd.merge(df1, df2, on='ID', how='outer')
print("\nOuter join:")
print(outer_join)
# 多键连接
df3 = pd.DataFrame({
'ID': [1, 2, 3],
'Department': ['IT', 'HR', 'IT'],
'Salary': [50000, 60000, 70000]
})
df4 = pd.DataFrame({
'ID': [1, 2, 3],
'Department': ['IT', 'HR', 'Finance'],
'Bonus': [5000, 6000, 7000]
})
multi_key_join = pd.merge(df3, df4, on=['ID', 'Department'], how='inner')
print("\nMulti-key join:")
print(multi_key_join)
7.2 数据合并
# 按行合并
df_a = pd.DataFrame({
'Name': ['Alice', 'Bob'],
'Age': [25, 30]
})
df_b = pd.DataFrame({
'Name': ['Charlie', 'David'],
'Age': [35, 28]
})
# 垂直合并
vertical_concat = pd.concat([df_a, df_b], ignore_index=True)
print("Vertical concatenation:")
print(vertical_concat)
# 按列合并
df_c = pd.DataFrame({
'Salary': [50000, 60000],
'Department': ['IT', 'HR']
})
df_d = pd.DataFrame({
'Experience': [2, 5],
'City': ['New York', 'London']
})
horizontal_concat = pd.concat([df_c, df_d], axis=1)
print("\nHorizontal concatenation:")
print(horizontal_concat)
# 带键的合并
keyed_concat = pd.concat([df_a, df_b], keys=['Group1', 'Group2'])
print("\nKeyed concatenation:")
print(keyed_concat)
# 处理不同列名的合并
df_e = pd.DataFrame({
'A': ['A0', 'A1'],
'B': ['B0', 'B1']
})
df_f = pd.DataFrame({
'A': ['A2', 'A3'],
'C': ['C2', 'C3']
})
outer_concat = pd.concat([df_e, df_f], axis=0, sort=False)
print("\nOuter concatenation:")
print(outer_concat)
7.3 数据组合
# 创建时间序列数据
dates1 = pd.date_range('2023-01-01', periods=3)
dates2 = pd.date_range('2023-01-04', periods=3)
ts1 = pd.Series([1, 2, 3], index=dates1)
ts2 = pd.Series([4, 5, 6], index=dates2)
# 组合时间序列
combined_ts = ts1.combine_first(ts2)
print("Combined time series:")
print(combined_ts)
# 更新数据
df_original = pd.DataFrame({
'ID': [1, 2, 3],
'Value': [10, 20, 30]
})
df_update = pd.DataFrame({
'ID': [2, 3, 4],
'Value': [25, 35, 45]
})
# 使用combine_first更新
df_updated = df_update.combine_first(df_original)
print("\nUpdated DataFrame:")
print(df_updated)
# 使用update方法
df_original_copy = df_original.copy()
df_original_copy.update(df_update)
print("\nUsing update method:")
print(df_original_copy)
8. 实际应用案例
8.1 销售数据分析
# 创建销售数据
np.random.seed(42)
sales_data = pd.DataFrame({
'Date': pd.date_range('2023-01-01', periods=100, freq='D'),
'Product': np.random.choice(['A', 'B', 'C', 'D'], 100),
'Region': np.random.choice(['North', 'South', 'East', 'West'], 100),
'Sales': np.random.randint(100, 1000, 100),
'Quantity': np.random.randint(10, 100, 100),
'Customer': [f'Customer_{i}' for i in range(100)]
})
# 数据清洗
print("Original data shape:", sales_data.shape)
# 检查缺失值
print("\nMissing values:")
print(sales_data.isnull().sum())
# 检查重复值
duplicates = sales_data.duplicated().sum()
print(f"\nDuplicate rows: {duplicates}")
# 数据类型检查
print("\nData types:")
print(sales_data.dtypes)
# 基本统计分析
print("\nSales statistics by product:")
product_stats = sales_data.groupby('Product')['Sales'].agg(['mean', 'sum', 'count'])
print(product_stats)
print("\nSales statistics by region:")
region_stats = sales_data.groupby('Region')['Sales'].agg(['mean', 'sum', 'count'])
print(region_stats)
# 时间序列分析
sales_data['Date'] = pd.to_datetime(sales_data['Date'])
sales_data['Month'] = sales_data['Date'].dt.month
sales_data['Weekday'] = sales_data['Date'].dt.day_name()
print("\nMonthly sales:")
monthly_sales = sales_data.groupby('Month')['Sales'].sum()
print(monthly_sales)
print("\nWeekday sales:")
weekday_sales = sales_data.groupby('Weekday')['Sales'].mean()
print(weekday_sales)
# 透视表分析
pivot_sales = pd.pivot_table(sales_data,
values='Sales',
index='Product',
columns='Region',
aggfunc='sum')
print("\nSales pivot table:")
print(pivot_sales)
# 找出最佳销售日
best_day = sales_data.loc[sales_data['Sales'].idxmax()]
print("\nBest sales day:")
print(best_day)
# 找出最畅销的产品
best_product = sales_data.groupby('Product')['Quantity'].sum().idxmax()
print(f"\nBest selling product: {best_product}")
# 客户分析
customer_analysis = sales_data.groupby('Customer').agg({
'Sales': ['sum', 'mean', 'count'],
'Quantity': 'sum'
})
customer_analysis.columns = ['Total_Sales', 'Avg_Sales', 'Purchase_Count', 'Total_Quantity']
top_customers = customer_analysis.nlargest(5, 'Total_Sales')
print("\nTop 5 customers:")
print(top_customers)
8.2 员工绩效分析
# 创建员工绩效数据
np.random.seed(123)
employee_data = pd.DataFrame({
'Employee_ID': range(1, 51),
'Name': [f'Employee_{i}' for i in range(1, 51)],
'Department': np.random.choice(['Sales', 'Marketing', 'IT', 'HR', 'Finance'], 50),
'Performance_Score': np.random.randint(60, 100, 50),
'Years_Experience': np.random.randint(1, 15, 50),
'Training_Hours': np.random.randint(20, 100, 50),
'Projects_Completed': np.random.randint(5, 30, 50),
'Salary': np.random.randint(40000, 120000, 50)
})
# 数据清洗和预处理
print("Employee data info:")
print(employee_data.info())
# 检查异常值
print("\nPerformance score statistics:")
print(employee_data['Performance_Score'].describe())
# 部门绩效分析
print("\nDepartment performance summary:")
dept_performance = employee_data.groupby('Department').agg({
'Performance_Score': ['mean', 'std', 'min', 'max'],
'Salary': 'mean',
'Years_Experience': 'mean',
'Projects_Completed': 'mean'
})
print(dept_performance)
# 绩效与经验的关系
print("\nPerformance by experience level:")
employee_data['Experience_Level'] = pd.cut(employee_data['Years_Experience'],
bins=[0, 3, 7, 15],
labels=['Junior', 'Mid', 'Senior'])
exp_performance = employee_data.groupby('Experience_Level')['Performance_Score'].mean()
print(exp_performance)
# 培训效果分析
print("\nTraining effectiveness:")
employee_data['Training_Category'] = pd.cut(employee_data['Training_Hours'],
bins=[0, 40, 70, 100],
labels=['Low', 'Medium', 'High'])
training_effect = employee_data.groupby('Training_Category')['Performance_Score'].mean()
print(training_effect)
# 高绩效员工特征
high_performers = employee_data[employee_data['Performance_Score'] >= 90]
print(f"\nHigh performers (score >= 90): {len(high_performers)} out of {len(employee_data)}")
print("\nHigh performers characteristics:")
print(high_performers[['Years_Experience', 'Training_Hours', 'Projects_Completed', 'Salary']].describe())
# 绩效预测模型（简单线性回归）
from sklearn.linear_model import LinearRegression
from sklearn.model_selection import train_test_split
from sklearn.metrics import mean_squared_error, r2_score
# 准备特征
features = ['Years_Experience', 'Training_Hours', 'Projects_Completed']
X = employee_data[features]
y = employee_data['Performance_Score']
# 分割数据
X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2, random_state=42)
# 训练模型
model = LinearRegression()
model.fit(X_train, y_train)
# 预测
y_pred = model.predict(X_test)
# 评估模型
mse = mean_squared_error(y_test, y_pred)
r2 = r2_score(y_test, y_pred)
print(f"\nModel Performance:")
print(f"Mean Squared Error: {mse:.2f}")
print(f"R² Score: {r2:.2f}")
print(f"Feature Coefficients:")
for feature, coef in zip(features, model.coef_):
print(f" {feature}: {coef:.2f}")
# 识别需要改进的员工
low_performers = employee_data[employee_data['Performance_Score'] < 75]
print(f"\nLow performers (score < 75): {len(low_performers)}")
if len(low_performers) > 0:
print("\nRecommendations for low performers:")
for _, employee in low_performers.head(3).iterrows():
recommendations = []
if employee['Training_Hours'] < 50:
recommendations.append("Increase training hours")
if employee['Projects_Completed'] < 15:
recommendations.append("Take on more projects")
if employee['Years_Experience'] < 3:
recommendations.append("Seek mentorship")
print(f" {employee['Name']}: {', '.join(recommendations) if recommendations else 'Continue current development path'}")
# 薪资公平性分析
print("\nSalary fairness by department:")
dept_salary_stats = employee_data.groupby('Department')['Salary'].agg(['mean', 'median', 'std'])
print(dept_salary_stats)
# 创建可视化（简单文本图表）
print("\nPerformance distribution by department:")
for dept in employee_data['Department'].unique():
dept_data = employee_data[employee_data['Department'] == dept]
avg_performance = dept_data['Performance_Score'].mean()
print(f"{dept}: {'█' * int(avg_performance/5)} {avg_performance:.1f}")
总结
本章深入介绍了使用pandas进行数据清洗和分析的核心技能：
- pandas的基本数据结构和数据查看方法
- 各种数据格式的读取和写入
- 数据选择和索引操作
- 数据清洗技巧（处理缺失值、重复值、类型转换）
- 字符串处理方法
- 数据筛选和排序
- 数据分组和聚合操作
- 透视表和交叉表
- 数据合并和连接
- 实际应用案例（销售数据分析、员工绩效分析）
掌握pandas是数据分析和数据科学的基础，能够帮助你高效地处理和分析各种结构化数据。
更多推荐
4
0- 0
已为社区贡献27条内容
所有评论(0)