[数据清洗]-混乱的邮编数据

 内容概要:

  • 我们怎么知道是混乱的数据
  • 修复 nan 值和字符串/浮点类型的混乱问题
  • “-”怎么处理
  • 整合代码
# 导入需要的包
import pandas as pd
import numpy as np

# 可以展示比较多的列,60 列
pd.set_option('display.line_width', 5000) 
pd.set_option('display.max_columns', 60)

 混杂数据最重要的一个问题就是:怎么知道是否是混杂的数据。

下面准备使用 NYC 311 服务请求数据集,因为这个是一个庞杂的数据集。

requests = pd.read_csv('../data/311-service-requests.csv')

我们怎么知道是混乱的数据

我们开始少看几列,因为现在一直 Zip Code(邮编)有些问题,所以我们首先看看这个。

为了搞清楚 whether 列是否有问题,常常使用 .unique() 来查看一列的所有数据。如果是一个数值类型的列,最好使用一个直方图来获取数值的分布情况。

requests['Incident Zip'].unique()

array([11432.0, 11378.0, 10032.0, 10023.0, 10027.0, 11372.0, 11419.0,
       11417.0, 10011.0, 11225.0, 11218.0, 10003.0, 10029.0, 10466.0,
       11219.0, 10025.0, 10310.0, 11236.0, nan, 10033.0, 11216.0, 10016.0,
       10305.0, 10312.0, 10026.0, 10309.0, 10036.0, 11433.0, 11235.0,
       11213.0, 11379.0, 11101.0, 10014.0, 11231.0, 11234.0, 10457.0,
       10459.0, 10465.0, 11207.0, 10002.0, 10034.0, 11233.0, 10453.0,
       10456.0, 10469.0, 11374.0, 11221.0, 11421.0, 11215.0, 10007.0,
       10019.0, 11205.0, 11418.0, 11369.0, 11249.0, 10005.0, 10009.0,
       11211.0, 11412.0, 10458.0, 11229.0, 10065.0, 10030.0, 11222.0,
       10024.0, 10013.0, 11420.0, 11365.0, 10012.0, 11214.0, 11212.0,
       10022.0, 11232.0, 11040.0, 11226.0, 10281.0, 11102.0, 11208.0,
       10001.0, 10472.0, 11414.0, 11223.0, 10040.0, 11220.0, 11373.0,
       11203.0, 11691.0, 11356.0, 10017.0, 10452.0, 10280.0, 11217.0,
       10031.0, 11201.0, 11358.0, 10128.0, 11423.0, 10039.0, 10010.0,
       11209.0, 10021.0, 10037.0, 11413.0, 11375.0, 11238.0, 10473.0,
       11103.0, 11354.0, 11361.0, 11106.0, 11385.0, 10463.0, 10467.0,
       11204.0, 11237.0, 11377.0, 11364.0, 11434.0, 11435.0, 11210.0,
       11228.0, 11368.0, 11694.0, 10464.0, 11415.0, 10314.0, 10301.0,
       10018.0, 10038.0, 11105.0, 11230.0, 10468.0, 11104.0, 10471.0,
       11416.0, 10075.0, 11422.0, 11355.0, 10028.0, 10462.0, 10306.0,
       10461.0, 11224.0, 11429.0, 10035.0, 11366.0, 11362.0, 11206.0,
       10460.0, 10304.0, 11360.0, 11411.0, 10455.0, 10475.0, 10069.0,
       10303.0, 10308.0, 10302.0, 11357.0, 10470.0, 11367.0, 11370.0,
       10454.0, 10451.0, 11436.0, 11426.0, 10153.0, 11004.0, 11428.0,
       11427.0, 11001.0, 11363.0, 10004.0, 10474.0, 11430.0, 10000.0,
       10307.0, 11239.0, 10119.0, 10006.0, 10048.0, 11697.0, 11692.0,
       11693.0, 10573.0, 83.0, 11559.0, 10020.0, 77056.0, 11776.0, 70711.0,
       10282.0, 11109.0, 10044.0, '10452', '11233', '10468', '10310',
       '11105', '10462', '10029', '10301', '10457', '10467', '10469',
       '11225', '10035', '10031', '11226', '10454', '11221', '10025',
       '11229', '11235', '11422', '10472', '11208', '11102', '10032',
       '11216', '10473', '10463', '11213', '10040', '10302', '11231',
       '10470', '11204', '11104', '11212', '10466', '11416', '11214',
       '10009', '11692', '11385', '11423', '11201', '10024', '11435',
       '10312', '10030', '11106', '10033', '10303', '11215', '11222',
       '11354', '10016', '10034', '11420', '10304', '10019', '11237',
       '11249', '11230', '11372', '11207', '11378', '11419', '11361',
       '10011', '11357', '10012', '11358', '10003', '10002', '11374',
       '10007', '11234', '10065', '11369', '11434', '11205', '11206',
       '11415', '11236', '11218', '11413', '10458', '11101', '10306',
       '11355', '10023', '11368', '10314', '11421', '10010', '10018',
       '11223', '10455', '11377', '11433', '11375', '10037', '11209',
       '10459', '10128', '10014', '10282', '11373', '10451', '11238',
       '11211', '10038', '11694', '11203', '11691', '11232', '10305',
       '10021', '11228', '10036', '10001', '10017', '11217', '11219',
       '10308', '10465', '11379', '11414', '10460', '11417', '11220',
       '11366', '10027', '11370', '10309', '11412', '11356', '10456',
       '11432', '10022', '10013', '11367', '11040', '10026', '10475',
       '11210', '11364', '11426', '10471', '10119', '11224', '11418',
       '11429', '11365', '10461', '11239', '10039', '00083', '11411',
       '10075', '11004', '11360', '10453', '10028', '11430', '10307',
       '11103', '10004', '10069', '10005', '10474', '11428', '11436',
       '10020', '11001', '11362', '11693', '10464', '11427', '10044',
       '11363', '10006', '10000', '02061', '77092-2016', '10280', '11109',
       '14225', '55164-0737', '19711', '07306', '000000', 'NO CLUE',
       '90010', '10281', '11747', '23541', '11776', '11697', '11788',
       '07604', 10112.0, 11788.0, 11563.0, 11580.0, 7087.0, 11042.0,
       7093.0, 11501.0, 92123.0, 0.0, 11575.0, 7109.0, 11797.0, '10803',
       '11716', '11722', '11549-3650', '10162', '92123', '23502', '11518',
       '07020', '08807', '11577', '07114', '11003', '07201', '11563',
       '61702', '10103', '29616-0759', '35209-3114', '11520', '11735',
       '10129', '11005', '41042', '11590', 6901.0, 7208.0, 11530.0,
       13221.0, 10954.0, 11735.0, 10103.0, 7114.0, 11111.0, 10107.0], dtype=object)

