第十六篇 Py数据分析（基本操作&连结数据库）

概要

1 常用pandas操作方法
2 pandas操作数据库

1 常用pandas操作方法：

  • 创建DataFrame数据
  • 查看数据相关信息
  • 查看头尾文件
  • 花样取数
  • 切片取数
  • 常见函数使用
  • 首先，导入包
import pandas as pd
import numpy as np

# 01 创建DataFrame数据
# 方式1：通过字典直接创建

df1 = pd.DataFrame({
    "name":["小明","小红","小孙","王小","关宇","刘蓓","张菲"],
    "age":[20,18,27,20,28,18,25],
    "sex":["男","女","男","男","男","女","女"],
    "score":[669,570,642,590,601,619,701],
    "address":["北京","深圳","广州","武汉","深圳","广州","长沙"]
})
df1
name age sex score address
0 小明 20 男 669 北京
1 小红 18 女 570 深圳
2 小孙 27 男 642 广州
3 王小 20 男 590 武汉
4 关宇 28 男 601 深圳
5 刘蓓 18 女 619 广州
6 张菲 25 女 701 长沙 
# dataframe 写入excel文件
df1.to_excel("../data/stu.xls")

# 方式2：通过本地数据读取
# pandas 读取excel

df2 =pd.read_excel("../data/stu1.xls")
df2
name age sex score address
0 小明 20 男 669 北京
1 小红 18 女 570 深圳
2 小孙 27 男 642 广州
3 王小 20 男 590 武汉
4 关宇 28 男 601 深圳
5 刘蓓 18 女 619 广州
6 张菲 25 女 701 长沙 
# 02 数据探索
# shape表示数据是由多少行和列组成：
df1.shape # (7,5)

(7, 5)

# 查看字段属性名称
# 查看属性的数据类型
df1.columns
df1.dtypes

name       object
age         int64
sex        object
score       int64
address    object
dtype: object

#查看数据是否缺失
df1.isnull() # 如果缺失显示为True，否则显示False
name age sex score address
0 False False False False False
1 False False False False False
2 False False False False False
3 False False False False False
4 False False False False False
5 False False False False False
6 False False False False False 
df1.isnull().sum() # 统计缺失值的个数。一个True计数一次

name       0
age        0
sex        0
score      0
address    0
dtype: int64

# 查看数据行索引
df1.index

RangeIndex(start=0, stop=7, step=1)

# 查看数据描述信息
df1.describe

<bound method NDFrame.describe of   name  age sex  score address
0   小明   20   男    669      北京
1   小红   18   女    570      深圳
2   小孙   27   男    642      广州
3   王小   20   男    590      武汉
4   关宇   28   男    601      深圳
5   刘蓓   18   女    619      广州
6   张菲   25   女    701      长沙>

# 查看数据统计值
df1.describe()  #统计值的结果包含：个数count、均值mean、方差std、最值minmax、四分位数25%、中位数50%、四分之三分位数75%。
age score
count 7.000000 7.000000
mean 22.285714 627.428571
std 4.270608 46.227182
min 18.000000 570.000000
25% 19.000000 595.500000
50% 20.000000 619.000000
75% 26.000000 655.500000
max 28.000000 701.000000 
# 03 查看头尾文件
df1.head()   # 默认是查看前5行数据
df1.head(3)  # 指定显示的行数
name age sex score address
0 小明 20 男 669 北京
1 小红 18 女 570 深圳
2 小孙 27 男 642 广州 
df1.tail()    # 默认尾部5行
df1.tail(3)   # 指定尾部3行数据
name age sex score address
4 关宇 28 男 601 深圳
5 刘蓓 18 女 619 广州
6 张菲 25 女 701 长沙 
# 04 花样取数
# 取出某个字段的数据
# 我们取出name这列的数据：

name = df1["name"]
name

0    小明
1    小红
2    小孙
3    王小
4    关宇
5    刘蓓
6    张菲
Name: name, dtype: object

# 取出多个字段的数据
# 比如我们取出name和age列的数据：

name_age = df1[["name","age"]]  
name_age
name age
0 小明 20
1 小红 18
2 小孙 27
3 王小 20
4 关宇 28
5 刘蓓 18
6 张菲 25 
# 根据字段类型选择数据
# 比如，我们想选择字段类型为int64的数据，通过查看的字段数据类型显示：age和score都是int64类型

