数据导入与预处理-第4章-pandas数据获取

1 数据获取

1.1 概述

数据经过采集后通常会被存储到Word、Excel、JSON等文件或数据库中，从而为后期的预处理工作做好数据储备。数据获取是数据预处理的第一步操作，主要是从不同的渠道中读取数据。Pandas支持CSV、TXT、Excel、JSON这几种格式文件、HTML表格的读取操作，另外Python可借助第三方库实现Word与PDF文件的读取操作。本章主要为大家介绍如何从多个渠道中获取数据，为预处理做好数据准备。

1.2 从CSV和TXT文件获取数据

参考连接：https://zhuanlan.zhihu.com/p/340441922
掌握read_csv()函数的用法，可以熟练地使用该方法从CSV或TXT文件中获取数据
CSV（Comma-Separated Values，字符分隔值）和TXT是比较常见的文本格式，其文件以纯文本形式存储数据，其中CSV文件通常是以逗号或制表符为分隔符来分隔值的文本文档，扩展名为“.csv”，可通过Excel等文本编辑器查看与编辑；TXT是微软公司在操作系统上附带的一种文本格式，其文件扩展名为“.txt”，可通过记事本等软件查看。
Pandas中使用read_csv()函数读取CSV或TXT文件的数据，并将读取的数据转换成一个DataFrame类对象。

read_csv(filepath_or_buffer,sep=',', delimiter=None, 
               header='infer', names=None, index_col=None, usecols=None, 
               squeeze=False, prefix=None, mangle_dupe_cols=True, encoding=None...)
1
2
3

filepath_or_buffe：表示文件的路径，可以取值为有效的路径字符串、路径对象或类似文件的对象。
sep：表示指定的分隔符，默认为“,”。
header：表示指定文件中的哪一行数据作为DataFrame类对象的列索引，默认为0，即第一行数据作为列索引。
names：表示DataFrame类对象的列索引列表,当names没被赋值时，header会变成0，即选取数据文件的第一行作为列名；当 names 被赋值，header 没被赋值时，那么header会变成None。如果都赋值，就会实现两个参数的组合功能。
encoding：表示指定的编码格式。

有一个csv文件，名称为phones.csv

商品名称,价格,颜色
Apple iPhone X (A1865) 64GB,6299,深空灰色
Apple iPhone XS Max (A2104) 256GB ,10999,深空灰色
Apple iPhone XR (A2108) 128GB,6199,黑色
Apple iPhone 8 (A1863) 64GB,3999,深空灰色
Apple iPhone 8 Plus (A1864) 64GB,4799,深空灰色
Apple iPhone XS (A2100) 64GB,8699,深空灰色
Apple 苹果 iPhone Xs Max  256GB,9988,金色
Apple 苹果 iPhone Xs 64GB,8058,金色
Apple 苹果 iPhone XR 128GB,5788,黑色
Apple iPhone 7 (A1660) 128G,4139,玫瑰金色
1
2
3
4
5
6
7
8
9
10
11

1.2.1 读取csv案例-指定sep,encoding,engine

import pandas as pd
evaluation_data = pd.read_csv(
       "phones.csv", sep=',',encoding='gbk',engine = 'python')
print(evaluation_data)
1
2
3
4

engine：使用的分析引擎。可以选择C或者是python。C引擎快但是Python引擎功能更加完备。
encoding：指定字符集类型，即编码，通常指定为’utf-8’

1.2.2 读取csv案例-names和header

1. names 没有被赋值，header 也没赋值

evaluation_data = pd.read_csv(
       "phones.csv", sep=',',encoding='gbk',engine = 'python')
1
2

上面的案例中，names 没有被赋值，header 也没赋值：这种情况下，header为0，即选取文件的第一行作为表头

2. names 没有被赋值，header 被赋值：

#不指定names，指定header为1，则选取第二行当做表头，第二行下面为数据
pd.read_csv("phones.csv", encoding='gbk',header=1)
1
2

输出为：