当我们在 “Incident Zip” 列使用 .unique(),很轻易的发现这些数据很混乱。

下面是问题列表:

  • 类型问题,有些值是字符串的数值,有些是浮点型的数值
  • 空值问题,有些值是 nan
  • 格式问题,有些 Zip Code 中间有“-”,有些是有两位数值
  • 正规化问题,有些值是 Pandas 不能识别的值,如 ‘N/A’或 ‘NO CLUE’

如何处理:

  • 正规化 ‘N/A’或 ‘NO CLUE’为常规的 nan 值
  • 仔细分析 ‘83’,再决定如何处理
  • 全部转换为 string 类型

修复 nan 值和字符串/浮点类型的混乱问题

我们在使用 pd.read_csv() 时候,通过传递可选参数 “na_values”来清洗一部分数据。我们还会通过参数指定 “Incident Zip”的数据类型,将类型确定为字符串,而不是浮点型

na_values = ['NO CLUE', 'N/A', '0']
requests = pd.read_csv('../data/311-service-requests.csv', na_values=na_values, dtype={'Incident Zip': str})

requests['Incident Zip'].unique()

array(['11432', '11378', '10032', '10023', '10027', '11372', '11419',
       '11417', '10011', '11225', '11218', '10003', '10029', '10466',
       '11219', '10025', '10310', '11236', nan, '10033', '11216', '10016',
       '10305', '10312', '10026', '10309', '10036', '11433', '11235',
       '11213', '11379', '11101', '10014', '11231', '11234', '10457',
       '10459', '10465', '11207', '10002', '10034', '11233', '10453',
       '10456', '10469', '11374', '11221', '11421', '11215', '10007',
       '10019', '11205', '11418', '11369', '11249', '10005', '10009',
       '11211', '11412', '10458', '11229', '10065', '10030', '11222',
       '10024', '10013', '11420', '11365', '10012', '11214', '11212',
       '10022', '11232', '11040', '11226', '10281', '11102', '11208',
       '10001', '10472', '11414', '11223', '10040', '11220', '11373',
       '11203', '11691', '11356', '10017', '10452', '10280', '11217',
       '10031', '11201', '11358', '10128', '11423', '10039', '10010',
       '11209', '10021', '10037', '11413', '11375', '11238', '10473',
       '11103', '11354', '11361', '11106', '11385', '10463', '10467',
       '11204', '11237', '11377', '11364', '11434', '11435', '11210',
       '11228', '11368', '11694', '10464', '11415', '10314', '10301',
       '10018', '10038', '11105', '11230', '10468', '11104', '10471',
       '11416', '10075', '11422', '11355', '10028', '10462', '10306',
       '10461', '11224', '11429', '10035', '11366', '11362', '11206',
       '10460', '10304', '11360', '11411', '10455', '10475', '10069',
       '10303', '10308', '10302', '11357', '10470', '11367', '11370',
       '10454', '10451', '11436', '11426', '10153', '11004', '11428',
       '11427', '11001', '11363', '10004', '10474', '11430', '10000',
       '10307', '11239', '10119', '10006', '10048', '11697', '11692',
       '11693', '10573', '00083', '11559', '10020', '77056', '11776',
       '70711', '10282', '11109', '10044', '02061', '77092-2016', '14225',
       '55164-0737', '19711', '07306', '000000', '90010', '11747', '23541',
       '11788', '07604', '10112', '11563', '11580', '07087', '11042',
       '07093', '11501', '92123', '00000', '11575', '07109', '11797',
       '10803', '11716', '11722', '11549-3650', '10162', '23502', '11518',
       '07020', '08807', '11577', '07114', '11003', '07201', '61702',
       '10103', '29616-0759', '35209-3114', '11520', '11735', '10129',
       '11005', '41042', '11590', '06901', '07208', '11530', '13221',
       '10954', '11111', '10107'], dtype=object)

