数据预处理

title: 数据预处理
category: 数据分析基础

Dataframe 的数据预处理

import numpy as np
import pandas as pd
import matplotlib.pyplot as plt

plt.rcParams['font.sans-serif'] = ['STFangsong']
plt.rcParams['axes.unicode_minus'] = False

%config InlineBackend.figure_format = 'svg'

0. 加载数据

   • read_csv
   • read_excel
   • read_sql

1. 数据抽取

   • 布尔索引
   • query
   • drop

2. 数据清洗

   • 缺失值：isnull、isna、dropna、fillna
   • 重复值：duplicated、drop_duplicates、nunique
   • 异常值：replace、drop
     • Z-score
     • IQR
     • DBScan
     • Isolation Forest
   • 预处理：apply、transform、applymap
     • str:extract(regexp)、contains
     • dt:year、quarter、month、day、weekday…
     • to_datetime
     • cut(data,bins)、qcut(data,[0,0.1,0.25,0.5,0.75,0.9,1])

3. 数据透视

   • 分组：groupby --> agg
   • 透视表：pivot_table --> index、columns、values、aggfunc
   • 交叉表：crosstab
   • 排序：sort_values(by,ascending)、sort_index(level)
   • 取头部：nlargest、nsmallest

4. 可视化

   • 绘图：plot
     • kind:line、scatter、pie、bar、barh、hist、boxplot…
     • figsize

5. 业务洞察

数据分析师岗位信息数据预处理

导入数据

df = pd.read_csv('../files/data/lagou.csv',
                 usecols=['city', 'companyFullName', 'companyLabelList', 'companySize', 'district', 
             'education', 'financeStage', 'industryField', 'positionName', 'salary',
             'workYear'])
df

3140 rows × 11 columns

df.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 3140 entries, 0 to 3139
Data columns (total 11 columns):
 #   Column            Non-Null Count  Dtype 
---  ------            --------------  ----- 
 0   city              3140 non-null   object
 1   companyFullName   3140 non-null   object
 2   companyLabelList  3140 non-null   object
 3   companySize       3140 non-null   object
 4   district          3133 non-null   object
 5   education         3140 non-null   object
 6   financeStage      3140 non-null   object
 7   industryField     3140 non-null   object
 8   positionName      3140 non-null   object
 9   salary            3140 non-null   object
 10  workYear          3140 non-null   object
dtypes: object(11)
memory usage: 270.0+ KB

筛选出数据分析岗位

# 将df['positionName']处理成字符串后使用str.contains('数据分析')查找到包含数据分析关键字的岗位（返回布尔值）后使用布尔索引
temp_df = df[df['positionName'].str.contains('数据分析')]

将薪资处理成上下限的均值

# 捕获组捕获薪资上下限
# str.extract(正则表达式) - 抽取正则表达式捕获组目标值
avg_salary = temp_df.salary.str.extract(r'(\d+)[Kk]?-(\d+)[Kk]').applymap(int).mean(axis = 1)
temp_df['salary'] = avg_salary
temp_df

C:\Users\zs\AppData\Local\Temp/ipykernel_12772/422559916.py:4: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  temp_df['salary'] = avg_salary

1515 rows × 11 columns

将工作经验改为最低要求

# 使用正则表达式将工作经验改为最低要求
import re

def work_experience(content):
    matcher = re.search(r'\d+',content)
    result = matcher.group() if matcher else '0'
    return int(result)

temp_df['workYear'] = temp_df.workYear.apply(work_experience)
temp_df
# apply - 作用在某行/某列  applymap - 作用在每个单元格

C:\Users\zs\AppData\Local\Temp/ipykernel_12772/490244383.py:9: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  temp_df['workYear'] = temp_df.workYear.apply(work_experience)

1515 rows × 11 columns

图表绘制

# 1. 找出各大城市（city）数据分析岗位的需求量并绘制柱状图。
temp_ser = temp_df.groupby('city').positionName.count()
temp_ser.plot(kind = 'bar',figsize = (8,4),color = '#98C3A1')
plt.xticks(rotation = 0)
plt.yticks(np.arange(50,551,100))
plt.xlabel('城市')
plt.ylabel('招聘需求数')
plt.title('各大城市数据分析岗位的需求量')
for i in range(temp_ser.size):
    plt.text(i,temp_ser[i] + 5,temp_ser[i],ha = 'center')
plt.show()
    

# 2. 找出不同领域（industryField）对数据分析的需求量并绘制饼图。
# 1)、将不同领域做划分并统计各个领域需求量

# 法一：
# temp_ser1 = temp_df['industryField'].str.extract(r'([\u4e00-\u4e27\u4e29-\u9fa5]+)[,\u4e28]?([\u4e00-\u9fa5]+)?')[0].value_counts()