3. names 被赋值，header 没有被赋值：

pd.read_csv("phones.csv", encoding='gbk',names=['商品名称1','价格1','颜色1'])
1

输出为：
可以看到，names适用于没有表头的情况，指定names没有指定header，那么header相当于None。
一般来说，读取文件的时候会有一个表头，一般默认是第一行，但是有的文件中是没有表头的，那么这个时候就可以通过names手动指定、或者生成表头，而文件里面的数据则全部是内容。所以这里id、name、address、date也当成是一条记录了，本来它是表头的，但是我们指定了names，所以它就变成数据了，表头是我们在names里面指定的。

4. names和header都被赋值：

pd.read_csv("phones.csv", encoding='gbk',names=['商品名称1','价格1','颜色1'],header=0)
1

输出为：

这个时候，相当于先不看names，只看header，header为0代表先把第一行当做表头，下面的当成数据；然后再把表头用names给替换掉。

所以names和header的使用场景主要如下：

1. csv文件有表头并且是第一行，那么names和header都无需指定;
2. csv文件有表头、但表头不是第一行，可能从下面几行开始才是真正的表头和数据，这个时候指定header即可;
3. csv文件没有表头，全部是纯数据，那么我们可以通过names手动生成表头;
4. csv文件有表头、但是这个表头你不想用，这个时候同时指定names和header。先用header选出表头和数据，然后再用names将表头替换掉，就等价于将数据读取进来之后再对列名进行rename；

1.2.3 读取csv案例-指定index_col和usecols

1. 指定index_col
   index_col：我们在读取文件之后所得到的DataFrame的索引默认是0、1、2……，我们可以通过set_index设定索引，但是也可以在读取的时候就指定某列为索引。

pd.read_csv("phones.csv", index_col="商品名称1",encoding='gbk', names=['商品名称1','价格1','颜色1'],header=0)
1

输出为：

这里，我们在读取的时候指定了name列作为索引；
此外，除了指定单个列，还可以指定多列作为索引，比如[“id”, “name”]。同时，我们除了可以输入列名外，还可以输入列对应的索引。比如：“id”、“name”、“address”、"date"对应的索引就分别是0、1、2、3。

2. 指定usecols
   usecols：如果一个数据集中有很多列，但是我们在读取的时候只想要使用到的列，我们就可以使用这个参数。

pd.read_csv("phones.csv", usecols=['价格1','颜色1'],encoding='gbk', names=['商品名称1','价格1','颜色1'],header=0)
1

输出为：

1.2.4 读取csv案例-指定nrows和skiprows

skiprows：表示过滤行，想过滤掉哪些行，就写在一个列表里面传递给skiprows即可。注意的是：这里是先过滤，然后再确定表头
nrows：设置一次性读入的文件行数，在读入大文件时很有用，比如 16G 内存的PC无法容纳几百 G 的大文件。

pd.read_csv("phones.csv",encoding='gbk',nrows = 2,skiprows = [i for i in range(1,9)])
1

输出为：
其中skiprows = [i for i in range(1,9)]跳过了前8条数据，nrows = 2输出为跳过之后的2条数据。

1.2.5 读取csv案例-指定dtype

df = pd.read_csv("phones.csv",encoding='gbk')
df.info()
1
2

输出为：

df1 = pd.read_csv("phones.csv",encoding='gbk', dtype={'价格': str})
# 也可以df1['价格'] = df1['价格'].astype("str")
df1.info()
1
2
3

输出为：

1.2.6读取csv案例-分块读取chunk

df = pd.read_csv("phones.csv",encoding='gbk', chunksize= 4)
count = 0
print('*'*10)
for i in df:
    print(count)
    count +=1 
    print(i)
    print('*'*10)
1
2
3
4
5
6
7
8

输出为：

1.2.7 读取txt案例

采用read_csv也可以读取txt文件，同时pandas也提供了read_table用于读取文本文件。

pd.read_table("phones.csv",sep=',',encoding='gbk')
1

