AI业务数据分析

数据清洗-格式,类型与编码

字数统计: 3.4k字   |   阅读时长: 13分
,

摘要: 数据清洗中挂怒格式、类型、编码的处理

【对算法，数学，计算机感兴趣的同学，欢迎关注我哈，阅读更多原创文章】
我的网站：潮汐朝夕的生活实验室
我的公众号：算法题刷刷
我的知乎：潮汐朝夕
我的github：FennelDumplings
我的leetcode：FennelDumplings

  • 文件格式，归档和压缩
  • 数据类型
  • 用于表示缺失数据的类型
  • 字符编码

文件格式

文本文件与二进制文件

网上收集数据时，有可能遇到几种情况：

  • 数据以文件形式下载
  • 数据可以通过存储系统的交互式界面访问
  • 数据可以通过持续不断的流的形式访问
  • 数据可以通过 API 访问

无论那种情况，如果想分享数据给其他人，都需要自己动手创建自己的数据文件。

计算机系统广义存在两种文件类型：文本文件和二进制文件。严格地说，所有文件都是二进制的，但是如果文件中的字节都是以纯粹的字符形式保存(字母、数字、控制字符)，可以认为这个文件是文本格式。

常见的文本文件格式

工作中关注的文本文件类型主要有三种：

  • 分隔格式(结构化数据)
  • JSON格式(半结构化数据)
  • HTML格式(非结构化数据)

(1) 分隔格式

不可见字符

从一个系统生产的文件到另一个系统中可能会有一些不可见字符。

如果是 Linux 系统，vim 打开后用 set list 可以查看不可见字符。

封闭分隔数据

在分隔文件中，除了分隔符外，另一个重要的选项是用什么字符来封闭分隔数据。逗号分隔符对一般值有效，但如果分隔数据中有逗号就没办法了，比如以逗号作为千位分隔符的数值，这种情况在创建文件时就要用额外的符号将分隔数据封闭起来，通常用引号。例如 129,000 改为 "129,000"

字符转义

如果数据本身含有引号(与封闭分隔数据所用的符号冲突)，此时需要对分隔数据内部的引号转义。

工作中，对接方给的 csv 如果有分隔错误(可能是以上两种情况，也可能是别的情况)，需要额外的数据清洗工作。

将分隔文件加载进数据库

很多 DBMS 都提供从 CSV 文件导入数据的功能。如果是 MySQL，可以用 LOAD DATA IN FILE 命令。

1
2
3
4
5
6
7
LOAD DATA
  LOCAL
  INFILE "myFile.csv"
  INTO TABLE freenode_topics
  FIELDS 
  TERMINATED BY ',' 
  (dateOfTopic, channel, numUsers, message)

(2) JSON格式

JSON 的特点是数据的值都有其对应的属性标识，且顺序无关紧要。

JSON 的字符串值必须用双引号进行封闭处理。内部的双引号必须转义。

JSON 中逗号不能出现在数字类型数据中，除非这个值被当做字符串使用并用引号封闭。

JSON 支持多值属性和多层级结构，这在分隔文件中很难实现。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
{
    "firstName": "Sally",
    "birthDate": "1971-09-16",
    "faveColor": "lignt\"Carolina\" blue",
    "pet": 
    [
        {
            "type": "dog",
            "name": "Lucky"
        },
        {
            "type": "dog",
            "name": "Lucky"
        }
    ],
    "job":
    {
        "jobTitle": "Data Scientist",
        "company": "Data Wizards",
        "salary": 129000
    }
}

上面这段 json 中，pet 是多值属性，job 是多层级结构

HTML格式

HTML也是一种文本格式文件，其中经常夹杂这冗余的数据。从中抽取数据很复杂，且这次用的方法未必适用以后的情况，因为网站随时可能改源码。

归档和压缩

有的时候一个文件既是文本文件又是二进制文件。在归档或压缩的时候就属于这种情况。

归档文件

归档文件是内部包含了许多文件的独立文件。内部文本文件和二进制文件都可以有。

tar

1
2
tar cvf
tar xvf

zip, RAR

归档的同时压缩

压缩文件

zip

1
2
zip file.csv file.zip
unzip file.zip

gzip

1
2
gzip file.csv file.gz
gunzip file.gz

bzip2

1
2
bzip2 file.csv file.bz2
bunzip2 file.bz2

tar内置的压缩选项

gzip 压缩

压缩

1
2
tar cvf file.tar file1.csv file2.csv
gzip file.tar file.tar.gz

可以合成一步

1
tar zcvf file.tar.gz file1.csv file2.csv

解压

1
2
gunzip file.tar.gz
tar xvf file.tar

可以合成一步

1
tar zxvf file.tar.gz

bzip2 压缩

1
2
tar jcvf file.tar.bz2 file1.csv file2.csv
tar jxvf file.tar.bz2

ditto