# 法二：
temp_ser1 = temp_df.industryField.str.split(r'[,，、|丨\s]', expand = True)[0].value_counts()
# 2)、作图
temp_ser1.nlargest(10).plot(kind = 'pie',autopct = '%.2f%%', figsize = (8,4))
plt.title('不同领域（industryField）对数据分析的需求量Top10')
# plt.legend(loc = 'upper right')
plt.show()
    

# 3. 分析各城市的薪资水平并绘制柱状图和折线图。
temp_ser2 = np.round(temp_df.groupby('city').salary.mean(),2)
temp_ser2.plot(kind = 'bar',figsize = (8,4), color = '#17BECF')
plt.plot(temp_ser2.index,temp_ser2,color = '#98C3A1')
plt.xticks(rotation = 0)
plt.ylabel('薪资(单位:K)')
plt.grid(axis='y', alpha = 0.5, linestyle = '--')
for i in range(temp_ser2.size):
    plt.text(i,temp_ser2[i] + 0.2,temp_ser2[i],ha = 'center')
plt.title('各城市的薪资水平')
plt.show()
    

2020销售数据预处理

导入数据

sales_df = pd.read_excel('../files/data/2020年销售数据-2.xlsx')
sales_df

1945 rows × 23 columns

sales_df.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 1945 entries, 0 to 1944
Data columns (total 23 columns):
 #   Column       Non-Null Count  Dtype         
---  ------       --------------  -----         
 0   销售日期         1945 non-null   datetime64[ns]
 1   销售区域         1945 non-null   object        
 2   销售渠道         1945 non-null   object        
 3   销售订单         1945 non-null   object        
 4   品牌           1945 non-null   object        
 5   售价           1945 non-null   int64         
 6   销售数量         1945 non-null   int64         
 7   销售额          1945 non-null   int64         
 8   Unnamed: 8   0 non-null      float64       
 9   Unnamed: 9   40 non-null     object        
 10  Unnamed: 10  35 non-null     object        
 11  Unnamed: 11  12 non-null     object        
 12  Unnamed: 12  14 non-null     object        
 13  Unnamed: 13  14 non-null     object        
 14  Unnamed: 14  13 non-null     object        
 15  Unnamed: 15  12 non-null     object        
 16  Unnamed: 16  6 non-null      object        
 17  Unnamed: 17  7 non-null      object        
 18  Unnamed: 18  6 non-null      object        
 19  Unnamed: 19  6 non-null      object        
 20  Unnamed: 20  6 non-null      object        
 21  Unnamed: 21  6 non-null      object        
 22  Unnamed: 22  10 non-null     object        
dtypes: datetime64[ns](1), float64(1), int64(3), object(18)
memory usage: 349.6+ KB

根据销售日期划分销售季度

sales_df['年'] = sales_df.销售日期.dt.year
sales_df['季度'] = sales_df.销售日期.dt.quarter
sales_df['月份'] = sales_df.销售日期.dt.month
sales_df

1945 rows × 26 columns

计算销售额

sales_df['销售额'] = sales_df.售价 * sales_df.销售数量
sales_df

1945 rows × 26 columns

各维度拆解及透视

# 1、统计月度销售额
sales_df.groupby('月份').销售额.sum()

月份
1     5409855
2     4608455
3     4164972
4     3996770
5     3239005
6     2817936
7     3501304
8     2948189
9     2632960
10    2375385
11    2385283
12    1691973
Name: 销售额, dtype: int64

# 同时计算作用多个聚合函数
sales_df.groupby('月份').销售额.agg([np.sum, np.max, np.min])

# 2、统计品牌销售占比
temp = sales_df.groupby('品牌').销售额.sum()
temp.plot(kind = 'pie', autopct = '%.2f%%')
plt.show()
    

# 3、统计各地区月度销售额
# sales_df.groupby(['销售区域', '月份']).销售额.sum()
pd.pivot_table(data = sales_df,
              columns=['月份'],
              index = ['销售区域'],
               values = ['销售额'],
              aggfunc = np.sum,   # 指定聚合函数（默认为np.mean）
              fill_value=0,   # 空值填充值
              margins=True,   # 是否显示总计
              margins_name='总计'   # 设置总计名字
              )

# 4、统计各渠道品牌销量
sales_df.groupby(['销售渠道','品牌']).销售额.sum()

销售渠道  品牌  
京东    八匹马     1030702
      啊哟喂      148487
      壁虎       984961
      皮皮虾     1362590
      花花姑娘    4167728
天猫    八匹马     1722144
      啊哟喂      190694
      壁虎      1489656
      皮皮虾     2511090
      花花姑娘    7785319
实体    八匹马      843010
      啊哟喂       99498
      壁虎       702396
      皮皮虾     1269346
      花花姑娘    2234169
