<jupyter_start><jupyter_text>Pandas 数据处理  1 数据合并类似SQL语句的**join**操作<jupyter_code>import numpy as np
import pandas as pd
frame1 = pd.DataFrame({'id':['ball','pencil','pen','mug','ashtray'],
                      'price':[12.33,11.44,33.21,13.23,33.62]})
frame1
frame2 = pd.DataFrame({'id':['ball','pencil','pen','ashtray'],
                     'color':['white','red','red','black']})
frame2
pd.merge(frame1,frame2)<jupyter_output><empty_output><jupyter_text>也可以通过制定on关键词，使用条件join<jupyter_code>frame1 = pd.DataFrame({'id':['ball','pencil','pen','mug','ashtray'],
                      'color':['white','red','red','black','green'],
                      'brand':['OMG','ABC','ABC','POD','POD']})
frame2 = pd.DataFrame({'id':['pencil','pencil','ball','pen'],
                      'brand':['OMG','POD','ABC','POD']})
pd.merge(frame1,frame2,on='id')
pd.merge(frame1,frame2,on='brand')<jupyter_output><empty_output><jupyter_text>使用*left_on*和*right_on*进行有选择的连接<jupyter_code>pd.merge(frame1,frame2,left_on='id',right_on='id')<jupyter_output><empty_output><jupyter_text>使用类似left_join,right_join和out_join等方式<jupyter_code>pd.merge(frame1,frame2,on='id')
pd.merge(frame1,frame2,on='id',how='outer')
pd.merge(frame1,frame2,on='id',how='left')
pd.merge(frame1,frame2,on='id',how='right')
# 合并多个键
pd.merge(frame1,frame2,on=['id','brand'],how='outer')<jupyter_output><empty_output><jupyter_text>根据索引合并<jupyter_code>pd.merge(frame1,frame2,right_index=True,left_index=True)<jupyter_output><empty_output><jupyter_text>join 函数更适合根据索引进行合并<jupyter_code>#重命名frame2列的名称
frame2.columns=['brand2','id2']
# frame1.join(frame2)<jupyter_output><empty_output><jupyter_text>2 拼接与另外一种数据整合操作叫拼接（concatenation)  numpy 数组拼接<jupyter_code>import numpy as np
import pandas as pd
array1 = np.arange(9).reshape((3,3))
array1
array2 = np.arange(9).reshape((3,3))+6
array2
# 拼接
# axis=1 行拼接
np.concatenate([array1,array2],axis=1)
# axis = 0 列拼接
np.concatenate([array1,array2],axis=0)<jupyter_output><empty_output><jupyter_text>Series 和DataFrame拼接<jupyter_code>ser1 = pd.Series(np.random.rand(4),index=[1,2,3,4])
ser1
ser2 = pd.Series(np.random.rand(4),index=[5,6,7,8])
ser2
pd.concat([ser1,ser2])<jupyter_output><empty_output><jupyter_text>concat()函数默认是按照axis=0这条轴拼接,返回Series对象，如果指定axis=1,那么将会返回DataFrame对象<jupyter_code>pd.concat([ser1,ser2],axis=1)<jupyter_output><empty_output><jupyter_text>上述结果返回的相当于'out_join'操作，可以吧join选项修改为'inner'<jupyter_code>pd.concat([ser1,ser1],axis=1,join='inner')<jupyter_output><empty_output><jupyter_text>创建等级索引，借助keys完成<jupyter_code>pd.concat([ser1,ser2],keys=[1,2])<jupyter_output><empty_output><jupyter_text>使用axis=1的话，将会DataFrame的各列对象<jupyter_code>pd.concat([ser1,ser2],keys=[1,2],axis=1)<jupyter_output><empty_output><jupyter_text>DataFrame 对象拼接<jupyter_code>frame1 = pd.DataFrame(np.random.rand(9).reshape(3,3),index=[1,2,3],columns=['A','B','C'])
frame2 = pd.DataFrame(np.random.rand(9).reshape(3,3),index=[4,5,6],columns=['A','B','C'])
pd.concat([frame1,frame2])
pd.concat([frame1,frame2],axis=1)<jupyter_output><empty_output><jupyter_text>组合数据无法通过合并和拼接的方法组合数据，例如数据结构索引相同<jupyter_code>ser1 = pd.Series(np.random.rand(5),index=[1,2,3,4,5])
ser2 = pd.Series(np.random.rand(4),index=[2,4,5,6])
ser1.combine_first(ser2)
ser2.combine_first(ser1)
ser1[:3].combine_first(ser2[:3])<jupyter_output><empty_output><jupyter_text>轴向旋转  按等级索引旋转+ stacking旋转数据，把列转换成行+ unstacking旋转数据，把行转换成列<jupyter_code>frame1 = pd.DataFrame(np.arange(9).reshape(3,3),
                     index=['white','black','red'],
                     columns=['ball','pen','pencil'])
