06 pandas回顧 檔案的讀取read_excel、索引與切片（loc、iloc）、過濾、刪除、級聯、對映、排序、分組 的詳細例子

luqin_發表於2019-03-24
Excel索引排序

pandas回顧 1 檔案的讀取 索引與切片

文章目錄

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

讀取包含多層級索引的excel，使用header引數,header = [index1,index2…] 多層級使用，index1…index2表示多級索引的行， header = None 無列標籤時使用

pd.read_excel('業務表.xlsx',header=[0,1])
城市 上半年 下半年
90# 93# 97# 90# 93# 97#
成都 8000 9000 3000 9000 10000 3200
綿陽 5600 7000 1600 5800 8000 2100
汶川 4000 5000 2000 4600 7800 1700
攀枝花 3200 4500 1000 3400 3800 2000

sheet_name 用於指定表格當中sheet的編號，0表示sheet1，1表示sheet2。。。,index_col 表示以哪一列作為行索引

sheet2 = pd.read_excel('業務表.xlsx',sheet_name=1,header=None,index_col=0)
sheet2
0 1 2 3 4 5 6
成都 8000 9000 3000 9000 10000 3200
綿陽 5600 7000 1600 5800 8000 2100
汶川 4000 5000 2000 4600 7800 1700
攀枝花 3200 4500 1000 3400 3800 2000 
# 設定某一列為行索引，結果與上面index_col結果一致
# sheet2.set_index(0)
# 把行索引設定為新的列
sheet2.reset_index()
0 1 2 3 4 5 6
0 成都 8000 9000 3000 9000 10000 3200
1 綿陽 5600 7000 1600 5800 8000 2100
2 汶川 4000 5000 2000 4600 7800 1700
3 攀枝花 3200 4500 1000 3400 3800 2000 
column = pd.MultiIndex.from_product([['上半年','下半年'],['90#','93#','97#']])
# 使用dataFrame的columns屬性，來對錶格索引重新賦值
sheet2.columns = column
sheet2
上半年 下半年
0 90# 93# 97# 90# 93# 97#
成都 8000 9000 3000 9000 10000 3200
綿陽 5600 7000 1600 5800 8000 2100
汶川 4000 5000 2000 4600 7800 1700
攀枝花 3200 4500 1000 3400 3800 2000

多級索引的賦值使用如下方法

sheet2.loc['成都',('上半年','90#')] = 8900

# 這種屬於跨級訪問，相當於對sheet2['上半年']產生的臨時物件賦值 不推薦使用
sheet2['上半年'].loc['成都','90#'] = 18000

多級索引賦值還可以採用如下辦法

sheet2_copy = sheet2['上半年'].copy()
sheet2_copy.loc['成都','90#'] = 9700
sheet2['上半年'] = sheet2_copy
sheet2
上半年 下半年
0 90# 93# 97# 90# 93# 97#
成都 9700 9000 3000 9000 10000 3200
綿陽 5600 7000 1600 5800 8000 2100
汶川 4000 5000 2000 4600 7800 1700
攀枝花 3200 4500 1000 3400 3800 2000 
sheet2_copy
0 90# 93# 97#
成都 9700 9000 3000
綿陽 5600 7000 1600
汶川 4000 5000 2000
攀枝花 3200 4500 1000

顯式訪問

  1. 訪問元素
sheet2_copy.loc['綿陽']['97#']  
sheet2_copy['97#'].loc['綿陽']
sheet2_copy.loc['綿陽','97#']
前面兩種只限於訪問，不能複製，都屬於跨級訪問
  1. 訪問行
sheet2_copy.loc['行索引']
sheet2_copy.loc[index1:index2]
  1. 訪問列
sheet2_copy['列索引']
sheet2_copy.loc[:,column1:column2]

# dataframe的列切片要從行的方向著手
sheet2_copy.loc[:,'93#':'97#']
0 93# 97#
成都 9000 3000
綿陽 7000 1600
汶川 5000 2000
攀枝花 4500 1000 
sheet2_copy.loc['成都':'汶川','90#':'97#']
0 90# 93# 97#
成都 9700 9000 3000
綿陽 5600 7000 1600
汶川 4000 5000 2000

隱式訪問

