安装及导入模块

模块安装

打开 cmd 终端，执行：

1
pip install openpyxl sqlalchemy lxml pymysql

导入所需的库

新建 python 文件，导入 pandas 和 numpy 模块

1
2
import pandas as pd
import numpy as np

透视表: pd.pivot_table()

测试数据：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
df = pd.DataFrame({
    'sex': list('FFMFMMF'),
    'smoker': list('YNYYNYY'),
    'age': [21, 20, 17, 37, 40, 18, 26],
    'weight': [120, 100, 132, 140, 94, 89, 123]
})

print(df)
'''
  sex smoker  age  weight
0   F      Y   21     120
1   F      N   20     100
2   M      Y   17     132
3   F      Y   37     140
4   M      N   40      94
5   M      Y   18      89
6   F      Y   26     123
'''

不同列做相同的聚合，比如 mean

1
2
3
4
5
6
7
print(pd.pivot_table(df, values=['age', 'weight'], index='smoker', aggfunc='mean'))
'''
         age  weight
smoker              
N       30.0    97.0
Y       23.8   120.8
'''

不同列做不同聚合

1
2
3
4
5
6
7
print(pd.pivot_table(df, values=['age', 'weight'], index='smoker', aggfunc={'age': 'mean', 'weight': 'median'}))
'''
         age  weight
smoker              
N       30.0    97.0
Y       23.8   123.0
'''

生活实例

源数据：

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
48
49
50
51
52
53
54
55
56
57
58
59
data = {
    '订单日期': pd.date_range(start='2023-01-01', periods=12, freq='M'),
    '产品类别': ['电子产品', '家居用品','服装','电子产品','家居用品','服装','电子产品','家居用品','服装','电子产品','家居用品','服装',],
    '销售额': [5000, 3000, 2000, 5500, 3200, 2200, 6000, 3500, 2300, 5200, 3100, 2100]
}

df = pd.DataFrame(data)
print(df)
'''
         订单日期  产品类别   销售额
0  2023-01-31  电子产品  5000
1  2023-02-28  家居用品  3000
2  2023-03-31    服装  2000
3  2023-04-30  电子产品  5500
4  2023-05-31  家居用品  3200
5  2023-06-30    服装  2200
6  2023-07-31  电子产品  6000
7  2023-08-31  家居用品  3500
8  2023-09-30    服装  2300
9  2023-10-31  电子产品  5200
10 2023-11-30  家居用品  3100
11 2023-12-31    服装  2100
'''

print(pd.pivot_table(df, values='销售额', columns='产品类别', index='订单日期', aggfunc=np.sum))
'''
产品类别          家居用品      服装    电子产品
订单日期                              
2023-01-31     NaN     NaN  5000.0
2023-02-28  3000.0     NaN     NaN
2023-03-31     NaN  2000.0     NaN
2023-04-30     NaN     NaN  5500.0
2023-05-31  3200.0     NaN     NaN
2023-06-30     NaN  2200.0     NaN
2023-07-31     NaN     NaN  6000.0
2023-08-31  3500.0     NaN     NaN
2023-09-30     NaN  2300.0     NaN
2023-10-31     NaN     NaN  5200.0
2023-11-30  3100.0     NaN     NaN
2023-12-31     NaN  2100.0     NaN
'''

print(pd.pivot_table(df, values='销售额', columns='产品类别', index='订单日期', aggfunc=np.sum, fill_value=0))
'''
产品类别        家居用品    服装  电子产品
订单日期                        
2023-01-31     0     0  5000
2023-02-28  3000     0     0
2023-03-31     0  2000     0
2023-04-30     0     0  5500
2023-05-31  3200     0     0
2023-06-30     0  2200     0
2023-07-31     0     0  6000
2023-08-31  3500     0     0
2023-09-30     0  2300     0
2023-10-31     0     0  5200
2023-11-30  3100     0     0
2023-12-31     0  2100     0
'''

数据读取

源数据数据在 gilab 上，做下面的实验之前，需要事先将源数据下载到本地

