第十六篇 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、最值min\max、四分位数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


posted @ 2021-06-16 12:19  风hua  阅读(811)  评论(0编辑  收藏  举报