pandas的合并、连接、去重、替换

import pandas as pd
import numpy as np

# merge合并 ，类似于Excel中的vlookup

df1 = pd.DataFrame({'key': ['K0', 'K1', 'K2', 'K3'],
                    'A': ['A0', 'A1', 'A2', 'A3'],
                    'B': ['B0', 'B1', 'B2', 'B3']})
df2 = pd.DataFrame({'key': ['K0', 'K1', 'K2', 'K3'],
                    'C': ['C0', 'C1', 'C2', 'C3'],
                    'D': ['D0', 'D1', 'D2', 'D3']})
df3 = pd.DataFrame({'key1': ['K0', 'K0', 'K2', 'K3'],
                    'key2': ['K0', 'K1', 'K0', 'K1'],
                    'A': ['A0', 'A1', 'A2', 'A3'],
                    'B': ['B0', 'B1', 'B2', 'B3']})
df4 = pd.DataFrame({'key1': ['K0', 'K0', 'K2', 'K3'],
                    'key2': ['K0', 'K0', 'K0', 'K0'],
                    'C': ['C0', 'C1', 'C2', 'C3'],
                    'D': ['D0', 'D1', 'D2', 'D3']})
print(pd.merge(df1,df2,on='key'))
# 第一个DataFrame为拼接后左边的
# 第二个DataFrame为拼接后右边的
# on 为参考键
'''
  key   A   B   C   D
0  K0  A0  B0  C0  D0
1  K1  A1  B1  C1  D1
2  K2  A2  B2  C2  D2
3  K3  A3  B3  C3  D3
'''
# 多个键连接
print(pd.merge(df3, df4, on=['key1', 'key2']))
# 当两个DataFrame中的key1和key2都相同时，才会连，否则不连
'''
  key1 key2   A   B   C   D
0   K0   K0  A0  B0  C0  D0
1   K0   K0  A0  B0  C1  D1
2   K2   K0  A2  B2  C2  D2
'''
# 参数how  ， 合并方式
# 默认，取交集
print(pd.merge(df3, df4, on=['key1', 'key2'], how='inner'))
print('-' * 8)
'''
  key1 key2   A   B   C   D
0   K0   K0  A0  B0  C0  D0
1   K0   K0  A0  B0  C1  D1
2   K2   K0  A2  B2  C2  D2
--------
'''
# 取并集，outer，数据缺失范围NaN
print(pd.merge(df3, df4, on=['key1', 'key2'], how='outer'))
print('-' * 8)
'''
  key1 key2    A    B    C    D
0   K0   K0   A0   B0   C0   D0
1   K0   K0   A0   B0   C1   D1
2   K0   K1   A1   B1  NaN  NaN
3   K2   K0   A2   B2   C2   D2
4   K3   K1   A3   B3  NaN  NaN
5   K3   K0  NaN  NaN   C3   D3
--------
'''
# 参照df3为参考合并，数据缺失范围NaN
print(pd.merge(df3, df4, on=['key1', 'key2'], how='left'))
print('-' * 8)
'''
  key1 key2   A   B    C    D
0   K0   K0  A0  B0   C0   D0
1   K0   K0  A0  B0   C1   D1
2   K0   K1  A1  B1  NaN  NaN
3   K2   K0  A2  B2   C2   D2
4   K3   K1  A3  B3  NaN  NaN
--------
'''
# 参照df4为参考合并，数据缺失范围NaN
print(pd.merge(df3, df4, on=['key1', 'key2'], how='right'))
print('-' * 8)
'''
  key1 key2    A    B   C   D
0   K0   K0   A0   B0  C0  D0
1   K0   K0   A0   B0  C1  D1
2   K2   K0   A2   B2  C2  D2
3   K3   K0  NaN  NaN  C3  D3
--------
'''
# 参数left_on，right_on，left_index, right_index  ,当键不为一个列时，可以单独设置左键与右键
df5 = pd.DataFrame({'lkey': list('bbacaab'),
                    'data1': range(7)})
df6 = pd.DataFrame({'rkey': list('abd'),
                    'date2': range(3)})
print(df5)
print(df6)
print(pd.merge(df5,df6,left_on='lkey',right_on='rkey'))
'''
  lkey  data1
0    b      0
1    b      1
2    a      2
3    c      3
4    a      4
5    a      5
6    b      6
  rkey  date2
0    a      0
1    b      1
2    d      2
  lkey  data1 rkey  date2
0    b      0    b      1
1    b      1    b      1
2    b      6    b      1
3    a      2    a      0
4    a      4    a      0
5    a      5    a      0
'''

# concat() 连接，默认axis=0  行+行，当axis=1时，列+列  成为Dataframe
s1 = pd.Series([2, 3, 4])
s2 = pd.Series([1, 2, 3])
print(pd.concat([s1, s2]))
'''
0    2
1    3
2    4
0    1
1    2
2    3
dtype: int64
'''
print(pd.concat([s1,s2],axis=1))
'''
   0  1
0  2  1
1  3  2
2  4  3
'''
snew = pd.concat([s1, s2], axis=1)
snew.reset_index(inplace=True)
print(snew)
'''
   index  0  1
0      0  2  1
1      1  3  2
2      2  4  3
'''
snew2 = pd.concat([s1, s2], axis=1)
snew2.reset_index(inplace=True, drop=True)
print(snew2)
'''
   0  1
0  2  1
1  3  2
2  4  3
'''

# 去重  .duplicated()
s3 = pd.Series([1, 2, 2, 4, 4, 6, 7, 6, 87])
# 判断是否重复
print(s3.duplicated())
'''
0    False
1    False
2     True
3    False
4     True
5    False
6    False
7     True
8    False
dtype: bool
'''
# 取出重复的值
s4 = s3[s3.duplicated()]
print(s4)
# 取出唯一的元素
s5 = s3[s3.duplicated() == False]
print(s5)
'''
0     1
1     2
3     4
5     6
6     7
8    87
dtype: int64
'''
s5 = s3.drop_duplicates()
# 可以通过设置参数：inplace控制是否替换原先的值
print(s5)
'''
0     1
1     2
3     4
5     6
6     7
8    87
dtype: int64
'''
df7 = pd.DataFrame({'key1':['a','a',3,4,3],
                    'key2':['a','a','b','b',5]})
print(df7.duplicated())
# 按行检测，第二次出现时，返回True
'''
0     1
1     2
3     4
5     6
6     7
8    87
dtype: int64
'''
# 今查看key2列
print(df7['key2'].duplicated())
'''
0    False
1     True
2    False
3     True
4    False
Name: key2, dtype: bool
'''
# 直接去重
print(df7.drop_duplicates())
'''
  key1 key2
0    a    a
2    3    b
3    4    b
4    3    5
'''
print(df7['key2'].drop_duplicates())
'''
0    a
2    b
4    5
Name: key2, dtype: object
'''

# 替换  .replace()
s6 = pd.Series(list('askjdghs'))
# 一次性替换一个值
# print(s6.replace('s','dsd'))
'''
0      a
1    dsd
2      k
3      j
4      d
5      g
6      h
7    dsd
dtype: object
'''
# 一次性替换多个值
print(s6.replace(['a','s'],np.nan))
'''
0    NaN
1    NaN
2      k
3      j
4      d
5      g
6      h
7    NaN
dtype: object
'''
# 通过字典的形式替换值
print(s6.replace({'a':np.nan}))
'''
0    NaN
1      s
2      k
3      j
4      d
5      g
6      h
7      s
dtype: object

'''