read_csv

不能存储多个 sheet

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
file_path = r'/data/gitlab/python3-data-analysis/009-20231026-数据读取和保存/exa5.csv'
print(pd.read_csv(file_path, encoding='gbk'))
'''
            日期   股票0   股票1   股票2   股票3   股票4
0     2020/5/8  1.45  2.52 -1.82  1.86  4.50
1     2020/5/8  0.46  3.05 -5.54  3.26  2.17
2     2020/5/8 -5.05  1.55  0.22  0.53  1.63
3     2020/5/8  7.39  3.30  1.46  1.18  1.31
4     2020/5/8 -1.11  0.04  1.50 -3.21  1.58
...        ...   ...   ...   ...   ...   ...
9995  2020/5/8  3.23  2.18  2.46 -5.91  1.85
9996  2020/5/8  1.73  3.41  1.46  1.71 -1.13
9997  2020/5/8 -4.17 -4.04 -2.02  4.45  3.91
9998  2020/5/8  5.57  2.89 -0.81  2.60 -1.68
9999  2020/5/8 -2.09  5.27 -2.04  4.75  3.32

[10000 rows x 6 columns]
'''

header 设置表头

将数据标题作为表头

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
print(pd.read_csv(file_path, header=0, encoding='gbk'))
'''
            日期   股票0   股票1   股票2   股票3   股票4
0     2020/5/8  1.45  2.52 -1.82  1.86  4.50
1     2020/5/8  0.46  3.05 -5.54  3.26  2.17
2     2020/5/8 -5.05  1.55  0.22  0.53  1.63
3     2020/5/8  7.39  3.30  1.46  1.18  1.31
4     2020/5/8 -1.11  0.04  1.50 -3.21  1.58
...        ...   ...   ...   ...   ...   ...
9995  2020/5/8  3.23  2.18  2.46 -5.91  1.85
9996  2020/5/8  1.73  3.41  1.46  1.71 -1.13
9997  2020/5/8 -4.17 -4.04 -2.02  4.45  3.91
9998  2020/5/8  5.57  2.89 -0.81  2.60 -1.68
9999  2020/5/8 -2.09  5.27 -2.04  4.75  3.32

[10000 rows x 6 columns]
'''

将数据作为表头，注意 header 中的 0 和 1 的区别（行索引）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
print(pd.read_csv(file_path, header=1, encoding='gbk'))
'''
      2020/5/8  1.45  2.52  -1.82  1.86   4.5
0     2020/5/8  0.46  3.05  -5.54  3.26  2.17
1     2020/5/8 -5.05  1.55   0.22  0.53  1.63
2     2020/5/8  7.39  3.30   1.46  1.18  1.31
3     2020/5/8 -1.11  0.04   1.50 -3.21  1.58
4     2020/5/8  0.05  0.48   0.95  0.14 -0.64
...        ...   ...   ...    ...   ...   ...
9994  2020/5/8  3.23  2.18   2.46 -5.91  1.85
9995  2020/5/8  1.73  3.41   1.46  1.71 -1.13
9996  2020/5/8 -4.17 -4.04  -2.02  4.45  3.91
9997  2020/5/8  5.57  2.89  -0.81  2.60 -1.68
9998  2020/5/8 -2.09  5.27  -2.04  4.75  3.32

[9999 rows x 6 columns]
'''

index_col 设置索引列

使用列号设置：设置日期列为索引列

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
print(pd.read_csv(file_path, index_col=0, encoding='gbk'))
'''
           股票0   股票1   股票2   股票3   股票4
日期                                    
2020/5/8  1.45  2.52 -1.82  1.86  4.50
2020/5/8  0.46  3.05 -5.54  3.26  2.17
2020/5/8 -5.05  1.55  0.22  0.53  1.63
2020/5/8  7.39  3.30  1.46  1.18  1.31
2020/5/8 -1.11  0.04  1.50 -3.21  1.58
...        ...   ...   ...   ...   ...
2020/5/8  3.23  2.18  2.46 -5.91  1.85
2020/5/8  1.73  3.41  1.46  1.71 -1.13
2020/5/8 -4.17 -4.04 -2.02  4.45  3.91
2020/5/8  5.57  2.89 -0.81  2.60 -1.68
2020/5/8 -2.09  5.27 -2.04  4.75  3.32

[10000 rows x 5 columns]
'''