如果是在 mac 上，有时用 unzip 解压 zip 文件会报错，此时可以试试 ditto。

解压命令：

1
2
# xxx.zip 是待解压缩文件名， xx 为解压后的文件夹名称
ditto -V -x -k --sequesterRsrc --rsrc xxx.zip xx

一些经验

  • gzip 压缩和解压都比较快，但是 windows 上有些用户没有 gunzip。
  • bzip2 压缩的文件比 gzip 和 zip 都小，但压缩时间长一些。windows 一般需要单独装可以应对 bzip2 格式的软件。
  • zip 压缩和解压速度比较快，且 windows 上也有，但是压缩比率低
  • RAR 是 windows 上广泛使用的归档和压缩方案。但 Linux 只有特殊软件才能处理。且速度也不理想

数据类型，空值和编码

数据类型

1. 数字

(1) 整数

没有小数点和小数部分

规则设置：整数的范围，是否允许有符号

(2) 小数

含有小数部分，一般都用小数点形式而不是分子分母形式。

规则设置：小数部分长度限制，允许包含的数字总个数(精度)

例如 34.984 小数部分长度为 3，精度为 5

  • 不同存储系统允许用不同类型的小数，例如 DBMS 可以在搭建数据库时自行决定数据存放格式(浮点数，货币等)，细微差别需要看指南
  • 电子表格应用除了存储数据还能显示数据，因此可以用某一种类型的形式存储，另一种类型的形式显示

(3) 计算与比较

数字类型设计的最初目的是参与数学计算或比较。

因此如果后面不计划做加减操作，求平均值、标准差等操作，用文本形式保存更好。比如电话号码。

2. 日期和时间

日期的具体写法有很多，但完整信息都包含年月日，任何日期数据都应该能解析出这三个部分。

大多数 DBMS 都有一套专属的方法导入日期格式的数据，且用同样方法以日期格式导出数据。这些系统还同时提供很多函数。

有些电子表格程序(例如Excel)内部以数字形式存储日期，但用户可以以喜欢的格式显示这些值。Excel 内部用小数存储日期，其中小数部分是时间的部分。

不同存储系统采用的起点标准不同，有的以数字形式记录 Unix 时间(1970年1月1日0时0分0秒)后经过的秒数。

DBMS 和电子表格都支持与数字类似的日期计算。

3. 字符串

字符串非常灵活，几乎能够存储任何其他类型的数据(不计效率)，是数据通信或系统间数据移植的最廉价选择。

DBMS 和存储表格也有一些规则设置：长度限制，字符编码。通常会提供可变长字符串和固定长度字符串两种字符串类型。

字符串类型的数据可能会变得越来越大没有边际，会发展成文本类型的数据。

4. 其它数据类型

  • 集合/枚举
  • 布尔
  • Blob(二进制大对象)

数据类型间的转换

类型之间的转换是数据清洗的意向必不可少的工作。

例如希望对字符串做一些数学运算时，希望能够以数字类型重新存储这些字符串；或者碰到字符串形式的日期数据，希望改变日期表现形式等。

数据转换有一个数据损耗的问题:

在目标数据类型无法保存与原始数据类型同样多的信息时，损耗就会发生。主要风险来源：

  • 不同类型间的不同范围转换:
    • 长200字符的字符串转到一个只能容纳100字符的字段上
    • 长整数类型想一般长度的整数类型转换
  • 不同精度之间的转换:
    • 四舍五入
    • 截取

数据转换策略

  • 基于 SQL 的操作

适用于处理保存在数据库中的数据。用 SQL 函数把数据加工成不同格式，直至适合以查询结果的形式直接导出或者存放到另一个字段去。

MySQL 上常见案例

1> 解析 MySQL 数据并格式化为字符串

将 2000-01-21 04:51:00 转换为格式 4:51am, Friday, January 21, 2000

方法1：concat() 结合日期时间函数
1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT concat(hour(date)
              ,':'
              ,minute(date)
              ,', '
              ,dayname(date)
              ,', '
              ,monthname(date)
              ,' '
              ,day(date)
              ,', '
              ,year(date))
FROM message
WHERE mid=52
MySQL 高级函数 date_format()
1
2
3
SELECT date_format(date, '%1:%i%p, %W, %M %e, %Y')
FROM message
WHERE mid=52

2> 从字符串类型转换到 MySQL 的日期类型

str_to_date()，与 date_format() 有点类似。但是是逆向处理。

1
2
3
4
5
6
7
8
9
10
11
SELECT 
str_to_date(
    substring_index(
        substring_index(reference, '>', 3),
        'Sent: ',
        -1
    ),
    '%W,%M %e, %Y %h:%i %p'
)
FROM referenceinfo
WHERE mid=79;

3> 把 MySQL 字符串类型转换成小数

