Python 关联处理

在日常工作中,我们难免会遇到将两个表或者多个表关联在一起的情况,这时通常是使用 SQL join 的方式进行关联并进行后续计算。但有时数据并不存储在数据库,而是以文件的形式存储在文件系统,单纯为了计算而把数据存储到数据库有点得不偿失。

Python 的 Pandas 提供了很好的关联运算函数,能更方便的完成文本文件间的关联计算,现在我们就一起来讨论下 Python 的关联处理。

关联基础

所谓关联是指两张数据表通过某列或者某些列之间存在的某种关系,将两张表以某种条件联系起来。SQL 中的关联可以是等值 JOIN 也可以是非等值 JOIN,所谓非等值 JOIN 就是 JOIN 时的过滤条件不一定是相等的,比如 select A.x,B.y from A join B on A.a<B.b。但在实际工作中这种运算是很少的,绝大多数情况都是等值 JOIN,而且即使遇到非等值 JOIN 的情况,多数情况下也可以转换成等值 JOIN。Python 的 Pandas 中的关联函数也都是等值关联的,因此在这里我们只讨论等值 JOIN 的情况。

关联的方式有内连接、左连接、右连接、全连接,Pandas 使用 merge() 函数的 how 参数完成,其中 inner:内连接,left:左连接,right:右连接,outer:全连接(默认 how=‘inner’),我们以一个例子加以说明。

合同数据文件和老客户数据文件分别存储在两份文件中,合同数据文件是 2019 年的合同信息,其中的 Client 可能有新客户,部分内容如下:

ContractNo

ActualSale

SellDate

Product

Quantity

Amount

Client

ApplyArea

ApplyMethod

10961

8

2019/3/19

52

6

1122

C67350

EastChina

SELF_USE

10962

8

2019/3/19

7

45

3584

C67393

SouthChina

SELF_USE

10963

9

2019/3/19

60

2

68

C27452

NorthChina

RESELL

10964

3

2019/3/20

18

6

2052.5

C77608

SouthChina

RESELL

10965

6

2019/3/20

51

16

848

C57885

EastChina

SELF_USE

10966

4

2019/3/20

37

8

1255.6

C13113

SouthChina

SELF_USE

老客户数据文件是历年来经常合作的老客户的信息,部分内容:

ID

Name

City

State

Country

SalesGoal

C908

ALFKI

Shenyang

Liaoning

China

3

C2262

ANATR

Dalian

Liaoning

China

1

C4352

ANTON

Yingkou

Liaoning

China

10

C4842

AROUT

benxi

Liaoning

China

6

C5505

BERGS

Shijiazhuang

Hebei

China

5

内连接(inner)

内连接,是两表关联后,保留两表共有的键。

问题一:计算老客户 2019 年的合同总金额

问题分析:由于合同数据中可能有新客户,也可能有老客户没有在 2019 年签订合同,所以需要使用内连接的方式将两表关联。

Python 代码

import pandas as pd

contract = pd.read_csv('Contract.csv')

client = pd.read_csv('Client.csv')

join_inner =   pd.merge(contract,client,left_on='Client',right_on='ID')

print(join_inner['Amount'].sum())

导入 pandas

合同数据

客户数据

内连接

 

计算合同总金额

讨论:contract 左侧 dataframe,client,右侧 dataframe,因为本例中关联的列在两个 dataframe 中的列名不同,所以不使用 on 参数,而是使用 left_on,左 dataframe 的关联列名,和 right_on,右 dataframe 的关联列名,此时默认的参数 how=’inner’。

对关联结果的 Amount 列求和得到结果。

左连接(left)

左连接,指两表关联后,保留左侧表的全部键,右侧表不具备左侧键的列为 nan。

问题二:计算出各省客户的合同金额,新客户的省份用 unknown 表示。

问题分析:因为不光要求各省老客户的合同金额,还要把新客户作为 unknown 来计算,因此关联的时候需要使用 left 的方式。

Python 代码

import pandas as pd

contract = pd.read_csv('Contract.csv')

client = pd.read_csv('Client.csv',usecols=['ID','State'])

join_left =   pd.merge(contract,client,left_on='Client',right_on='ID',how='left')

join_left.fillna('unknown',inplace=True)

state_amount =   join_left.groupby('State',as_index=False).Amount.sum()

print(state_amount)

 

 

 

左连接

 

‘unknown’填补缺失值

 

分组计算各省客户合同金额

讨论:修改默认参数 how=‘inner’为‘left’,实现左连接,保留新客户的合同信息。后续再用‘unknown’填补缺失值,分组求和得到结果。