使用列名设置：设置日期列为索引列

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
print(pd.read_csv(file_path, index_col='日期', encoding='gbk'))
'''
           股票0   股票1   股票2   股票3   股票4
日期                                    
2020/5/8  1.45  2.52 -1.82  1.86  4.50
2020/5/8  0.46  3.05 -5.54  3.26  2.17
2020/5/8 -5.05  1.55  0.22  0.53  1.63
2020/5/8  7.39  3.30  1.46  1.18  1.31
2020/5/8 -1.11  0.04  1.50 -3.21  1.58
...        ...   ...   ...   ...   ...
2020/5/8  3.23  2.18  2.46 -5.91  1.85
2020/5/8  1.73  3.41  1.46  1.71 -1.13
2020/5/8 -4.17 -4.04 -2.02  4.45  3.91
2020/5/8  5.57  2.89 -0.81  2.60 -1.68
2020/5/8 -2.09  5.27 -2.04  4.75  3.32

[10000 rows x 5 columns]
'''

usecols 取部分数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
print(pd.read_csv(file_path, usecols=['股票1', '股票2'], encoding='gbk'))
'''
       股票1   股票2
0     2.52 -1.82
1     3.05 -5.54
2     1.55  0.22
3     3.30  1.46
4     0.04  1.50
...    ...   ...
9995  2.18  2.46
9996  3.41  1.46
9997 -4.04 -2.02
9998  2.89 -0.81
9999  5.27 -2.04

[10000 rows x 2 columns]
'''

dtype 设置数据类型

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
dd = pd.read_csv(file_path, dtype={'股票1': np.float_, '股票2': str }, encoding='gbk')
print(dd)
'''
            日期   股票0   股票1    股票2   股票3   股票4
0     2020/5/8  1.45  2.52  -1.82  1.86  4.50
1     2020/5/8  0.46  3.05  -5.54  3.26  2.17
2     2020/5/8 -5.05  1.55   0.22  0.53  1.63
3     2020/5/8  7.39  3.30   1.46  1.18  1.31
4     2020/5/8 -1.11  0.04    1.5 -3.21  1.58
...        ...   ...   ...    ...   ...   ...
9995  2020/5/8  3.23  2.18   2.46 -5.91  1.85
9996  2020/5/8  1.73  3.41   1.46  1.71 -1.13
9997  2020/5/8 -4.17 -4.04  -2.02  4.45  3.91
9998  2020/5/8  5.57  2.89  -0.81  2.60 -1.68
9999  2020/5/8 -2.09  5.27  -2.04  4.75  3.32

[10000 rows x 6 columns]
'''

print(dd.info())
'''
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 10000 entries, 0 to 9999
Data columns (total 6 columns):
 #   Column  Non-Null Count  Dtype  
---  ------  --------------  -----  
 0   日期      10000 non-null  object 
 1   股票0     10000 non-null  float64
 2   股票1     10000 non-null  float64
 3   股票2     10000 non-null  object 
 4   股票3     10000 non-null  float64
 5   股票4     10000 non-null  float64
dtypes: float64(4), object(2)
memory usage: 468.9+ KB
None
'''

