通常,我们获取原始数据后,往往需要进行一些整理,清洗之后才进行特定的指标运算。如果原始数据分散在多个数据文件中,或是关系数据库的多张表中,那使用Merge的进行数据整理的可能性就非常大。Merge(合并)是Pandas提供的一个数据整理方法,它的处理结果非常类似于关系数据库中的表连接,select … join。可以让我们通过指定的键(key),把数据关联起来。
Merge基础
假设有两个数据集,一个包含用户的账号信息(用户id和用户名),一个包含用户操作日志,记录某个用户在某个时间在系统中做两个某个操作。两个数据集分布如下:
账号信息
1 2 3 4 5
| acc_id acc_name 0 a-001 张三 1 a-002 李四 2 a-003 王二麻子 3 a-004 奥特曼
|
其中 acc_id 代表用户id, acc_name代表用户名称
操作日志
1 2 3 4 5 6 7
| acc_id func_id act_time 0 a-001 1 2015-09-28 10:21:22 1 a-001 2 2015-09-28 10:21:23 2 a-003 3 2015-09-28 10:21:24 3 a-002 2 2015-09-28 10:22:16 4 a-001 1 2015-09-28 10:225:56 5 a-005 -1 0000-00-00 00:00:00
|
func_id 代表用户操作的编码, act_time 代表用户的操作时间,注意: 第五条记录实际上是无效的。(我们会在后续的文章介绍如何过滤或改造这些无效数据)
现在,目标是将数据合并,在一个数据表中看到所有的相关信息(用户id, 用户名,操作id, 操作时间), 这是就可以用 pandas中的merge来完成。示例代码如下:
1 2 3 4 5 6 7 8 9 10 11 12 13
| import pandas as pd from pandas import DataFrame
f1 = DataFrame({'acc_id': ['a-001', 'a-002', 'a-003', 'a-004'], 'acc_name': ['张三', '李四', '王二麻子', '奥特曼']})
f2 = DataFrame({'acc_id': ['a-001', 'a-001', 'a-003', 'a-002', 'a-001', 'a-005'], 'func_id': [1, 2, 3, 2, 1, -1], 'act_time': ['2015-09-28 10:21:22', '2015-09-28 10:21:23', '2015-09-28 10:21:24', '2015-09-28 10:22:16', '2015-09-28 10:225:56', '0000-00-00 00:00:00']})
f3 = pd.merge(f1, f2)
print (f3)
|
执行这段代码,系统输出:
1 2 3 4 5 6
| acc_id acc_name func_id act_time 0 a-001 张三 1 2015-09-28 10:21:22 1 a-001 张三 2 2015-09-28 10:21:23 2 a-001 张三 1 2015-09-28 10:225:56 3 a-002 李四 2 2015-09-28 10:22:16 4 a-003 王二麻子 3 2015-09-28 10:21:24
|
可以看到,pandas自动以两个DataFrame中相同的key: acc_id进行了数据合并,注意:f1中a-004和f2中a-005因为没有匹配的数据,没有出现在合并后的数据集中,这点类似与关系数据库中的内连接,形成的是数据交集。
指定key的Merge
上一节中的代码时基于数据集中有相同的列值(key), 那如果原始数据的列名不匹配呢? Merge为我们提供了指定连接键名的参数: left_on 和 right_on 。 看下面的代码,假设在操作日志数据中的用户id名为user_id, 我们修改代码如下:
1 2 3 4 5 6 7 8 9 10 11 12 13
| import pandas as pd from pandas import DataFrame
f1 = DataFrame({'acc_id': ['a-001', 'a-002', 'a-003', 'a-004'], 'acc_name': ['张三', '李四', '王二麻子', '奥特曼']})
f2 = DataFrame({'user_id': ['a-001', 'a-001', 'a-003', 'a-002', 'a-001', 'a-005'], 'func_id': [1, 2, 3, 2, 1, -1], 'act_time': ['2015-09-28 10:21:22', '2015-09-28 10:21:23', '2015-09-28 10:21:24', '2015-09-28 10:22:16', '2015-09-28 10:225:56', '0000-00-00 00:00:00']})
f3 = pd.merge(f1, f2, left_on='acc_id', right_on='user_id')
print (f3)
|
运行程序,系统输出为:
1 2 3 4 5 6
| acc_id acc_name user_id func_id act_time 0 a-001 张三 a-001 1 2015-09-28 10:21:22 1 a-001 张三 a-001 2 2015-09-28 10:21:23 2 a-001 张三 a-001 1 2015-09-28 10:225:56 3 a-002 李四 a-002 2 2015-09-28 10:22:16 4 a-003 王二麻子 a-003 3 2015-09-28 10:21:24
|
外连接
如果我们希望得到类似外连接的效果,可以使用 how 参数,代码展示如下:
1 2 3 4 5 6 7 8 9 10 11 12 13
| import pandas as pd from pandas import DataFrame
f1 = DataFrame({'acc_id': ['a-001', 'a-002', 'a-003', 'a-004'], 'acc_name': ['张三', '李四', '王二麻子', '奥特曼']})
f2 = DataFrame({'acc_id': ['a-001', 'a-001', 'a-003', 'a-002', 'a-001', 'a-005'], 'func_id': [1, 2, 3, 2, 1, -1], 'act_time': ['2015-09-28 10:21:22', '2015-09-28 10:21:23', '2015-09-28 10:21:24', '2015-09-28 10:22:16', '2015-09-28 10:225:56', '0000-00-00 00:00:00']})
f3 = pd.merge(f1, f2, how='outer')
print (f3)
|
运行程序后,系统输出:
1 2 3 4 5 6 7 8
| acc_id acc_name func_id act_time 0 a-001 张三 1.0 2015-09-28 10:21:22 1 a-001 张三 2.0 2015-09-28 10:21:23 2 a-001 张三 1.0 2015-09-28 10:225:56 3 a-002 李四 2.0 2015-09-28 10:22:16 4 a-003 王二麻子 3.0 2015-09-28 10:21:24 5 a-004 奥特曼 NaN NaN 6 a-005 NaN -1.0 0000-00-00 00:00:00
|
注意: 第5, 6 条数据分别是两边数据集中不能进行匹配的数据项
左连接
how 这个参数除了支持”outer”,以外,还可以支持 “left” 值,结果和SQL中的左连接类似, 看代码:
1 2 3 4 5 6 7 8 9 10 11 12 13
| import pandas as pd from pandas import DataFrame
f1 = DataFrame({'acc_id': ['a-001', 'a-002', 'a-003', 'a-004'], 'acc_name': ['张三', '李四', '王二麻子', '奥特曼']})
f2 = DataFrame({'acc_id': ['a-001', 'a-001', 'a-003', 'a-002', 'a-001', 'a-005'], 'func_id': [1, 2, 3, 2, 1, -1], 'act_time': ['2015-09-28 10:21:22', '2015-09-28 10:21:23', '2015-09-28 10:21:24', '2015-09-28 10:22:16', '2015-09-28 10:225:56', '0000-00-00 00:00:00']})
f3 = pd.merge(f1, f2, how='left')
print (f3)
|
运行代码,系统输出:
1 2 3 4 5 6 7
| acc_id acc_name func_id act_time 0 a-001 张三 1.0 2015-09-28 10:21:22 1 a-001 张三 2.0 2015-09-28 10:21:23 2 a-001 张三 1.0 2015-09-28 10:225:56 3 a-002 李四 2.0 2015-09-28 10:22:16 4 a-003 王二麻子 3.0 2015-09-28 10:21:24 5 a-004 奥特曼 NaN NaN
|
可以看到,只有f1中不能匹配的数据被带到合并后的结果集中。
右连接
有左连接当然会有右连接,指定 how 的值为 “right” 即可。看代码:
1 2 3 4 5 6 7 8 9 10 11 12 13
| import pandas as pd from pandas import DataFrame
f1 = DataFrame({'acc_id': ['a-001', 'a-002', 'a-003', 'a-004'], 'acc_name': ['张三', '李四', '王二麻子', '奥特曼']})
f2 = DataFrame({'acc_id': ['a-001', 'a-001', 'a-003', 'a-002', 'a-001', 'a-005'], 'func_id': [1, 2, 3, 2, 1, -1], 'act_time': ['2015-09-28 10:21:22', '2015-09-28 10:21:23', '2015-09-28 10:21:24', '2015-09-28 10:22:16', '2015-09-28 10:225:56', '0000-00-00 00:00:00']})
f3 = pd.merge(f1, f2, how='right')
print (f3)
|
输出:
1 2 3 4 5 6 7
| acc_id acc_name func_id act_time 0 a-001 张三 1 2015-09-28 10:21:22 1 a-001 张三 2 2015-09-28 10:21:23 2 a-001 张三 1 2015-09-28 10:225:56 3 a-002 李四 2 2015-09-28 10:22:16 4 a-003 王二麻子 3 2015-09-28 10:21:24 5 a-005 NaN -1 0000-00-00 00:00:00
|
和左连接相反,只有f2中不能匹配的数据被带到合并后的数据集中了。