右连接和全连接的方式就是修改 how=‘right’或者 how=‘outer’。这里就不再一一举例了。

左排除连接(left_exluding)

左排除连接,指两表关联后,只保留左侧键值未在右侧表的键出现的行,右侧表的列为 nan。

问题三:计算新客户的合同总金额

问题分析,只求新客户,只需要左连接后,排除掉两表共有的键的行就可以了。

Python 代码

import pandas as pd

contract = pd.read_csv('Contract.csv')

client =   pd.read_csv('Client.csv',usecols=['ID','State'])

join_left = pd.merge(contract,client,left_on='Client',right_on='ID',how='left',indicator=True)

join_left_exluding = join_left.query('_merge ==  "left_only"').drop('_merge', 1)

new_client_amount = join_left_exluding.Amount.sum()

print(new_client_amount)

 

 

 

左连接

 

左排除连接

计算新客户合同金额

讨论:左连接时,增加参数 indicator,那些两表共有的键的行为 both,左表独有的键的行为 left_only,右表独有的为 right_only。然后筛选出 left_only 标记的行就得到了新客户的行,直接求和得到结果。

右排除连接和全排除连接和左排除连接类似,这里也不举例说明了。

Pandas 中的关联函数除了 merge,还有 join 和 concat,我们继续以问题一——计算老客户 2019 年的合同总金额作为案例简单介绍一下这两个函数的使用方法,详细的使用方法请参考 Pandas 官网。

join 函数

Python 代码

import pandas as pd

contract =   pd.read_csv('Contract.csv',index_col='Client')

client = pd.read_csv('Client.csv',index_col='ID')

join_inner = contract.join(client,how='inner')

print(join_inner.Amount.sum())

 

导入合同数据,同时设置索引为‘Client’

 

导入客户数据,同时设置索引为‘ID’

使用 join 关联两表

讨论:join() 函数关联时,只能使用两表的索引,因此需要先将关联列设置成索引。join 时默认的方式 how=‘left’,这里需要修改为‘inner’。最后求和即可。

concat 函数

问题分析:concat()函数关联时,求关联表的索引是唯一的,这里合同数据的 Client 列有重复,不适合使用 concat() 函数关联,不过为了更好的理解 concat 函数,我们对数据中的客户去重,然后再用 concat 连接。

问题四:求老客户 2019 年第一份合同的总金额。

Python 代码

import pandas as pd

contract = pd.read_csv('Contract.csv')

client = pd.read_csv('Client.csv',index_col='ID')

contract = contract.drop_duplicates('Client',   keep='first')

contract.set_index('Client',inplace=True)

join_concat = pd.concat([contract,client],axis=1,   join='inner')

print(join_concat.Amount.sum())

 

 

导入客户数据,同时设置索引为‘ID’

Client列去重

设置 Client 为索引

concat关联

 

讨论:把两个 dataframe 放入列表,axis=1 指按索引进行关联,默认的 join 方式是 outer,这里改为 inner,最后进行求和。

以上只是简单介绍了 Pandas 中常用的一些关联方式和函数。下面我们对关联问题作出进一步分析。

关联分析

我们把关联总结为以下几种情况:外键关联、同维表关联、主子表关联。

外键关联

表 A 的某些字段与表 B 的主键关联(所谓关联,是指 JOIN 的过滤条件即由这些对应字段相等构成)。A 表称为事实表,B 表称为维表。A 表中与 B 表主键关联的字段称为 A 指向 B 的外键,B 也称为 A 的外键表。外键表是多对一的关系。

典型例子:帐户交易记录和帐户基本信息。(关联基础中介绍的例子,合同表与客户表也是这种关系,其中合同表是事实表,客户表是维表,合同表的 Client 字段是合同表指向客户表的外键,客户表的 ID 字段是主键。)

同维表关联

表 A 的主键与表 B 的主键关联,A 和 B 互称为同维表。同维表是一对一的关系。

典型例子:员工表和销售员表。其中员工表的主键为员工编号(EID),销售员表的主键为销售员编号(SID),两者是一对一的关系。

主子表关联

表 A 的主键与表 B 的部分主键关联,A 称为主表,B 称为子表。主子表是一对多的关系。

典型例子:订单和订单明细。其中订单表的主键为订单编号(OrderID),订单明细表的部分主键为订单编号(OrderID)。

