索引
Series的索引(obj[…])与Numpy数组索引的功能类似,只不过Series的索引值可以不是整数。
import numpy as np
import pandas as pd
obj=pd.Series(np.arange(4.),index=['a','b','c','d'])
obj
普通的python切片不包含尾部
a 0.0
b 1.0
c 2.0
d 3.0
dtype: float64
使用这些方法会修改Series的相应部分:
obj['b':'c']=5
obj
a 0.0
b 5.0
c 5.0
d 3.0
dtype: float64
import numpy as np
import pandas as pd
df = pd.read_csv('data/table.csv',index_col='ID')
df.head()
| School | Class | Gender | Address | Height | Weight | Math | Physics |
---|
ID | | | | | | | | |
1101 | S_1 | C_1 | M | street_1 | 173 | 63 | 34.0 | A+ |
1102 | S_1 | C_1 | F | street_2 | 192 | 73 | 32.5 | B+ |
1103 | S_1 | C_1 | M | street_2 | 186 | 82 | 87.2 | B+ |
1104 | S_1 | C_1 | F | street_2 | 167 | 81 | 80.4 | B- |
1105 | S_1 | C_1 | F | street_4 | 159 | 64 | 84.8 | B+ |
使用单个值或者序列,可以从DataFrame中索引出一个或多个列
df['School']
df[['School','Class']]
这种索引也有特殊案例。首先可以根据一个布尔数组切片或者选择数据:
df[:2]
| School | Class | Gender | Address | Height | Weight | Math | Physics |
---|
ID | | | | | | | | |
1101 | S_1 | C_1 | M | street_1 | 173 | 63 | 34.0 | A+ |
1102 | S_1 | C_1 | F | street_2 | 192 | 73 | 32.5 | B+ |
df[df['Physics']=='A+']
| School | Class | Gender | Address | Height | Weight | Math | Physics |
---|
ID | | | | | | | | |
1101 | S_1 | C_1 | M | street_1 | 173 | 63 | 34.0 | A+ |
1203 | S_1 | C_2 | M | street_6 | 160 | 53 | 58.8 | A+ |
2203 | S_2 | C_2 | M | street_4 | 155 | 91 | 73.8 | A+ |
使用布尔值DataFrame进行索引,布尔值DataFrame可以是对表两只进行比较产生的。
这种索引方式在语法上更像是Numpy二维数组。
1.单级索引
1. 1 loc方法、iloc方法、[ ]操作符
最常用的索引方法可能就是这三类,其中iloc表示位置索引,loc表示标签索引,[ ]也具有很大的便利性,各有特点
1.1.1 loc方法(注意:所有在loc中使用的切片全部包含右端点!)
① 单行索引:
df.loc[1103]
School S_1
Class C_1
Gender M
Address street_2
Height 186
Weight 82
Math 87.2
Physics B+
Name: 1103, dtype: object
② 多行索引:
df.loc[[1102,2304]]
| School | Class | Gender | Address | Height | Weight | Math | Physics |
---|
ID | | | | | | | | |
1102 | S_1 | C_1 | F | street_2 | 192 | 73 | 32.5 | B+ |
2304 | S_2 | C_3 | F | street_6 | 164 | 81 | 95.5 | A- |
df.loc[1304:].head()
| School | Class | Gender | Address | Height | Weight | Math | Physics |
---|
ID | | | | | | | | |
1304 | S_1 | C_3 | M | street_2 | 195 | 70 | 85.2 | A |
1305 | S_1 | C_3 | F | street_5 | 187 | 69 | 61.7 | B- |
2101 | S_2 | C_1 | M | street_7 | 174 | 84 | 83.3 | C |
2102 | S_2 | C_1 | F | street_6 | 161 | 61 | 50.6 | B+ |
2103 | S_2 | C_1 | M | street_4 | 157 | 61 | 52.5 | B- |
df.loc[2402::-1].head()
| School | Class | Gender | Address | Height | Weight | Math | Physics |
---|
ID | | | | | | | | |
2402 | S_2 | C_4 | M | street_7 | 166 | 82 | 48.7 | B |
2401 | S_2 | C_4 | F | street_2 | 192 | 62 | 45.3 | A |
2305 | S_2 | C_3 | M | street_4 | 187 | 73 | 48.9 | B |
2304 | S_2 | C_3 | F | street_6 | 164 | 81 | 95.5 | A- |
2303 | S_2 | C_3 | F | street_7 | 190 | 99 | 65.9 | C |
③ 单列索引:
df.loc[:,'Height'].head()
ID
1101 173
1102 192
1103 186
1104 167
1105 159
Name: Height, dtype: int64
④ 多列索引:
df.loc[:,['Height','Math']].head()
| Height | Math |
---|
ID | | |
1101 | 173 | 34.0 |
1102 | 192 | 32.5 |
1103 | 186 | 87.2 |
1104 | 167 | 80.4 |
1105 | 159 | 84.8 |
df.loc[:,'Height':'Math'].head()
| Height | Weight | Math |
---|
ID | | | |
1101 | 173 | 63 | 34.0 |
1102 | 192 | 73 | 32.5 |
1103 | 186 | 82 | 87.2 |
1104 | 167 | 81 | 80.4 |
1105 | 159 | 64 | 84.8 |
⑥ 函数式索引:
df.loc[lambda x:x['Gender']=='M'].head()
| School | Class | Gender | Address | Height | Weight | Math | Physics |
---|
ID | | | | | | | | |
1101 | S_1 | C_1 | M | street_1 | 173 | 63 | 34.0 | A+ |
1103 | S_1 | C_1 | M | street_2 | 186 | 82 | 87.2 | B+ |
1201 | S_1 | C_2 | M | street_5 | 188 | 68 | 97.0 | A- |
1203 | S_1 | C_2 | M | street_6 | 160 | 53 | 58.8 | A+ |
1301 | S_1 | C_3 | M | street_4 | 161 | 68 | 31.5 | B+ |
def f(x):
return [1101,1103]
df.loc[f]
| School | Class | Gender | Address | Height | Weight | Math | Physics |
---|
ID | | | | | | | | |
1101 | S_1 | C_1 | M | street_1 | 173 | 63 | 34.0 | A+ |
1103 | S_1 | C_1 | M | street_2 | 186 | 82 | 87.2 | B+ |
⑦ 布尔索引
df.loc[df['Address'].isin(['street_7','street_4'])].head()
| School | Class | Gender | Address | Height | Weight | Math | Physics |
---|
ID | | | | | | | | |
1105 | S_1 | C_1 | F | street_4 | 159 | 64 | 84.8 | B+ |
1202 | S_1 | C_2 | F | street_4 | 176 | 94 | 63.5 | B- |
1301 | S_1 | C_3 | M | street_4 | 161 | 68 | 31.5 | B+ |
1303 | S_1 | C_3 | M | street_7 | 188 | 82 | 49.7 | B |
2101 | S_2 | C_1 | M | street_7 | 174 | 84 | 83.3 | C |
df.loc[[True if i[-1]=='4' or i[-1]=='7' else False for i in df['Address'].values]].head()
| School | Class | Gender | Address | Height | Weight | Math | Physics |
---|
ID | | | | | | | | |
1105 | S_1 | C_1 | F | street_4 | 159 | 64 | 84.8 | B+ |
1202 | S_1 | C_2 | F | street_4 | 176 | 94 | 63.5 | B- |
1301 | S_1 | C_3 | M | street_4 | 161 | 68 | 31.5 | B+ |
1303 | S_1 | C_3 | M | street_7 | 188 | 82 | 49.7 | B |
2101 | S_2 | C_1 | M | street_7 | 174 | 84 | 83.3 | C |
当然也有相对简便的写法:
df[df['Address'].str.contains('4|7')].head()
本质上说,loc中能传入的只有布尔列表和索引子集构成的列表,只要把握这个原则就很容易理解上面那些操作
1.1.2 iloc方法(注意与loc不同,切片右端点不包含)
- 单行索引:
df.iloc[3]
School S_1
Class C_1
Gender F
Address street_2
Height 167
Weight 81
Math 80.4
Physics B-
Name: 1104, dtype: object
- 多行索引:
df.iloc[3:5]
| School | Class | Gender | Address | Height | Weight | Math | Physics |
---|
ID | | | | | | | | |
1104 | S_1 | C_1 | F | street_2 | 167 | 81 | 80.4 | B- |
1105 | S_1 | C_1 | F | street_4 | 159 | 64 | 84.8 | B+ |
3.单列索引:
df.iloc[:,3].head()
ID
1101 street_1
1102 street_2
1103 street_2
1104 street_2
1105 street_4
Name: Address, dtype: object
- 多列索引:
df.iloc[:,7::-2].head()
| Physics | Weight | Address | Class |
---|
ID | | | | |
1101 | A+ | 63 | street_1 | C_1 |
1102 | B+ | 73 | street_2 | C_1 |
1103 | B+ | 82 | street_2 | C_1 |
1104 | B- | 81 | street_2 | C_1 |
1105 | B+ | 64 | street_4 | C_1 |
- 混合索引:
df.iloc[3::4,7::-2].head()
| Physics | Weight | Address | Class |
---|
ID | | | | |
1104 | B- | 81 | street_2 | C_1 |
1203 | A+ | 53 | street_6 | C_2 |
1302 | A- | 57 | street_1 | C_3 |
2101 | C | 84 | street_7 | C_1 |
2105 | A | 81 | street_4 | C_1 |
- 函数式索引:
df.iloc[lambda x:[3]].head()
| School | Class | Gender | Address | Height | Weight | Math | Physics |
---|
ID | | | | | | | | |
1104 | S_1 | C_1 | F | street_2 | 167 | 81 | 80.4 | B- |
由上所述,iloc中接收的参数只能为整数或整数列表,不能使用布尔索引
(c) [ ]操作符
如果不想陷入困境,请不要在行索引为浮点时使用 [ ]操作符,因为在Series中的浮点 [ ]并不是进行位置比较,而是值比较,非常特殊
- Series的 [ ]操作
1.1 单元素索引:
s = pd.Series(df['Math'],index=df.index)
s[1101]
34.0
1.2 多行索引:
s[0:4]
ID
1101 34.0
1102 32.5
1103 87.2
1104 80.4
Name: Math, dtype: float64
1.3 函数式索引:
s[lambda x: x.index[16::-6]]
ID
2102 50.6
1301 31.5
1105 84.8
Name: Math, dtype: float64
1.4 布尔索引:
s[s>80]
ID
1103 87.2
1104 80.4
1105 84.8
1201 97.0
1302 87.7
1304 85.2
2101 83.3
2205 85.4
2304 95.5
Name: Math, dtype: float64
- DataFrame的[ ]操作
2.1 单行索引:
df[1:2]
| School | Class | Gender | Address | Height | Weight | Math | Physics |
---|
ID | | | | | | | | |
1102 | S_1 | C_1 | F | street_2 | 192 | 73 | 32.5 | B+ |
row = df.index.get_loc(1102)
df[row:row+1]
| School | Class | Gender | Address | Height | Weight | Math | Physics |
---|
ID | | | | | | | | |
1102 | S_1 | C_1 | F | street_2 | 192 | 73 | 32.5 | B+ |
2.2 多行索引:
df[3:5]
| School | Class | Gender | Address | Height | Weight | Math | Physics |
---|
ID | | | | | | | | |
1104 | S_1 | C_1 | F | street_2 | 167 | 81 | 80.4 | B- |
1105 | S_1 | C_1 | F | street_4 | 159 | 64 | 84.8 | B+ |
2.3 单列索引:
df['School'].head()
ID
1101 S_1
1102 S_1
1103 S_1
1104 S_1
1105 S_1
Name: School, dtype: object
2.4 多列索引:
df[['School','Math']].head()
| School | Math |
---|
ID | | |
1101 | S_1 | 34.0 |
1102 | S_1 | 32.5 |
1103 | S_1 | 87.2 |
1104 | S_1 | 80.4 |
1105 | S_1 | 84.8 |
2.5 函数式索引:
df[lambda x:['Math','Physics']].head()
| Math | Physics |
---|
ID | | |
1101 | 34.0 | A+ |
1102 | 32.5 | B+ |
1103 | 87.2 | B+ |
1104 | 80.4 | B- |
1105 | 84.8 | B+ |
2.6 布尔索引:
df[df['Gender']=='F'].head()
| School | Class | Gender | Address | Height | Weight | Math | Physics |
---|
ID | | | | | | | | |
1102 | S_1 | C_1 | F | street_2 | 192 | 73 | 32.5 | B+ |
1104 | S_1 | C_1 | F | street_2 | 167 | 81 | 80.4 | B- |
1105 | S_1 | C_1 | F | street_4 | 159 | 64 | 84.8 | B+ |
1202 | S_1 | C_2 | F | street_4 | 176 | 94 | 63.5 | B- |
1204 | S_1 | C_2 | F | street_5 | 162 | 63 | 33.8 | B |
一般来说,[]操作符常用于列选择或布尔选择,尽量避免行的选择.
1.2 布尔索引
1.2.1 布尔符号:’&’,’|’,’~’:分别代表和and,或or,取反not
df[(df['Gender']=='F')&(df['Address']=='street_2')].head()
| School | Class | Gender | Address | Height | Weight | Math | Physics |
---|
ID | | | | | | | | |
1102 | S_1 | C_1 | F | street_2 | 192 | 73 | 32.5 | B+ |
1104 | S_1 | C_1 | F | street_2 | 167 | 81 | 80.4 | B- |
2401 | S_2 | C_4 | F | street_2 | 192 | 62 | 45.3 | A |
2404 | S_2 | C_4 | F | street_2 | 160 | 84 | 67.7 | B |
df[(df['Math']>85)|(df['Address']=='street_7')].head()
| School | Class | Gender | Address | Height | Weight | Math | Physics |
---|
ID | | | | | | | | |
1103 | S_1 | C_1 | M | street_2 | 186 | 82 | 87.2 | B+ |
1201 | S_1 | C_2 | M | street_5 | 188 | 68 | 97.0 | A- |
1302 | S_1 | C_3 | F | street_1 | 175 | 57 | 87.7 | A- |
1303 | S_1 | C_3 | M | street_7 | 188 | 82 | 49.7 | B |
1304 | S_1 | C_3 | M | street_2 | 195 | 70 | 85.2 | A |
df[~((df['Math']>75)|(df['Address']=='street_1'))].head()
| School | Class | Gender | Address | Height | Weight | Math | Physics |
---|
ID | | | | | | | | |
1102 | S_1 | C_1 | F | street_2 | 192 | 73 | 32.5 | B+ |
1202 | S_1 | C_2 | F | street_4 | 176 | 94 | 63.5 | B- |
1203 | S_1 | C_2 | M | street_6 | 160 | 53 | 58.8 | A+ |
1204 | S_1 | C_2 | F | street_5 | 162 | 63 | 33.8 | B |
1205 | S_1 | C_2 | F | street_6 | 167 | 63 | 68.4 | B- |
loc和[]中相应位置都能使用布尔列表选择:
df.loc[df['Math']>60,(df[:8]['Address']=='street_6').values].head()
| Physics |
---|
ID | |
1103 | B+ |
1104 | B- |
1105 | B+ |
1201 | A- |
1202 | B- |
1.2.2 isin方法
df[df['Address'].isin(['street_1','street_4'])&df['Physics'].isin(['A','A+'])]
| School | Class | Gender | Address | Height | Weight | Math | Physics |
---|
ID | | | | | | | | |
1101 | S_1 | C_1 | M | street_1 | 173 | 63 | 34.0 | A+ |
2105 | S_2 | C_1 | M | street_4 | 170 | 81 | 34.2 | A |
2203 | S_2 | C_2 | M | street_4 | 155 | 91 | 73.8 | A+ |
df[df[['Address','Physics']].isin({'Address':['street_1','street_4'],'Physics':['A','A+']}).all(1)]
| School | Class | Gender | Address | Height | Weight | Math | Physics |
---|
ID | | | | | | | | |
1101 | S_1 | C_1 | M | street_1 | 173 | 63 | 34.0 | A+ |
2105 | S_2 | C_1 | M | street_4 | 170 | 81 | 34.2 | A |
2203 | S_2 | C_2 | M | street_4 | 155 | 91 | 73.8 | A+ |
1.2.3 快速标量索引
当只需要取一个元素时,at和iat方法能够提供更快的实现:
display(df.at[1101,'School'])
display(df.loc[1101,'School'])
display(df.iat[0,0])
display(df.iloc[0,0])
'S_1'
'S_1'
'S_1'
'S_1'
1.2.4 区间索引
1.2.4.1利用interval_range方法
pd.interval_range(start=0,end=5)
IntervalIndex([(0, 1], (1, 2], (2, 3], (3, 4], (4, 5]],
closed='right',
dtype='interval[int64]')
pd.interval_range(start=0,periods=8,freq=5)
IntervalIndex([(0, 5], (5, 10], (10, 15], (15, 20], (20, 25], (25, 30], (30, 35], (35, 40]],
closed='right',
dtype='interval[int64]')
1.2.4.2 利用cut将数值列转为区间为元素的分类变量
例如统计数学成绩的区间情况:
math_interval = pd.cut(df['Math'],bins=[0,40,60,80,100])
math_interval.head()
ID
1101 (0, 40]
1102 (0, 40]
1103 (80, 100]
1104 (80, 100]
1105 (80, 100]
Name: Math, dtype: category
Categories (4, interval[int64]): [(0, 40] < (40, 60] < (60, 80] < (80, 100]]
1.2.5 区间索引的选取
df_i = df.join(math_interval,rsuffix='_interval')[['Math','Math_interval']]\
.reset_index().set_index('Math_interval')
df_i.head()
| ID | Math |
---|
Math_interval | | |
(0, 40] | 1101 | 34.0 |
(0, 40] | 1102 | 32.5 |
(80, 100] | 1103 | 87.2 |
(80, 100] | 1104 | 80.4 |
(80, 100] | 1105 | 84.8 |
df_i.loc[65].head()
| ID | Math |
---|
Math_interval | | |
(60, 80] | 1202 | 63.5 |
(60, 80] | 1205 | 68.4 |
(60, 80] | 1305 | 61.7 |
(60, 80] | 2104 | 72.2 |
(60, 80] | 2202 | 68.5 |
df_i.loc[[65,90]].head()
| ID | Math |
---|
Math_interval | | |
(60, 80] | 1202 | 63.5 |
(60, 80] | 1205 | 68.4 |
(60, 80] | 1305 | 61.7 |
(60, 80] | 2104 | 72.2 |
(60, 80] | 2202 | 68.5 |
如果想要选取某个区间,先要把分类变量转为区间变量,再使用overlap方法:
df_i[df_i.index.astype('interval').overlaps(pd.Interval(70, 85))].head()
| ID | Math |
---|
Math_interval | | |
(80, 100] | 1103 | 87.2 |
(80, 100] | 1104 | 80.4 |
(80, 100] | 1105 | 84.8 |
(80, 100] | 1201 | 97.0 |
(60, 80] | 1202 | 63.5 |
2 多级索引
2.1 创建多级索引
2.1.1 通过from_tuple或from_arrays
- 直接创建元组
tuples = [('A','a'),('A','b'),('B','a'),('B','b')]
mul_index = pd.MultiIndex.from_tuples(tuples, names=('Upper', 'Lower'))
mul_index
MultiIndex([('A', 'a'),
('A', 'b'),
('B', 'a'),
('B', 'b')],
names=['Upper', 'Lower'])
pd.DataFrame({'Score':['perfect','good','fair','bad']},index=mul_index)
| | Score |
---|
Upper | Lower | |
A | a | perfect |
| b | good |
B | a | fair |
| b | bad |
- 利用zip创建元组:
L1 = list('AABB')
L2 = list('abab')
tuples = list(zip(L1,L2))
mul_index = pd.MultiIndex.from_tuples(tuples, names=('Upper', 'Lower'))
pd.DataFrame({'Score':['perfect','good','fair','bad']},index=mul_index)
| | Score |
---|
Upper | Lower | |
A | a | perfect |
| b | good |
B | a | fair |
| b | bad |
- 通过Array创建
arrays = [['A','a'],['A','b'],['B','a'],['B','b']]
mul_index = pd.MultiIndex.from_tuples(arrays, names=('Upper', 'Lower'))
pd.DataFrame({'Score':['perfect','good','fair','bad']},index=mul_index)
| | Score |
---|
Upper | Lower | |
A | a | perfect |
| b | good |
B | a | fair |
| b | bad |
mul_index
MultiIndex([('A', 'a'),
('A', 'b'),
('B', 'a'),
('B', 'b')],
names=['Upper', 'Lower'])
2.1.2 通过from_product
L1 = ['A','B']
L2 = ['a','b']
pd.MultiIndex.from_product([L1,L2],names=('Upper', 'Lower'))
MultiIndex([('A', 'a'),
('A', 'b'),
('B', 'a'),
('B', 'b')],
names=['Upper', 'Lower'])
2.1.3 指定df中的列创建(set_index方法)
df_using_mul = df.set_index(['Class','Address'])
df_using_mul.head()
| | School | Gender | Height | Weight | Math | Physics |
---|
Class | Address | | | | | | |
C_1 | street_1 | S_1 | M | 173 | 63 | 34.0 | A+ |
| street_2 | S_1 | F | 192 | 73 | 32.5 | B+ |
| street_2 | S_1 | M | 186 | 82 | 87.2 | B+ |
| street_2 | S_1 | F | 167 | 81 | 80.4 | B- |
| street_4 | S_1 | F | 159 | 64 | 84.8 | B+ |
2.2 多层索引切片
df_using_mul.head()
| | School | Gender | Height | Weight | Math | Physics |
---|
Class | Address | | | | | | |
C_1 | street_1 | S_1 | M | 173 | 63 | 34.0 | A+ |
| street_2 | S_1 | F | 192 | 73 | 32.5 | B+ |
| street_2 | S_1 | M | 186 | 82 | 87.2 | B+ |
| street_2 | S_1 | F | 167 | 81 | 80.4 | B- |
| street_4 | S_1 | F | 159 | 64 | 84.8 | B+ |
2.2.1 一般切片
df_using_mul.sort_index().loc['C_2','street_5']
| | School | Gender | Height | Weight | Math | Physics |
---|
Class | Address | | | | | | |
C_2 | street_5 | S_1 | M | 188 | 68 | 97.0 | A- |
| street_5 | S_1 | F | 162 | 63 | 33.8 | B |
| street_5 | S_2 | M | 193 | 100 | 39.1 | B |
df_using_mul.sort_index().loc[('C_2','street_6'):('C_3','street_4')]
| | School | Gender | Height | Weight | Math | Physics |
---|
Class | Address | | | | | | |
C_2 | street_6 | S_1 | M | 160 | 53 | 58.8 | A+ |
| street_6 | S_1 | F | 167 | 63 | 68.4 | B- |
| street_7 | S_2 | F | 194 | 77 | 68.5 | B+ |
| street_7 | S_2 | F | 183 | 76 | 85.4 | B |
C_3 | street_1 | S_1 | F | 175 | 57 | 87.7 | A- |
| street_2 | S_1 | M | 195 | 70 | 85.2 | A |
| street_4 | S_1 | M | 161 | 68 | 31.5 | B+ |
| street_4 | S_2 | F | 157 | 78 | 72.3 | B+ |
| street_4 | S_2 | M | 187 | 73 | 48.9 | B |
df_using_mul.sort_index().loc[('C_2','street_7'):'C_3'].head()
| | School | Gender | Height | Weight | Math | Physics |
---|
Class | Address | | | | | | |
C_2 | street_7 | S_2 | F | 194 | 77 | 68.5 | B+ |
| street_7 | S_2 | F | 183 | 76 | 85.4 | B |
C_3 | street_1 | S_1 | F | 175 | 57 | 87.7 | A- |
| street_2 | S_1 | M | 195 | 70 | 85.2 | A |
| street_4 | S_1 | M | 161 | 68 | 31.5 | B+ |
2.2.2 第一类特殊情况:由元组构成列表
df_using_mul.sort_index().loc[[('C_2','street_7'),('C_3','street_2')]]
| | School | Gender | Height | Weight | Math | Physics |
---|
Class | Address | | | | | | |
C_2 | street_7 | S_2 | F | 194 | 77 | 68.5 | B+ |
| street_7 | S_2 | F | 183 | 76 | 85.4 | B |
C_3 | street_2 | S_1 | M | 195 | 70 | 85.2 | A |
2.2.3 第二类特殊情况:由列表构成元组
df_using_mul.sort_index().loc[(['C_2','C_3'],['street_4','street_7']),:]
| | School | Gender | Height | Weight | Math | Physics |
---|
Class | Address | | | | | | |
C_2 | street_4 | S_1 | F | 176 | 94 | 63.5 | B- |
| street_4 | S_2 | M | 155 | 91 | 73.8 | A+ |
| street_7 | S_2 | F | 194 | 77 | 68.5 | B+ |
| street_7 | S_2 | F | 183 | 76 | 85.4 | B |
C_3 | street_4 | S_1 | M | 161 | 68 | 31.5 | B+ |
| street_4 | S_2 | F | 157 | 78 | 72.3 | B+ |
| street_4 | S_2 | M | 187 | 73 | 48.9 | B |
| street_7 | S_1 | M | 188 | 82 | 49.7 | B |
| street_7 | S_2 | F | 190 | 99 | 65.9 | C |
2.3 多层索引中的slice对象
L1,L2 = ['A','B','C'],['a','b','c']
mul_index1 = pd.MultiIndex.from_product([L1,L2],names=('Upper', 'Lower'))
L3,L4 = ['D','E','F'],['d','e','f']
mul_index2 = pd.MultiIndex.from_product([L3,L4],names=('Big', 'Small'))
df_s = pd.DataFrame(np.random.rand(9,9),index=mul_index1,columns=mul_index2)
df_s
Big | | | | D | | | E | | | F | |
---|
Small | | d | e | f | d | e | f | d | e | f | |
Upper | Lower | | | | | | | | | | |
A | a | 0.294849 | 0.808944 | 0.286861 | 0.079801 | 0.498002 | 0.974476 | 0.261653 | 0.792329 | 0.671085 | |
| b | 0.038281 | 0.802229 | 0.689221 | 0.258438 | 0.526583 | 0.527028 | 0.662287 | 0.424268 | 0.312776 | |
| c | 0.360262 | 0.096973 | 0.491884 | 0.843597 | 0.040154 | 0.563663 | 0.065939 | 0.342367 | 0.928119 | |
B | a | 0.483322 | 0.226118 | 0.695874 | 0.804036 | 0.056607 | 0.405535 | 0.960890 | 0.409557 | 0.044850 | |
| b | 0.902773 | 0.889656 | 0.212978 | 0.986362 | 0.733843 | 0.234746 | 0.266932 | 0.360693 | 0.856747 | |
| c | 0.204079 | 0.424308 | 0.878692 | 0.027883 | 0.604838 | 0.292223 | 0.814461 | 0.633649 | 0.195861 | |
C | a | 0.745168 | 0.065248 | 0.408881 | 0.647361 | 0.042335 | 0.245639 | 0.351357 | 0.945664 | 0.546389 | |
| b | 0.682388 | 0.948741 | 0.072672 | 0.179705 | 0.775370 | 0.565560 | 0.935742 | 0.149620 | 0.806942 | |
| c | 0.079893 | 0.031765 | 0.425390 | 0.557884 | 0.827765 | 0.650221 | 0.519717 | 0.708033 | 0.128069 | |
idx=pd.IndexSlice
df_s.loc[idx['B':,df_s['D']['d']>0.3],idx[df_s.sum()>4]]
Big | D | E | F | | | | |
---|
Small | | d | e | f | e | f | e |
Upper | Lower | | | | | | |
B | a | 0.8898 | 0.364698 | 0.347762 | 0.975053 | 0.494357 | 0.600218 |
| b | 0.831198 | 0.922369 | 0.121323 | 0.104072 | 0.965247 | 0.951544 |
C | a | 0.469555 | 0.202389 | 0.360323 | 0.789773 | 0.620175 | 0.284863 |
| c | 0.684378 | 0.494056 | 0.995441 | 0.738033 | 0.198539 | 0.770315 |
2.4 索引层的交换
2.4.1 swaplevel方法(两层交换)
df_using_mul.head()
Class | Address | | | | | | |
---|
C_1 | street_1 | S_1 | M | 173 | 63 | 34 | A+ |
| street_2 | S_1 | F | 192 | 73 | 32.5 | B+ |
| street_2 | S_1 | M | 186 | 82 | 87.2 | B+ |
| street_2 | S_1 | F | 167 | 81 | 80.4 | B- |
| street_4 | S_1 | F | 159 | 64 | 84.8 | B+ |
df_using_mul.swaplevel(i=1,j=0,axis=0).sort_index().head()
| School | Gender | Height | Weight | Math | Physics | |
---|
Address | Class | | | | | | |
street_1 | C_1 | S_1 | M | 173 | 63 | 34 | A+ |
| C_2 | S_2 | M | 175 | 74 | 47.2 | B- |
| C_3 | S_1 | F | 175 | 57 | 87.7 | A- |
street_2 | C_1 | S_1 | F | 192 | 73 | 32.5 | B+ |
| C_1 | S_1 | M | 186 | 82 | 87.2 | B+ |
2.4.2 reorder_levels方法(多层交换)
df_muls = df.set_index(['School','Class','Address'])
df_muls.head()
| Gender | Height | Weight | Math | Physics | | |
---|
School | Class | Address | | | | | |
S_1 | C_1 | street_1 | M | 173 | 63 | 34 | A+ |
| | street_2 | F | 192 | 73 | 32.5 | B+ |
| | street_2 | M | 186 | 82 | 87.2 | B+ |
| | street_2 | F | 167 | 81 | 80.4 | B- |
| | street_4 | F | 159 | 64 | 84.8 | B+ |
df_muls.reorder_levels([2,0,1],axis=0).sort_index().head()
| | | Gender | Height | Weight | Math | Physics |
---|
Address | School | Class | | | | | |
street_1 | S_1 | C_1 | M | 173 | 63 | 34 | A+ |
| | C_3 | F | 175 | 57 | 87.7 | A- |
| S_2 | C_2 | M | 175 | 74 | 47.2 | B- |
street_2 | S_1 | C_1 | F | 192 | 73 | 32.5 | B+ |
| | C_1 | M | 186 | 82 | 87.2 | B+ |
df_muls.reorder_levels(['Address','School','Class'],axis=0).sort_index().head()
| | | Gender | Height | Weight | Math | Physics |
---|
Address | School | Class | | | | | |
street_1 | S_1 | C_1 | M | 173 | 63 | 34 | A+ |
| | C_3 | F | 175 | 57 | 87.7 | A- |
| S_2 | C_2 | M | 175 | 74 | 47.2 | B- |
street_2 | S_1 | C_1 | F | 192 | 73 | 32.5 | B+ |
| | C_1 | M | 186 | 82 | 87.2 | B+ |
3 索引设定
3.1 index_col参数
index_col是read_csv中的一个参数,而不是某一个方法:
pd.read_csv('data/table.csv',index_col=['Address','School']).head()
| Class | ID | Gender | Height | Weight | Math | Physics | |
---|
Address | School | | | | | | | |
street_1 | S_1 | C_1 | 1101 | M | 173 | 63 | 34 | A+ |
street_2 | S_1 | C_1 | 1102 | F | 192 | 73 | 32.5 | B+ |
| S_1 | C_1 | 1103 | M | 186 | 82 | 87.2 | B+ |
| S_1 | C_1 | 1104 | F | 167 | 81 | 80.4 | B- |
street_4 | S_1 | C_1 | 1105 | F | 159 | 64 | 84.8 | B+ |
3.2 reindex和reindex_like
reindex是指重新索引,它的重要特性在于索引对齐,很多时候用于重新排序
df.head()
| School | Class | Gender | Address | Height | Weight | Math | Physics |
---|
ID | | | | | | | | |
1101 | S_1 | C_1 | M | street_1 | 173 | 63 | 34.0 | A+ |
1102 | S_1 | C_1 | F | street_2 | 192 | 73 | 32.5 | B+ |
1103 | S_1 | C_1 | M | street_2 | 186 | 82 | 87.2 | B+ |
1104 | S_1 | C_1 | F | street_2 | 167 | 81 | 80.4 | B- |
1105 | S_1 | C_1 | F | street_4 | 159 | 64 | 84.8 | B+ |
df.reindex(index=[1101,1203,1206,2402])
| School | Class | Gender | Address | Height | Weight | Math | Physics |
---|
ID | | | | | | | | |
1101 | S_1 | C_1 | M | street_1 | 173.0 | 63.0 | 34.0 | A+ |
1203 | S_1 | C_2 | M | street_6 | 160.0 | 53.0 | 58.8 | A+ |
1206 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
2402 | S_2 | C_4 | M | street_7 | 166.0 | 82.0 | 48.7 | B |
df.reindex(columns=['Height','Gender','Average']).head()
| Height | Gender | Average |
---|
ID | | | |
1101 | 173 | M | NaN |
1102 | 192 | F | NaN |
1103 | 186 | M | NaN |
1104 | 167 | F | NaN |
1105 | 159 | F | NaN |
可以选择缺失值的填充方法:fill_value和method(bfill/ffill/nearest),其中method参数必须索引单调。
df.reindex(index=[1101,1203,1206,2402],method='bfill')
| School | Class | Gender | Address | Height | Weight | Math | Physics |
---|
ID | | | | | | | | |
1101 | S_1 | C_1 | M | street_1 | 173 | 63 | 34.0 | A+ |
1203 | S_1 | C_2 | M | street_6 | 160 | 53 | 58.8 | A+ |
1206 | S_1 | C_3 | M | street_4 | 161 | 68 | 31.5 | B+ |
2402 | S_2 | C_4 | M | street_7 | 166 | 82 | 48.7 | B |
df.reindex(index=[1101,1203,1206,2402],method='nearest')
| School | Class | Gender | Address | Height | Weight | Math | Physics |
---|
ID | | | | | | | | |
1101 | S_1 | C_1 | M | street_1 | 173 | 63 | 34.0 | A+ |
1203 | S_1 | C_2 | M | street_6 | 160 | 53 | 58.8 | A+ |
1206 | S_1 | C_2 | F | street_6 | 167 | 63 | 68.4 | B- |
2402 | S_2 | C_4 | M | street_7 | 166 | 82 | 48.7 | B |
reindex_like的作用为生成一个横纵索引完全与参数列表一致的DataFrame,数据使用被调用的表
df_temp = pd.DataFrame({'Weight':np.zeros(5),
'Height':np.zeros(5),
'ID':[1101,1104,1103,1106,1102]}).set_index('ID')
df_temp.reindex_like(df[0:5][['Weight','Height']])
| Weight | Height |
---|
ID | | |
1101 | 0.0 | 0.0 |
1102 | 0.0 | 0.0 |
1103 | 0.0 | 0.0 |
1104 | 0.0 | 0.0 |
1105 | NaN | NaN |
如果df_temp单调还可以使用method参数:
df_temp = pd.DataFrame({'Weight':range(5),
'Height':range(5),
'ID':[1101,1104,1103,1106,1102]}).set_index('ID').sort_index()
df_temp.reindex_like(df[0:5][['Weight','Height']],method='bfill')
| Weight | Height |
---|
ID | | |
1101 | 0 | 0 |
1102 | 4 | 4 |
1103 | 2 | 2 |
1104 | 1 | 1 |
1105 | 3 | 3 |
3.3 set_index和reset_index
et_index:从字面意思看,就是将某些列作为索引
使用表内列作为索引:
df.head()
| School | Class | Gender | Address | Height | Weight | Math | Physics |
---|
ID | | | | | | | | |
1101 | S_1 | C_1 | M | street_1 | 173 | 63 | 34.0 | A+ |
1102 | S_1 | C_1 | F | street_2 | 192 | 73 | 32.5 | B+ |
1103 | S_1 | C_1 | M | street_2 | 186 | 82 | 87.2 | B+ |
1104 | S_1 | C_1 | F | street_2 | 167 | 81 | 80.4 | B- |
1105 | S_1 | C_1 | F | street_4 | 159 | 64 | 84.8 | B+ |
df.set_index('Class').head()
| School | Gender | Address | Height | Weight | Math | Physics |
---|
Class | | | | | | | |
C_1 | S_1 | M | street_1 | 173 | 63 | 34.0 | A+ |
C_1 | S_1 | F | street_2 | 192 | 73 | 32.5 | B+ |
C_1 | S_1 | M | street_2 | 186 | 82 | 87.2 | B+ |
C_1 | S_1 | F | street_2 | 167 | 81 | 80.4 | B- |
C_1 | S_1 | F | street_4 | 159 | 64 | 84.8 | B+ |
利用append参数可以将当前索引维持不变
df.set_index('Class',append=True).head()
| | School | Gender | Address | Height | Weight | Math | Physics |
---|
ID | Class | | | | | | | |
1101 | C_1 | S_1 | M | street_1 | 173 | 63 | 34.0 | A+ |
1102 | C_1 | S_1 | F | street_2 | 192 | 73 | 32.5 | B+ |
1103 | C_1 | S_1 | M | street_2 | 186 | 82 | 87.2 | B+ |
1104 | C_1 | S_1 | F | street_2 | 167 | 81 | 80.4 | B- |
1105 | C_1 | S_1 | F | street_4 | 159 | 64 | 84.8 | |
当使用与表长相同的列作为索引(需要先转化为Series,否则报错):
df.set_index(pd.Series(range(df.shape[0]))).head()
| School | Class | Gender | Address | Height | Weight | Math | Physics |
---|
0 | S_1 | C_1 | M | street_1 | 173 | 63 | 34.0 | A+ |
1 | S_1 | C_1 | F | street_2 | 192 | 73 | 32.5 | B+ |
2 | S_1 | C_1 | M | street_2 | 186 | 82 | 87.2 | B+ |
3 | S_1 | C_1 | F | street_2 | 167 | 81 | 80.4 | B- |
4 | S_1 | C_1 | F | street_4 | 159 | 64 | 84.8 | B+ |
可以直接添加多级索引:
df.set_index([pd.Series(range(df.shape[0])),pd.Series(np.ones(df.shape[0]))]).head()
| | School | Class | Gender | Address | Height | Weight | Math | Physics |
---|
0 | 1.0 | S_1 | C_1 | M | street_1 | 173 | 63 | 34.0 | A+ |
1 | 1.0 | S_1 | C_1 | F | street_2 | 192 | 73 | 32.5 | B+ |
2 | 1.0 | S_1 | C_1 | M | street_2 | 186 | 82 | 87.2 | B+ |
3 | 1.0 | S_1 | C_1 | F | street_2 | 167 | 81 | 80.4 | B- |
4 | 1.0 | S_1 | C_1 | F | street_4 | 159 | 64 | 84.8 | B+ |
reset_index方法,它的主要功能是将索引重置
默认状态直接恢复到自然数索引:
df.reset_index().head()
| ID | School | Class | Gender | Address | Height | Weight | Math | Physics |
---|
0 | 1101 | S_1 | C_1 | M | street_1 | 173 | 63 | 34.0 | A+ |
1 | 1102 | S_1 | C_1 | F | street_2 | 192 | 73 | 32.5 | B+ |
2 | 1103 | S_1 | C_1 | M | street_2 | 186 | 82 | 87.2 | B+ |
3 | 1104 | S_1 | C_1 | F | street_2 | 167 | 81 | 80.4 | B- |
4 | 1105 | S_1 | C_1 | F | street_4 | 159 | 64 | 84.8 | B+ |
用level参数指定哪一层被reset,用col_level参数指定set到哪一层:
L1,L2 = ['A','B','C'],['a','b','c']
mul_index1 = pd.MultiIndex.from_product([L1,L2],names=('Upper', 'Lower'))
L3,L4 = ['D','E','F'],['d','e','f']
mul_index2 = pd.MultiIndex.from_product([L3,L4],names=('Big', 'Small'))
df_temp = pd.DataFrame(np.random.rand(9,9),index=mul_index1,columns=mul_index2)
df_temp.head()
Big | | | | D | | | E | | | F |
---|
Small | | d | e | f | d | e | f | d | e | f |
Upper | Lower | | | | | | | | | |
A | a | 0.415195 | 0.399841 | 0.432031 | 0.268736 | 0.148571 | 0.972363 | 0.89448 | 0.15669 | 0.462923 |
| b | 0.490508 | 0.150571 | 0.10015 | 0.772613 | 0.847422 | 0.320051 | 0.550675 | 0.069499 | 0.191721 |
| c | 0.193515 | 0.069548 | 0.720209 | 0.180638 | 0.988839 | 0.111657 | 0.649108 | 0.529743 | 0.739764 |
B | a | 0.956325 | 0.139672 | 0.631007 | 0.704335 | 0.104828 | 0.262378 | 0.607194 | 0.725691 | 0.096959 |
| b | 0.465611 | 0.288648 | 0.236706 | 0.706129 | 0.298313 | 0.342365 | 0.543323 | 0.490258 | 0.027482 |
df_temp1 = df_temp.reset_index(level=1,col_level=1)
df_temp1.head()
Big | | | | D | | | E | | | F |
---|
Small | Lower | d | e | f | d | e | f | d | e | f |
Upper | | | | | | | | | | |
A | a | 0.455772 | 0.568383 | 0.36601 | 0.142834 | 0.232744 | 0.045931 | 0.85495 | 0.082319 | 0.80917 |
A | b | 0.446874 | 0.459203 | 0.49244 | 0.891093 | 0.619426 | 0.127137 | 0.136137 | 0.111108 | 0.424913 |
A | c | 0.6719 | 0.195127 | 0.175704 | 0.940491 | 0.017882 | 0.195829 | 0.844949 | 0.335865 | 0.55247 |
B | a | 0.114634 | 0.973796 | 0.847149 | 0.606002 | 0.939 | 0.773361 | 0.52839 | 0.700832 | 0.516844 |
B | b | 0.683624 | 0.544524 | 0.904016 | 0.518869 | 0.839697 | 0.834856 | 0.874863 | 0.766719 | 0.598977 |
df_temp1.columns
MultiIndex([( '', 'Lower'),
('D', 'd'),
('D', 'e'),
('D', 'f'),
('E', 'd'),
('E', 'e'),
('E', 'f'),
('F', 'd'),
('F', 'e'),
('F', 'f')],
names=['Big', 'Small'])
df_temp1.index
Index(['A', 'A', 'A', 'B', 'B', 'B', 'C', 'C', 'C'], dtype='object', name='Upper')
3.4 rename_axis和rename
rename_axis是针对多级索引的方法,作用是修改某一层的索引名,而不是索引标签
df_temp.rename_axis(index={'Lower':'LowerLower'},columns={'Big':'BigBig'})
BigBig | | | D | | | E | | | F | |
---|
Small | | d | e | f | d | e | f | d | e | f |
Upper | LowerLower | | | | | | | | | |
A | a | 0.025169 | 0.113144 | 0.118285 | 0.775066 | 0.801492 | 0.497375 | 0.958616 | 0.685357 | 0.549634 |
| b | 0.837498 | 0.337893 | 0.231633 | 0.831686 | 0.716031 | 0.626049 | 0.619013 | 0.877352 | 0.310406 |
| c | 0.115737 | 0.111587 | 0.922571 | 0.258403 | 0.64657 | 0.936869 | 0.893763 | 0.014768 | 0.533456 |
B | a | 0.024406 | 0.15918 | 0.530797 | 0.312714 | 0.230486 | 0.310252 | 0.695073 | 0.195527 | 0.302385 |
| b | 0.453045 | 0.441613 | 0.327158 | 0.55987 | 0.951331 | 0.903411 | 0.406168 | 0.586651 | 0.284886 |
| c | 0.66042 | 0.719856 | 0.860304 | 0.064605 | 0.331111 | 0.163686 | 0.920732 | 0.409673 | 0.219974 |
C | a | 0.684049 | 0.900708 | 0.870861 | 0.044754 | 0.026487 | 0.146095 | 0.648923 | 0.187175 | 0.704501 |
| b | 0.237486 | 0.6841 | 0.255666 | 0.166265 | 0.401716 | 0.040835 | 0.914865 | 0.696318 | 0.653498 |
| c | 0.559561 | 0.044497 | 0.803069 | 0.374056 | 0.688914 | 0.56057 | 0.29154 | 0.705021 | 0.60011 |
rename方法用于修改列或者行索引标签,而不是索引名:
df_temp.rename(index={'A':'T'},columns={'e':'changed_e'}).head()
Big | | | | D | | | E | | | F |
---|
Small | | d | changed_e | f | d | changed_e | f | d | changed_e | f |
Upper | Lower | | | | | | | | | |
T | a | 0.967945 | 0.10047 | 0.592643 | 0.192416 | 0.990223 | 0.098881 | 0.198654 | 0.422801 | 0.304565 |
| b | 0.326164 | 0.289546 | 0.829121 | 0.761338 | 0.008772 | 0.738461 | 0.340697 | 0.513727 | 0.385448 |
| c | 0.475534 | 0.513195 | 0.825845 | 0.665717 | 0.359606 | 0.207218 | 0.55601 | 0.09565 | 0.880627 |
B | a | 0.75757 | 0.872631 | 0.08105 | 0.52173 | 0.7818 | 0.84037 | 0.649206 | 0.577388 | 0.0943 |
| b | 0.991114 | 0.033176 | 0.392372 | 0.590603 | 0.607997 | 0.171789 | 0.036382 | 0.106561 | 0.893742 |
4 常用索引型函数
4.1 where函数
当对条件为False的单元进行填充:
df.where(df['Gender']=='M').head()
| School | Class | Gender | Address | Height | Weight | Math | Physics |
---|
ID | | | | | | | | |
1101 | S_1 | C_1 | M | street_1 | 173.0 | 63.0 | 34.0 | A+ |
1102 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
1103 | S_1 | C_1 | M | street_2 | 186.0 | 82.0 | 87.2 | B+ |
1104 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
1105 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
通过这种方法筛选结果和[]操作符的结果完全一致:
df.where(df['Gender']=='M').dropna().head()
| School | Class | Gender | Address | Height | Weight | Math | Physics |
---|
ID | | | | | | | | |
1101 | S_1 | C_1 | M | street_1 | 173.0 | 63.0 | 34.0 | A+ |
1103 | S_1 | C_1 | M | street_2 | 186.0 | 82.0 | 87.2 | B+ |
1201 | S_1 | C_2 | M | street_5 | 188.0 | 68.0 | 97.0 | A- |
1203 | S_1 | C_2 | M | street_6 | 160.0 | 53.0 | 58.8 | A+ |
1301 | S_1 | C_3 | M | street_4 | 161.0 | 68.0 | 31.5 | B+ |
第一个参数为布尔条件,第二个参数为填充值:
df.where(df['Gender']=='M',np.random.rand(df.shape[0],df.shape[1])).head()
| School | Class | Gender | Address | Height | Weight | Math | Physics |
---|
ID | | | | | | | | |
1101 | S_1 | C_1 | M | street_1 | 173.000000 | 63.000000 | 34.000000 | A+ |
1102 | 0.0152467 | 0.708444 | 0.917199 | 0.302185 | 0.689643 | 0.010126 | 0.724636 | 0.895387 |
1103 | S_1 | C_1 | M | street_2 | 186.000000 | 82.000000 | 87.200000 | B+ |
1104 | 0.369195 | 0.459211 | 0.464191 | 0.964486 | 0.365797 | 0.127602 | 0.501496 | 0.0287754 |
1105 | 0.812232 | 0.999634 | 0.825782 | 0.285692 | 0.340197 | 0.083982 | 0.792310 | 0.133054 |
4.2 mask函数
mask函数与where功能上相反,其余完全一致,即对条件为True的单元进行填充
df.mask(df['Gender']=='M').dropna().head()
| School | Class | Gender | Address | Height | Weight | Math | Physics |
---|
ID | | | | | | | | |
1102 | S_1 | C_1 | F | street_2 | 192.0 | 73.0 | 32.5 | B+ |
1104 | S_1 | C_1 | F | street_2 | 167.0 | 81.0 | 80.4 | B- |
1105 | S_1 | C_1 | F | street_4 | 159.0 | 64.0 | 84.8 | B+ |
1202 | S_1 | C_2 | F | street_4 | 176.0 | 94.0 | 63.5 | B- |
1204 | S_1 | C_2 | F | street_5 | 162.0 | 63.0 | 33.8 | B |
df.mask(df['Gender']=='M',np.random.rand(df.shape[0],df.shape[1])).head()
| School | Class | Gender | Address | Height | Weight | Math | Physics |
---|
ID | | | | | | | | |
1101 | 0.273962 | 0.25028 | 0.587471 | 0.977206 | 0.442403 | 0.319460 | 0.460991 | 0.842498 |
1102 | S_1 | C_1 | F | street_2 | 192.000000 | 73.000000 | 32.500000 | B+ |
1103 | 0.436674 | 0.741524 | 0.46996 | 0.688603 | 0.938241 | 0.531811 | 0.794352 | 0.17495 |
1104 | S_1 | C_1 | F | street_2 | 167.000000 | 81.000000 | 80.400000 | B- |
1105 | S_1 | C_1 | F | street_4 | 159.000000 | 64.000000 | 84.800000 | B+ |
4.3 query函数
query函数中的布尔表达式中,下面的符号都是合法的:行列索引名、字符串、and/not/or/&/|/~/not in/in/==/!=、四则运算符
df.query('(Address in ["street_6","street_7"])&(Weight>(70+10))&(ID in [1303,2304,2402])')
| School | Class | Gender | Address | Height | Weight | Math | Physics |
---|
ID | | | | | | | | |
1303 | S_1 | C_3 | M | street_7 | 188 | 82 | 49.7 | B |
2304 | S_2 | C_3 | F | street_6 | 164 | 81 | 95.5 | A- |
2402 | S_2 | C_4 | M | street_7 | 166 | 82 | 48.7 | B |
5 重复元素处理
5.1 duplicated方法
该方法返回了是否重复的布尔列表
df.duplicated('Class').head()
ID
1101 False
1102 True
1103 True
1104 True
1105 True
dtype: bool
可选参数keep默认为first,即首次出现设为不重复,若为last,则最后一次设为不重复,若为False,则所有重复项为False
df.duplicated('Class',keep='last').tail()
ID
2401 True
2402 True
2403 True
2404 True
2405 False
dtype: bool
df.duplicated('Class',keep=False).head()
ID
1101 True
1102 True
1103 True
1104 True
1105 True
dtype: bool
5.2 drop_duplicates方法
剔除重复项
df.drop_duplicates('Class')
| School | Class | Gender | Address | Height | Weight | Math | Physics |
---|
ID | | | | | | | | |
1101 | S_1 | C_1 | M | street_1 | 173 | 63 | 34.0 | A+ |
1201 | S_1 | C_2 | M | street_5 | 188 | 68 | 97.0 | A- |
1301 | S_1 | C_3 | M | street_4 | 161 | 68 | 31.5 | B+ |
2401 | S_2 | C_4 | F | street_2 | 192 | 62 | 45.3 | A |
参数与duplicate函数类似:
df.drop_duplicates('Class',keep='last')
| School | Class | Gender | Address | Height | Weight | Math | Physics |
---|
ID | | | | | | | | |
2105 | S_2 | C_1 | M | street_4 | 170 | 81 | 34.2 | A |
2205 | S_2 | C_2 | F | street_7 | 183 | 76 | 85.4 | B |
2305 | S_2 | C_3 | M | street_4 | 187 | 73 | 48.9 | B |
2405 | S_2 | C_4 | F | street_6 | 193 | 54 | 47.6 | B |
在传入多列时等价于将多列共同视作一个多级索引,比较重复项:
df.drop_duplicates(['School','Class'])
| School | Class | Gender | Address | Height | Weight | Math | Physics |
---|
ID | | | | | | | | |
1101 | S_1 | C_1 | M | street_1 | 173 | 63 | 34.0 | A+ |
1201 | S_1 | C_2 | M | street_5 | 188 | 68 | 97.0 | A- |
1301 | S_1 | C_3 | M | street_4 | 161 | 68 | 31.5 | B+ |
2101 | S_2 | C_1 | M | street_7 | 174 | 84 | 83.3 | C |
2201 | S_2 | C_2 | M | street_5 | 193 | 100 | 39.1 | B |
2301 | S_2 | C_3 | F | street_4 | 157 | 78 | 72.3 | B+ |
2401 | S_2 | C_4 | F | street_2 | 192 | 62 | 45.3 | A |
6 抽样函数
这里的抽样函数指的就是sample函数
6.1 n为样本量
df.sample(n=5)
| School | Class | Gender | Address | Height | Weight | Math | Physics |
---|
ID | | | | | | | | |
2103 | S_2 | C_1 | M | street_4 | 157 | 61 | 52.5 | B- |
1102 | S_1 | C_1 | F | street_2 | 192 | 73 | 32.5 | B+ |
1301 | S_1 | C_3 | M | street_4 | 161 | 68 | 31.5 | B+ |
1304 | S_1 | C_3 | M | street_2 | 195 | 70 | 85.2 | A |
1105 | S_1 | C_1 | F | street_4 | 159 | 64 | 84.8 | B+ |
6.2 frac为抽样比
df.sample(frac=0.05)
| School | Class | Gender | Address | Height | Weight | Math | Physics |
---|
ID | | | | | | | | |
1105 | S_1 | C_1 | F | street_4 | 159 | 64 | 84.8 | B+ |
2402 | S_2 | C_4 | M | street_7 | 166 | 82 | 48.7 | B |
6.3 replace为是否放回
df.sample(n=df.shape[0],replace=True).head()
| School | Class | Gender | Address | Height | Weight | Math | Physics |
---|
ID | | | | | | | | |
2403 | S_2 | C_4 | F | street_6 | 158 | 60 | 59.7 | B+ |
2404 | S_2 | C_4 | F | street_2 | 160 | 84 | 67.7 | B |
2405 | S_2 | C_4 | F | street_6 | 193 | 54 | 47.6 | B |
2303 | S_2 | C_3 | F | street_7 | 190 | 99 | 65.9 | C |
1203 | S_1 | C_2 | M | street_6 | 160 | 53 | 58.8 | A+ |
df.sample(n=35,replace=True).index.is_unique
False
6.4 axis为抽样维度,默认为0,即抽行
df.sample(n=3,axis=1).head()
| Address | Weight | School |
---|
ID | | | |
1101 | street_1 | 63 | S_1 |
1102 | street_2 | 73 | S_1 |
1103 | street_2 | 82 | S_1 |
1104 | street_2 | 81 | S_1 |
1105 | street_4 | 64 | S_1 |
6.5 weights为样本权重,自动归一化
df.sample(n=3,weights=np.random.rand(df.shape[0])).head()
| School | Class | Gender | Address | Height | Weight | Math | Physics |
---|
ID | | | | | | | | |
1302 | S_1 | C_3 | F | street_1 | 175 | 57 | 87.7 | A- |
1305 | S_1 | C_3 | F | street_5 | 187 | 69 | 61.7 | B- |
2404 | S_2 | C_4 | F | street_2 | 160 | 84 | 67.7 | B |
df.sample(n=3,weights=df['Math']).head()
| School | Class | Gender | Address | Height | Weight | Math | Physics |
---|
ID | | | | | | | | |
1305 | S_1 | C_3 | F | street_5 | 187 | 69 | 61.7 | B- |
2103 | S_2 | C_1 | M | street_4 | 157 | 61 | 52.5 | B- |
2105 | S_2 | C_1 | M | street_4 | 170 | 81 | 34.2 | A |