抖音    八匹马      622542
      啊哟喂      108039
      壁虎       619105
      皮皮虾      833905
      花花姑娘    2599314
拼多多   八匹马     1116248
      啊哟喂      146441
      壁虎       882861
      皮皮虾     1915340
      花花姑娘    4386502
Name: 销售额, dtype: int64

sales_df.pivot_table(index = ['销售渠道'],
                    columns=['品牌'],
                    values = ['销售额'],
                    aggfunc=np.sum,
                    margins=True,
                    margins_name='总计')

# 统计不同售价区间月度销量占比
# 1)、查找最小值
min_price = sales_df['售价'].min()
max_price = sales_df['售价'].max()
min_price,max_price

(59, 1499)

# 2)、分箱
bins = np.arange(min_price,max_price + 1, 180)
bins

array([  59,  239,  419,  599,  779,  959, 1139, 1319, 1499], dtype=int64)

cate = pd.cut(sales_df.售价, bins)
sales_df.groupby([cate,'月份']).销售数量.sum()

售价                月份
(90.75, 95.75]    1       0
                  2       0
                  3       0
                  4       0
                  5       0
                       ... 
(115.75, 120.75]  8     178
                  9      61
                  10      0
                  11    199
                  12     77
Name: 销售数量, Length: 72, dtype: int64

# 3)、分箱数据透视各品牌月度求和
sales_df_ = pd.pivot_table(data = sales_df,
              index = cate,
              columns=['月份'],
              values = ['销售数量'],
              aggfunc = np.sum)
sales_df_

ser = sales_df_.sum()
sales_df_ = np.round(sales_df_.divide(ser) * 100, 2)
sales_df_

sales_df_.applymap(lambda x: f'{x}%')

df1 = pd.DataFrame({
    "类别": ["手机", "手机", "手机", "手机", "手机", "电脑", "电脑", "电脑", "电脑"],
    "品牌": ["华为", "华为", "华为", "小米", "小米", "华为", "华为", "小米", "小米"],
    "等级": ["A类", "B类", "A类", "B类", "C类", "A类", "B类", "C类", "A类"],
    "A组": [1, 2, 2, 3, 3, 4, 5, 6, 7],
    "B组": [2, 4, 5, 5, 6, 6, 8, 9, 9]
})
df1

# 任务1：A组手机和电脑各卖出多少台
# 法一：
# df1.groupby(['类别','等级']).A组.sum()
# 法二：
pd.pivot_table(data = df1,
              index = ['类别'],
              columns=['等级'],
              values = ['A组'],
              aggfunc=np.sum,
              margins=True,
              margins_name='总计')

# 任务2：A组不同品牌的手机和电脑各卖出多少台
pd.pivot_table(data = df1,
              index = ['类别'],
              columns=['品牌'],
              values = ['A组'],
              aggfunc=np.sum,
              margins=True,
              margins_name='总计')

# 任务3：不同品牌的手机和电脑，A组和B组各卖出多少台
pd.pivot_table(data = df1,
              index = ['类别'],
              columns=['品牌'],
              values = ['A组','B组'],
              aggfunc=np.sum,
              margins=True,
              margins_name='总计')

df_luohu = pd.read_csv('../files/data/2018年北京积分落户数据.csv')
df_luohu

6019 rows × 5 columns

# 1. 根据company将落户人员分组，统计每个公司有多少积分落户人员？
luohu_ser = df_luohu.groupby('company').id.count()
luohu_ser.nlargest(20).plot(kind = 'bar',figsize = (8,4))
for i in range(luohu_ser.nlargest(20).size):
    plt.text(i,luohu_ser.nlargest(20)[i] + 1,luohu_ser.nlargest(20)[i],ha = 'center')
plt.show()
    

# 2. 根据年龄将落户人员分组，统计每个年龄段有多少人（建议5岁一个年龄段）？
from datetime import datetime

date = pd.to_datetime(df_luohu.birthday)
df_luohu['age'] = (datetime(2018,7,1) - date).dt.days // 365
bins = np.arange(30,61,5)
box = pd.cut(df_luohu.age,bins,right = False)
ser = df_luohu.groupby(box).name.count()
ser.plot(kind = 'bar')
plt.xticks(rotation = 0)
for i in range(ser.size):
    plt.text(i,ser[i]+30,ser[i],ha = 'center')   #数字居中显示
plt.show()
    

# 3. 根据落户积分将落户人员分组，统计每个积分段有多少人？
max_score = df_luohu['score'].max()
min_score = df_luohu['score'].min()

bins = np.arange(min_score,max_score,5)
box = pd.cut(df_luohu.score,bins,right = False)
ser = df_luohu.groupby(box).name.count()
ser.plot(kind = 'bar')
for i in range(ser.size):
    plt.text(i,ser[i]+30,ser[i],ha = 'center')   #数字居中显示
plt.show()

​
​