06 pandas 连接

最新推荐文章于 2024-07-24 13:29:35 发布

宁宁的快乐小朋友

最新推荐文章于 2024-07-24 13:29:35 发布

阅读量339

点赞数

文章标签： c# 开发语言

本文链接：https://blog.csdn.net/qq_42101003/article/details/126692593

版权

# 1 关系型连接

# 01 值连接merge

# 02 索引连接

# 2 方向连接

# 01 concat 索引拼接

# 02 序列与表合并

# 3 类连接

# 01 比较

# 02 组合

import pandas as pd
import numpy as np
# 1 关系型连接
# 01 值连接merge
df1 = pd.DataFrame({'Name':['LQ','YY'],'Age':[20,30]})
df2 = pd.DataFrame({'Name':['XX','YY'],'Gender':['F','M']})
df1.merge(df2, on='Name', how='left')

df1 = pd.DataFrame({'Name1':['LQ','YY'],'Age':[20,30]})
df2 = pd.DataFrame({'Name2':['XX','YY'],'Gender':['F','M']})
df1.merge(df2, left_on='Name1', right_on='Name2', how='left')
# 若有重复的列名  
df1 = pd.DataFrame({'Name':['YY'],'Grade':[90]})
df2 = pd.DataFrame({'Name':['YY'],'Grade':[96]})
df1.merge(df2, on='Name', how='left', suffixes=['_Chinese','_Math'])
# 姓名相同，班级不同
df1 = pd.DataFrame({'Name':['LQ','LQ'],'Age':[20,21],'Class':['one','two']})
df2 = pd.DataFrame({'Name':['LQ','LQ'],'Gender':['F','M'],'Class':['two','one']})
df1.merge(df2, on=['Name','Class'], how='outer', validate='m:1')

df1 = pd.DataFrame({'Name':['LQ','LQ'],'Age':[20,21],'Class':['one','two']})
df2 = pd.DataFrame({'Name':['LQ','LQ'],'Gender':['F','M'],'Class':['one','one']})
df1.merge(df2, on=['Name','Class'], how='outer', validate='1:m')

# validate检查重复键
left = pd.DataFrame({'A' : [11,12], 'B' : [13, 13]})           #B列重复数字
right = pd.DataFrame({'A' : [14,15,16], 'B': [13,13,13]})      #B列重复数字
left1 = pd.DataFrame({'A' : [11,12], 'B' : [13, 14]})          #B列无重复数字
right1 = pd.DataFrame({'A' : [14,15,16], 'B': [17,18,19]})     #B列无重复数字
pd.merge(left, right, on='B', how='outer', validate="m:m") 
pd.merge(left, left1, on='B', how='outer',validate="m:1")
pd.merge(left1, right, on='B', how='outer',validate="1:m")
pd.merge(left1, right1, on='B', how='outer',validate="1:1") # 左右表键都唯一

# 02 索引连接
df1 = pd.DataFrame({'Age':[20,30]},index=pd.Series(['LQ','YY'],name='Name'))
df2 = pd.DataFrame({'Gender':['F','M']},index=pd.Series(['YY','XX'],name='Name'))
df1.join(df2, how='left')

df1 = pd.DataFrame({'Grade':[70]},index=pd.Series(['LQ'],name='Name'))
df2 = pd.DataFrame({'Grade':[80]},index=pd.Series(['LQ'],name='Name'))
df1.join(df2, how='left', lsuffix='_Chinese', rsuffix='_Math')
# 多级索引
df1 = pd.DataFrame({'Age':[20,21]},
                   index=pd.MultiIndex.from_arrays([['LQ','LQ'],['two','one']], 
                                                   names=('Name','Class')))
df2 = pd.DataFrame({'Gender':['F','M']},
                   index=pd.MultiIndex.from_arrays([['LQ','LQ'],['one','two']], 
                                                   names=('Name','Class')))
df1.join(df2)


# 2 方向连接
# 01 concat 索引拼接
df1 = pd.DataFrame({'Name':['LQ','YY'],'Age':[25,26]})
df2 = pd.DataFrame({'Name':['XX'],'Age':[25]})
pd.concat([df1,df2]) # 默认axis=0按行纵向拼接
pd.concat([df1,df2],keys=['df1','df2']) # 显示属于来源于哪个表

df3 = pd.DataFrame({'Grade':[90,100]})
df4 = pd.DataFrame({'Gender':['F','F']})
pd.concat([df1,df3,df4],1)

df5 = pd.DataFrame({'Grade':[89,88]},index=[1,2])
pd.concat([df1,df5],axis=1,join='inner')

# 02 序列与表合并
s = pd.Series(['Q',21],index=df1.columns)
df1.append(s,ignore_index=True) # 对新序列的索引自动标号

s = pd.Series([80,90])
df1.assign(Grade=s) # 返回临时副本 不会修改原表
df1['Grade']=[80,90] # 在原表上直接改动


# 3 类连接
# 01 比较
df1 = pd.DataFrame({'Name':['a','b','c'],'Age':[20,21,22],'Class':[1,2,3]})
df2 = pd.DataFrame({'Name':['a','e','c'],'Age':[20,21,22],'Class':[0,6,4]})
df1.compare(df2)
df1.compare(df2, keep_shape=True)

# 02 组合
def choose_min(s1,s2):
    s2 = s2.reindex_like(s1) # 用s1的索引来更新s2的索引，原索引中不存在的默认填充NaN
    res = s1.where(s1<s2,s2) # 替换条件为Flase处的值
    res = res.mask(s1.isna(),s2) # 替换条件为True处的值
    return res
df1 = pd.DataFrame({'A':[1,np.nan],'B':[3,np.nan],'C':[5,6]})
df2 = pd.DataFrame({'B':[5,6],'C':[7,8],'D':[9,10]},index=[1,2])

df1.combine(df2, choose_min)
df1.combine(df2, choose_min, overwrite=False) #保留df1中存在，df2中不存在的
df1.combine_first(df2) # 完整的保留df1，补充df2


# Ex1：美国疫情数据集
date = pd.date_range('20200412','20201116').to_series()
date = date.dt.month.astype('string').str.zfill(
    2)+'-'+date.dt.day.astype('string').str.zfill(2)+'-'+'2020'
date = date.tolist()
date[:5]
path = r'C:\Users\lenovo\Desktop\最近要用\pandas\joyful-pandas\data\us_report'

L=[]
for d in date:
    df = pd.read_csv('C:/Users/lenovo/Desktop/最近要用/pandas/joyful-pandas/data/us_report/'+d+'.csv',
                     index_col='Province_State')
    data = df.loc['New York', ['Confirmed','Deaths','Recovered','Active']]
    L.append(data.to_frame().T)
res = pd.concat(L)
res.index = date