# 1、选择单个数据类型
df1.select_dtypes(include='int64')
age score
0 20 669
1 18 570
2 27 642
3 20 590
4 28 601
5 18 619
6 25 701 
# 2、同时选择多个类型
df1.select_dtypes(include=['int64','object'])
name age sex score address
0 小明 20 男 669 北京
1 小红 18 女 570 深圳
2 小孙 27 男 642 广州
3 王小 20 男 590 武汉
4 关宇 28 男 601 深圳
5 刘蓓 18 女 619 广州
6 张菲 25 女 701 长沙 
# 3、选择排除某些数据类型之外的数据：
# 选择除了int64类型之外的数据
# 排除name和score字段之外的数据
df1.select_dtypes(exclude='int64')
name sex address
0 小明 男 北京
1 小红 女 深圳
2 小孙 男 广州
3 王小 男 武汉
4 关宇 男 深圳
5 刘蓓 女 广州
6 张菲 女 长沙 
# 根据数值大小取数

# 1、直接通过判断大小来取数：
df1[df1["age"] == 20]  # 年龄等于20
df1[df1["age"] != 20]  # 年龄不等于20
df1[df1["age"] >= 20]  # 年龄大于等于20
name age sex score address
0 小明 20 男 669 北京
3 王小 20 男 590 武汉 
# 2、多个判断条件连用
df1[(df1["age"] >= 20) & (df1["age"] < 27)]
name age sex score address
0 小明 20 男 669 北京
3 王小 20 男 590 武汉
6 张菲 25 女 701 长沙 
# 根据字符串取数

# 1、单条数据
df1[df1["name"] == "小明"]
name age sex score address
0 小明 20 男 669 北京 
# 2、通过多个条件取数

# 选择姓名是小明，或者年龄大于25的数据
df1[(df1["name"] == "小明") | (df1["age"] > 25)]
name age sex score address
0 小明 20 男 669 北京
2 小孙 27 男 642 广州
4 关宇 28 男 601 深圳 
# 3、字符串的开始、结尾、包含函数
# str.startswith(string)
# str.endswith(string)
# str.contains(string)

# 1、取出以“小”开头的姓名
df1[df1["name"].str.startswith("小")]  # name以"小"开头
name age sex score address
0 小明 20 男 669 北京
1 小红 18 女 570 深圳
2 小孙 27 男 642 广州 
# 以“关”开始
df1[df1["name"].str.startswith("关")]
name age sex score address
4 关宇 28 男 601 深圳 
# 3、以“菲”结尾
df1[df1["name"].str.endswith("菲")]
name age sex score address
6 张菲 25 女 701 长沙 
# 取出包含“小”的数据：不管小是在开头，还是结尾都会被选出来
df1[df1["name"].str.contains("小")]
name age sex score address
0 小明 20 男 669 北京
1 小红 18 女 570 深圳
2 小孙 27 男 642 广州
3 王小 20 男 590 武汉 
# 4、字符串取反操作
# 取反符号是波浪线：~
# 取出不包含小的数据
df1[~df1["name"].str.contains("小")]
name age sex score address
4 关宇 28 男 601 深圳
5 刘蓓 18 女 619 广州
6 张菲 25 女 701 长沙 
# 05 切片取数