1.訪問元素

sheet2_copy.iloc[indexnumber,columnnumber]

2.訪問行、行切片

sheet2_copy.iloc[0]
sheet2_copy.iloc[0:2]

3.訪問列、列切片

sheet2_copy.iloc[:,0]
sheet2_copy.iloc[:,0:2]

   index=['tom','jack','mery','lucy','lili'],

                  columns=['python','java','php'])

pandas回顧 2 合併與級聯

score
python java php
tom 44 35 14
jack 44 80 10
mery 31 97 2
lucy 46 34 31
lili 13 69 25 
score.loc['tom','java'] = None
score
python java php
tom 44 NaN 14
jack 44 80.0 10
mery 31 97.0 2
lucy 46 34.0 31
lili 13 69.0 25

pandas的聚合操作會自動最佳化空值,pandas是一種更貼合業務需求的資料結構

score.sum()
結果為：
python    178.0
java      280.0
php        82.0
dtype: float64

score2 = np.random.randint(0,100,size=(5,3))
score2.sum(axis=0)
結果為：
array([185, 298, 269])

score3 = np.float32(score2)
score3[0,0] = np.nan
score3.sum()
結果為：nan

np.nansum(score3)
結果為：700.0

score.values
結果為：
array([[44., nan, 14.],
       [44., 80., 10.],
       [31., 97.,  2.],
       [46., 34., 31.],
       [13., 69., 25.]])

table1 = pd.read_excel('關係表.xlsx',sheet_name=1)
table2 = pd.read_excel('關係表.xlsx',sheet_name=2)
table3 = pd.read_excel('關係表.xlsx',sheet_name=3)
table4 = pd.read_excel('關係表.xlsx',sheet_name=4)

table1
手機型號 重量
0 windowsPhone 0.50
1 iPhone 0.40
2 Android 0.45
3 other 0.60 
table2
經銷商 發貨地區 手機型號
0 pegge beijing iPhone
1 lucy beijing Android
2 tom guangzhou iPhone
3 petter shenzhen windowsPhone
4 mery guangzhou Android 
table3
發貨地區 手機型號 價格
0 beijing iPhone 7000
1 beijing windowsPhone 2300
2 beijing Android 3600
3 guangzhou iPhone 7600
4 guangzhou windowsPhone 2800
5 guangzhou Android 4200
6 shenzhen iPhone 7400
7 shenzhen windowsPhone 2750
8 shenzhen Android 3900 
table4
型號 價格
0 iPhone 7000
1 windowsPhone 2300
2 Android 3600
3 iPhone 7600
4 windowsPhone 2800
5 Android 4200
6 iPhone 7400
7 windowsPhone 2750
8 Android 3900 
pd.merge(table1,table2)
手機型號 重量 經銷商 發貨地區
0 windowsPhone 0.50 petter shenzhen
1 iPhone 0.40 pegge beijing
2 iPhone 0.40 tom guangzhou
3 Android 0.45 lucy beijing
4 Android 0.45 mery guangzhou

一對多合併

pd.merge(table1,table3)
手機型號 重量 發貨地區 價格
0 windowsPhone 0.50 beijing 2300
1 windowsPhone 0.50 guangzhou 2800
2 windowsPhone 0.50 shenzhen 2750
3 iPhone 0.40 beijing 7000
4 iPhone 0.40 guangzhou 7600
5 iPhone 0.40 shenzhen 7400
6 Android 0.45 beijing 3600
7 Android 0.45 guangzhou 4200
8 Android 0.45 shenzhen 3900 
pd.merge(table3,table4,on='手機型號',suffixes=['_A','_B'])

replace 替換值
rename 熱換索引
map 對映一行或一列，通常是一列

table5 = table1.rename(columns={'手機型號':'型號'})
table5
型號 參考價格
0 windowsPhone 2500
1 iPhone 7500
2 Android 4000 
pd.merge(table5,table2,left_on='型號',right_on='手機型號')
型號 重量 經銷商 發貨地區 手機型號
0 windowsPhone 0.50 petter shenzhen windowsPhone
1 iPhone 0.40 pegge beijing iPhone
2 iPhone 0.40 tom guangzhou iPhone
3 Android 0.45 lucy beijing Android
4 Android 0.45 mery guangzhou Android 
table6 = table1.set_index('手機型號')