这里说的主键是指逻辑上的主键,也就是在表中取值唯一的字段(组),一个表上可能有多个字段(组)都取值唯一(并不常见),可以认为都是主键。不是一定是在物理表上建立的那个主键。

这三种 JOIN 已经涵盖了绝大多数等值 JOIN 的情况,甚至可以说几乎全部有业务意义的等值 JOIN 都属于这三类,把等值 JOIN 限定在这三种情况之中,几乎不会减少其适应范围。

仔细考察这三种 JOIN,我们发现所有关联都涉及主键,没有多对多的情况,可以不考虑这种情况吗?

是的!多对多的等值 JOIN 几乎没有业务意义。

如果 JOIN 两个表时的关联字段没有涉及到任何主键,那就会发生多对多的情况,而这种情况几乎一定还会有一个规模更大的表把这两个表作为维表关联起来。比如学生表和科目表在 JOIN 时,会有个成绩表以学生表和科目表作为维表,单纯只有学生表和科目表的 JOIN 没有业务意义了。

不过,我们一直在说“几乎”,并没有用完全肯定的说法,也就是说,多对多在非常罕见的情况下也会有业务意义。可举一例,实现矩阵乘法时会发生多对多的等值 JOIN。

关联实例

外键关联

我们在第一部分关联基础中所举的例子就是典型的外键关联,其中合同表是事实表,客户表是外键表,合同表的 Client 字段是合同表指向客户表的外键,客户表的 ID 字段是主键。问题沿用前面的问题:计算老客户 2019 年的合同总金额。

第一部分已经详细介绍了这种情况,这里不再赘述。

再来看一个例子,设有两张表:

employee 员工表

部分数据如下:

EID

NAME

SURNAME

GENDER

STATE

BIRTHDAY

HIREDATE

DEPT

SALARY

1

Rebecca

Moore

F

California

1974/11/20

2005/3/11

6

7000

2

Ashley

Wilson

F

New York

1980/7/19

2008/3/16

2

11000

3

Rachel

Johnson

F

New   Mexico

1970/12/17

2010/12/1

7

9000

4

Emily

Smith

F

Texas

1985/3/7

2006/8/15

3

7000

5

Ashley

Smith

F

Texas

1975/5/13

2004/7/30

6

16000

6

Matthew

Johnson

M

California

1984/7/7

2005/7/7

7

11000

department部门表

部分数据如下:

DEPTID

NAME

MANAGER

1

Administration

20

2

Finance

2

3

HR

162

4

Marketing

47

5

Production

58

6

R&D

5

employee 表和 delpartment 表的主键分别是 EID 和 DEPTID 字段,employee 表的 DEPT 字段是指向 department 表的外键,department 表的 MANAGER 字段又是指向 employee 表的外键。这是很常规的表结构设计。

问题五:找出纽约州经理的纽约州员工

问题分析:首先需要使用 employee 表的 DEPT 字段关联 department 表的主键 DEPTID,获得 department 表的 MANAGER 字段,然后还要使用连接后的表的 MANAGER 字段关联 employee 表的主键 EID,获得经理的一些员工信息。

Python 代码如下:

import pandas as pd

emp = pd.read_csv('EMPLOYEES.csv')

dpt = pd.read_csv('DEPARTMENT.csv')

emp_m =   pd.merge(emp,dpt,left_on='DEPT',right_on='DEPTID')

emp_m_info =   pd.merge(emp_m,emp,left_on='MANAGER',right_on='EID')

emp_New_York = emp_m_info.query('STATE_x=="New   York"and STATE_y=="New York"')

print(emp_New_York)

 

 

 

员工表与部门表关联

 

新表与员工表关联

 

按条件筛选

 

 

讨论:外键关联时,left_on 总是事实表指向外键表的外键,right_on 是外表表的主键。因为两次使用了员工表,其中会有相同的字段,pandas 会自动生成后缀 _x 和 _y 加以区分。

同维表关联

同维表的情况相对简单,举例如下:

设有两张表

employee 员工表

EID

NAME

SURNAME

GENDER

STATE

BIRTHDAY

HIREDATE

DEPT

SALARY

1

Rebecca

Moore

F

California

1974/11/20

2005/3/11

6

7000

2

Ashley

Wilson

F

New York

1980/7/19

2008/3/16

2

11000

3

Rachel

Johnson

F

New   Mexico

1970/12/17

2010/12/1

7

9000

4

Emily

Smith

F

Texas

1985/3/7

2006/8/15

3

7000

5

Ashley

Smith

F

Texas

1975/5/13

2004/7/30

6

16000

6

Matthew