# 1、通过下面的3个案例说明：起始索引默认从0开始，步长默认是1
df1[:4] #
name age sex score address
0 小明 20 男 669 北京
1 小红 18 女 570 深圳
2 小孙 27 男 642 广州
3 王小 20 男 590 武汉 
df1[0:4] #默认步长是一
name age sex score address
0 小明 20 男 669 北京
1 小红 18 女 570 深圳
2 小孙 27 男 642 广州
3 王小 20 男 590 武汉 
df1[0:4:1] #指定起始，结束索引，步长
name age sex score address
0 小明 20 男 669 北京
1 小红 18 女 570 深圳
2 小孙 27 男 642 广州
3 王小 20 男 590 武汉 
# 2、指定起始索引，不指定结束索引，表示一直取到数据末尾
df1[4:]  # 从索引4开始取到末尾
name age sex score address
4 关宇 28 男 601 深圳
5 刘蓓 18 女 619 广州
6 张菲 25 女 701 长沙 
# 3、改变步长的值
df1[0:4:2]  # 改变步长：每隔2个值取一行数据
name age sex score address
0 小明 20 男 669 北京
2 小孙 27 男 642 广州 
# 4、只指定步长
df1[::2]   # 从头到尾，步长为2
name age sex score address
0 小明 20 男 669 北京
2 小孙 27 男 642 广州
4 关宇 28 男 601 深圳
6 张菲 25 女 701 长沙 
# 步长为负数
# 1、步长为-1，默认是倒序输出结果
df1[::-1]
name age sex score address
6 张菲 25 女 701 长沙
5 刘蓓 18 女 619 广州
4 关宇 28 男 601 深圳
3 王小 20 男 590 武汉
2 小孙 27 男 642 广州
1 小红 18 女 570 深圳
0 小明 20 男 669 北京 
# 2、步长为负，指定起始和终止索引，起始索引大于终止索引
df1[4:0:-1]
name age sex score address
4 关宇 28 男 601 深圳
3 王小 20 男 590 武汉
2 小孙 27 男 642 广州
1 小红 18 女 570 深圳 
# 3、起始和终止索引为负数
df1[-1:-5:-1]  # 最后一行记录索引为-1，不包含索引为-5的数据
name age sex score address
6 张菲 25 女 701 长沙
5 刘蓓 18 女 619 广州
4 关宇 28 男 601 深圳
3 王小 20 男 590 武汉 
# 06 常用函数
# 统计元素个数
# 很多时候我们需要统计某个列中每个元素出现的个数，相当于是做词频统计，使用：value_counts()方法，具体案例为：

# ⚠️：新数据中df1增加了一列：班级class，后续有作用

df1 = pd.DataFrame({
    "name":["小明","小红","小孙","王小","关宇","刘蓓","张菲"],
    "age":[20,18,27,20,28,18,25],
    "sex":["男","女","男","男","男","女","女"],
    "score":[669,570,642,590,601,619,701],
    "address":["北京","深圳","广州","武汉","深圳","广州","长沙"],
    "class":["two","one","two","one","one","one","two"]
})
df1
name age sex score address class
0 小明 20 男 669 北京 two
1 小红 18 女 570 深圳 one
2 小孙 27 男 642 广州 two
3 王小 20 男 590 武汉 one
4 关宇 28 男 601 深圳 one
5 刘蓓 18 女 619 广州 one
6 张菲 25 女 701 长沙 two 
# 比如我们想统计每个城市出现了多少次：
address = df1["address"].value_counts()
address

广州    2
深圳    2
长沙    1
武汉    1
北京    1
Name: address, dtype: int64

type(address) #结果自动是降序排列的Series类型数据

pandas.core.series.Series