“-”怎么处理

rows_with_dashes = requests['Incident Zip'].str.contains('-').fillna(False)
len(requests[rows_with_dashes])

5

requests[rows_with_dashes]

29136    77092-2016
30939    55164-0737
70539    11549-3650
85821    29616-0759
89304    35209-3114
Name: Incident Zip, dtype: object

我们可以简单粗暴的认为这些数据是缺失值,将其删除

requests['Incident Zip'][rows_with_dashes] = np.nan

但是,仔细分析下来9位数字长度的邮编也是正常的,接下来,我们查找下所有大于 5 位数字长度的邮编,确保这些是正常的,然后截断他们

long_zip_codes = requests['Incident Zip'].str.len() > 5
requests['Incident Zip'][long_zip_codes].unique()

array(['77092-2016', '55164-0737', '000000', '11549-3650', '29616-0759',
       '35209-3114'], dtype=object)

这些都是可以被我们截断的

requests['Incident Zip'] = requests['Incident Zip'].str.slice(0, 5) 

完成

最开始,认为 00083 是一个错误的邮编,最后发现这是一个真实存在的邮编!数据中还有 “00000” 的邮编,我们还是需要考虑下这个邮编的,下面我们找出所有这样邮编的数据。

requests[requests['Incident Zip'] == '00000']

这个结果看起来并不好,还是把他们赋值成 nan

zero_zips = requests['Incident Zip'] == '00000'
requests['Incident Zip'][zero_zips] = np.nan

见证一下阶段性的成果

# 正常这里是不需要再次进行类型转换,我这里不转换,排序会报错

unique_zips = requests['Incident Zip'].unique()
unique_zips.sort()
unique_zips