I
手機型號 重量
windowsPhone 0.50
iPhone 0.40
Android 0.45
other 0.60

right_index bool 設定是否參考索引進行合併

pd.merge(table2,table6,right_index=True,left_on='手機型號')
手機型號 重量 參考價格
0 windowsPhone 0.50 2500
1 iPhone 0.40 7500
2 Android 0.45 4000

級聯

df1 = DataFrame(data=np.random.randint(0,100,size=(3,3)),
                index=list('012'),columns=list('ABC'))
df1
A B C
0 71 1 54
1 41 78 40
2 23 49 68 
df2 = DataFrame(data=np.random.randint(0,100,size=(5,5)),
                index=list('01234'),columns=list('ABCDE'))
df2
A B C D E
0 25 98 53 61 35
1 90 91 51 78 79
2 56 34 2 71 28
3 6 61 44 11 48
4 46 0 60 6 9 
pd.concat((df1,df2),join='outer',axis=1,join_axes=[pd.Index(['1','2'])])
A B C A B C D E
1 41 78 40 90 91 51 78 79
2 23 49 68 56 34 2 71 28 
pd.concat((df1,df2),join='outer',axis=1,join_axes=[df2.index])
A B C A B C D E
0 71.0 1.0 54.0 25 98 53 61 35
1 41.0 78.0 40.0 90 91 51 78 79
2 23.0 49.0 68.0 56 34 2 71 28
3 NaN NaN NaN 6 61 44 11 48
4 NaN NaN NaN 46 0 60 6 9

儘量不要破壞列索引

pd.concat((df1,df2),join='outer',axis=1,join_axes=[df2.index],ignore_index=True)
0 1 2 3 4 5 6 7
0 71.0 1.0 54.0 25 98 53 61 35
1 41.0 78.0 40.0 90 91 51 78 79
2 23.0 49.0 68.0 56 34 2 71 28
3 NaN NaN NaN 6 61 44 11 48
4 NaN NaN NaN 46 0 60 6 9 
pd.concat((df1,df2),join='outer',axis=1,join_axes=[df2.index],keys=['上學期','下學期'])
上學期 下學期
A B C A B C D E
0 71.0 1.0 54.0 25 98 53 61 35
1 41.0 78.0 40.0 90 91 51 78 79
2 23.0 49.0 68.0 56 34 2 71 28
3 NaN NaN NaN 6 61 44 11 48
4 NaN NaN NaN 46 0 60 6 9

總結·

級聯引數

  1. axis 軸向 0~1
  2. join 級聯方式 inner outer
  3. join_axes [pd.Index([index1,index2…])] 指定索引為連線物件
  4. ignore_index True\False 開啟是否忽略索引
  5. keys 設定級聯後分割槽的名稱

合併引數

  1. on label，[label1,label2…] 設定合併參考列
  2. left_on\right_on label分別設定左右兩個表的合併參考列，一般用於不存在相同列標籤的情況
  3. left_index\right_index True,False 分別設定是否開啟以左\右行索引作為合併參考列
  4. how 指定合併方式 inner outer left right
  5. suffiex [suffiex1,suffiex2] 設定沒有參與合併的相同的列標籤的字尾

pandas回顧 3 資料處理 過濾 刪除 排序

score = DataFrame(data = np.random.randint(0,100,size=(5,3)),
                  index=['tom','jack','mery','lucy','lili'],
                  columns=['python','java','php'])
score
python java php
tom 0 25 25
jack 67 52 69
mery 75 50 24
lucy 15 29 27
lili 3 8 79
  • score.drop 刪除一行或一列
    • label 指定刪除的行或列標籤(也可以是列表)
    • axis 改變刪除的方向
  • score.drop_duplicates 刪除重複元素
    • keep ‘first’,‘last’,保留第一個或者最後一個
  • score.dropna 刪除空值
    • axis 軸向
    • how ‘any’,‘all’,有一個就刪除或者全部為nan才刪除

刪除

刪除列

score.drop(labels='python',axis=1)
java php
tom 25 25
jack 52 69
mery 50 24
lucy 29 27
lili 8 79

