import numpy as np
import pandas as pd
df = pd. read_csv( r"C:\SAIC\DataHunter\GitHub\joyful-pandas\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+
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[ 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 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[ 1102 : 2401 : 3 , 'Height' : 'Math' ] . head( )
Height Weight Math ID 1102 192 73 32.5 1105 159 64 84.8 1203 160 53 58.8 1301 161 68 31.5 1304 195 70 85.2
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. 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+
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-
s = pd. Series( df[ 'Math' ] , index= df. index)
s[ 1101 ]
34.0
s[ lambda x: x. index[ 16 : : - 6 ] ]
ID
2102 50.6
1301 31.5
1105 84.8
Name: Math, dtype: float64
s[ lambda x: [ 16 : : - 6 ] ]
File "<ipython-input-21-ada17acead14>", line 1
s[lambda x: [16::-6]]
^
SyntaxError: invalid syntax
s[ 0 : 4 ]
ID
1101 34.0
1102 32.5
1103 87.2
1104 80.4
Name: Math, dtype: float64
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
df. loc[ df[ 'Math' ] > 60 , ( df[ : 8 ] [ 'Address' ] == 'street_6' ) . values] . head( )
Physics ID 1103 B+ 1104 B- 1105 B+ 1201 A- 1202 B-
df[ : 8 ]
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+ 1201 S_1 C_2 M street_5 188 68 97.0 A- 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+
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+
df. at[ 1101 , 'School' ]
'S_1'
% timeit df. at[ 1101 , 'School' ] 最快
% timeit df. loc[ 1101 , 'School' ]
% timeit df. iat[ 0 , 0 ]
% timeit df. iloc[ 0 , 0 ]
3.74 µs ± 120 ns per loop (mean ± std. dev. of 7 runs, 100000 loops each)
6.99 µs ± 993 ns per loop (mean ± std. dev. of 7 runs, 100000 loops each)
4 µs ± 28 ns per loop (mean ± std. dev. of 7 runs, 100000 loops each)
6.7 µs ± 618 ns per loop (mean ± std. dev. of 7 runs, 100000 loops each)
pd. interval_range( start= 0 , end= 5 )
IntervalIndex([(0, 1], (1, 2], (2, 3], (3, 4], (4, 5]],
closed='right',
dtype='interval[int64]')
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]]
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
df_i. index. astype( 'interval' )
IntervalIndex([(0, 40], (0, 40], (80, 100], (80, 100], (80, 100] ... (40, 60], (40, 60], (40, 60], (60, 80], (40, 60]],
closed='right',
dtype='interval[int64]')
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
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
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
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'])
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'])
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+
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+
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
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.868146 0.779973 0.040035 0.312931 0.742738 0.400927 0.719805 0.098784 0.808752 b 0.640475 0.871246 0.300074 0.559656 0.032805 0.815044 0.909367 0.617527 0.071200 c 0.564827 0.720181 0.168413 0.605086 0.815405 0.919883 0.767163 0.099041 0.510799 B a 0.645498 0.212847 0.239187 0.664830 0.243526 0.907248 0.760947 0.811720 0.898771 b 0.598414 0.095890 0.124890 0.379378 0.723209 0.842317 0.817238 0.257404 0.982154 c 0.332865 0.472630 0.017562 0.741558 0.254717 0.023202 0.131158 0.988882 0.026893 C a 0.715418 0.653554 0.738060 0.161244 0.851520 0.902496 0.843573 0.761424 0.366131 b 0.994121 0.362501 0.292338 0.016310 0.532978 0.890472 0.621069 0.709472 0.949864 c 0.378175 0.358023 0.968979 0.021041 0.198345 0.901327 0.165456 0.930577 0.554062
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 e f d e f Upper Lower B a 0.645498 0.212847 0.243526 0.907248 0.760947 0.811720 0.898771 b 0.598414 0.095890 0.723209 0.842317 0.817238 0.257404 0.982154 c 0.332865 0.472630 0.254717 0.023202 0.131158 0.988882 0.026893 C a 0.715418 0.653554 0.851520 0.902496 0.843573 0.761424 0.366131 b 0.994121 0.362501 0.532978 0.890472 0.621069 0.709472 0.949864 c 0.378175 0.358023 0.198345 0.901327 0.165456 0.930577 0.554062
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.0 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+
df_using_mul. swaplevel( i= 1 , j= 0 , axis= 0 ) . sort_index( ) . head( )
好多方法仅仅是看了一遍,周六再仔细看看