Johnson

M

California

1984/7/7

2005/7/7

7

11000

manager经理表

ID

ALLOWANCE

20

595

2

574

162

613

47

592

58

616

5

557

两个表的主键分别是 EID 和 ID,经理也是员工,两表共用同样的员工编号,经理会比普通员多一些属性,另用一个经理表来保存。

问题六:统计所有员工(包括经理)的总收入(加上津贴)

Python 代码如下:

import pandas as pd

emp = pd.read_csv('EMPLOYEES.csv')

man =   pd.read_csv('MANAGER.csv',usecols=['ID','ALLOWANCE'])

emp_m =   pd.merge(emp,man,left_on='EID',right_on='ID',how='left')

emp_total =   (emp_m['SALARY']+emp_m['ALLOWANCE'].fillna(0)).sum()

print(emp_total)

 

 

 

读取经理表的 ID 和 ALLOWANCE 字段

 

员工表经理表左关联

 

津贴为 nan 的用 0 填补,求和汇总

 

讨论:计算所有员工的总收入,需要员工表左连接经历表,获得经理表的津贴字段,因此 merge 的参数 how 应该为‘left’,连接后非经理员工的津贴字段为 nan,此时计算每个员工的收入时需要把津贴为 nan 的行填 0,最后求和。

主子表关联

订单及订单明细是典型的主子表:

Orders 订单表

ordered

clientid

date

10012

100658

2019/2/13

10023

103478

2019/1/12

10040

108013

2019/1/4

10045

100373

2019/1/20

10054

102525

2019/3/7

10057

102740

2019/3/21

OrderDetails 订单明细

orderid

no

productid

price

10012

1

3018

428.5

10012

2

3019

349.2

10023

1

3019

349.2

10040

1

3093

139.5

10040

2

3070

137.9

10040

3

3050

210.6

Orders 表的主键是 orderid,OrderDetails 表中的主键是 (orderid,no),前者的主键是后者的一部分。

问题七:计算客户每张订单的总金额。

问题分析:想计算客户每张订单的总金额,需要将两表进行关联,然后再根据 orderid 分组求和

Python 代码如下:

import pandas as pd

order = pd.read_csv('Orders.csv')

detail = pd.read_csv('OrderDetails.csv')

order_d = pd.merge(order,detail,left_on='orderid',right_on='orderid')

c_total =   order_d.groupby(['clientid','orderid',],as_index=False).price.sum()

print(c_total)

 

 

 

两表关联

 

 

分组求和

讨论:两表关于 oderid 关联后,订单表就获得了订单的明细信息,然后根据客户 ID 和订单 ID 分组汇总即得到结果。

我们进一步讨论下主子表关联的情况

假设还有一个回款表,存储各个订单的分期回款情况,部分数据如下:

Payment 订单回款表

orderid

term

payment

10012

1

259.2

10012

2

259.2

10012

3

259.3

10023

1

349.2

10040

1

488

10045

1

21.8

回款表同样可以看做是订单表的子表,而且订单表的主键 orderid 同样是回款表主键(orderid,term)的部分主键。

问题八:查询还未完全回款的订单信息

问题分析:此时是主表与两个子表关联,如果还像上例那样先关联后分组,则会出现订单明细表与订单回款表的多对多之间的关联,再计算时会出现错误。正确的做法应该是先对订单明细和订单回款表分组汇总后,再分别与主表订单表关联。

Python 代码如下:

import pandas as pd

order = pd.read_csv('Orders.csv')

detail = pd.read_csv('OrderDetails.csv')

pay = pd.read_csv('Payment.csv')

pay_order =   pay.groupby('orderid',as_index=False).payment.sum()

detail_order =   detail.groupby('orderid',as_index=False).price.sum()

order_d = pd.merge(order,detail_order,on='orderid')

order_d_p = pd.merge(order_d,pay_order,on='orderid')

nopay_order = order_d_p.query('price>payment')

print(nopay_order)

 

 

 

 

回款表计算各订单回款金额

 

明细表计算各订单的总金额

 

订单表与明细表关联

新表与回款表关联

条件过滤

 

讨论:分别汇总各子表的金额后,再依次与主表进行关联。此处可以先把 orderid 设置为索引,然后使用 join 同时完成主表与两表的关联,代码如下:

import pandas as pd

order = pd.read_csv('Orders.csv',index_col='orderid')

detail = pd.read_csv('OrderDetails.csv')

pay = pd.read_csv('Payment.csv')

pay_order = pay.groupby('orderid').payment.sum()