输出为：

1.3读取Excel文件

Excel文件（Excel 2007及以上版本的扩展名为.xlsx）是日常工作中经常使用的，该文件主要以工作表存储数据，工作表中包含排列成行和列的单元格。Excel文件中默认有3个工作表，用户可根据需要添加一定个数（因可用内存的限制）的工作表。
Pandas中使用read_excel()函数读取Excel文件中指定工作表的数据，并将数据转换成一个结构与工作表相似的DataFrame类对象。

pandas.read_excel(io, sheet_name=0, header=0, names=None, index_col=None,
        usecols=None,squeeze=False, dtype=None, engine=None,converters=None,
        true_values=None, false_values=None, skiprows=None, nrows=None,na_values=None, 
        parse_dates=False, date_parser=None,thousands=None, comment=None, 
        skipfooter=0,convert_float=True,**kwds)
1
2
3
4
5

sheet_name：表示要读取的工作表，默认值为0。
header：表示指定文件中的哪一行数据作为DataFrame类对象的列索引。
names：表示DataFrame类对象的列索引列表。

值得一提的是，当使用read_excel()函数读取Excel文件时，若出现importError异常，说明当前Python环境中缺少读取Excel文件的依赖库xlrd，需要手动安装依赖库xlrd（pip install xlrd）进行解决。或是安装

pip install openpyxl==3.0.9
1

1.3.1 读取Excel案例

有Excel文件

data3 = pd.read_excel('Athletes_info.xlsx',sheet_name='Sheet1',header=0,engine='openpyxl')
data3.head(3)
1
2

输出为：

io ：文件路径。
sheetname：返回多表使用sheetname=[0,1],若sheetname=None是返回全表 → ① int/string 返回的是dataframe ②而none和list返回的是dict
header：指定列名行，默认0，即取第一行
index_col：指定列为索引列，也可以使用u”strings”

备注:使用 pandas 读取 CSV 与 读取 xlsx 格式的 Excel 文件方法大致相同

1.4读取json文件

掌握read_json()函数的用法，可以熟练地使用该方法从JSON文件中获取数据
JSON（JavaScript Object Notation）是一种轻量级的数据交换格式，它以简洁和清晰的层次结构来组织数据，易于被人们阅读和编写。JSON采用独立于编程语言的文本格式来存储数据，其文件的后缀名为.json，可通过文本编辑工具查看。
Pandas中使用read_json()函数读取JSON文件的数据，并将数据转换成一个DataFrame类对象。

pandas.read_json(path_or_buf=None, orient=None, typ='frame', dtype=None,
         convert_axes=None, convert_dates=True, keep_default_dates=True,
         numpy=False, precise_float=False, date_unit=None, encoding=None,
         lines=False, chunksize=None, compression='infer')
1
2
3
4

path_or_buf:接收格式为[a valid JSON string or file-like, default: None] 选择JSON文件或者是指定可以是URL。有效的URL形式包括http、ftp、s3和文件。对于URL文件，需要指定本地文件目录。例如，本地文件可以是file://localhost/path/to/table.json。