刪除行

score.drop(labels='tom',axis=0)
python java php
jack 67 52 69
mery 75 50 24
lucy 15 29 27
lili 3 8 79 
score.loc['lucy'] = score.loc['tom']
score.loc['lili'] = score.loc['tom']
score
python java php
tom 0 25 25
jack 67 52 69
mery 75 50 24
lucy 0 25 25
lili 0 25 25

檢查重複行

index = score.duplicated(keep='last')
index
結果為：
tom      True
jack    False
mery    False
lucy     True
lili    False
dtype: bool

獲取重複元素的索引

drop_index = score.loc[index].index
結果為：
Index(['tom', 'lucy'], dtype='object')

根據索引刪除重複的行

score.drop(drop_index)
python java php
jack 67 52 69
mery 75 50 24
lili 0 25 25 
score.drop_duplicates(keep='last')
python java php
jack 67 52 69
mery 75 50 24
lili 0 25 25

刪除值為空的元素

score.loc['jack','python'] = None
python java php
tom 0.0 25 25
jack NaN 52 69
mery 75.0 50 24
lucy 0.0 25 25
lili 0.0 25 25 
score.dropna(axis=1,how='all')
python java php
tom 0.0 25 25
jack NaN 52 69
mery 75.0 50 24
lucy 0.0 25 25
lili 0.0 25 25

新增一個學科

score['c++'] = np.random.randint(0,100,size=5)
score
python java php c++
tom 0.0 25 25 6
jack NaN 52 69 87
mery 75.0 50 24 19
lucy 0.0 25 25 27
lili 0.0 25 25 17

map是Sereis物件的函式,不是DataFrame的

score['c++'].map(lambda x:x+10)
結果為：
tom     16
jack    97
mery    29
lucy    37
lili    27
Name: c++, dtype: int64

對映關係 字典\lmabda\function

score['sex'] = ['boy','boy','gril','gril','boy']
score.dtypes
結果為：
python    float64
java        int32
php         int32
c++         int32
sex        object
dtype: object

def sex_transform(x):
    if x == 'boy':
        return 1
    else:
        return 0

score['sex'] = score['sex'].map(sex_transform)
score.dtypes
結果為：
python    float64
java        int32
php         int32
c++         int32
sex         int64
dtype: object

異常值檢測和過濾

  1. 確定過濾條件 bool_list
  2. 使用過濾條件篩選符合條件的資料
  3. 獲取符合條件的索引標籤
  4. 使用drop函式,透過索引標籤把異常值刪除
data = DataFrame(data = np.random.randn(1000,3))
data.head()
0 1 2
0 -0.009128 -0.344595 0.817780
1 1.970853 -0.525745 -1.222032
2 -0.298042 0.834860 -0.862041
3 0.063863 -0.972073 0.746626
4 -0.286017 1.686189 0.325281 
data.std()
結果為：
0    0.992051
1    1.004347
2    1.005172
dtype: float64

假設大於3*std的資料即為異常資料

condition = np.abs(data) > 3*data.std()

獲取包含空值的行,相當於確認了滿足條件的資料樣本

drop_datas = data[condition.any(axis=1)]

獲取符合條件的索引

drop_index = drop_datas.index
drop_index
結果為：
Int64Index([74, 89, 143, 183, 285, 821], dtype='int64')

根據索引刪除符合條件的資料

data.drop(drop_index,inplace=True)
data.shape
結果為：
(993, 3)

即刪除了七條不符合條件的元素

排序

numpy特性之一,可以用索引列表來對資料進行重新排序

a = np.array(['boy','girl'])
b = [0,1,0,0,0,1,1,0]
a[b]
結果為：
array(['boy', 'girl', 'boy', 'boy', 'boy', 'girl', 'girl', 'boy'],
      dtype='<U4')

df = DataFrame(data = np.random.randint(0,100,size=(5,5)),
   index = list('甲乙丙丁戊'),columns = list('ABCDE'))
   df
A B C D E
甲 72 32 54 69 24
乙 43 26 29 93 52
丙 76 65 71 43 10
丁 30 53 45 11 73
戊 81 86 47 11 10 
df.take([1,1,1,3,4],axis=1)
B B B D E
甲 32 32 32 69 24
乙 26 26 26 93 52
丙 65 65 65 43 10
丁 53 53 53 11 73
戊 86 86 86 11 10