parse_dates 尝试将数据转换成日期类型

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
dd = pd.read_csv(file_path, parse_dates=['日期'], encoding='gbk')
print(dd)
'''
             日期   股票0   股票1   股票2   股票3   股票4
0    2020-05-08  1.45  2.52 -1.82  1.86  4.50
1    2020-05-08  0.46  3.05 -5.54  3.26  2.17
2    2020-05-08 -5.05  1.55  0.22  0.53  1.63
3    2020-05-08  7.39  3.30  1.46  1.18  1.31
4    2020-05-08 -1.11  0.04  1.50 -3.21  1.58
...         ...   ...   ...   ...   ...   ...
9995 2020-05-08  3.23  2.18  2.46 -5.91  1.85
9996 2020-05-08  1.73  3.41  1.46  1.71 -1.13
9997 2020-05-08 -4.17 -4.04 -2.02  4.45  3.91
9998 2020-05-08  5.57  2.89 -0.81  2.60 -1.68
9999 2020-05-08 -2.09  5.27 -2.04  4.75  3.32

[10000 rows x 6 columns]
'''

print(dd.info())
'''
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 10000 entries, 0 to 9999
Data columns (total 6 columns):
 #   Column  Non-Null Count  Dtype         
---  ------  --------------  -----         
 0   日期      10000 non-null  datetime64[ns]
 1   股票0     10000 non-null  float64       
 2   股票1     10000 non-null  float64       
 3   股票2     10000 non-null  float64       
 4   股票3     10000 non-null  float64       
 5   股票4     10000 non-null  float64       
dtypes: datetime64[ns](1), float64(5)
memory usage: 468.9 KB
None
'''

str

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
df1 = pd.read_csv(r'/data/gitlab/python3-data-analysis/009-20231026-数据读取和保存/job.csv', encoding='gbk')
print(df1)


df1[['最低薪资', '最高薪资']] = df1['薪资'].str.extract(r'(\d+)-(\d+)')
print(df1)
'''
               工作名       工作地区         公司名称  ...              薪资   最低薪资   最高薪资
0          web全栈开发  guangzhou      全域营销研究所  ...   5500-15000元/月   5500  15000
1          web全栈开发  guangzhou      全域营销研究所  ...   5500-15000元/月   5500  15000
2        初级后端开发工程师  guangzhou         阅酷科技  ...     500-1500元/月    500   1500
.....................................................................................
49       java全栈工程师   changsha          零一云  ...    3000-8000元/月   3000   8000
50        小游戏开发工程师    tianjin  天津市宝坻区梦弘...  ...   8000-15000元/月   8000  15000
51             PHP    tianjin         核桃科技  ...    7500-8000元/月   7500   8000
'''

chunksize 大文件读取

1
2
3
4
5
6
7
8
9
10
11
df = pd.read_csv(file_path, chunksize=1000, encoding='gbk')
chunks = []
for chunk in df:
    chunk = pd.DataFrame(chunk)
    chunks.append(chunk)
data = pd.concat(chunks, ignore_index=True)
print(data.shape)
'''
[52 rows x 7 columns]
(10000, 6)
'''

read_table

默认分割符为制表符 \t
主要用于提起 txt 中的数据

1
2
3
4
5
6
7
8
9
file_path=r'/data/gitlab/python3-data-analysis/009-20231026-数据读取和保存/exa2.txt'
print(pd.read_table(file_path, sep="\s+"))          # s 空格，sep="\s+" ，指定空格为分割符
'''
          A         B         C
a -0.264438 -1.026059 -0.619500
b  0.927272  0.302904 -0.032399
c -0.264273 -0.386314 -0.217601
d -0.871858 -0.348382  1.100491
'''

read_excel

源数据

1
file_path=r'/data/gitlab/python3-data-analysis/009-20231026-数据读取和保存/exa10.xlsx'

单个 excel 表格包含多个 sheet 如何读取？答案当然是按 sheet_name 获取数据

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
data1 = pd.read_excel(file_path, sheet_name='股票')
print(data1)
'''
             日期   股票0   股票1   股票2   股票3   股票4
0    2020-05-08  1.45  2.52 -1.82  1.86  4.50
1    2020-05-08  0.46  3.05 -5.54  3.26  2.17
2    2020-05-08 -5.05  1.55  0.22  0.53  1.63
3    2020-05-08  7.39  3.30  1.46  1.18  1.31
4    2020-05-08 -1.11  0.04  1.50 -3.21  1.58
...         ...   ...   ...   ...   ...   ...
9995 2020-05-08  3.23  2.18  2.46 -5.91  1.85
9996 2020-05-08  1.73  3.41  1.46  1.71 -1.13
9997 2020-05-08 -4.17 -4.04 -2.02  4.45  3.91
9998 2020-05-08  5.57  2.89 -0.81  2.60 -1.68
9999 2020-05-08 -2.09  5.27 -2.04  4.75  3.32

[10000 rows x 6 columns]
'''

