merge合并DataFrame
import pandas as pdleft=pd.DataFrame({ 'key':['k0','k1','k2','k3','k4','k5'], 'A':['A0','A1','A2','A3','A4','A5'], 'B':['B0','B1','B2','B3','B4','B5']})right=pd.DataFrame({ 'key':['k0','k1','k2','k3','k4','k5'], 'C':['C0','C1','C2','C3','C4','C5'], 'D':['D0','D1','D2','D3','D4','D5']})print(left)print('-'*20)print(right)
key A B0 k0 A0 B01 k1 A1 B12 k2 A2 B23 k3 A3 B34 k4 A4 B45 k5 A5 B5-------------------- key C D0 k0 C0 D01 k1 C1 D12 k2 C2 D23 k3 C3 D34 k4 C4 D45 k5 C5 D5
# 合并res=pd.merge(left,right)print(res)print('-'*20)# 指定合并的keyres=pd.merge(left,right,on='key')print(res)
key A B C D0 k0 A0 B0 C0 D01 k1 A1 B1 C1 D12 k2 A2 B2 C2 D23 k3 A3 B3 C3 D34 k4 A4 B4 C4 D45 k5 A5 B5 C5 D5-------------------- key A B C D0 k0 A0 B0 C0 D01 k1 A1 B1 C1 D12 k2 A2 B2 C2 D23 k3 A3 B3 C3 D34 k4 A4 B4 C4 D45 k5 A5 B5 C5 D5
left = pd.DataFrame({'key1': ['K0', 'K1', 'K2', 'K3'], 'key2': ['K0', 'K1', 'K2', 'K3'], 'A': ['A0', 'A1', 'A2', 'A3'], 'B': ['B0', 'B1', 'B2', 'B3']})right = pd.DataFrame({'key1': ['K0', 'K1', 'K2', 'K3'], 'key2': ['K0', 'K1', 'K2', 'K4'], 'C': ['C0', 'C1', 'C2', 'C3'], 'D': ['D0', 'D1', 'D2', 'D3']})print(left)print('-'*20)print(right)
key1 key2 A B0 K0 K0 A0 B01 K1 K1 A1 B12 K2 K2 A2 B23 K3 K3 A3 B3-------------------- key1 key2 C D0 K0 K0 C0 D01 K1 K1 C1 D12 K2 K2 C2 D23 K3 K4 C3 D3
# 默认取交加 how='inner'res=pd.merge(left,right,on=['key1','key2'])print(res)
key1 key2 A B C D0 K0 K0 A0 B0 C0 D01 K1 K1 A1 B1 C1 D12 K2 K2 A2 B2 C2 D2
# how='outer' 取并集res=pd.merge(left,right,on=['key1','key2'],how='outer')print(res)
key1 key2 A B C D0 K0 K0 A0 B0 C0 D01 K1 K1 A1 B1 C1 D12 K2 K2 A2 B2 C2 D23 K3 K3 A3 B3 NaN NaN4 K3 K4 NaN NaN C3 D3
# 显示合并数据中数据来自哪个表res=pd.merge(left,right,on=['key1','key2'],how='outer',indicator=True)print(res)
key1 key2 A B C D _merge0 K0 K0 A0 B0 C0 D0 both1 K1 K1 A1 B1 C1 D1 both2 K2 K2 A2 B2 C2 D2 both3 K3 K3 A3 B3 NaN NaN left_only4 K3 K4 NaN NaN C3 D3 right_only
# 左链接res=pd.merge(left,right,on=['key1','key2'],how='left')print(res)print('-'*30)# 右链接res=pd.merge(left,right,on=['key1','key2'],how='right')print(res)
key1 key2 A B C D0 K0 K0 A0 B0 C0 D01 K1 K1 A1 B1 C1 D12 K2 K2 A2 B2 C2 D23 K3 K3 A3 B3 NaN NaN------------------------------ key1 key2 A B C D0 K0 K0 A0 B0 C0 D01 K1 K1 A1 B1 C1 D12 K2 K2 A2 B2 C2 D23 K3 K4 NaN NaN C3 D3
join 拼接列，次要用于索引上的合并
left = pd.DataFrame({'A': ['A0', 'A1', 'A2', 'A3'], 'B': ['B0', 'B1', 'B2', 'B3'], 'key': ['K0', 'K1', 'K0', 'K1']})right = pd.DataFrame({'C': ['C0', 'C1'], 'D': ['D0', 'D1']}, index=['K0', 'K1'])print(left)print('-'*15)print(right)
A B key0 A0 B0 K01 A1 B1 K12 A2 B2 K03 A3 B3 K1--------------- C DK0 C0 D0K1 C1 D1
res=left.join(right,on='key')print(res)
A B key C D0 A0 B0 K0 C0 D01 A1 B1 K1 C1 D12 A2 B2 K0 C0 D03 A3 B3 K1 C1 D1
Pandas数据透视表
df = pd.DataFrame({'Month': ["January", "January", "January", "January", "February", "February", "February", "February", "March", "March", "March", "March"], 'Category': ["Transportation", "Grocery", "Household", "Entertainment", "Transportation", "Grocery", "Household", "Entertainment", "Transportation", "Grocery", "Household", "Entertainment"], 'Amount': [74., 235., 175., 100., 115., 240., 225., 125., 90., 260., 200., 120.]})print(df)
Month Category Amount0 January Transportation 74.01 January Grocery 235.02 January Household 175.03 January Entertainment 100.04 February Transportation 115.05 February Grocery 240.06 February Household 225.07 February Entertainment 125.08 March Transportation 90.09 March Grocery 260.010 March Household 200.011 March Entertainment 120.0
# 结构一个索引为Category 列为Month 值为Amount的表res=df.pivot(index='Category',columns='Month',values='Amount')print(res)
Month February January MarchCategory Entertainment 125.0 100.0 120.0Grocery 240.0 235.0 260.0Household 225.0 175.0 200.0Transportation 115.0 74.0 90.0
# 按列求和res.sum(axis=0)
MonthFebruary 705.0January 584.0March 670.0dtype: float64
# 按行求和res.sum(axis=1)
CategoryEntertainment 345.0Grocery 735.0Household 600.0Transportation 279.0dtype: float64
pivot_table
df=pd.read_csv('./pandas/data/titanic.csv')df.columns
Index(['PassengerId', 'Survived', 'Pclass', 'Name', 'Sex', 'Age', 'SibSp', 'Parch', 'Ticket', 'Fare', 'Cabin', 'Embarked'], dtype='object')
#默认求平均值res=df.pivot_table(index='Sex',columns='Pclass',values='Fare')print(res)
Pclass 1 2 3Sex female 106.125798 21.970121 16.118810male 67.226127 19.741782 12.661633
# 求最大值res=df.pivot_table(index='Sex',columns='Pclass',values='Fare',aggfunc='max')print(res)
Pclass 1 2 3Sex female 512.3292 65.0 69.55male 512.3292 73.5 69.55
# 统计个数res=df.pivot_table(index='Sex',columns='Pclass',values='Fare',aggfunc='count')print(res)print('-'*20)# crosstab 统计个数res=pd.crosstab(index=df['Sex'],columns=df['Pclass'])print(res)
Pclass 1 2 3Sex female 94 76 144male 122 108 347--------------------Pclass 1 2 3Sex female 94 76 144male 122 108 347
# 求平均值res=df.pivot_table(index='Sex',columns='Pclass',values='Fare',aggfunc='mean')print(res)
Pclass 1 2 3Sex female 106.125798 21.970121 16.118810male 67.226127 19.741782 12.661633
# 计算未成年男女存活率df['minor']=df['Age']<=18res=df.pivot_table(index='minor',columns='Sex',values='Survived',aggfunc='mean')print(res)
Sex female maleminor False 0.760163 0.167984True 0.676471 0.338028