orient:接收格式为[string]，指示预期的JSON字符串格式。兼容的JSON字符串可以由to_json生成并且具有具体的orient。其中设定的orient取决于JSON文件的形式以及你想要转为dataframe的形式。
    'split'：将行索引index，列索引columns，值数据data分开来。dict like {‘index’ -> [index], ‘columns’ -> [columns], ‘data’ -> [values]}。json文件的每一行都类似如下，而且json文件的key的名字只能为index,cloumns,data这三个，另外多一个key都不行，少一个也不行。'{"index":[1,2,3],"columns":["a","b"],"data":[[1,3],[2,8],[3,9]]}'否则报bug ：SyntaxError: EOL while scanning string literal
    ‘records':将列表list格式，以[{列名->值}，..]形式输出。list like [{column -> value}, … , {column -> value}]。‘[{“col 1”:“a”,“col 2”:“b”},{“col 1”:“c”,“col 2”:“d”}]’
    'index'：将字典以｛行索引:{列索引:值}}以这种形式输出dict like {index -> {column -> value}}。‘{“row 1”:{“col 1”:“a”,“col 2”:“b”},“row 2”:{“col 1”:“c”,“col 2”:“d”}}’
    'columns':将字典以｛列索引:{行索引:值}}以这种形式输出 dict like {column -> {index -> value}}。'{"city":{"guangzhou":"20","zhuhai":"20"},"home":{"price":"5W","data":"10"}}'
    'values'：就全部输出值就好了。允许值和默认值取决于type参数的值。v='[["a",1],["b",2]]'
    当想要转化的格式为'series'
        允许的orients为 {'split','records','index'}
        默认为index
        当orient为index时，Series的行索引index必须唯一
    当想要转化的格式为‘frame’时
        允许的orients为{'split','records','index'}
        默认定义为columns，列索引
        当指定的orien为index或是columns时，Dataframe的index必须唯一
        当指定的orient为index、columns和records时，Dataframe的columns必须唯一
        版本0.23.0中的新增内容：“table”作为orient参数的允许值
        
typ：指定将JSON文件转化的格式，(series or frame)，默认为frame
dtype：如果为True，则推断数据类型，如果将列的dict转换为数据类型，则使用它们，如果为False，则根本不推断数据类型，仅适用于数据。
convert_axes:将轴转换为正确的数据类型。默认为True
convert_dates：boolean类型，默认True。用于解析时间数据，如果为True，则将选定带有以下的datelike列名称标签：
    it ends with '_at',以_at结尾
    it ends with '_time',以_time结尾
    it begins with 'timestamp',以timestamp开始
    it is 'modified', 以modified命名的label标签
    it is 'date'以date命名的label标签
keep_default_dates:boolean类型，默认为True。如果分析日期，则分析默认的datelike列
numpy:默认为False，直接解码到numpy阵列。仅支持数字数据，但不支持非数字列和索引标签。还要注意，如果numpy=True，则每个术语的JSON顺序必须相同。
precise_float：boolean类型，默认False。设置为在将字符串解码为双倍值时启用更高精度（STROD）函数。默认（False）使用快速但不太精确的内置功能。
date_unit：string类型，默认None。用于检测是否转换日期的时间戳单元。默认行为是尝试并检测正确的精度，但如果不需要，则传递“s”、“ms”、“us”或“ns”中的一个，以分别强制解析秒、毫秒、微秒或纳秒。
lines：boolean类型，默认False。以每行json对象的形式读取文件。
encoding:str, default is ‘utf-8’。用于解码py3字节的编码。
chunksize:integer类型，默认为None。返回JsonReader对象进行迭代。有关chunksize的更多信息，请参阅line-delimted json docs文件。只有当lines=True时，才能传递此消息。如果该值为“无”，则文件将一次全部读入内存。
compression:{‘infer’, ‘gzip’, ‘bz2’, ‘zip’, ‘xz’, None},默认为infer。用于实时解压缩磁盘数据。如果“推断”，则使用gzip、bz2、zip或xz，如果path\u或\u buf是以“”结尾的字符串。gz'，'。bz2’，”。zip”或“xz”，否则不进行解压缩。如果使用“zip”，zip文件必须只包含一个要读入的数据文件。设置为“无”表示无解压缩。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

1.4.1 读取json字符串

strtext='[{"ttery":"min","issue":"20130801-3391","code":"8,4,5,2,9","code1":"297734529","code2":null,"time":1013395466000},\
{"ttery":"min","issue":"20130801-3390","code":"7,8,2,1,2","code1":"298058212","code2":null,"time":1013395406000},\
{"ttery":"min","issue":"20130801-3389","code":"5,9,1,2,9","code1":"298329129","code2":null,"time":1013395346000},\
{"ttery":"min","issue":"20130801-3388","code":"3,8,7,3,3","code1":"298588733","code2":null,"time":1013395286000},\
{"ttery":"min","issue":"20130801-3387","code":"0,8,5,2,7","code1":"298818527","code2":null,"time":1013395226000}]'
 
