<jupyter_start><jupyter_text>pandas的拼接操作<jupyter_code>import numpy as np

import pandas as pd

from pandas import Series,DataFrame<jupyter_output><empty_output><jupyter_text>pandas的拼接分为两种：- 级联：pd.concat, pd.append- 合并：pd.merge, pd.join  0. 回顾numpy的级联<jupyter_code>nd1 = np.random.randint(0,10,size = (4,5))

nd2 = np.random.randint(0,10,size = (7,5))

nd3 = np.random.randint(0,10,size = (4,8))
b = np.random.randint(0,10,size = (11,5))
c = np.concatenate([nd1,nd2],axis = 0,out = b)
b
np.concatenate((nd1,nd3),axis = 1)<jupyter_output><empty_output><jupyter_text>============================================练习12：1. 生成2个3*3的矩阵，对其分别进行两个维度上的级联============================================ 为方便讲解，我们首先定义一个生成DataFrame的函数：  1. 使用pd.concat()级联 pandas使用pd.concat函数，与np.concatenate函数类似，只是多了一些参数：```pd.concat(objs, axis=0, join='outer', join_axes=None, ignore_index=False,          keys=None, levels=None, names=None, verify_integrity=False,          copy=True)```  1)  简单级联 和np.concatenate一样，优先增加行数（默认axis=0）<jupyter_code>df1 = DataFrame(np.random.randint(0,150,size = (5,3)),columns=['Python','Math','En'],index = list('ABCDE'))

df2 = DataFrame(np.random.randint(0,150,size = (5,3)),columns=['Python','Math','En'],index=list('HIJKL'))

display(df1,df2)
pd.concat([df1,df2])<jupyter_output><empty_output><jupyter_text>可以通过设置axis来改变级联方向<jupyter_code>df3 = DataFrame(np.random.randint(0,150,size = (5,3)),columns=['Chinese','Physic','Chem'],index = list('QWRTY'))

df3
df4 = pd.concat([df1,df3],axis = 1)
df4.fillna(0)<jupyter_output>D:\Anaconda3.5.2\lib\site-packages\ipykernel_launcher.py:1: FutureWarning: Sorting because non-concatenation axis is not aligned. A future version
of pandas will change to not sort by default.

To accept the future behavior, pass 'sort=True'.