array(['00083', '02061', '06901', '07020', '07087', '07093', '07109',
       '07114', '07201', '07208', '07306', '07604', '08807', '10000',
       '10001', '10002', '10003', '10004', '10005', '10006', '10007',
       '10009', '10010', '10011', '10012', '10013', '10014', '10016',
       '10017', '10018', '10019', '10020', '10021', '10022', '10023',
       '10024', '10025', '10026', '10027', '10028', '10029', '10030',
       '10031', '10032', '10033', '10034', '10035', '10036', '10037',
       '10038', '10039', '10040', '10044', '10048', '10065', '10069',
       '10075', '10103', '10107', '10112', '10119', '10128', '10129',
       '10153', '10162', '10280', '10281', '10282', '10301', '10302',
       '10303', '10304', '10305', '10306', '10307', '10308', '10309',
       '10310', '10312', '10314', '10451', '10452', '10453', '10454',
       '10455', '10456', '10457', '10458', '10459', '10460', '10461',
       '10462', '10463', '10464', '10465', '10466', '10467', '10468',
       '10469', '10470', '10471', '10472', '10473', '10474', '10475',
       '10573', '10803', '10954', '11001', '11003', '11004', '11005',
       '11040', '11042', '11101', '11102', '11103', '11104', '11105',
       '11106', '11109', '11111', '11201', '11203', '11204', '11205',
       '11206', '11207', '11208', '11209', '11210', '11211', '11212',
       '11213', '11214', '11215', '11216', '11217', '11218', '11219',
       '11220', '11221', '11222', '11223', '11224', '11225', '11226',
       '11228', '11229', '11230', '11231', '11232', '11233', '11234',
       '11235', '11236', '11237', '11238', '11239', '11249', '11354',
       '11355', '11356', '11357', '11358', '11360', '11361', '11362',
       '11363', '11364', '11365', '11366', '11367', '11368', '11369',
       '11370', '11372', '11373', '11374', '11375', '11377', '11378',
       '11379', '11385', '11411', '11412', '11413', '11414', '11415',
       '11416', '11417', '11418', '11419', '11420', '11421', '11422',
       '11423', '11426', '11427', '11428', '11429', '11430', '11432',
       '11433', '11434', '11435', '11436', '11501', '11518', '11520',
       '11530', '11549', '11559', '11563', '11575', '11577', '11580',
       '11590', '11691', '11692', '11693', '11694', '11697', '11716',
       '11722', '11735', '11747', '11776', '11788', '11797', '13221',
       '14225', '19711', '23502', '23541', '29616', '35209', '41042',
       '55164', '61702', '70711', '77056', '77092', '90010', '92123', 'nan'], dtype=object)

还是不错的,数据已经更加清晰了。

最后整合一下所有代码

下面是我们上面做的清洗邮编的代码,如下:

na_values = ['NO CLUE', 'N/A', '0']
requests = pd.read_csv('../Data/311-service-requests.csv', 
                       na_values=na_values, 
                       dtype={'Incident Zip': str}

 def fix_zip_codes(zips):

    # 将长度大于 5 位数字的邮编,截断为 5 位 
    zips = zips.str.slice(0, 5)
    
    # 将 00000 赋值为 nan
    zero_zips = zips == '00000'
    zips[zero_zips] = np.nan
    
    return zips



requests['Incident Zip'] = fix_zip_codes(requests['Incident Zip'])

requests['Incident Zip'].unique()

311-service-requests.csv 链接:https://pan.baidu.com/s/1mh9HxTe 密码:ksq4

知乎地址:https://zhuanlan.zhihu.com/p/32548330

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏python3

习题27:if和else

if语句为代码创建了一个所谓的"分支",就跟RPG游戏中的情节分支一样,if语句告诉你的脚本:“如果这个布尔表达式为真,就运行接下来的代码,否则就跳过这一段”

6510
来自专栏码洞

《快学 Go 语言》第 3 课 —— 分支与循环

上面这个等式每一个初学编程的同学都从老师那里听说过。它并不是什么严格的数据公式,它只是对一般程序的简单认知。数据结构是内存数据关系的静态表示,算法是数据结构从一...

11030
来自专栏我是业余自学C/C++的

各种编码

26130
来自专栏喵了个咪的博客空间

zephir-(6)运算符

#zephir-运算符# ? ##前言## 先在这里感谢各位zephir开源技术提供者 了解的动态变量和静态变量之后我们今天来了解一下在编码工作中至关重要的运算...

37090
来自专栏CDA数据分析师

那些容易被忽略的Python编程方式

Python 之禅 The Zen of Python, by Tim Peters Beautiful is better than ugly. 优美胜于丑陋...

218100
来自专栏IT大咖说

程序员必知的 Python 陷阱与缺陷列表

阅读字数:3754 | 10分钟阅读 我个人对陷阱的定义是这样的:代码看起来可以工作,但不是以你“想当然“”的方式。如果一段代码直接出错,抛出了异常,我不认为这...

37970
来自专栏Java学习网

学习Java需吃透这些基本概念

学习好比盖房子,打地基好很重要,房了能盖多高关键看地基;学习同样道理,基础知识是以后学习一切技术的必要条件,我们在准备学习一门开发语言时,首先要学习它的基础,不...

258100
来自专栏GopherCoder

专栏:003:正则表达式

23470
来自专栏吴伟祥

编码总结笔记 原

我们知道,在计算机内部,所有的信息最终都表示为一个二进制的字符串。每一个二进制位(bit)有0和1两种状态,因此八个二进制位就可以组合出256种状态,这被称为一...

13460
来自专栏日常学python

python中一切皆对象

众所周知python是一款面向对象语言,在python语言中,可以说python的一切皆对象是不会错的。如果你学过java的话,你也会知道java也是一款面向对...

11600

扫码关注云+社区

领取腾讯云代金券