df=pd.read_json(strtext,orient='records')
1
2
3
4
5
6
7

输出为：

1.4.2 读取json文件

json文件：

{"哺乳动物":["虎","猴子","狗","猫","鹿"],
  "鱼类动物":["龙鱼","鲶鱼","鳟鱼","章鱼","草鱼"],
  "飞禽类动物":["丹顶鹤","金雕","白鹭","鸽子","天鹅"],
  "昆虫类动物":["蝴蝶","金龟子","蜜蜂","蜻蜓","螳螂"]}
1
2
3
4

import pandas as pd
json_data = pd.read_json('Animal_species.json', 
                    encoding='utf8')
json_data
1
2
3
4

输出为：

1.5 读取HTML数据

从HTML表格获取数据
数据除了在文件中呈现，还可以在网页的HTML表格中呈现，为此Pandas提供了用于从HTML网页表格中读取数据的read_html()函数。

pandas.read_html(io, match='.+', flavor=None, header=None,
         index_col=None,skiprows=None, attrs=None, parse_dates=False,
         tupleize_cols=None, thousands=', ', encoding=None, decimal='.',
         converters=None,na_values=None, keep_default_na=True,
         displayed_only=True)
1
2
3
4
5

match：表示返回与指定的正则表达式或字符串匹配的文本。
flavor：表示使用的解析引擎。
index_col：表示将网页表格中的列标题作为DataFrame的行索引。
encoding：表示解析网页的编码方式。
需要注意的是，read_html()函数只能用于读取网页中的表格数据，该函数会返回一个包含网页中所有表格数据的列表。我们可通过索引获取对应位置的表格数据。

import requests  
html_data = requests.get('https://www.tiobe.com/tiobe-index/')
html_table_data = pd.read_html(html_data.content,encoding='utf-8')
html_table_data[3].head(5)
1
2
3
4

