05 Pandas(2)

# Author:Nimo_Ding

import pandas as pd
import numpy as np
from pandas import Series,DataFrame

# 数据表的合并
df1=DataFrame({
    'name':['ZhangFei','GuanYu','a','b','c'],
    'data1':range(5)
})
df2=DataFrame({
    'name':['ZhangFei','GuanYu','A','B','c'],
    'data2':range(5)
})
print('\n1、基于指定列进行连接：')
print(df1)
print(df2)
'''
结果为：
       name  data1
0  ZhangFei      0
1    GuanYu      1
2         a      2
3         b      3
4         c      4
       name  data2
0  ZhangFei      0
1    GuanYu      1
2         A      2
3         B      3
4         c      4
'''

df3=pd.merge(df1,df2,on='name')
print(df3)
'''
结果为：
       name  data1  data2
0  ZhangFei      0      0
1    GuanYu      1      1
2         c      4      4
'''

print('\n2、inner内连接：是merge合并的默认模式')
df3=pd.merge(df1,df2,how='inner')
print(df3)
'''
结果为：
       name  data1  data2
0  ZhangFei      0      0
1    GuanYu      1      1
2         c      4      4
'''

print('\n3、left左连接')
df3=pd.merge(df1,df2,how='left')
print(df3)
'''
结果为：
       name  data1  data2
0  ZhangFei      0    0.0
1    GuanYu      1    1.0
2         a      2    NaN
3         b      3    NaN
4         c      4    4.0
'''

print('\n4、right右连接')
df3=pd.merge(df1,df2,how='right')
print(df3)
'''
结果为：
       name  data1  data2
0  ZhangFei    0.0      0
1    GuanYu    1.0      1
2         c    4.0      4
3         A    NaN      2
4         B    NaN      3
'''

print('\n5、outer外连接')
df3=pd.merge(df1,df2,how='outer')
print(df3)
'''
结果为：
       name  data1  data2
0  ZhangFei    0.0    0.0
1    GuanYu    1.0    1.0
2         a    2.0    NaN
3         b    3.0    NaN
4         c    4.0    4.0
5         A    NaN    2.0
6         B    NaN    3.0
'''


print('\n如何用SQL方式打开pandas:')
# 借助pandasql工具,pandasql中的主要函数式sqldf，
# 它接收两个参数：一个SQL查询语句，还有一组环境变量globals()或locals()
# 这样就可以直接用SQL语句对DataFrame进行操作。
import pandas as pd
from pandas import DataFrame
from pandasql import sqldf,load_meat,load_births
df1=DataFrame({
    'name':['ZhangFei','GuanYu','a','b','c'],
    'data1':range(5)
})
pysqldf=lambda sql:sqldf(sql,globals())
sql="select * from df1 where name='ZhangFei'"
print(pysqldf(sql))
'''
结果为：
       name  data1
0  ZhangFei      0
'''


print('\n练习题：根据表数据对数据进行清洗，同时新增一列"总和"计算每个人的三科成绩之和')
data={
    '语文':[66,95,95,90,80,80],
    '英语':[65,85,92,88,90,90],
    '数学':[None,98,96,77,90,90]
}
df1=DataFrame(data,
     index=['张飞','关羽','赵云','黄忠','典韦','典韦'],
     columns=['语文','英语','数学'])
# 去除重复行
df1.drop_duplicates()

# 补充缺失值
# 将数学成绩均值作为张飞的缺失值。
df1['数学'].fillna(df1['数学'].mean(),inplace=True)

# 定义函数：增加一列
def add_col(df1):
    df1['总和']=df1['语文']+df1['英语']+df1['数学']
    return df1
df1=df1.apply(add_col,axis=1) 
print(df1)