pandas数据处理(二)
简单研究下读取mysql、查询、分组、聚合、绘图。
其还有窗口函数等更加复杂的操作，暂时不做研究。
1. 准备数据
DROP TABLE IF EXISTS `t_user_log`;
CREATE TABLE `t_user_log` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`msg` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`create_time` datetime(0) DEFAULT NULL,
`fullname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`userage` int(255) DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of t_user_log
-- ----------------------------
INSERT INTO `t_user_log` VALUES (1, 'zs', '登录系统', '2022-08-19 10:09:37', '张三', 20);
INSERT INTO `t_user_log` VALUES (2, 'zs', '点击用户信息', '2022-08-19 10:09:59', '张三', 20);
INSERT INTO `t_user_log` VALUES (3, 'zs', NULL, '2022-08-19 10:10:12', '张三', 20);
INSERT INTO `t_user_log` VALUES (4, NULL, '登录系统', '2022-08-19 10:10:31', NULL, NULL);
INSERT INTO `t_user_log` VALUES (5, 'ls', '访问产品1', '2022-08-19 10:10:46', '李四', 30);
mysql 数据如下：
2. 分组聚合
1. 读取mysql 数据
import pymysql
import pandas as pd
if __name__ == '__main__':
# 打开数据库连接
conn = pymysql.connect(host="localhost",
port=3306,
user="root",
passwd="123456",
db="test")
sql = "select * from `t_user_log` "
df = pd.read_sql_query(sql, conn)
print(df.to_string())
print('=====1')
# 关闭连接
conn.close()
结果：
id username msg create_time fullname userage
0 1 zs 登录系统 2022-08-19 10:09:37 张三 20.0
1 2 zs 点击用户信息 2022-08-19 10:09:59 张三 20.0
2 3 zs None 2022-08-19 10:10:12 张三 20.0
3 4 None 登录系统 2022-08-19 10:10:31 None NaN
4 5 ls 访问产品1 2022-08-19 10:10:46 李四 30.0
补充：写出到mysql
import pandas as pd
import sqlalchemy
df = pd.read_excel('E:/test_data.xlsx')
# 处理空值 'null' 串
df.fillna('null', inplace=True)
# 打开数据库连接
engine = sqlalchemy.create_engine("mysql+pymysql://root:root@192.168.XX.XXX:3306/test?charset=utf8")
# 会自动建表
# df.to_sql(name='table_name', con=engine)
# index_label: 指定添加的id索引列, 不指定为插入index列作为自增列
# if_exists: {'fail', 'replace', 'append'}, default 'fail'. 重复数据如何处理， 默认是报错
df.to_sql(name='table_name', con=engine, index_label="id", if_exists='replace')
如果表不存在会建立表，如果表存在，我们一般if_exists 会选择append； 对于replace，即使存在表也会删除表重建(有时候数据类型和我们期望的类型不同)。 所以一般如果表有特殊的业务需求，推荐自己建表结构和字段、如果没有业务只做结果存储可以依赖pandas自动建表。
2. 查询操作
1. 简单的查询：
import pymysql
import pandas as pd
if __name__ == '__main__':
# 打开数据库连接
conn = pymysql.connect(host="localhost",
port=3306,
user="root",
passwd="123456",
db="test")
sql = "select * from `t_user_log` "
df = pd.read_sql_query(sql, conn)
print(df.to_string())
print('=====0')
# 1. 按列选择. 下面等价
# df.A 等价于 df['A'], 多列可以用 df[['A', 'B']]
print(df['username'])
print(df.username)
print('=====01')
# []选择行
# 选择所有数据
print(df[:])
print('=====02')
# 通过索引位置选择某几行，该方法按照左闭右开方式截取数据
print(df[0:2])
print('=====1')
'''
1.df.loc方法，根据行、列的标签值查询
# 定位单个元素。xIndex和yIndex 可以省略，也可以多个。 多个写法是 [1, 2]; 范围写法是 0:2
df.loc[xIndex: yIndex]
'''
# 找行
# 找到第一行
print(df.loc[0])
print('=====2')
# 找到第一行和第三行
print(df.loc[[0, 2]])
print('=====21')
# 按范围找0-3行(开始和结束都包括)
print(df.loc[0 : 2])
print('=====2')
# 找列
# 单列
print(df.loc[:, 'username'])
print('=====3')
# 多列
print(df.loc[:, ['username', 'msg']])
print('=====4')
# 找username=zs 的fullname 和 userage
print(df.loc[df['username'] == 'zs', ['fullname', 'userage']])
'''
2.df.iloc方法，根据行、列的数字位置查询
'''
print('=====5')
print(df.iloc[0, 0])
print(df.iloc[0, [0, 1, 2]])
'''
3.df.where & mask(where 取反)方法
'''
print('=====6')
print(df.where(df.userage > 20))
print('=====61')
print(df.mask(df.userage > 20))
'''
4.df.query方法
'''
print('=====7')
print(df.query('username=="zs" & msg=="登录系统"'))
# 关闭连接
conn.close()
结果：
id username msg create_time fullname userage
0 1 zs 登录系统 2022-08-19 10:09:37 张三 20.0
1 2 zs 点击用户信息 2022-08-19 10:09:59 张三 20.0
2 3 zs None 2022-08-19 10:10:12 张三 20.0
3 4 None 登录系统 2022-08-19 10:10:31 None NaN
4 5 ls 访问产品1 2022-08-19 10:10:46 李四 30.0
=====0
0 zs
1 zs
2 zs
3 None
4 ls
Name: username, dtype: object
0 zs
1 zs
2 zs
3 None
4 ls
Name: username, dtype: object
=====01
id username msg create_time fullname userage
0 1 zs 登录系统 2022-08-19 10:09:37 张三 20.0
1 2 zs 点击用户信息 2022-08-19 10:09:59 张三 20.0
2 3 zs None 2022-08-19 10:10:12 张三 20.0
3 4 None 登录系统 2022-08-19 10:10:31 None NaN
4 5 ls 访问产品1 2022-08-19 10:10:46 李四 30.0
=====02
id username msg create_time fullname userage
0 1 zs 登录系统 2022-08-19 10:09:37 张三 20.0
1 2 zs 点击用户信息 2022-08-19 10:09:59 张三 20.0
=====1
id 1
username zs
msg 登录系统
create_time 2022-08-19 10:09:37
fullname 张三
userage 20.0
Name: 0, dtype: object
=====2
id username msg create_time fullname userage
0 1 zs 登录系统 2022-08-19 10:09:37 张三 20.0
2 3 zs None 2022-08-19 10:10:12 张三 20.0
=====21
id username msg create_time fullname userage
0 1 zs 登录系统 2022-08-19 10:09:37 张三 20.0
1 2 zs 点击用户信息 2022-08-19 10:09:59 张三 20.0
2 3 zs None 2022-08-19 10:10:12 张三 20.0
=====2
0 zs
1 zs
2 zs
3 None
4 ls
Name: username, dtype: object
=====3
username msg
0 zs 登录系统
1 zs 点击用户信息
2 zs None
3 None 登录系统
4 ls 访问产品1
=====4
fullname userage
0 张三 20.0
1 张三 20.0
2 张三 20.0
=====5
1
id 1
username zs
msg 登录系统
Name: 0, dtype: object
=====6
id username msg create_time fullname userage
0 NaN NaN NaN NaT NaN NaN
1 NaN NaN NaN NaT NaN NaN
2 NaN NaN NaN NaT NaN NaN
3 NaN NaN NaN NaT NaN NaN
4 5.0 ls 访问产品1 2022-08-19 10:10:46 李四 30.0
=====61
id username msg create_time fullname userage
0 1.0 zs 登录系统 2022-08-19 10:09:37 张三 20.0
1 2.0 zs 点击用户信息 2022-08-19 10:09:59 张三 20.0
2 3.0 zs None 2022-08-19 10:10:12 张三 20.0
3 4.0 None 登录系统 2022-08-19 10:10:31 None NaN
4 NaN NaN NaN NaT NaN NaN
=====7
id username msg create_time fullname userage
0 1 zs 登录系统 2022-08-19 10:09:37 张三 20.0
2. 查找某一列为空的数据：
import pymysql
import pandas as pd
if __name__ == '__main__':
# 打开数据库连接
conn = pymysql.connect(host="localhost",
port=3306,
user="root",
passwd="123456",
db="test")
sql = "select * from `t_user_log` "
df = pd.read_sql_query(sql, conn)
print(df.to_string())
print('=====0')
# 查找为空的数据(查出来之后，其他数据全部为NaN)
print(df.where(df['msg'].isnull()))
print('=====1')
# 将其他无效数据剔除
print(df.where(df['msg'].isnull()).dropna(subset=['id']).to_string())
# 关闭连接
conn.close()
结果：
id username msg create_time fullname userage
0 1 zs 登录系统 2022-08-19 10:09:37 张三 20.0
1 2 zs 点击用户信息 2022-08-19 10:09:59 张三 20.0
2 3 zs None 2022-08-19 10:10:12 张三 20.0
3 4 None 登录系统 2022-08-19 10:10:31 None NaN
4 5 ls 访问产品1 2022-08-19 10:10:46 李四 30.0
=====0
id username msg create_time fullname userage
0 NaN NaN NaN NaT NaN NaN
1 NaN NaN NaN NaT NaN NaN
2 3.0 zs None 2022-08-19 10:10:12 张三 20.0
3 NaN NaN NaN NaT NaN NaN
4 NaN NaN NaN NaT NaN NaN
=====1
id username msg create_time fullname userage
2 3.0 zs None 2022-08-19 10:10:12 张三 20.0
3. 简单的统计以及聚合
import pymysql
import pandas as pd
if __name__ == '__main__':
# 打开数据库连接
conn = pymysql.connect(host="localhost",
port=3306,
user="root",
passwd="123456",
db="test")
sql = "select * from `t_user_log` "
df = pd.read_sql_query(sql, conn)
print(df.to_string())
print("======1")
# 返回series
print(df.count().__class__)
print(df.count())
print("======2")
print(df.count().max())
print("======3")
print(df.id.sum())
print(df.id.min())
print(df.id.max())
# 均值
print(df.id.mean())
# 中位数
print(df.id.median())
# 众数：出现次数最多的
print(df.username.mode())
# 关闭连接
conn.close()
结果：
id username msg create_time fullname userage
0 1 zs 登录系统 2022-08-19 10:09:37 张三 20.0
1 2 zs 点击用户信息 2022-08-19 10:09:59 张三 20.0
2 3 zs None 2022-08-19 10:10:12 张三 20.0
3 4 None 登录系统 2022-08-19 10:10:31 None NaN
4 5 ls 访问产品1 2022-08-19 10:10:46 李四 30.0
======1
<class 'pandas.core.series.Series'>
id 5
username 4
msg 4
create_time 5
fullname 4
userage 4
dtype: int64
======2
5
======3
15
1
5
3.0
3.0
0 登录系统
Name: msg, dtype: object
4. 排序
import pymysql
import pandas as pd
if __name__ == '__main__':
# 打开数据库连接
conn = pymysql.connect(host="localhost",
port=3306,
user="root",
passwd="123456",
db="test")
sql = "select * from `t_user_log` "
df = pd.read_sql_query(sql, conn)
print(df.to_string())
print('=====1')
# 按横坐标标签进行排序，逆序排序。 默认升序
print(df.sort_index(ascending=False))
print('=====2')
# 按照列进行排序
print(df.sort_values(by='id', ascending=False))
print('=====3')
# 按照列进行排序, 且指定排序算法
# mergesort、 heapsort、 quicksort
print(df.sort_values(by='userage', ascending=False, kind='quicksort'))
# 关闭连接
conn.close()
结果：
id username msg create_time fullname userage
0 1 zs 登录系统 2022-08-19 10:09:37 张三 20.0
1 2 zs 点击用户信息 2022-08-19 10:09:59 张三 20.0
2 3 zs None 2022-08-19 10:10:12 张三 20.0
3 4 None 登录系统 2022-08-19 10:10:31 None NaN
4 5 ls 访问产品1 2022-08-19 10:10:46 李四 30.0
=====1
id username msg create_time fullname userage
4 5 ls 访问产品1 2022-08-19 10:10:46 李四 30.0
3 4 None 登录系统 2022-08-19 10:10:31 None NaN
2 3 zs None 2022-08-19 10:10:12 张三 20.0
1 2 zs 点击用户信息 2022-08-19 10:09:59 张三 20.0
0 1 zs 登录系统 2022-08-19 10:09:37 张三 20.0
=====2
id username msg create_time fullname userage
4 5 ls 访问产品1 2022-08-19 10:10:46 李四 30.0
3 4 None 登录系统 2022-08-19 10:10:31 None NaN
2 3 zs None 2022-08-19 10:10:12 张三 20.0
1 2 zs 点击用户信息 2022-08-19 10:09:59 张三 20.0
0 1 zs 登录系统 2022-08-19 10:09:37 张三 20.0
=====3
id username msg create_time fullname userage
4 5 ls 访问产品1 2022-08-19 10:10:46 李四 30.0
0 1 zs 登录系统 2022-08-19 10:09:37 张三 20.0
1 2 zs 点击用户信息 2022-08-19 10:09:59 张三 20.0
2 3 zs None 2022-08-19 10:10:12 张三 20.0
3 4 None 登录系统 2022-08-19 10:10:31 None NaN
3. 数据更新
import pymysql
import pandas as pd
if __name__ == '__main__':
# 打开数据库连接
conn = pymysql.connect(host="localhost",
port=3306,
user="root",
passwd="123456",
db="test")
sql = "select * from `t_user_log` "
df = pd.read_sql_query(sql, conn)
print(df.to_string())
print("======1")
# 更新单个位置
df.loc[3, 'username'] = 'zs'
df.loc[3, 'fullname'] = '张三'
print(df.to_string())
print("======1")
# 新增一行
df.loc[5]=df.loc[4]
print(df.to_string())
# 新增一列
print("======2")
df['username(fullname)'] = df.username + '(' + df.fullname + ')'
print(df.to_string())
# 关闭连接
conn.close()
结果：
id username msg create_time fullname userage
0 1 zs 登录系统 2022-08-19 10:09:37 张三 20.0
1 2 zs 点击用户信息 2022-08-19 10:09:59 张三 20.0
2 3 zs None 2022-08-19 10:10:12 张三 20.0
3 4 None 登录系统 2022-08-19 10:10:31 None NaN
4 5 ls 访问产品1 2022-08-19 10:10:46 李四 30.0
======1
id username msg create_time fullname userage
0 1 zs 登录系统 2022-08-19 10:09:37 张三 20.0
1 2 zs 点击用户信息 2022-08-19 10:09:59 张三 20.0
2 3 zs None 2022-08-19 10:10:12 张三 20.0
3 4 zs 登录系统 2022-08-19 10:10:31 张三 NaN
4 5 ls 访问产品1 2022-08-19 10:10:46 李四 30.0
======1
id username msg create_time fullname userage
0 1 zs 登录系统 2022-08-19 10:09:37 张三 20.0
1 2 zs 点击用户信息 2022-08-19 10:09:59 张三 20.0
2 3 zs None 2022-08-19 10:10:12 张三 20.0
3 4 zs 登录系统 2022-08-19 10:10:31 张三 NaN
4 5 ls 访问产品1 2022-08-19 10:10:46 李四 30.0
5 5 ls 访问产品1 2022-08-19 10:10:46 李四 30.0
======2
id username msg create_time fullname userage username(fullname)
0 1 zs 登录系统 2022-08-19 10:09:37 张三 20.0 zs(张三)
1 2 zs 点击用户信息 2022-08-19 10:09:59 张三 20.0 zs(张三)
2 3 zs None 2022-08-19 10:10:12 张三 20.0 zs(张三)
3 4 zs 登录系统 2022-08-19 10:10:31 张三 NaN zs(张三)
4 5 ls 访问产品1 2022-08-19 10:10:46 李四 30.0 ls(李四)
5 5 ls 访问产品1 2022-08-19 10:10:46 李四 30.0 ls(李四)
4. 删除数据
import pymysql
import pandas as pd
if __name__ == '__main__':
# 打开数据库连接
conn = pymysql.connect(host="localhost",
port=3306,
user="root",
passwd="123456",
db="test")
sql = "select * from `t_user_log` "
df = pd.read_sql_query(sql, conn)
print(df.to_string())
print("======1")
# 根据index 行号进行删除
df.drop(0, inplace=True)
print(df.to_string())
print("======2")
# 找到msg为空的然后删除
# 实际是先找到为空的数据，然后找到行号index。 然后根据index进行删除
df1 = df.where(df['msg'].isnull()).dropna(subset=['id'])
for x in df1.index:
print("x: ", x)
print(df.loc[x])
df.drop(x, inplace=True)
print("======3")
print(df.to_string())
# 关闭连接
conn.close()
结果：
id username msg create_time fullname userage
0 1 zs 登录系统 2022-08-19 10:09:37 张三 20.0
1 2 zs 点击用户信息 2022-08-19 10:09:59 张三 20.0
2 3 zs None 2022-08-19 10:10:12 张三 20.0
3 4 None 登录系统 2022-08-19 10:10:31 None NaN
4 5 ls 访问产品1 2022-08-19 10:10:46 李四 30.0
======1
id username msg create_time fullname userage
1 2 zs 点击用户信息 2022-08-19 10:09:59 张三 20.0
2 3 zs None 2022-08-19 10:10:12 张三 20.0
3 4 None 登录系统 2022-08-19 10:10:31 None NaN
4 5 ls 访问产品1 2022-08-19 10:10:46 李四 30.0
======2
x: 2
id 3
username zs
msg None
create_time 2022-08-19 10:10:12
fullname 张三
userage 20.0
Name: 2, dtype: object
======3
id username msg create_time fullname userage
1 2 zs 点击用户信息 2022-08-19 10:09:59 张三 20.0
3 4 None 登录系统 2022-08-19 10:10:31 None NaN
4 5 ls 访问产品1 2022-08-19 10:10:46 李四 30.0
5. 简单分组聚合
import numpy as np
import pymysql
import pandas as pd
if __name__ == '__main__':
# 打开数据库连接
conn = pymysql.connect(host="localhost",
port=3306,
user="root",
passwd="123456",
db="test")
sql = "select * from `t_user_log` "
df = pd.read_sql_query(sql, conn)
print(df.to_string())
print("======1")
print(df.groupby('username').max())
print("======2")
print(df.groupby('username').count())
print("======3")
print(df.groupby('username').min())
print("======4")
print(df.groupby('username').mean())
print("======5")
# agg 自选聚合函数
print(df.groupby(['username', 'msg']).agg({'id': [np.count_nonzero]}).rename(columns={'count_nonzero': '次数'}))
# 关闭连接
conn.close()
结果：
id username msg create_time fullname userage
0 1 zs 登录系统 2022-08-19 10:09:37 张三 20.0
1 2 zs 点击用户信息 2022-08-19 10:09:59 张三 20.0
2 3 zs None 2022-08-19 10:10:12 张三 20.0
3 4 None 登录系统 2022-08-19 10:10:31 None NaN
4 5 ls 访问产品1 2022-08-19 10:10:46 李四 30.0
======1
E:\pyspace\craw\pandas_test\client4.py:18: FutureWarning: Dropping invalid columns in DataFrameGroupBy.max is deprecated. In a future version, a TypeError will be raised. Before calling .max, select only columns which should be valid for the function.
print(df.groupby('username').max())
id create_time fullname userage
username
ls 5 2022-08-19 10:10:46 李四 30.0
zs 3 2022-08-19 10:10:12 张三 20.0
======2
id msg create_time fullname userage
username
ls 1 1 1 1 1
zs 3 2 3 3 3
======3
id create_time fullname userage
username
ls 5 2022-08-19 10:10:46 李四 30.0
zs 1 2022-08-19 10:09:37 张三 20.0
======4
id userage
username
ls 5.0 30.0
zs 2.0 20.0
======5
id
次数
username msg
ls 访问产品1 1
zs 点击用户信息 1
登录系统 1
6. 绘图
简单的画图。(需要借助于Matplotlib 库。 Matplotlib 是一个专门的画图库 )
Pandas 之所以能够实现了数据可视化，主要利用了 Matplotlib 库的 plot() 方法，它对 plot() 方法做了简单的封装，因此您可以直接调用该接口。
参考： https://www.runoob.com/matplotlib/matplotlib-tutorial.html
http://c.biancheng.net/pandas/plot.html
1. 简单折线图
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
#创建包含时间序列的数据
df = pd.DataFrame(np.random.randn(8,4),index=pd.date_range('2/1/2020',periods=8), columns=list('ABCD'))
# 下面三种方式是等价的，默认是折线图
# df.plot()
# df.plot(kind='line')
df.plot.line()
plt.show()
还有下面相关的图：
柱状图：bar() 或 barh()
直方图：hist()
箱状箱：box()
区域图：area()
散点图：scatter()
饼图: pie()
2. 简单的柱状图
import numpy as np
import matplotlib.pyplot as plt
x = np.array(["Runoob-1", "Runoob-2", "Runoob-3", "C-RUNOOB"])
y = np.array([12, 22, 6, 18])
plt.bar(x, y, color = ["#4CAF50","red","hotpink","#556B2F"])
plt.show()
还可以自己设置柱状图的宽度等参数。
3. 基于测试数据生成用户、count 柱状图
import matplotlib.pyplot as plt
import pymysql
import pandas as pd
if __name__ == '__main__':
# 打开数据库连接
conn = pymysql.connect(host="localhost",
port=3306,
user="root",
passwd="123456",
db="test")
sql = "select * from `t_user_log` "
df = pd.read_sql_query(sql, conn)
print(df.to_string())
print('=====1')
df2 = df.groupby('username').count()._rename(columns={'id': 'count'}).loc[:,['count']]
df2.plot.bar()
plt.title('user-count')
plt.show()
# 关闭连接
conn.close()
结果：
4. 基于用户、count 生成饼图
import matplotlib.pyplot as plt
import pymysql
import pandas as pd
if __name__ == '__main__':
# 打开数据库连接
conn = pymysql.connect(host="localhost",
port=3306,
user="root",
passwd="123456",
db="test")
sql = "select * from `t_user_log` "
df = pd.read_sql_query(sql, conn)
print('=====1')
df2 = df.groupby('username').count()._rename(columns={'id': 'count'}).loc[:,['count']]
plt.pie(df2['count'], labels=df2.index,
autopct='%.2f%%')
plt.title('user-count')
plt.show()
# 关闭连接
conn.close()
结果：
补充:pandas 读excel，读取生成dataframe对象，然后处理dataframe 生成新的dataframe，最后导入到mysql
import pandas as pd
import sqlalchemy
df = pd.read_excel('E:/test_data.xlsx')
# 处理空值 'null' 串
null_val = 'null'
df.fillna(null_val, inplace=True)
# 遍历df，构造成二维数据。 CASE_ID, USER
data = []
for x in df.index:
# 遍历出的每一行是一个 Series
obj = df.loc[x]
case_id = str(obj.loc['CASE_ID'])
for y in obj.index:
if y != 'CASE_ID':
# 遍历列收集起来
if obj.loc[y] != null_val:
data.append([case_id, obj.loc[y]])
df_new = pd.DataFrame(data,columns=['CASE_ID','USER'])
# 打开数据库连接
engine = sqlalchemy.create_engine("mysql+pymysql://uname:pwd@192.168.xx.xx:3306/test?charset=utf8")
# 会自动建表
# df.to_sql(name='new_case2', con=engine)
# 指定添加的id索引列
# if_exists: {'fail', 'replace', 'append'}, default 'fail'. 重复数据如何处理
df_new.to_sql(name='new_case', con=engine, index_label="ID", if_exists='append')
【当你用心写完每一篇博客之后,你会发现它比你用代码实现功能更有成就感!】