# 小练习
a =[{'count': 1, 'source': 'www.caacnews.com.cn'}, {'count': 2, 'source': 'www.zhitongcaijing.com'}, {'count': 1, 'source': 'www.myzaker.com'}, {'count': 6, 'source': 'www.hdzc.net'}, {'count': 1, 'source': 'news.sohu.com'}, {'count': 2, 'source': 'www.chinastock.com.cn'}, {'count': 1, 'source': 'tech.hexun.com'}, {'count': 30, 'source': 'news.10jqka.com.cn'}, {'count': 1, 'source': 'it.sohu.com'}, {'count': 1, 'source': 'futures.eastmoney.com'}, {'count': 1, 'source': 'funds.hexun.com'}, {'count': 26, 'source': 'www.yuncaijing.com'}, {'count': 16, 'source': 'www.163.com'}, {'count': 1, 'source': 'www.subaonet.com'}, {'count': 2, 'source': 'www.thepaper.cn'}, {'count': 1, 'source': 'www.jxyuging.com'}, {'count': 1, 'source': 'www.nbd.com.cn'}, {'count': 1, 'source': '36kr.com'}, {'count': 7, 'source': 'yuanchuang.10jqka.com.cn'}, {'count': 2, 'source': 'libattery.ofweek.com'}, {'count': 77, 'source': 'finance.ifeng.com'}, {'count': 11, 'source': 'www.sohu.com'}, {'count': 1, 'source': 'fund.cfi.cn'}, {'count': 6, 'source': 'dy.163.com'}, {'count': 5, 'source': 'www.cs.com.cn'}, {'count': 8, 'source': 'www.yicai.com'}, {'count': 1, 'source': 'www.cfi.net.cn'}, {'count': 1, 'source': 'forex.cngold.org'}, {'count': 1, 'source': 'www.zt5.com'}, {'count': 3, 'source': 'www.zhicheng.com'}, {'count': 1, 'source': 'm.elecfans.com'}, {'count': 3, 'source': 'stock.stockstar.com'}, {'count': 1, 'source': 'www.gg-led.com'}, {'count': 8, 'source': 'www.p5w.net'}, {'count': 3, 'source': 'stock.gucheng.com'}, {'count': 1, 'source': 'gd.china.com'}, {'count': 1, 'source': 'laoyaoba.com'}, {'count': 4, 'source': 'www.cfbond.com'}, {'count': 4, 'source': 'xw.qq.com'}, {'count': 1, 'source': 'www.xfrb.com.cn'}, {'count': 1, 'source': 'szsb.sznews.com'}, {'count': 2, 'source': 'news.hexun.com'}, {'count': 1, 'source': 'www.cqcb.com'}, {'count': 2, 'source': 'www.linkshop.com.cn'}, {'count': 4, 'source': 'barb.sznews.com'}, {'count': 47, 'source': 'stock.eastmoney.com'}, {'count': 9, 'source': 'sc.stock.cnfol.com'}, {'count': 2, 'source': 'www.csrc.gov.cn'}, {'count': 1, 'source': 'm.thepaper.cn'}, {'count': 1, 'source': 'www.zhimeng.com.cn'}, {'count': 1, 'source': 'xg.stock.cnfol.com'}, {'count': 1, 'source': 'www.kejixun.com'}, {'count': 1, 'source': 'www.jinse.com'}, {'count': 1, 'source': 'news.longhoo.net'}, {'count': 1, 'source': 'www.jiemian.com'}, {'count': 3, 'source': 'stock.cfi.cn'}, {'count': 2, 'source': 'www.itdcw.com'}, {'count': 1, 'source': 'ggjd.cnstock.com'}, {'count': 1, 'source': 'www.cet.com.cn'}, {'count': 1, 'source': 'news.163.com'}, {'count': 91, 'source': 'stock.10jqka.com.cn'}, {'count': 1, 'source': 'www.ceweekly.cn'}, {'count': 1, 'source': 'stock.caijing.com.cn'}, {'count': 14, 'source': 'stock.hexun.com'}, {'count': 20, 'source': 'finance.eastmoney.com'}]
df2=pd.DataFrame(a)
df2
count source
0 1 www.caacnews.com.cn
1 2 www.zhitongcaijing.com
2 1 www.myzaker.com
3 6 www.hdzc.net
4 1 news.sohu.com
... ... ...
60 91 stock.10jqka.com.cn
61 1 www.ceweekly.cn
62 1 stock.caijing.com.cn
63 14 stock.hexun.com
64 20 finance.eastmoney.com

65 rows × 2 columns



# 索引重置

# 索引重置使用reset_index()
address_new = address.reset_index()
address_new
index address
0 广州 2
1 深圳 2
2 长沙 1
3 武汉 1
4 北京 1 
# 还比如我们想从数据中单独取出sex="男"的数据：
fale = df1[df1["sex"] == "男"]
fale
name age sex score address class
0 小明 20 男 669 北京 one
2 小孙 27 男 642 广州 one
3 王小 20 男 590 武汉 one
4 关宇 28 男 601 深圳 one 
fale_1=fale.reset_index()
fale_1
index name age sex score address class
0 0 小明 20 男 669 北京 one
1 2 小孙 27 男 642 广州 one
2 3 王小 20 男 590 武汉 one
3 4 关宇 28 男 601 深圳 one 
# 出现的结果中索引是我们想要的结果，但是出现了一列新的数据，就是原来的索引构成的数据，这不是我们想要的数据，需要去除：
fale_1 = fale.reset_index(drop=True)  # 加上参数即可解决
fale_1
name age sex score address class
0 小明 20 男 669 北京 one
1 小孙 27 男 642 广州 one
2 王小 20 男 590 武汉 one
3 关宇 28 男 601 深圳 one 
# 属性重命名
# 使用的是rename函数，传入columsn参数：
address_new
index address
0 广州 2
1 深圳 2
2 长沙 1
3 武汉 1
4 北京 1 
address_new = address_new.rename(columns={"index":"address",
                                         "address":"number"
                                        })