convert() 函数，与cast(()) 函数功能相似。

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT convert(
    substring_index(
        substring(
            body,
            locate('$', body) + 1
        ),
        '/bbl',
        1
    ),
    decimal(4, 2)
) as price
FROM message
WHERE body LIKE "%$%" AND body LIKE "%/bbl%" AND sender = "energybulletin@platts.com";
  • 基于文件的操作

适用于处理文本类型的数据文件。例如电子表格或 json。

隐藏在数据森林中的空值

混有空值，零和 null 值的大杂烩。

不同 DBMS，存储系统和编程语言对待零，空值和NULL的概念时稍有不同。

三种类型的值又不是在所有环境下都有明显区别。

因此只能了解一些通用的东西，实际工作遇到的时候，是必须搞清楚含义的。

(1) 零

0 是可测量数字，是三种类型的值中最容易处理的。

可以用 0 做排序，做数学计算(不可做除数)

0 只有在数字类型中才可以发挥最大的意义，以字符串类型那个表现的零就没这么多功能。

(2) 空

csv

后两条数据的 favourite color 字段都是空

1
2
3
4
First name,birth date,favourite color,salary
"Sally","1971-09-16","light blue",129000
"Manu","1984-11-03","",159960
"Martin","1978-12-10","",76888

MySQL

插入一条数据，favouriteColor 字段为空

1
2
INSERT INTO people (firstName, birthDate, favouriteColor, salary)
VALUES ("Manu","1984-11-03","",159960);

JSON

JSON 中可以有空对象或空字符串

1
2
3
4
5
6
7
8
9
10
11
{
    "firstName": "Sally",
    "birthDate": "1971-09-16",
    "favouriteColor": "",
    "pet": [],
    "job": {
        "jobTitle": "Data Scientist",
        "company": "Data Wizards",
        "salary": 129000
    }
}

空格与不可见字符

空格 " " 与空 "" 是不同的。

有时其它不可见字符(制表符，回车，换行符等)也会被误当成空或者空格，使用这些字符时必须细心

(3) null

空等价于空值: 即空是一个实实在在的值，可以针对它做一些比较操作。

但 NULL 不等与 NULL，NOT NULL 不等于 NOT NULL

可以想象答案为空和未得到答案的区别。

只有在不希望出现任何数据的情况下才应该用NULL

字符编码

早期：ASCII

20世纪90年代早期：可变长编码系统被提出并被标准化，即 UTF-8。

UTF-8 可以容纳更多的自然语言符号和数学符号，这些符号集合称为 Unicode

将某个字段的默认编码改为utf8

MySQL 的默认编码一般是 Latin1，将某个字段的默认编码改为 utf8 可以用以下写法。其中 COLLATE 后面的 utf8_general_ci 为排序规则

1
2
3
4
5
ALTER TABLE Country
  CHANGE Name `Name` CHAR(52)
  CHARACTER SET utf8
  COLLATE utf8_general_ci
  NOT NULL DEFAULT '';

从 MySQL 数据中找出多字节字符

通过比较字符长度(char_length())和字节长度(length())找到多字节格式编码的字符。

1
2
3
SELECT *
FROM Country
WHERE length(name) != char_length(Name);

当面对大量数据时，可以一条 SQL 找出含有多字节字符编码的记录，然后制定后续计划。

找出 MySQL 中以 Latin-1 编码存储的 Unicode 字符以及其等价的 UTF-8 编码形式

用 convert() 函数和 RLIKE 操作符打印出保存在 MySQL 中并且以 Latin-1 格式编码的 Unicode 字符串。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT
{
    convert(convert(title USING BINARY) USING latin1) AS 'latin1 version',
    convert(convert(title USING BINARY) USING utf8) AS 'utf8 version'
}
FROM movies
WHERE
{
    convert(title USING BINARY) RLIKE concat(
        '[',
        unhex('80'),
        '-',
        unhex('FF'),
        ']'
    );
}

一些经验

UTF-8 格式在 web 中的应用非常普遍，强烈建议一开始建库的时候就用 UTF-8，后续工作会更容易

如果表已经用了非 UTF-8 编码，但尚未填充数据，改成 UTF-8 并把其中每一个字段的字符集改成 UTF-8，接下来就可以用 UTF-8 插入数据了

当面对大量采用非 UTF-8，需要转换成 UTF-8。这时候需要做一些计划，不同情况采用的方法不一样。需要知道要调整的字段和表的数量是多少，并且要看具体的数据库系统。如果是 MySQL，执行转换时可以采用几套不同的方案，可以用 mysqldump、SELECT、convert()、INSERT。

处理文件的 UTF-8 编码

许多编程语言需要做一些额外的调整才能处理 UTF-8 数据。例如在 python 中如果报 关于 UnicodeEncoderError 的错误，可以看看 codecs 和 io 库里有没有解决方案。

注：python3 对 UTF-8 有了原生的处理能力。

Share