detail_order = detail.groupby('orderid').price.sum()

order_d_p = order.join([pay_order,detail_order])

nopay_order = order_d_p.query('price>payment')

print(nopay_order)

 

设置索引为 orderid

 

 

ordered作为索引

ordered作为索引

利用索引同时与两个子表关联

讨论:多表关联时,可以考虑使用 join 同时完成多个 dataframe 的关联。

虽然多对多的关联常常是没有业务意义的,但再极少数的情况下还是有意义的,下面我们就以矩阵乘法为例简单介绍下。

总结

Python 中的关联是将两表根据某个或者某些列相等连接在一起,组成一张宽表,这和 SQL 类似。很容易完成两表的关联计算,从上述实例中也可以看出,Python 在解决这些不算太复杂的问题时,还是比较方便的,一步一步计算下来,思路清晰,代码也好理解,这是 SQL 所不能的(感兴趣的读者可以尝试用 SQL 写下问题七和问题八)。但在多表关联时,尤其是当表很多时,是先分组再关联还是先关联再分组就很容易搞错,使得关联后的表非常大,不仅计算错误,有时候也会使内存放不下,影响其他应用,这时尽量使用 merge() 函数的参数 validate,判断是多对一还是一对一还是多对多,这样可以避免失误造成多对多 JOIN。

不过,这些还不是大问题,Python 最致命的缺点是当数据量大到无法一次性载入内存时,使用 Python 进行关联运算将会是灾难性的,几乎所有的关联方式都需要自己来实现,涉及到外存排序,hash 分段等等复杂代码,非高级程序员几乎不可能完成。这些代码实在过于繁琐,这里也就没有再给出来了。对于大文件的场景,Python 不再合适,推荐使用 esProc SPL,它提供有游标对象,可以轻松解决内存装不下的文件之间的关联及其它运算。

比如问题一计算 2019 年老客户的合同总金额。当合同数据很大,无法一次性载入内存时,而客户数据可以放入内存时,可以把为合同表创建游标来计算,SPL 代码也没有几行:


A

B

1

=file(contract_path).cursor@tc()

/为事实表——合同表创建游标

2

=file(client_path).import@tc()

/导入维表——客户表

3

=A2.switch@i(Client,A3:ID)

/游标关联

4

=A4.total(sum(Amount))

/汇总结果

两个表都特别大时,SPL 还提供了 joinx() 函数,对有序游标进行关联,可以非常高效的完成大数据的关联任务。

当然,小数据关联对于 SPL 更不在话下,而且相对于 Python 也会更简单一点,比如前面的问题写出来是这样:

问题

SPL代码

简单说明

relate=A.switch@i(Client,B:ID)

res=relate.sum(Amount)

A是合同表,B 是客户表。switch() 函数完成外键属性化,将记录作为引用替换原来的字段,@选项关联不到则删除该记录

newA=A.switch(MANAGER,   B:EID)

newB=B.switch(DEPT,   newA:DEPTID)

res=newB.select(STATE=="New   York" &&

 DEPT.MANAGER.STATE=="New York")

A是部门表,B 是员工表。

newA=A.join(EID,B:ID,ALLOWANCE)

res=newA.sum(SALARY+ALLOWANCE)

A是员工表,B 是经理表。A.join() 函数完成同维表等同化

groupB=B.group(orderid)

joinAB=join(A:Order,orderid;groupB:Detail,orderid)

res=joinAB.new(Order.clientid:client,Order.orderid:ID,

Detail.price:Amount)

子表 B 先分组

A是订单表,B 是订单明细表。join() 函数完成主子表一体化

groupB=B.group(orderid)

groupC=C.group(orderid)

joinABC=join(A:Order,orderid;groupB:Detail,orderid;

groupC:Payment,orderid)

res= joinABC.select(Detail.sum(price)>

Payment.sum(payment)).(Order)

子表 B 分组

子表 C 分组

join()函数完成主子表一体化

 

过滤满足条件的表

 

以下是广告时间

对润乾产品感兴趣的小伙伴,一定要知道软件还能这样卖哟性价比还不过瘾? 欢迎加入好多乾计划。
这里可以低价购买软件产品,让已经亲民的价格更加便宜!
这里可以销售产品获取佣金,赚满钱包成为土豪不再是梦!
这里还可以推荐分享抢红包,每次都是好几块钱的巨款哟!
来吧,现在就加入,拿起手机扫码,开始乾包之旅



嗯,还不太了解好多乾?
猛戳这里
玩转好多乾