address_new
address number
0 广州 2
1 深圳 2
2 长沙 1
3 武汉 1
4 北京 1 
df1
name age sex score address class
0 小明 20 男 669 北京 one
1 小红 18 女 570 深圳 two
2 小孙 27 男 642 广州 one
3 王小 20 男 590 武汉 one
4 关宇 28 男 601 深圳 one
5 刘蓓 18 女 619 广州 two
6 张菲 25 女 701 长沙 two 
# groupby使用
# groupby主要是实现分组统计的功能:
# 1、比如我们想统计男女各自的总分

# 统计男女的总成绩：sum
sex_score = df1.groupby("sex")["score"].sum()
sex_score

sex
女    1890
男    2502
Name: score, dtype: int64

# 2、求男女各自的平均分mean
# 统计男女的平均成绩：mean
sex_score = df1.groupby("sex")["score"].mean()
sex_score

sex
女    630.0
男    625.5
Name: score, dtype: float64

# 3、根据男女性别sex、班级class求总分
# 先根据性别、班级求总分

sex_class = df1.groupby(["sex","class"])["score"].sum()
sex_class

sex  class
女    one      1189
     two       701
男    one      1191
     two      1311
Name: score, dtype: int64

#  一行代码实现

df1.groupby(["sex","class"])["score"].sum().reset_index()
sex class score
0 女 one 1189
1 女 two 701
2 男 one 1191
3 男 two 1311 
# apply函数
# 还是上面的df1数据集：

df1
name age sex score address class
0 小明 20 男 669 北京 two
1 小红 18 女 570 深圳 one
2 小孙 27 男 642 广州 two
3 王小 20 男 590 武汉 one
4 关宇 28 男 601 深圳 one
5 刘蓓 18 女 619 广州 one
6 张菲 25 女 701 长沙 two 
# 1、需求1：我们想将性别中的男变成1，女变成0
# 1、改变：男-1，女-0

df2 = df1.copy()  # 生成一个副本

df2["sex"] = df2["sex"].apply(lambda x: 1 if x=="男" else 0)  # 通过匿名函数解决
df2
name age sex score address class
0 小明 20 1 669 北京 two
1 小红 18 0 570 深圳 one
2 小孙 27 1 642 广州 two
3 王小 20 1 590 武汉 one
4 关宇 28 1 601 深圳 one
5 刘蓓 18 0 619 广州 one
6 张菲 25 0 701 长沙 two 
# 我们还可以自定义一个函数来实现：

#  自定义函数

def apply_sex(x):
    return 1 if x == "男" else 0

df3 = df1.copy()  # 生成一个副本df3

df3["sex"] = df3["sex"].apply(apply_sex)  # 通过自定义函数解决
df3
name age sex score address class
0 小明 20 1 669 北京 two
1 小红 18 0 570 深圳 one
2 小孙 27 1 642 广州 two
3 王小 20 1 590 武汉 one
4 关宇 28 1 601 深圳 one
5 刘蓓 18 0 619 广州 one
6 张菲 25 0 701 长沙 two 
# 2、还比如我们想给每个城市的后面加上一个“市”，变成北京市、深圳市等：

# 2、给每个城市加上一个字：市，变成北京市、深圳市等

df4 = df1.copy()

df4["address"] = df4["address"].apply(lambda x: x + "市")
df4
name age sex score address class
0 小明 20 男 669 北京市 two
1 小红 18 女 570 深圳市 one
2 小孙 27 男 642 广州市 two
3 王小 20 男 590 武汉市 one
4 关宇 28 男 601 深圳市 one
5 刘蓓 18 女 619 广州市 one
6 张菲 25 女 701 长沙市 two

2 pandas操作数据库（导出excel,csv文件等）

  • 导入必要模块