To retain the current behavior and silence the warning, pass sort=False

  """Entry point for launching an IPython kernel.<jupyter_text>注意index在级联时可以重复 也可以选择忽略ignore_index，重新索引<jupyter_code>df1
df5 = DataFrame(np.random.randint(0,150,size = (5,3)),columns=['Python','Math','Java'],index = list('ABCGH'))
df5
pd.concat([df1,df5],join = 'outer')
pd.concat([df1,df5],join = 'inner')
pd.concat([df1,df5],join_axes=[df1.columns])
pd.concat([df1,df5],ignore_index=True)
# 期中
df1
# 期末考试
df6 =DataFrame(np.random.randint(0,150,size = (5,3)),columns=df1.columns,index= df1.index)
df6
df7 = pd.concat([df1,df6],keys = ['期中','期末'])
df7
df7.unstack(level=0).stack()<jupyter_output><empty_output><jupyter_text>或者使用多层索引 keys  concat([x,y],keys=['x','y']) ============================================练习13：1. 想一想级联的应用场景？2. 使用昨天的知识，建立一个期中考试张三、李四的成绩表ddd3. 假设新增考试学科"计算机"，如何实现？4. 新增王老五同学的成绩，如何实现？============================================  2) 不匹配级联 不匹配指的是级联的维度的索引不一致。例如纵向级联时列索引不一致，横向级联时行索引不一致 有3种连接方式：- 外连接：补NaN（默认模式）<jupyter_code># join = 'outer'<jupyter_output><empty_output><jupyter_text>- 内连接：只连接匹配的项<jupyter_code># join = 'inner'<jupyter_output><empty_output><jupyter_text>- 连接指定轴 join_axes ============================================练习14：    假设【期末】考试ddd2的成绩没有张三的，只有李四、王老五、赵小六的，使用多种方法级联============================================  3) 使用append()函数添加 由于在后面级联的使用非常普遍，因此有一个函数append专门用于在后面添加<jupyter_code>display(df1,df2)
df1.append(df2)<jupyter_output><empty_output><jupyter_text>============================================练习15：    新建一个只有张三李四王老五的期末考试成绩单ddd3，使用append()与期中考试成绩表ddd级联============================================  2. 使用pd.merge()合并 merge与concat的区别在于，merge需要依据某一共同的行或列来进行合并使用pd.merge()合并时，会自动根据两者相同column名称的那一列，作为key来进行合并。注意每一列元素的顺序不要求一致   1) 一对一合并<jupyter_code>df1 = DataFrame({'id':[1,10,1024],'name':['po','alisa','Michael'],'sex':['male','female','male']})
df1
df2 = DataFrame({'id':[1,11,1025],'salary':[10000,20000,30000],'age':[20,30,40]})
df2
pd.concat([df1,df2])
df1.merge(df2)<jupyter_output><empty_output><jupyter_text>2) 多对一合并<jupyter_code>df2 = DataFrame({'id':[1,1,1],'salary':[10000,20000,30000],'age':[20,30,40]})
df2
df1.merge(df2)<jupyter_output><empty_output><jupyter_text>3) 多对多合并<jupyter_code>df3 = DataFrame({'id':[1,1,1],'name':['po','alisa','Michael'],'sex':['male','female','male']})
df3
df2
pd.merge(df2,df3)<jupyter_output><empty_output><jupyter_text>4) key的规范化 - 使用on=显式指定哪一列为key,当有多个key相同时使用<jupyter_code>df1 = DataFrame({'id':[1,10,1024],'name':['po','alisa','Michael'],'sex':['male','female','male']})
df1
df2 = DataFrame({'id':[1,11,1025],'name':['softpo','alisa','Michael'],'age':[20,30,40]})
df2
display(df1,df2)
pd.merge(df1,df2,on = 'name')<jupyter_output><empty_output><jupyter_text>- 使用left_on和right_on指定左右两边的列作为key，当左右两边的key都不想等时使用<jupyter_code>df1
df2 = DataFrame({'Id':[1,11,1025],'salary':[10000,20000,30000],'age':[20,30,40]})
df2
display(df1,df2)
pd.merge(df1,df2,left_on='id',right_on='Id')<jupyter_output><empty_output><jupyter_text>============================================练习16：1. 假设有两份成绩单，除了ddd是张三李四王老五之外，还有ddd4是张三和赵小六的成绩单，如何合并？2. 如果ddd4中张三的名字被打错了，成为了张十三，怎么办？3. 自行练习多对一，多对多的情况  4. 自学left_index,right_index============================================  5) 内合并与外合并 - 内合并：只保留两者都有的key（默认模式）<jupyter_code>'''how : {'left', 'right', 'outer', 'inner'}'''
pd.merge(df1,df2,how = 'outer',left_on='id',right_on='Id')<jupyter_output><empty_output><jupyter_text>- 外合并 how='outer'：补NaN - 左合并、右合并：how='left'，how='right'，<jupyter_code># 左合并，左边所有数据都要保留
pd.merge(df1,df2,how = 'left',left_on='id',right_on='Id')<jupyter_output><empty_output><jupyter_text>============================================练习17：1. 如果只有张三赵小六语数英三个科目的成绩，如何合并？3. 考虑应用情景，使用多种方式合并ddd与ddd4============================================  6) 列冲突的解决 当列冲突时，即有多个列名称相同时，需要使用on=来指定哪一个列作为key，配合suffixes指定冲突列名 可以使用suffixes=自己指定后缀<jupyter_code>df1 = DataFrame({'id':[1,10,1024],'name':['po','alisa','Michael'],'sex':['male','female','male']})
df1
df2 = DataFrame({'id':[1,11,1025],'name':['softpo','alisa','Michael'],'age':[20,30,40]})
df2
display(df1,df2)
pd.merge(df1,df2,on = 'id',suffixes=('_A','_B'))<jupyter_output><empty_output><jupyter_text>============================================练习18：    假设有两个同学都叫李四，ddd5、ddd6都是张三和李四的成绩表，如何合并？============================================<jupyter_code>df = DataFrame(np.random.randint(0,150,size = (10,3)),columns=['Python','Math','En'],index = list('ABCDEFHIJK'))
df
df_sum = DataFrame(df.sum(axis = 1),columns=['sum'])
df_sum
pd.merge(df,df_sum,left_index=True,right_index=True)<jupyter_output><empty_output><jupyter_text>作业 3. 案例分析：美国各州人口数据分析 首先导入文件，并查看数据样本<jupyter_code>areas = pd.read_csv('./state-areas.csv')
areas
abbr = pd.read_csv('./state-abbrevs.csv')
abbr
pop = pd.read_csv('./state-population.csv')
pop
pop.head()<jupyter_output><empty_output><jupyter_text>合并pop与abbrevs两个DataFrame，分别依据state/region列和abbreviation列来合并。为了保留所有信息，使用外合并。<jupyter_code>pop.shape
# 外合并
pop2 = pd.merge(pop,abbr,how = 'outer',left_on='state/region',right_on='abbreviation')
pop2.shape
# 使用外合并，数据不会丢失
# 数据，存在空值
pop2.info()
pop2.head()<jupyter_output><empty_output><jupyter_text>去除abbreviation的那一列（axis=1）<jupyter_code>pop2.drop(labels='abbreviation',axis = 1,inplace=True)
pop2.head()<jupyter_output><empty_output><jupyter_text>查看存在缺失数据的列。使用.isnull().any()，只有某一列存在一个缺失数据，就会显示True。<jupyter_code>pop2.isnull().any()
areas
cond = pop2['state'].isnull()

a = pop2['state/region'][cond]

# 去重
a.unique()
# USA -- United States
# PR -- Puerto Rico<jupyter_output><empty_output><jupyter_text>查看缺失数据 根据数据是否缺失情况显示数据，如果缺失为True，那么显示<jupyter_code>import warnings
warnings.filterwarnings('ignore')
cond = pop2['state/region'] == 'PR'

pop2['state'][cond] = 'Puerto Rico'
cond = pop2['state/region'] == 'USA'

pop2['state'][cond] = 'United States'
pop2.isnull().any()
cond = pop2['population'].isnull()
pop2[cond]
pop2.dropna(inplace=True)
pop2.isnull().any()<jupyter_output><empty_output><jupyter_text>找到有哪些state/region使得state的值为NaN，使用unique()查看非重复值 为找到的这些state/region的state项补上正确的值，从而去除掉state这一列的所有NaN！记住这样清除缺失数据NaN的方法！ 合并各州面积数据areas，使用左合并。思考一下为什么使用外合并？<jupyter_code>pop2.head()
areas.head()
pop2.shape
pop3 = pd.merge(pop2,areas,how='outer')
display(pop3.head(),pop3.shape)<jupyter_output><empty_output><jupyter_text>继续寻找存在缺失数据的列<jupyter_code>pop3.isnull().any()<jupyter_output><empty_output><jupyter_text>我们会发现area(sq.mi)这一列有缺失数据，为了找出是哪一行，我们需要找出是哪个state没有数据<jupyter_code>cond = pop3['area (sq. mi)'].isnull()
pop3[cond]
areas['area (sq. mi)'].sum()
pop3.fillna(3790399,inplace=True)<jupyter_output><empty_output><jupyter_text>去除含有缺失数据的行<jupyter_code>pop3.notnull().all()<jupyter_output><empty_output><jupyter_text>查看数据是否缺失 找出2010年的全民人口数据,df.query(查询语句)<jupyter_code>pop3.head()
pop_2010 = pop3.query("(year == 2010 or year == 2012) and ages == 'total'")
pop_2010
pop_2010 = pop3.query("year == 2010 and ages == 'total'")
pop_2010<jupyter_output><empty_output><jupyter_text>对查询结果进行处理，以state列作为新的行索引:set_index<jupyter_code>pop_2010.set_index('state/region',inplace=True)
pop_2010<jupyter_output><empty_output><jupyter_text>计算人口密度。注意是Series/Series，其结果还是一个Series。<jupyter_code>pop_density = pop_2010['population']/pop_2010['area (sq. mi)']
pop_density
pop_density = DataFrame(pop_density,columns=['pop_density'])
pop_density
# 全局设置
pd.set_option('display.float_format',lambda x:'%0.1f'%(x))
pop_density.round(2)
pop_density
pop_2010
display(pop_2010.head(),pop_density.head())
display(pop_2010.shape,pop_density.shape)
pop4= pd.merge(pop_2010,pop_density,left_index=True,right_index=True)
pop4
result = pop_2010.merge(pop_density,left_index=True,right_index=True)
result<jupyter_output><empty_output><jupyter_text>排序，并找出人口密度最高的五个州sort_values()<jupyter_code>result.sort_values(by = 'pop_density',inplace=True)<jupyter_output><empty_output><jupyter_text>找出人口密度最低的五个州<jupyter_code>result
result.to_csv('./pop_2010.csv')
pop3.shape
pop3.head()
pop3.to_excel('./pop.xlsx')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.2375387797311272, 'fraction_numerical': 0.047466390899689764, 'mean_word_length': 12.796005706134094, 'pattern_counts': {'":': 0, '<': 124, '<?xml version=': 0, '>': 124, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '31', 'dir': 'jupyter-structured-clean-dedup', 'id': '140735', 'license': 'Apache-2.0', 'n_tokens_mistral': 4859, 'n_tokens_neox': 4881, 'n_words': 363, 'path': 'jupyterlab/day04/code/3-pandas/softpo/4-pandas_combine.ipynb', 'repo_name': 'marxlee/py-data-process', 'size': '9670'}