take函式結合此方法,可以對資料進行隨機排序

random_index = np.random.permutation(5)
df.take(random_index,axis=1)
E A D C B
甲 24 72 69 54 32
乙 52 43 93 29 26
丙 10 76 43 71 65
丁 73 30 11 45 53
戊 10 81 11 47 86

take函式也可以結合random.randint進行隨機抽樣

random_index = np.random.randint(0,5,size=2)
df.take(random_index,axis=1)
D A
甲 69 72
乙 93 43
丙 43 76
丁 11 30
戊 11 81

資料分類處理

彙總 交叉表 透視表

分類–聚合–合併

一般 非數字型別作為分類標籤,數值型別不作為分類標籤

連續型\數值型不能作為分類標籤的,(1.2,2.4,5,6,6.7,…)

離散型\標稱型才可以作為分類標籤(red,blue,orange)

df = DataFrame({'color':['white','red','green','red'],
               'item':['ball','mug','pen','pencil'],
               'price':np.random.rand(4),
               'weight':np.random.rand(4)})
df.dtypes

按照顏色劃分的總重量

df.groupby('color').sum()['weight']
結果為：
color
green    0.704648
red      0.985205
white    0.232294
Name: weight, dtype: float64

groups = df.groupby('color')
groups.groups
結果為：
{'green': Int64Index([2], dtype='int64'),
 'red': Int64Index([1, 3], dtype='int64'),
 'white': Int64Index([0], dtype='int64')}

groups['weight'].sum()
結果為：
color
green    0.704648
red      0.985205
white    0.232294
Name: weight, dtype: float64

按照多個條件分組

df.groupby(['color','item']).sum()['weight']
結果為：
color  item  
green  pen       0.704648
red    mug       0.056610
       pencil    0.928595
white  ball      0.232294
Name: weight, dtype: float64

可以接受可迭代函式

df.groupby('color')['weight'].apply(np.sum)
結果為：
color
green    0.704648
red      0.985205
white    0.232294
Name: weight, dtype: float64

map函式不接受可迭代函式

df['color'].map(lambda x:x+'10')
結果為：
0    white10
1      red10
2    green10
3      red10
Name: color, dtype: object

練習：美國選舉政治獻金

usa_data = pd.read_csv('usa_election.txt',low_memory=False)
usa_data.shape
結果為：
(536041, 16)

usa_data.head(1)
cmte_id cand_id cand_nm contbr_nm contbr_city contbr_st contbr_zip contbr_employer contbr_occupation contb_receipt_amt contb_receipt_dt receipt_desc memo_cd memo_text form_tp file_num
0 C00410118 P20002978 Bachmann, Michelle HARVEY, WILLIAM MOBILE AL 366010290 RETIRED RETIRED 250.0 20-JUN-11 NaN NaN NaN SA17A 736166 
months = {'JAN' : 1, 'FEB' : 2, 'MAR' : 3, 'APR' : 4, 'MAY' : 5, 'JUN' : 6,
          'JUL' : 7, 'AUG' : 8, 'SEP' : 9, 'OCT': 10, 'NOV': 11, 'DEC' : 12}