data2 = pd.read_excel(file_path, sheet_name='评论')
print(data2)
'''
     姓名 性别  年龄         评论
0  lucy  女  20         很好
1  Jack  男  30  东西还行，就是太贵
2  Lily  女  35     还不错，好评
'''

read_sql

常规

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
import pymysql

conn = pymysql.connect(
    host='192.168.3.254',
    user='root',
    password='chekir0214',
    db='db2',
    port=3306,
    charset='utf8'
)
print(conn)
'''
<pymysql.connections.Connection object at 0x7fb20a93f010>
'''

sql = "select * from t_dept"
df = pd.read_sql(sql, conn)
print(df)
'''
   id dept_name
0   1       研发部
1   2       市场部
2   3       财务部
3   4       人事部
4   5       运营部
'''

使用 sqlalchemy:

1
2
3
4
5
6
7
8
9
10
11
12
13
import sqlalchemy

engine = sqlalchemy.create_engine('mysql+pymysql://root:[email protected]:3306/db2?charset=utf8')
df2 = pd.read_sql(sql, engine)
print(df2)
'''
   id dept_name
0   1       研发部
1   2       市场部
2   3       财务部
3   4       人事部
4   5       运营部
'''

read_html

获取网页(https://www.air-level.com/rank)数据

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
48
49
50
51
df1 = pd.read_html("https://www.air-level.com/rank", encoding='utf-8', header=0)[0]
print(df1)
'''
    排名   城市  AQI 空气质量等级
0    1   周口  202   重度污染
1    2   孝感  187   中度污染
2    3   漯河  165   中度污染
3    4   许昌  152   中度污染
4    5   黄石  146   轻度污染
5    6   随州  143   轻度污染
6    7   信阳  139   轻度污染
7    8   商丘  138   轻度污染
8    9   黄冈  132   轻度污染
9   10   铜陵  131   轻度污染
10  11   合肥  131   轻度污染
11  12   阜阳  129   轻度污染
12  13   武汉  127   轻度污染
13  14  平顶山  125   轻度污染
14  15   开封  122   轻度污染
15  16  驻马店  122   轻度污染
16  17   池州  120   轻度污染
17  18   襄阳  119   轻度污染
18  19   运城  119   轻度污染
19  20   鄂州  118   轻度污染
'''

df2 = pd.read_html("https://www.air-level.com/rank", encoding='utf-8', header=0)[1]
print(df2)
'''
    排名          城市  AQI 空气质量等级
0    1     玉树藏族自治州    4      优
1    2         日喀则    8      优
2    3          三亚    9      优
3    4          儋州   10      优
4    5          海口   11      优
5    6          昌都   11      优
6    7          拉萨   12      优
7    8     甘孜藏族自治州   13      优
8    9          桂林   13      优
9   10        塔城地区   13      优
10  11  黔南布依族苗族自治州   13      优
11  12        阿里地区   15      优
12  13          山南   15      优
13  14          河池   15      优
14  15      伊犁哈萨克州   16      优
15  16          曲靖   16      优
16  17  黔东南苗族侗族自治州   17      优
17  18          上海   17      优
18  19          昆明   17      优
19  20          柳州   17      优
'''

数据保存

to_csv

to_excel

1
data2.to_excel('./to_excel.xlsx', sheet_name='哈哈', index=False)

to_sql

1
data2.to_sql(name='comment', con='mysql+pymysql://root:[email protected]:3306/db2?charset=utf8', if_exists='replace', index=False)

to_html

将网页(https://www.air-level.com/rank)数据写入 excel

df1.to_html(‘./weather.html’, index=False)