import pymysql
import pandas as pd
from sqlalchemy import create_engine
  • 初始化数据库连接，使用pymysql模块
  • MySQL的用户：root, 密码:, ip:@127.0.0.1,:端口：3306,数据库：sw_news
engine = create_engine('mysql+pymysql://root:@127.0.0.1:3306/sw_news')
  • 查询语句，选出 jianjie_gj_member_bill_record 表中的所有数据
sql = '''
      select * from company;
      '''
  • 查询：read_sql_query的两个参数: sql语句， 数据库连接
df = pd.read_sql_query(sql, engine)
  • 输出company表的查询结果
print(df)
df.to_csv('bill.csv',index=False)   #将结果存储到csv文件
df.to_excel('aa.xls',index=False,sheet_name='bill')   #将结果存储到excel
  • 新建pandas中的DataFrame, 只有id,num两列
df = pd.DataFrame({'id':[1,2,3,4],'full_name':[12,34,56,89]})
print(df)
  • 将新建的DataFrame储存为MySQL中的数据表，不储存index列
df.to_sql('mydf', engine, index= False)
print('Read from and write to Mysql table successfully!')

# 需求：统计42家公司的各个新闻源数据总数
import pymysql
import pandas as pd
from sqlalchemy import create_engine
engine = create_engine('mysql+pymysql://root:@127.0.0.1:3306/sw_news')

# 先找到42家公司id
company_id_list =[25, 794, 1473, 929, 1316, 1368, 790, 2319, 1225, 2344, 9, 2181, 671, 2301, 1130, 2338, 55, 1852, 3760, 1993, 1723, 2366, 1224, 1611, 2024, 12141, 1988, 4113, 1593, 639, 2368, 1196, 2247, 1228, 1588, 1270, 1572, 1445, 1686, 562, 806, 1662]
company_id_tuple=tuple(company_id_list)

sql = f'select * from news_detail where company in {company_id_tuple};'

df = pd.read_sql_query(sql, engine)
# print(df)

# 选择需要的列进行展示["title","web_source","company","id"]
# name_age = df1[["name","age"]] 
select_field_df = df[["web_source","company","id"]] 
print(select_field_df)


      web_source company    id
0  diancheziyuan     794  2625

# 需求：统计42家公司的各个新闻源数据总数
import pymysql
import pandas as pd
from sqlalchemy import create_engine
engine = create_engine('mysql+pymysql://root:@127.0.0.1:3306/sw_news')

sql = 'select * from news_detail where company !=-1;'

df = pd.read_sql_query(sql, engine)
# print(df)

# 选择需要的列进行展示["title","web_source","company","id"]
# name_age = df1[["name","age"]] 
select_field_df = df[["web_source","company","id"]] 
print(select_field_df)


       web_source company    id
0     qicheshidai     962     2
1     jingrongjie    3024    31
2     jingrongjie     962    35
3     jingrongjie    2696    38
4     jingrongjie    3567    39
...           ...     ...   ...
1611          163    3110  6987
1612          163    1045  6994
1613          163    2691  6995
1614          163     588  6996
1615          163    3110  6998

[1616 rows x 3 columns]

# df1.groupby(["sex","class"])["score"].sum().reset_index()
a =select_field_df.groupby(["company","web_source"]).count().reset_index()
a
id
company web_source
1 caijing 3
eastmoney 1
huitongcaijing 1
jingrongjie 2
10029 dianman 1
... ... ...
962 lieyun 1
qicheshidai 1
9840 jingrongjie 1
989 dianman 1
9912 jingrongjie 4

800 rows × 1 columns

a.to_excel('count2.xls',sheet_name='bill')   #将结果存储到excel

a =select_field_df.groupby(["company","web_source"]).count().reset_index()
company web_source id
0 1 caijing 3
1 1 eastmoney 1
2 1 huitongcaijing 1
3 1 jingrongjie 2
4 10029 dianman 1
... ... ... ...
795 962 lieyun 1
796 962 qicheshidai 1
797 9840 jingrongjie 1
798 989 dianman 1
799 9912 jingrongjie 4

800 rows × 3 columns
作者：华王 博客：https://www.cnblogs.com/huahuawang/
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/huahuawang/p/14888895.html