美文网首页
07 数据清洗

07 数据清洗

作者: 夏威夷的芒果 | 来源:发表于2018-04-22 23:44 被阅读24次

目标:能够对原始数据进行清理,并获得适合分析的整洁数据。

  • 清理和重新整理混乱的数据。
  • 将列转换为不同的数据类型。
  • 处理 NULL 的技巧。

三个新的函数:

  • LEFT

  • RIGHT

  • LENGTH

  • LEFT 从起点(或左侧)开始,从特定列中的每行获取一定数量的字符。正如此处看到的,你可以使用 LEFT(phone_number, 3) 获取电话号码中的前三位。

  • RIGHT 从末尾(或右侧)开始,从特定列中的每行获取一定数量的字符。正如此处看到的,你可以使用 RIGHT(phone_number, 8) 获取电话号码的最后 8 位。

  • LENGTH 提供了特定列每行的字符数。这里,我们可以使用 LENGTH(phone_number) 得出每个电话号码的长度。

使用上述函数截断号码,注意横杠也算一个字符

练习

LEFT & RIGHT 练习

  1. accounts 表格中,有一个列存储的是每个公司的网站。最后三个数字表示他们使用的是什么类型的网址。此处给出了扩展(和价格)列表。请获取这些扩展并得出 accounts 表格中每个网址类型的存在数量。

  2. 对于公司名称(甚至名称的第一个字母)的作用存在颇多争议(https://www.quora.com/Does-a-companys-name-matter)。请从%E3%80%82%E8%AF%B7%E4%BB%8E) accounts 表格中获取每个公司名称的第一个字母,看看以每个字母(数字)开头的公司名称分布情况。

  3. 使用 accounts 表格和 CASE 语句创建两个群组:一个是以数字开头的公司名称群组,另一个是以字母开头的公司名称群组。以字母开头的公司名称所占的比例是多少?

  4. 元音是指 aeiou。有多少比例的公司名称以元音开头,以其他音节开头的公司名称百分比是多少?

答案

SELECT RIGHT(website, 3) AS domain, COUNT(*) num_companies
FROM accounts
GROUP BY 1
ORDER BY 2 DESC;
SELECT LEFT(UPPER(name), 1) AS first_letter, COUNT(*) num_companies
FROM accounts
GROUP BY 1
ORDER BY 2 DESC;
SELECT SUM(num) nums, SUM(letter) letters
FROM (SELECT name, CASE WHEN LEFT(UPPER(name), 1) IN ('0','1','2','3','4','5','6','7','8','9') 
                       THEN 1 ELSE 0 END AS num, 
         CASE WHEN LEFT(UPPER(name), 1) IN ('0','1','2','3','4','5','6','7','8','9') 
                       THEN 0 ELSE 1 END AS letter
      FROM accounts) t1;

有 350 个公司名称以字母开头,1 个公司以数字开头。因此有 350/351 的公司名称以字母开头,即百分比是 99.7%。

SELECT SUM(vowels) vowels, SUM(other) other
FROM (SELECT name, CASE WHEN LEFT(UPPER(name), 1) IN ('A','E','I','O','U') 
                        THEN 1 ELSE 0 END AS vowels, 
          CASE WHEN LEFT(UPPER(name), 1) IN ('A','E','I','O','U') 
                       THEN 0 ELSE 1 END AS other
         FROM accounts) t1;

有 80 的公司名称以元音开头,271 的公司以其他音节开头。元音的比例是 80/351,或 22.8%。因此,有 77.2% 的公司名称没有以元音开头。

位置函数

  • POSITION
  • STRPOS
  • LOWER
  • UPPER

POSITION 获取字符和列,并提供该字符在每行的索引。第一个位置的索引在 SQL 中是 1。如果你之前学习了其他编程语言,就会发现很多语言的索引是从 0 开始。这里,你发现可以使用 POSITION(',' IN city_state) 获取逗号的索引。

image.png

STRPOS 和 POSITION 提供的结果相同,但是语法不太一样,如下所示:STRPOS(city_state, ‘,’)。

注意,POSITION 和 STRPOS 都区分大小写,因此查找 A 的位置与查找 a 的结果不同。

因此,如果你想获取某个字母的索引,但是不区分大小写,则需要使用 LOWER 或 UPPER 让所有字符变成小写或大写。

如果不想要逗号,可以后面减1

练习:POSITION 和 STRPOS

对于以下练习,需要用到 LEFT 和 RIGHT 以及 POSITION 或 STRPOS 知识。

使用 accounts 表格创建一个名字和姓氏列,用于存储 primary_poc 的名字和姓氏。

现在创建一个包含 sales_rep 表格中每个销售代表姓名的列,同样,需要提供名字和姓氏列。

解决方案

SELECT LEFT(primary_poc, STRPOS(primary_poc, ' ') -1 ) first_name, 
RIGHT(primary_poc, LENGTH(primary_poc) - STRPOS(primary_poc, ' ')) last_name
FROM accounts;
SELECT LEFT(name, STRPOS(name, ' ') -1 ) first_name, 
       RIGHT(name, LENGTH(name) - STRPOS(name, ' ')) last_name
FROM sales_reps;

CONCAT 和 Piping ||

这两个工具都能将不同行的列组合到一起。在此视频中,你学习了如何将存储在不同列中的名字和姓氏组合到一起,形成全名:CONCAT(first_name, ' ', last_name),或者使用双竖线:first_name || ' ' || last_name。

练习:CONCAT

  1. accounts 表格中的每个客户都想为每个 primary_poc 创建一个电子邮箱。邮箱应该是 primary_poc 的名字.primary_poc的姓氏@公司名称.com

  2. 你可能注意到了,在上一个答案中,有些公司名称存在空格,肯定不适合作为邮箱地址。看看你能否通过删掉客户名称中的所有空格来创建合适的邮箱地址,否则你的答案就和问题 1. 的一样。此处是一些实用的文档。

  3. 我们还需要创建初始密码,在用户第一次登录时将更改。初始密码将是 primary_poc 的名字的第一个字母(小写),然后依次是名字的最后一个字母(小写)、姓氏的第一个字母(小写)、姓氏的最后一个字母(小写)、名字的字母数量、姓氏的字母数量,然后是合作的公司名称(全大写,没有空格)

答案

WITH t1 AS (
 SELECT LEFT(primary_poc,     STRPOS(primary_poc, ' ') -1 ) first_name,  
RIGHT(primary_poc, LENGTH(primary_poc) - STRPOS(primary_poc, ' ')) last_name, 
name
FROM accounts)
SELECT first_name, last_name, CONCAT(first_name, '.', last_name, '@', name, '.com')
FROM t1;
WITH t1 AS (
SELECT LEFT(primary_poc,     STRPOS(primary_poc, ' ') -1 ) first_name,  
RIGHT(primary_poc, LENGTH(primary_poc) - STRPOS(primary_poc, ' ')) last_name, 
name
FROM accounts)
SELECT first_name, last_name, CONCAT(first_name, '.', last_name, '@', REPLACE(name, ' ', ''), '.com')
FROM  t1;
WITH t1 AS (
 SELECT LEFT(primary_poc,     STRPOS(primary_poc, ' ') -1 ) first_name,  RIGHT(primary_poc, LENGTH(primary_poc) - STRPOS(primary_poc, ' ')) last_name, name
 FROM accounts)
SELECT first_name, last_name, CONCAT(first_name, '.', last_name, '@', name, '.com'), LEFT(LOWER(first_name), 1) || RIGHT(LOWER(first_name), 1) || LEFT(LOWER(last_name), 1) || RIGHT(LOWER(last_name), 1) || LENGTH(first_name) || LENGTH(last_name) || REPLACE(UPPER(name), ' ', '')
FROM t1;

其他数据清洗函数

  1. TO_DATE

DATE_PART('month', TO_DATE(month, 'month')) 将月份名称改成了与该月相关的数字。

将月份名称转换成数字
  1. CAST

然后,你可以使用 CAST 将字符串改为日期。CAST 实际上可以用来更改各种列类型。经常,你会像视频中一样,使用 CAST(date_column AS DATE)字符串改成日期。但是,你可能还会对列的数据类型做出其他更改。你可以在此处看到其他例子。

年月日连接起来,使之看起来像是真的日期,但是不具有日期的类型
  1. 使用 :: 进行转型,使之成为真正的日期

在此示例中,除了 CAST(date_column AS DATE) 之外,你可以使用 date_column::DATE

image.png
提示

在这节课演示的大部分函数都特定于字符串,它们不适用于日期、整数或浮点数。但是,使用这些函数将自动将数据转换为相应的类型。

LEFTRIGHTTRIM 都仅用来选择特定的字符串元素,但是使用它们选择数字或日期元素,系统会将它们处理为字符串。虽然我们没有明确地在这节课介绍 TRIM,但是它可以用来删掉字符串开头和末尾的字符,这样就可以删掉一行开头或末尾的空格,从 Excel 或其他存储系统转移过来的数据经常就需要这么处理。

这些函数有很多变体,还有这节课没介绍的其他几个字符串函数。 不同的数据库使用的这些函数有所不同,如果你连接的是私有数据库,一定要查看相应的数据库语法。Postgres literature 中包含了大量相关的函数。

练习

可以借助函数SUBSTR(str,pos,len)
就是从pos开始的位置,截取len个字符(空白也算字符)。

SELECT *
FROM sf_crime_data
LIMIT 10;
yyyy-mm-dd
  1. date 列的格式是 mm/dd/yyyy,日期末尾的时间也不正确。
SELECT date orig_date, (SUBSTR(date, 7, 4) || '-' || LEFT(date, 2) || '-' || SUBSTR(date, 4, 2)) new_date
FROM sf_crime_data;

注意,可以像之前的课程中一样,使用 DATE_TRUNC 和 DATE_PART 处理这一新的日期。

SELECT date orig_date, (SUBSTR(date, 7, 4) || '-' || LEFT(date, 2) || '-' || SUBSTR(date, 4, 2))::DATE new_date
FROM sf_crime_data;

COALESCE折叠函数

有时候最终的数据集会有一些空值NULL,但是往往我们希望其输出的是实际值,比如下图,我们希望将空值直接标记为no POC,这样结果比较容易理解,那么这个时候,可以用COALESCE替换空值。



使用数字时,如果希望将空值NULL置为0,则需要频繁执行该操作。



此外,当执行带有一些不匹配的行的外部JOIN时,我们可能希望在这些不匹配的行显示除了空值以外的内容。当一个函数(比如COUNT、AVERAGE)不将NULL作为零值处理时,这个操作才更能体现出它的价值来。
我们可以将那个CAOLLESCE函数包含在COUNT函数之中,同时对照一下无coalesce的primary POC行。运行一下:

这里的竟然多出了9个结果来,
通常,COALESCE 返回的是每行的第一个非 NULL 值。因此如果是在此示例中,行中的值是 NULL,上述解决方案使用了 no_poc。我们的数据集没有 NULL 值,因此你将在下个页面通过另一个示例来学习 COALESCE 函数。

还有其他几个函数的作用比较相似。你可以在此处详细了解这些函数。你还可以在此处查看这节课介绍的很多函数。

练习

其实只有一行是total空的

COALESCE 解决方案

SELECT *
FROM accounts a
LEFT JOIN orders o
ON a.id = o.account_id
WHERE o.total IS NULL;

这是一个左JOIN,因此会出现accounts里面有的项匹配不到order的情况不显示,这是什么原因造成的呢?我不知道。
结果如下:


image.png
SELECT COALESCE(a.id, a.id) filled_id, 
a.name, 
a.website, 
a.lat, 
a.long, 
a.primary_poc, 
a.sales_rep_id, o.*
FROM accounts a
LEFT JOIN orders o
ON a.id = o.account_id
WHERE o.total IS NULL;
SELECT COALESCE(a.id, a.id) filled_id, 
a.name, 
a.website, 
a.lat, 
a.long, 
a.primary_poc, 
a.sales_rep_id, 
COALESCE(o.account_id, a.id) account_id, 
o.occurred_at, 
o.standard_qty, 
o.gloss_qty, 
o.poster_qty, 
o.total, 
o.standard_amt_usd, 
o.gloss_amt_usd, 
o.poster_amt_usd, 
o.total_amt_usd
FROM accounts a
LEFT JOIN orders o
ON a.id = o.account_id
WHERE o.total IS NULL;
SELECT COALESCE(a.id, a.id) filled_id, 
a.name, 
a.website, 
a.lat, 
a.long, 
a.primary_poc, 
a.sales_rep_id, 
COALESCE(o.account_id, a.id) account_id, 
o.occurred_at, 
COALESCE(o.standard_qty, 0) standard_qty, 
COALESCE(o.gloss_qty,0) gloss_qty, 
COALESCE(o.poster_qty,0) poster_qty, 
COALESCE(o.total,0) total, 
COALESCE(o.standard_amt_usd,0) standard_amt_usd, 
COALESCE(o.gloss_amt_usd,0) gloss_amt_usd, 
COALESCE(o.poster_amt_usd,0) poster_amt_usd, 
COALESCE(o.total_amt_usd,0) total_amt_usd
FROM accounts a
LEFT JOIN orders o
ON a.id = o.account_id
WHERE o.total IS NULL;

相关文章

  • 07 数据清洗

    目标:能够对原始数据进行清理,并获得适合分析的整洁数据。 清理和重新整理混乱的数据。 将列转换为不同的数据类型。 ...

  • 利用Python进行数据分析复现(六)

    第07章 数据清洗和准备 7.1 处理缺失数据 pandas使用浮点值NaN(Not a Number)表示缺失数...

  • 第三章-数据预处理

    数据预处理的主要内容包括数据清洗、数据集成、数据变换和数据规约。 3.1数据清洗 数据清洗主要是删除原始数据集中的...

  • 2019-09-14 分析lianjia数据(四)——Power

    分析lianjia房源数据(一)——Python数据清洗 分析lianjia房源数据(二)——SPSS数据清洗 分...

  • 2019-10-03 分析lianjia数据(五)——生成词云图

    分析lianjia房源数据(一)——Python数据清洗 分析lianjia房源数据(二)——SPSS数据清洗 分...

  • 2019-08-19 分析lianjia数据(三)——SPSS数

    前置内容——lianjia数据清洗 分析lianjia房源数据(一)——Python数据清洗 分析lianjia房...

  • 数据清洗的步骤是什么(上)

    数据清洗工作是数据分析工作中不可缺少的步骤,这是因为数据清洗能够处理掉肮脏数据,如果不清洗数据的话,那么数据分析的...

  • 机器学习-数据清洗

    本文由brzhang发表 数据清洗 首先,为何需要对数据进行清洗 数据清洗的工作绝壁是非常枯燥的,做数据研究的的人...

  • 数据清洗

    从两个角度上看,数据清洗一是为了解决数据质量问题,二是让数据更适合做挖掘。不同的目的下分不同的情况,也都有相应的解...

  • 数据清洗

    数据清洗 重复数据处理(推荐使用顺序) 数据透视表可统计数据重复次数和重复数据 选中A、B两列,点击插入选项卡-数...

网友评论

      本文标题:07 数据清洗

      本文链接:https://www.haomeiwen.com/subject/osuhlftx.html