# 黨派資訊
parties = {
  'Bachmann, Michelle': 'Republican',
  'Romney, Mitt': 'Republican',
  'Obama, Barack': 'Democrat',
  "Roemer, Charles E. 'Buddy' III": 'Reform',
  'Pawlenty, Timothy': 'Republican',
  'Johnson, Gary Earl': 'Libertarian',
  'Paul, Ron': 'Republican',
  'Santorum, Rick': 'Republican',
  'Cain, Herman': 'Republican',
  'Gingrich, Newt': 'Republican',
  'McCotter, Thaddeus G': 'Republican',
  'Huntsman, Jon': 'Republican',
  'Perry, Rick': 'Republican'           
 }months = {'JAN' : 1, 'FEB' : 2, 'MAR' : 3, 'APR' : 4, 'MAY' : 5, 'JUN' : 6,

usa_data['party'] = usa_data['cand_nm'].map(parties)

usa_data.head(1)
cmte_id cand_id cand_nm contbr_nm contbr_city contbr_st contbr_zip contbr_employer contbr_occupation contb_receipt_amt contb_receipt_dt receipt_desc memo_cd memo_text form_tp file_num party
0 C00410118 P20002978 Bachmann, Michelle HARVEY, WILLIAM MOBILE AL 366010290 RETIRED RETIRED 250.0 20-JUN-11 NaN NaN NaN SA17A 736166 Republican

檢視一共有4個黨派參與競選

usa_data['party'].unique()
結果為：
array(['Republican', 'Democrat', 'Reform', 'Libertarian'], dtype=object)

檢視各個黨派收到政治獻金的次數

usa_data['party'].value_counts()
結果為：
Democrat       292400
Republican     237575
Reform           5364
Libertarian       702
Name: party, dtype: int64

檢視每一個黨派收到的政治獻金總數,可以看出哪個黨派更受歡迎

黨派 party 政治獻金 contb_receipt_amt

usa_data.groupby('party').sum()['contb_receipt_amt']
結果為：
party
Democrat       8.105758e+07
Libertarian    4.132769e+05
Reform         3.390338e+05
Republican     1.192255e+08
Name: contb_receipt_amt, dtype: float64

檢視各個黨派所支援的職業 職業 contbr_occupation

DataFrame(usa_data.groupby(['party','contbr_occupation']).sum()['contb_receipt_amt'])

檢視各個競選者所支援的職業及職業政治獻金總數

DataFrame(usa_data.groupby(['cand_nm','contbr_occupation']).sum()['contb_receipt_amt'])

檢視各黨派每天收到政治獻金總數

時間contb_receipt_dt

DataFrame(usa_data.groupby(['party','contb_receipt_dt']).sum()['contb_receipt_amt'])

對時間形式進行處理並排序，使用如下函式對標準時間格式的字串進行轉換

pd.to_datetime('20-JUN-11')
結果為：
Timestamp('2011-06-20 00:00:00')

定義一個用於轉換時間格式的函式

def transform_date(date):
    # 20-JUN-11
    day,mon,year = date.split('-')
    month = months[mon]
    return "20{}-{}-{}".format(year,month,day)

測試函式是否可用

pd.to_datetime(transform_date('20-JUN-11'))
結果為：
Timestamp('2011-06-20 00:00:00')

usa_data['contb_receipt_dt'] = usa_data['contb_receipt_dt'].map(transform_date)

或者直接用to_datetime函式轉換

usa_data['contb_receipt_dt'] = pd.to_datetime(usa_data['contb_receipt_dt'])

檢視資料型別,確認時間格式轉換成功

usa_data.dtypes
結果為：
contb_receipt_dt     datetime64[ns]

跟據日期對錶格進行排序sort_values函式

usa_data.sort_values('contb_receipt_dt',inplace=True)

檢視排序後的各個政黨每天收到的政治獻金

sort_data = DataFrame(usa_data.groupby(['party','contb_receipt_dt']).sum()['contb_receipt_amt'])

將縱座標party換為橫座標

sort_data1 = sort_data.unstack('party',fill_value=0)

plot繪製線性圖(描述資料的變化趨勢)

%matplotlib inline
sort_data1.cumsum().plot()

時間作為列,黨派作為行來觀察

sort_data1.stack('party').unstack('contb_receipt_dt')

根據職業,過濾出老兵的所有資料

VETERAN = usa_data[usa_data['contbr_occupation'] == 'DISABLED VETERAN']

可以看出Obama最受老兵歡迎

soldier = VETERAN.groupby(['cand_nm']).sum()['contb_receipt_amt']

soldier.reset_index()
cand_nm contb_receipt_amt
0 Cain, Herman 300.00
1 Obama, Barack 4205.00
2 Paul, Ron 2425.49
3 Santorum, Rick 250.00

查詢出政治獻金最多的人 職業等

usa_data['contb_receipt_amt'].max()
結果為：
1944042.43

方法一

usa_data[usa_data['contb_receipt_amt'] == 1944042.43]

方法二

usa_data.query("contb_receipt_amt == 1944042.43")

相關文章