输出为：
![在这里插入图片描述](https://img-blog.csdnimg.cn/04d9ecfc08b3430f87e7813b4b308026.png

1.6 读取数据库文件

有时我们需要从 SQL 中读取数据，如果先将数据导出再pandas读取并不是一个合适的选择。
在 pandas 中支持直接从 sql 中查询并读取。

掌握

read_sql_table()
read_sql_query()
read_sql()
1
2
3

函数的用法，可以熟练地使用这些方法从数据库中获取数据
数据除了被保存在CSV、TXT、Excel等文件中，还可以被保存在数据库中。常见的数据库有MySQL、Oracle、SQLite、PostgreSQL等，其中MySQL是主流的关系型数据库，它主要以数据表的形式组织数据。
Pandas读取MySQL数据库时需要保证当前的环境中已经安装了SQLAlchemy和PyMySQL模块，其中SQLAlchemy模块提供了与不同数据库连接的功能，而PyMySQL模块提供了Python操作MySQL数据库的功能。

pip install SQLAlchemy  # 安装SQLAlchemy模块
pip install PyMySQL      # 安装PyMySQL模块
1
2

read_sql_table()、read_sql_query()和read_sql()这3个函数的使用方式基本相同，这里以read_sql()函数为例进行介绍。

pandas.read_sql(sql, con, index_col=None, coerce_float=True, params=None,
     parse_dates=None, columns=None, chunksize=None)
1
2

sql：表示被执行的SQL查询语句或数据表名。
con：表示使用SQLAlchemy连接数据库。
index_col：表示将数据表中的列标题作为DataFrame的行索引。。
coerce_float：表示是否将非字符串、非数字对象的值转换为浮点值（可能会导致精度损失），默认为True。

1.6.1 读取sql数据

为了方便统一操作，请先执行下面的代码创建数据。

/*
 Navicat Premium Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 50723
 Source Host           : localhost:3306
 Source Schema         : jifenkaohe

 Target Server Type    : MySQL
 Target Server Version : 50723
 File Encoding         : 65001

 Date: 10/11/2021 08:58:53
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for dep_info
-- ----------------------------
CREATE DATABASE IF NOT EXISTS jifenkaohe;
DROP TABLE IF EXISTS `dep_info`;
CREATE TABLE `dep_info`  (
  `dep_id` int(11) NOT NULL,
  `dep_name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`dep_id`) USING BTREE,
  INDEX `idx_dep_name`(`dep_name`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of dep_info
-- ----------------------------
INSERT INTO `dep_info` VALUES (8, '凯里学院');
INSERT INTO `dep_info` VALUES (1, '商丘师范学院');
INSERT INTO `dep_info` VALUES (5, '山东英才学院');
INSERT INTO `dep_info` VALUES (7, '山东财经大学东方学院');
INSERT INTO `dep_info` VALUES (11, '枣庄学院');
INSERT INTO `dep_info` VALUES (6, '池州学院');
INSERT INTO `dep_info` VALUES (9, '河北民族师范学院');
INSERT INTO `dep_info` VALUES (3, '河南城建学院');
INSERT INTO `dep_info` VALUES (4, '洛阳理工学院');
INSERT INTO `dep_info` VALUES (2, '许昌学院');
INSERT INTO `dep_info` VALUES (10, '辽宁科技学院');

SET FOREIGN_KEY_CHECKS = 1;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

连接数据库

from sqlalchemy import create_engine  
import pandas as pd
db_info = {'user':'root',  
    'pwd':'XXXX',  # 修改为你的密码
    'host':'localhost',  
    'database':'XXX'  # 这里我们事先指定了数据库，后续操作只需要表即可
}  
#这里直接使用pymysql连接,echo=True，会显示在加载数据库所执行的SQL语句。      
engine = create_engine('mysql+pymysql://%(user)s:%(pwd)s@%(host)s/%(database)s?charset=utf8' % db_info,encoding='utf-8')  
1
2
3
4
5
6
7
8
9

执行sql查询

data = pd.read_sql('SELECT dep_id, dep_name FROM dep_info', con = engine)
data
1
2

输出为：

1.6.2 mysql8.0创建用户

-- 1.检查创建用户时密码校验条件 
-- 确认下密码验证规则，一般必须大于等于8位
# 显示创建用户的验证条件
SHOW VARIABLES LIKE 'validate_password%';

-- 2.创建用户
CREATE USER '用户名'@'%或者IP' IDENTIFIED BY '密码';
CREATE USER 'zj'@'localhost' IDENTIFIED BY '12345678z!';

ALTER USER 'zj'@'localhost' IDENTIFIED WITH mysql_native_password BY '12345678z!';
更新user为zj，host为% 的密码为123456



-- 3.授予权限

-- 授权某个用户所有数据库所有权限
GRANT ALL PRIVILEGES ON *.* TO "用户名"@"IP/localhost/%"
GRANT ALL PRIVILEGES ON *.* TO "zj"@"localhost"
-- 授予某个用户所有数据库读权限
GRANT SELECT ON *.* TO '用户名'@'%或者IP' ;
-- 授予某个用户某个数据库写权限
GRANT INSERT ON `数据库名`.* TO '用户名'@'%或者IP';

-- 其他权限：
ALL PRIVILEGES: 所有权限
INSERT: 允许用户插入权限
SELECT: 允许用户读权限.
UPDATE: 允许用户更新权限
CREATE: 允许用户创建表和数据库权限
DELETE: 允许用户删除表中的行数据权限
DROP: 允许用户删除数据库和表权限

-- 4.刷新权限
flush privileges;


-- 5.显示所有权限 
SHOW GRANTS FOR '用户名'@'%或者IP';

-- 6.删除某个权限
REVOKE SELECT ON *.* FROM '用户名'@'%或者IP';
-- 7.删除用户
DROP USER '用户名'@'%或者IP';;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44