python数据分析一周速成2.连表查询【含数据库实战项目】

连表查询

结合数据库实战（sql和hive跨库取数）

数据准备

# 前面省略数据库连接，提示：可以用pymysql和pyhive模块
pre_sql = """
select ap,time from biaoyi a
where a.time >20250101
"""
sql_df = run_mysql(pre_sql)

pre_hive="""
select 
application_number 
,activation_dte 
from  biaoer b 
where b.application_number in (xx)
"""

好理解的方案一

join连接字符串后，replace

ap_num = ','.join(f"'{num}'" for num in sql_df.iloc[:, 2] )  

pre_hive=pre_hive.replace('xx',ap_num)

hive_df = run_presto(pre_hive)

简洁的方案二

tolist直接转成字符串的列表[‘xxx’,‘xxx,’‘xxx’…]

pre_hive=pre_hive.replace('xx',str(sql_df['ap'].unique().tolist())[1:-1])

hive_df = run_presto(pre_hive)

1.易混淆点：concat 和 merge

concat 简单的纵向或横向拼接，主要根据索引或轴的方向进行拼接，不考虑列或行的具体值，不涉及键匹配。
merge 功能更强大，对键值（列名和单元格值）进行匹配，支持多种连接方式（内连接、左连接、右连接、外连接），可以处理复杂的键匹配情况。

import pandas as pd 

df1=pd.DataFrame({'A':['a','b','z'],
                  'B':[1,2,0]})

df1
'''
    	A	B
    0	a	1
    1	b	2
    2	z	0
'''

df2=pd.DataFrame([['b','2','x'],['c',3,6],['a',1],['a',99],['a',1,5]],columns=['A','B','C']) 
df2
'''
    	A	B	C
    0	b	2	x
    1	c	3	6
    2	a	1	
    3	a	99	
    4	a	1	5
'''

##################### concat横/纵向拼接,根据行名或列名拼接,不考虑列或行的具体值;merge根据指定的键（列）进行拼接，会对键列的值进行匹配
pd.concat([df1,df2],axis=0)    # 纵向拼接 axis=0，和append效果相同
pd.concat([df1,df2],axis=1)    # 横向拼接 axis=1
##################### merge根据指定的键（列）进行拼接，会对键列的值进行匹配
pd.merge(df1,df2,how='inner')  # 交集并集：inner,outerleft以左表为准 少补多删,right以右表为准
pd.merge(df1,df2,on ='A',how='inner')  # 交集并集：inner,outerleft以左表为准 少补多删,right以右表为准
pd.merge(df1,df2,on ='A',how='left')   # 左右拼接：左连接left,右连接right

2.易混淆点：append 和join

append 连表时，纵向拼接DF或series。还可用于列表追加元素
join 连表时，横向拼接DF，通过索引连接（别折腾，连表就用merge，听话O(∩_∩)O~）。还用于字符串拼接

##################### 追加 append
df1.append(df2,ignore_index=True)  # 连表时，无脑追加，强行拼接，无值就NA

data=[1,2,3,4]
a=[]
for i in data:
	a.append(i)                    # 往列表中追加元素
	
##################### 
result = df1.join(df2, how='inner')

import pandas as pd 
import numpy as np
d=pd.DataFrame(['a_b_c','c_d_e',np.nan,'f_g'],columns=['A'])
d['A'].str.join('!')               #在每个字符串后加标点

小技巧：判定包含关系 isin / ~列名.isin

~是否定符号，isin判断某列的值是否包含在另一个表，查询结果展示交集

##################### isin包含
df1[df1['A'].isin(df2['A'])]
df2[df2['A'].isin(df1['A'])]
##################### ~不含
df1[~df1['A'].isin(df2['A'])]   # 不含 ~列.isin

数据重塑/变形

df.pivot 数据变形
df.pivot_table 数透表——长表转宽表
df.stack/unstack 数据堆叠
df.melt 数据融合 ——宽表转长表
df.cross 数据交叉表

############################################################# pd.pivot_table 透视表
import pandas as pd 
import numpy as np
import random
random.seed(1024)
df=pd.DataFrame(
   {'专业':np.repeat(['应用学','计算机','统计学'],4)
    ,'班级':['1班','1班','2班','2班']*3
    ,'科目':['高数','现代']*6
    ,'平均分':[random.randint(60,100) for i in range(12)]   # 12行生成12次
    ,'及格人数':[random.randint(30,50) for i in range(12)]  # 12行生成12次
  })
df
# 各专业各科目的及格人数和平均分
pd.pivot_table(df,index=['专业','科目'],values=['平均分','及格人数'],aggfunc={'平均分':np.mean,'及格人数':np.sum})
'''
专业	 科目	 及格人数  平均分
应用学	现代	  85	  85.0
     高数	67	    72.5
统计学	现代	  90	  74.5
     高数	95	    83.5
计算机	现代	  87	  79.0
     高数	79	    90.5
'''

################################################# df.melt 宽表转长表(以姓名为标识变量的列，以科目为value_vars，统计各科成绩)
import pandas as pd 
import numpy as np

d=[['张三','语文',91]
    ,['张三','数学',80]
    ,['张三','英语',100]
    , ['李四','语文',80]
    ,['李四','数学',100]
     ,['李四','英语',96]
    ]
df=pd.DataFrame(data=d,columns=['姓名','科目','成绩'])
df

df_kuan.melt(id_vars=['姓名'],value_vars=['数学','语文','英语'])  
'''
	姓名	科目	value
0	张三	数学	80
1	李四	数学	100
2	张三	语文	91
3	李四	语文	80
4	张三	英语	100
5	李四	英语	96
'''

知识补充：

pivot_table与pivot与同样是长表转宽表，但pivot_table可以满足所有场景，aggfunc可以对重复值聚合;而pivot不去重就报错

############################################################ df.pivot 长表转宽表，了解即可，都用pivot_table
# pivot_table与pivot与同样是长表转宽表，但table可以满足所有场景，aggfunc可以对重复值聚合;而pivot不去重就报错
df_kuan= df.pivot(index='姓名',columns='科目',values='成绩').reset_index()                  
df_kuan
'''
科目	姓名	数学	英语	语文
0	张三	80	100	91
1	李四	100	96	80
'''