frame1
ser5 = frame1.stack()
ser5
ser5.unstack()
ser5.unstack(0)<jupyter_output><empty_output><jupyter_text>从“长”格式和“宽”格式旋转<jupyter_code>longframe = pd.DataFrame({'color':['white','white','white',
                                   'red','red','red',
                                  'black','black','black'],
                         'item':['ball','pen','mug',
                                'ball','pen','mug',
                                'ball','pen','mug'],
                         'value':np.random.rand(9)})
longframe
longframe.pivot('color','item')<jupyter_output><empty_output><jupyter_text>删除<jupyter_code>frame1 = pd.DataFrame(np.arange(9).reshape(3,3),
                     index=['white','black','red'],
                     columns=['ball','pen','pencil'])
frame1
del frame1['ball']
frame1
frame1.drop('white')<jupyter_output><empty_output><jupyter_text>3 数据转换  删除重复元素<jupyter_code>dframe = pd.DataFrame({'color':['white','white','red','red','white'],
                      'value':[2,1,3,3,2]})
dframe<jupyter_output><empty_output><jupyter_text>使用duplicate（）函数，返回Series对象，数值类型为bool类型<jupyter_code>dframe.duplicated()<jupyter_output><empty_output><jupyter_text>筛选数据<jupyter_code>dframe[dframe.duplicated()]
dframe.drop_duplicates()<jupyter_output><empty_output><jupyter_text>映射  用映射代替元素<jupyter_code>frame = pd.DataFrame({'item':['ball','mug','pen','pencil','ashtray'],
                     'color':['white','rosso','verdo','black','yellow'],
                     'price':[5.56,4.2,1.3,0.56,2.75]})
frame<jupyter_output><empty_output><jupyter_text>如果DataFrame中，有一些元素不正确，在映射关系中，使用旧元素为key,新元素作为value<jupyter_code>newcolor = {
    'rosso':'red',
    'verde':'green'
}
frame.replace(newcolor)<jupyter_output><empty_output><jupyter_text>将NaN转换成0<jupyter_code>ser = pd.Series([1,3,np.nan,4,6,np.nan,4])
ser.replace(np.nan,0)<jupyter_output><empty_output><jupyter_text>映射添加元素<jupyter_code>frame = pd.DataFrame({'item':['ball','mug','pen','pencil','ashtray'],
                     'color':['white','red','green','black','yellow']})
frame
price={
    'ball':5.56,
    'mug':4.2,
    'bottle':1.3,
    'scissors':3.41,
    'pen':1.3,
    'pencil':0.56,
    'ashtray':2.75
}
frame['price']=frame['item'].map(price)
frame<jupyter_output><empty_output><jupyter_text>重命名轴索引<jupyter_code># 重命名 index 索引
reindex = {
    0:'first',
    1:'second',
    2:'third',
    3:'fourth',
    4:'fifth'
}
frame.rename(reindex)
# 重命名列明，添加columns选项
recolumn = {
    'item':'object',
    'price':'value'
}
frame.rename(index=reindex,columns=recolumn)
frame.rename(index={1:'first'},columns={'item':'object'},inplace=True)
frame<jupyter_output><empty_output><jupyter_text>4 离散化和面元划分将数据打散为几个类别，把取值范围分割成一个个小区间<jupyter_code>results = np.random.randint(0,100,200)
bins=[0,25,50,75,100]
cat = pd.cut(results,bins)
cat
cat.categories
cat.codes
pd.value_counts(cat)
bin_names = ['unlikely','less likely','likely','high likely']
pd.cut(result,bins,labels=bin_names)
pd.cut(result,5)
pd.qcut(result,5)<jupyter_output><empty_output><jupyter_text>异常值检测<jupyter_code># 随机创建一个只有三列的DataFrame对象,每一列包含了一个100个数据
randFrame = pd.DataFrame(np.random.randn(100,3))
randFrame
randFrame.describe()<jupyter_output><empty_output><jupyter_text>假定比标准差3倍中误差为异常数据<jupyter_code>randFrame.std()
randFrame[(np.abs(randFrame) > 3 * randFrame.std()).any(1)]<jupyter_output><empty_output><jupyter_text>5 排序<jupyter_code>nframe = pd.DataFrame(np.arange(25).reshape(5,5))
nframe
new_order = np.random.permutation(5)
new_order
nframe.take(new_order)
new_order = [3,4,2]
nframe.take(new_order)<jupyter_output><empty_output><jupyter_text>随机抽样<jupyter_code>sample = np.random.randint(0,len(nframe),size=3)
sample
nframe.take(sample)<jupyter_output><empty_output><jupyter_text>6 字符串处理  Python内置的字符串处理方法<jupyter_code>text = '16 bolton Avenue , Boston'
text.split(',')
tokens = [s.strip() for s in text.split(',')]
tokens
strings =['A+','A','A+','B','BB','BBB','C+']
';'.join(strings)
'Boston' in text
text.index('Boston')
text.find('Boston')
text.index('China')
text.find('China')
text.count('e')
text.replace('Boston','China')
text.replace('1','')<jupyter_output><empty_output><jupyter_text>正则表达式<jupyter_code>import re
text = 'This is   an \t odd \n text'
re.split('\s+',text)
regex = re.compile('\s+')
regex.split(text)
# findall 函数匹配文本中所有符合条件的正则表达式
text ='This is my address: 16 bolton Avenue, boston'
re.findall('a\w+',text)
re.findall('[A,a]\w+',text)<jupyter_output><empty_output><jupyter_text>与findall函数相关的函数还有两个，match()和search()，findall()函数返回所有符合条件的模式的子串，而search()函数返回第一个符合条件的子串<jupyter_code>re.search('[A,a]\w+',text)
search = re.search('[A,a]\w+',text)
search.start()<jupyter_output><empty_output><jupyter_text>match 从字符串的开始匹配，如果第一个字符不匹配，则不再搜索字符串内部。<jupyter_code>re.match('[A,a]\w+',text)
re.match('T\w+',text)
match = re.match('T\w+',text)
text[match.start():match.end()]<jupyter_output><empty_output><jupyter_text>7 数据聚合  Groupby 内部机制：SPLIT-APPLY-COMBINE+ *分组*  将数据集分成多个组+ *用函数处理*  用函数处理每一组数据+ *合并*  将不同组得到的数据合并起来<jupyter_code>frame = pd.DataFrame({'color':['white','red','green','red','green'],
                     'object':['pen','pencil','pencil','ashtray','pen'],
                     'price1':[5.56,4.20,1.30,0.56,2.75],
                     'price2':[4.75,4.12,1.60,0.75,3.15]})
