项目背景
本次练习使用的数据集为美国城市巴尔的摩2016年公务员工资的数据集,将通过数据可视化和数据描述性分析等手段,分析其工资分布情况。
数据来源
本次使用的数据集可以从 https://catalog.data.gov/dataset/baltimore-city-employee-salaries-fy2016该网站进行下载。
其中,一共有13818条数据,7个字段,分别为:
- Name(姓名)
- JobTitle(职位名称)
- AgencyID(工号)
- Agency(单位)
- HireDate(入职日期)
- AnnualSalary(年薪)
- GrossPay(总薪资-税前)
定义问题
本次练习将围绕工资数据集进行。分别会以下问题展现分析:
- 年薪的总体分布情况
- 年薪最高的职务,人数最多的职位
- 公务人员入职日期的情况
数据清洗与整理
# 设置notebook cell多行输出
from IPython.core.interactiveshell import InteractiveShell
InteractiveShell.ast_node_interactivity = 'all' #默认为'last'
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import os
import warnings
os.chdir(r'E:\python_learn\train') # 设置相对路径
warnings.filterwarnings('ignore') # 设置忽略警告
plt.style.use('ggplot') # 设置matplotlib的整体风格
file_name = 'Baltimore_City_Employee_Salaries_FY2016.csv'
salary = pd.read_csv(file_name)
salary.head()
Name | JobTitle | AgencyID | Agency | HireDate | AnnualSalary | GrossPay | |
---|---|---|---|---|---|---|---|
0 | Aaron,Patricia G | Facilities/Office Services II | A03031 | OED-Employment Dev (031) | 10/24/1979 12:00:00 AM | $56705.00 | $54135.44 |
1 | Aaron,Petra L | ASSISTANT STATE'S ATTORNEY | A29045 | States Attorneys Office (045) | 09/25/2006 12:00:00 AM | $75500.00 | $72445.87 |
2 | Abbey,Emmanuel | CONTRACT SERV SPEC II | A40001 | M-R Info Technology (001) | 05/01/2013 12:00:00 AM | $60060.00 | $59602.58 |
3 | Abbott-Cole,Michelle | Operations Officer III | A90005 | TRANS-Traffic (005) | 11/28/2014 12:00:00 AM | $70000.00 | $59517.21 |
4 | Abdal-Rahim,Naim A | EMT Firefighter Suppression | A64120 | Fire Department (120) | 03/30/2011 12:00:00 AM | $64365.00 | $74770.82 |
# 查看数据结构
salary.info()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 13818 entries, 0 to 13817
Data columns (total 7 columns):
Name 13818 non-null object
JobTitle 13818 non-null object
AgencyID 13818 non-null object
Agency 13818 non-null object
HireDate 13818 non-null object
AnnualSalary 13818 non-null object
GrossPay 13546 non-null object
dtypes: object(7)
memory usage: 755.8+ KB
从返回的数据信息和结构信息可知,HireDate、AnnualSalary和GrossPay这3个字段的数据类型与实际不符,AnnualSalary和GrossPay字段的数据含有特殊符号,并且GrossPay字段部分数据缺失。
因此接下来要对存在问题的字段进行以下的清洗和整理工作:
- GrossPay字段的缺失数据处理
- AnnualSalary和GrossPay字段删除特殊符号
- AnnualSalary和GrossPay str → numeric
- HireDate str → datetime
- JobTitle、Agency str → category
# 缺失值判断及处理
salary.isna().sum()
salary.isna().sum().sum()
# 缺失数据比例较少,可以选择删除处理
Name 0
JobTitle 0
AgencyID 0
Agency 0
HireDate 0
AnnualSalary 0
GrossPay 272
dtype: int64
272
salary = salary.dropna()
salary.isna().sum()
salary.isna().sum().sum() # 缺失数据处理完成
Name 0
JobTitle 0
AgencyID 0
Agency 0
HireDate 0
AnnualSalary 0
GrossPay 0
dtype: int64
0
# AnnualSalary和GrossPay字段删除特殊符号
def f(x):
return x.strip('$') # 删除$符号
salary['AnnualSalary'] = salary['AnnualSalary'].map(f)
salary['GrossPay'] = salary['GrossPay'].map(f)
salary.head()
Name | JobTitle | AgencyID | Agency | HireDate | AnnualSalary | GrossPay | |
---|---|---|---|---|---|---|---|
0 | Aaron,Patricia G | Facilities/Office Services II | A03031 | OED-Employment Dev (031) | 10/24/1979 12:00:00 AM | 56705.00 | 54135.44 |
1 | Aaron,Petra L | ASSISTANT STATE'S ATTORNEY | A29045 | States Attorneys Office (045) | 09/25/2006 12:00:00 AM | 75500.00 | 72445.87 |
2 | Abbey,Emmanuel | CONTRACT SERV SPEC II | A40001 | M-R Info Technology (001) | 05/01/2013 12:00:00 AM | 60060.00 | 59602.58 |
3 | Abbott-Cole,Michelle | Operations Officer III | A90005 | TRANS-Traffic (005) | 11/28/2014 12:00:00 AM | 70000.00 | 59517.21 |
4 | Abdal-Rahim,Naim A | EMT Firefighter Suppression | A64120 | Fir |