frame
group=frame['price1'].groupby(frame['color'])
group.groups
group.mean()
group.sum()<jupyter_output><empty_output><jupyter_text>等级分组使用多列，使用多个关键字来进行分组<jupyter_code>ggroup = frame['price1'].groupby([frame['color'],frame['object']])
ggroup.groups
ggroup.sum()
frame[['price1','price2']].groupby(frame['color']).mean()
frame.groupby(frame['color']).mean()<jupyter_output><empty_output><jupyter_text>8 组迭代groupby 还支持迭代操作<jupyter_code>for name,group in frame.groupby('color'):
    print name
    print group<jupyter_output>green
   color  object  price1  price2
2  green  pencil    1.30    1.60
4  green     pen    2.75    3.15
red
  color   object  price1  price2
1   red   pencil    4.20    4.12
3   red  ashtray    0.56    0.75
white
   color object  price1  price2
0  white    pen    5.56    4.75<jupyter_text>链式转换<jupyter_code>frame['price1'].groupby(frame['color']).mean()
frame.groupby(frame['color'])['price1'].mean()<jupyter_output><empty_output><jupyter_text>分组函数<jupyter_code>group = frame.groupby('color')
group['price1'].quantile(0.5)
def range(series):
    return series.max() - series.min()
group['price1'].agg(range)   
group.agg(range)
group['price1'].agg(['mean','std',range])<jupyter_output><empty_output><jupyter_text>9 高级数据聚合<jupyter_code>frame
sums = frame.groupby('color').sum().add_prefix('tot_')
sums
pd.merge(frame,sums,left_on='color',right_index=True)
frame.groupby('color').transform(np.sum).add_prefix('tot_')
frame = pd.DataFrame({'color':['white','black','white','white','black','black'],
                     'status':['up','up','down','down','down','up'],
                     'value1':[12.33,14.55,22.34,27.84,23.40,18.33],
                     'value2':[11.23,31.80,29.99,31.18,18.25,22.44]})
frame
frame.groupby(['color','status']).apply(lambda x:x.max())<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.20026400150858004, 'fraction_numerical': 0.03780878747878559, 'mean_word_length': 6.658483754512635, 'pattern_counts': {'":': 0, '<': 160, '<?xml version=': 0, '>': 161, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '40', 'dir': 'jupyter-structured-clean-dedup', 'id': '283371', 'license': 'MIT', 'n_tokens_mistral': 4778, 'n_tokens_neox': 4818, 'n_words': 348, 'path': 'Data_Analytics_in_Action/pandasAdvance.ipynb', 'repo_name': 'gaufung/Data_Analytics_Learning_Note', 'size': '10606'}