【python】- 常用库之Pandas(3)
1 数据结构的简介:DataFrame 和 Series
pandas中有两类非常重要的数据结构,就是序列Series和数据框DataFrame.Series类似于NumPy中的一维数组,可以使用一维数组的可用函数和方法,而且还可以通过索引标签的方式获取数据,还具有索引的自动对齐功能;DataFrame类似于numpy中的二维数组,同样可以使用numpy数组的函数和方法,还具有一些其它灵活的使用。 相关素材: 链接:https://pan.baidu.com/s/1v6ozONcGBq20N4V-mGmFBw 提取码:p0yu
1.1 Series的创建
import pandas as pd
import numpy as np
arr1= np. arange( 10 )
print ( "数组arr1:" , arr1)
print ( "arr1的数据类型:" , type ( arr1) )
s1= pd. Series( arr1)
print ( "序列s1:\n" , s1)
print ( "s1的数据类型:" , type ( s1) )
数组arr1: [0 1 2 3 4 5 6 7 8 9]
arr1的数据类型: <class 'numpy.ndarray'>
序列s1:
0 0
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
dtype: int32
s1的数据类型: <class 'pandas.core.series.Series'>
dict1= { 'a' : 1 , 'b' : 2 , 'c' : 3 , 'd' : 4 , 'e' : 5 }
print ( "字典dict1:" , dict1)
print ( "dict1的数据类型:" , type ( dict1) )
s2= pd. Series( dict1)
print ( "序列s2:" , s2)
print ( "s2的数据类型:" , type ( s2) )
字典dict1: {'a': 1, 'b': 2, 'c': 3, 'd': 4, 'e': 5}
dict1的数据类型: <class 'dict'>
序列s2: a 1
b 2
c 3
d 4
e 5
dtype: int64
s2的数据类型: <class 'pandas.core.series.Series'>
print ( "第一种方法创建DataFrame" )
arr2= np. array( np. arange( 12 ) ) . reshape( 4 , 3 )
print ( "数组2:" , arr2)
print ( "数组2的类型" , type ( arr2) )
df1= pd. DataFrame( arr2)
print ( "数据框1:\n" , df1)
print ( "数据框1的类型:" , type ( df1) )
print ( "------------------------------------------------" )
print ( "\n" )
print ( "第二种方法创建DataFrame" )
dict2= { 'a' : [ 1 , 2 , 3 , 4 ] , 'b' : [ 5 , 6 , 7 , 8 ] , 'c' : [ 9 , 10 , 11 , 12 ] , 'd' : [ 13 , 14 , 15 , 16 ] }
print ( "字典2-字典列表:" , dict2)
print ( "字典2的类型" , type ( dict2) )
df2= pd. DataFrame( dict2)
print ( "数据框2:\n" , df2)
print ( "数据框2的类型:" , type ( df2) )
print ( "------------------------------------------------" )
print ( "\n" )
dict3= { 'one' : { 'a' : 1 , 'b' : 2 , 'c' : 3 , 'd' : 4 } ,
'two' : { 'a' : 5 , 'b' : 6 , 'c' : 7 , 'd' : 8 } ,
'three' : { 'a' : 9 , 'b' : 10 , 'c' : 11 , 'd' : 12 } }
print ( "字典3-嵌套字典:" , dict3)
print ( "字典3的类型" , type ( dict3) )
df3= pd. DataFrame( dict3)
print ( "数据框3:\n" , df3)
print ( "数据框3的类型:" , type ( df3) )
print ( "------------------------------------------------" )
print ( "\n" )
print ( "第三种方法创建DataFrame" )
df4= df3[ [ 'one' , 'three' ] ]
print ( "数据框4:\n" , df4)
print ( "数据框4的类型:" , type ( df4) )
print ( "------------------------------------------------" )
print ( "\n" )
print ( "------------------------------------------------" )
print ( "------------------------------------------------" )
print ( "\n" )
s3= df3[ 'one' ]
print ( "序列3:\n" , s3)
print ( "序列3的类型:" , type ( s3) )
print ( "------------------------------------------------" )
s4= df3. iloc[ 0 ]
print ( "序列4:\n" , s4)
print ( "序列4的类型:" , type ( s4) )
第一种方法创建DataFrame
数组2: [[ 0 1 2]
[ 3 4 5]
[ 6 7 8]
[ 9 10 11]]
数组2的类型 <class 'numpy.ndarray'>
数据框1:
0 1 2
0 0 1 2
1 3 4 5
2 6 7 8
3 9 10 11
数据框1的类型: <class 'pandas.core.frame.DataFrame'>
------------------------------------------------
第二种方法创建DataFrame
字典2-字典列表: {'a': [1, 2, 3, 4], 'b': [5, 6, 7, 8], 'c': [9, 10, 11, 12], 'd': [13, 14, 15, 16]}
字典2的类型 <class 'dict'>
数据框2:
a b c d
0 1 5 9 13
1 2 6 10 14
2 3 7 11 15
3 4 8 12 16
数据框2的类型: <class 'pandas.core.frame.DataFrame'>
------------------------------------------------
字典3-嵌套字典: {'one': {'a': 1, 'b': 2, 'c': 3, 'd': 4}, 'two': {'a': 5, 'b': 6, 'c': 7, 'd': 8}, 'three': {'a': 9, 'b': 10, 'c': 11, 'd': 12}}
字典3的类型 <class 'dict'>
数据框3:
one two three
a 1 5 9
b 2 6 10
c 3 7 11
d 4 8 12
数据框3的类型: <class 'pandas.core.frame.DataFrame'>
------------------------------------------------
第三种方法创建DataFrame
数据框4:
one three
a 1 9
b 2 10
c 3 11
d 4 12
数据框4的类型: <class 'pandas.core.frame.DataFrame'>
------------------------------------------------
------------------------------------------------
------------------------------------------------
序列3:
a 1
b 2
c 3
d 4
Name: one, dtype: int64
序列3的类型: <class 'pandas.core.series.Series'>
------------------------------------------------
序列4:
one 1
two 5
three 9
Name: a, dtype: int64
序列4的类型: <class 'pandas.core.series.Series'>
2 数据索引index
无论数据框还是序列,最左侧始终有一个非原始数据对象,这个就是接下来要介绍的数据索引。通过索引获取目标数据,对数据进行一系列的操作。
2.1 通过索引值或索引标签获取数据
s5= pd. Series( np. array( [ 1 , 2 , 3 , 4 , 5 , 6 ] ) )
print ( s5)
0 1
1 2
2 3
3 4
4 5
5 6
dtype: int32
s5. index
RangeIndex(start=0, stop=6, step=1)
s5. index= [ 'a' , 'b' , 'c' , 'd' , 'e' , 'f' ]
s5
a 1
b 2
c 3
d 4
e 5
f 6
dtype: int32
s5[ 3 ]
4
s5[ 'e' ]
5
s5[ [ 1 , 3 , 5 ] ]
b 2
d 4
f 6
dtype: int32
s5[ : 4 ]
a 1
b 2
c 3
d 4
dtype: int32
s5[ 'c' : ]
c 3
d 4
e 5
f 6
dtype: int32
s5[ 'b' : 'e' ]
b 2
c 3
d 4
e 5
dtype: int32
2.2 自动化对齐
s6= pd. Series( np. array( [ 10 , 15 , 20 , 30 , 55 , 80 ] ) , index= [ 'a' , 'b' , 'c' , 'd' , 'e' , 'f' ] )
print ( "序列6:" , s6)
s7= pd. Series( np. array( [ 12 , 11 , 13 , 15 , 14 , 16 ] ) , index= [ 'a' , 'c' , 'g' , 'b' , 'd' , 'f' ] )
print ( "序列7:" , s7)
print ( s6+ s7)
print ( s6/ s7)
序列6: a 10
b 15
c 20
d 30
e 55
f 80
dtype: int32
序列7: a 12
c 11
g 13
b 15
d 14
f 16
dtype: int32
a 22.0
b 30.0
c 31.0
d 44.0
e NaN
f 96.0
g NaN
dtype: float64
a 0.833333
b 1.000000
c 1.818182
d 2.142857
e NaN
f 5.000000
g NaN
dtype: float64
3 pandas查询数据
通过布尔索引有针对的选取原数据的子集,指定行,指定列等。
test_data= pd. read_csv( 'test_set.csv' )
test_data. head( )
ID age job marital education default balance housing loan contact day month duration campaign pdays previous poutcome 0 25318 51 housemaid married unknown no 174 no no telephone 29 jul 308 3 -1 0 unknown 1 25319 32 management married tertiary no 6059 yes no cellular 20 nov 110 2 -1 0 unknown 2 25320 60 retired married primary no 0 no no telephone 30 jul 130 3 -1 0 unknown 3 25321 32 student single tertiary no 64 no no cellular 30 jun 598 4 105 5 failure 4 25322 41 housemaid married secondary no 0 yes yes cellular 15 jul 368 4 -1 0 unknown
test_data[ 'job' ] , jnum= pd. factorize( test_data[ 'job' ] )
test_data[ 'job' ] = test_data[ 'job' ] + 1
test_data[ 'marital' ] , jnum= pd. factorize( test_data[ 'marital' ] )
test_data[ 'marital' ] = test_data[ 'marital' ] + 1
test_data[ 'education' ] , jnum= pd. factorize( test_data[ 'education' ] )
test_data[ 'education' ] = test_data[ 'education' ] + 1
test_data[ 'default' ] , jnum= pd. factorize( test_data[ 'default' ] )
test_data[ 'default' ] = test_data[ 'default' ] + 1
test_data[ 'housing' ] , jnum= pd. factorize( test_data[ 'housing' ] )
test_data[ 'housing' ] = test_data[ 'housing' ] + 1
test_data[ 'loan' ] , jnum= pd. factorize( test_data[ 'loan' ] )
test_data[ 'loan' ] = test_data[ 'loan' ] + 1
test_data[ 'contact' ] , jnum= pd. factorize( test_data[ 'contact' ] )
test_data[ 'contact' ] = test_data[ 'contact' ] + 1
test_data[ 'month' ] , jnum= pd. factorize( test_data[ 'month' ] )
test_data[ 'month' ] = test_data[ 'month' ] + 1
test_data[ 'poutcome' ] , jnum= pd. factorize( test_data[ 'poutcome' ] )
test_data[ 'poutcome' ] = test_data[ 'poutcome' ] + 1
test_data. head( )
ID age job marital education default balance housing loan contact day month duration campaign pdays previous poutcome 0 25318 51 1 1 1 1 174 1 1 1 29 1 308 3 -1 0 1 1 25319 32 2 1 2 1 6059 2 1 2 20 2 110 2 -1 0 1 2 25320 60 3 1 3 1 0 1 1 1 30 1 130 3 -1 0 1 3 25321 32 4 2 2 1 64 1 1 2 30 3 598 4 105 5 2 4 25322 41 1 1 4 1 0 2 2 2 15 1 368 4 -1 0 1
test_data. head( )
ID age job marital education default balance housing loan contact day month duration campaign pdays previous poutcome 0 25318 51 1 1 1 1 174 1 1 1 29 1 308 3 -1 0 1 1 25319 32 2 1 2 1 6059 2 1 2 20 2 110 2 -1 0 1 2 25320 60 3 1 3 1 0 1 1 1 30 1 130 3 -1 0 1 3 25321 32 4 2 2 1 64 1 1 2 30 3 598 4 105 5 2 4 25322 41 1 1 4 1 0 2 2 2 15 1 368 4 -1 0 1
test_data. tail( )
ID age job marital education default balance housing loan contact day month duration campaign pdays previous poutcome 10847 36165 29 2 2 2 1 54 2 2 3 26 4 257 3 -1 0 1 10848 36166 35 2 1 4 1 674 1 1 2 8 5 87 2 -1 0 1 10849 36167 38 6 1 4 1 435 2 1 3 23 4 193 2 -1 0 1 10850 36168 41 7 3 4 1 783 2 2 2 14 4 135 3 363 2 2 10851 36169 34 8 1 3 1 15841 2 1 2 20 6 423 5 -1 0 1
test_data. iloc[ [ 0 , 2 , 4 , 5 , 7 ] ]
ID age job marital education default balance housing loan contact day month duration campaign pdays previous poutcome 0 25318 51 1 1 1 1 174 1 1 1 29 1 308 3 -1 0 1 2 25320 60 3 1 3 1 0 1 1 1 30 1 130 3 -1 0 1 4 25322 41 1 1 4 1 0 2 2 2 15 1 368 4 -1 0 1 5 25323 26 5 1 4 1 -293 2 2 3 5 3 64 2 -1 0 1 7 25325 31 4 2 2 1 117 2 1 1 18 1 41 1 -1 0 1
test_data[ [ 'age' , 'job' , 'marital' ] ] . head( )
age job marital 0 51 1 1 1 32 2 1 2 60 3 1 3 32 4 2 4 41 1 1
test_data. loc[ [ 0 , 2 , 4 , 5 , 7 ] , [ 'age' , 'job' , 'marital' ] ]
age job marital 0 51 1 1 2 60 3 1 4 41 1 1 5 26 5 1 7 31 4 2
test_data[ test_data[ 'age' ] == 51 ] . head( )
ID age job marital education default balance housing loan contact day month duration campaign pdays previous poutcome 0 25318 51 1 1 1 1 174 1 1 1 29 1 308 3 -1 0 1 95 25413 51 2 1 2 1 346 1 1 2 11 5 237 1 180 6 4 350 25668 51 5 1 4 1 564 1 2 2 8 1 187 2 -1 0 1 408 25726 51 8 1 1 1 2547 2 1 3 15 4 491 2 -1 0 1 573 25891 51 10 1 3 1 183 1 2 2 7 5 292 2 -1 0 1
test_data[ ( test_data[ 'age' ] == 51 ) & ( test_data[ 'job' ] >= 5 ) ] . head( )
ID age job marital education default balance housing loan contact day month duration campaign pdays previous poutcome 350 25668 51 5 1 4 1 564 1 2 2 8 1 187 2 -1 0 1 408 25726 51 8 1 1 1 2547 2 1 3 15 4 491 2 -1 0 1 573 25891 51 10 1 3 1 183 1 2 2 7 5 292 2 -1 0 1 610 25928 51 8 1 3 1 440 1 1 2 11 5 46 5 -1 0 1 618 25936 51 5 3 4 1 -406 1 2 2 14 1 150 4 -1 0 1
test_data[ ( test_data[ 'age' ] == 51 ) & ( test_data[ 'job' ] >= 5 ) ] [ [ 'education' , 'housing' , 'loan' , 'contact' , 'poutcome' ] ] . head( )
education housing loan contact poutcome 350 4 1 2 2 1 408 1 2 1 3 1 573 3 1 2 2 1 610 3 1 1 2 1 618 4 1 2 2 1
多个条件的查询,需要在&或者|的两端的条件括起来
4 对DataFrames进行统计分析
Pandas为我们提供了很多描述性统计分析的指标函数,包括,总和,均值,最小值,最大值等。
a= np. random. normal( size= 10 )
d1= pd. Series( 2 * a+ 3 )
d2= np. random. f( 2 , 4 , size= 10 )
d3= np. random. randint( 1 , 100 , size= 10 )
print ( d1)
print ( d2)
print ( d3)
0 5.811077
1 2.963418
2 2.295078
3 0.279647
4 6.564293
5 1.146455
6 1.903623
7 1.157710
8 2.921304
9 2.397009
dtype: float64
[0.18147396 0.48218962 0.42565903 0.10258942 0.55299842 0.10859328
0.66923199 1.18542009 0.12053079 4.64172891]
[33 17 71 45 33 83 68 41 69 23]
d1. count( )
10
d1. min ( )
0.6149265534311872
d1. max ( )
6.217953512253818
d1. idxmin( )
8
d1. idxmax( )
1
d1. quantile( 0.1 )
1.4006153623854274
d1. sum ( )
27.43961378467516
d1. mean( )
2.743961378467515
d1. median( )
2.3460435427041384
d1. mode( )
0 0.279647
1 1.146455
2 1.157710
3 1.903623
4 2.295078
5 2.397009
6 2.921304
7 2.963418
8 5.811077
9 6.564293
dtype: float64
d1. var( )
4.027871738323722
d1. std( )
2.0069558386580715
d1. mad( )
1.456849211331346
d1. skew( )
1.0457755613918738
d1. kurt( )
0.39322767370407874
d1. describe( )
count 10.000000
mean 2.743961
std 2.006956
min 0.279647
25% 1.344189
50% 2.346044
75% 2.952890
max 6.564293
dtype: float64
def stats ( x) :
return pd. Series( [ x. count( ) , x. min ( ) , x. idxmin( ) , x. quantile( .25 ) , x. median( ) ,
x. quantile( .75 ) , x. mean( ) , x. max ( ) , x. idxmax( ) , x. mad( ) , x. var( ) , x. std( ) , x. skew( ) , x. kurt( ) ] ,
index= [ 'Count' , 'Min' , 'Which_Min' , 'Q1' , 'Median' , 'Q3' , 'Mean' , 'Max' , 'Which_Max' , 'Mad' , 'Var' , 'Std' , 'Skew' ,
'Kurt' ] )
stats( d1)
Count 10.000000
Min 0.279647
Which_Min 3.000000
Q1 1.344189
Median 2.346044
Q3 2.952890
Mean 2.743961
Max 6.564293
Which_Max 4.000000
Mad 1.456849
Var 4.027872
Std 2.006956
Skew 1.045776
Kurt 0.393228
dtype: float64
df= pd. DataFrame( np. array( [ d1, d2, d3] ) . T, columns= [ 'x1' , 'x2' , 'x3' ] )
print ( df. head( ) )
df. apply ( stats)
x1 x2 x3
0 5.811077 0.181474 33.0
1 2.963418 0.482190 17.0
2 2.295078 0.425659 71.0
3 0.279647 0.102589 45.0
4 6.564293 0.552998 33.0
x1 x2 x3 Count 10.000000 10.000000 10.000000 Min 0.279647 0.102589 17.000000 Which_Min 3.000000 3.000000 1.000000 Q1 1.344189 0.135767 33.000000 Median 2.346044 0.453924 43.000000 Q3 2.952890 0.640174 68.750000 Mean 2.743961 0.847042 48.300000 Max 6.564293 4.641729 83.000000 Which_Max 4.000000 9.000000 5.000000 Mad 1.456849 0.826613 19.560000 Var 4.027872 1.890358 520.900000 Std 2.006956 1.374903 22.823234 Skew 1.045776 2.833981 0.181633 Kurt 0.393228 8.395169 -1.498559
以上很简单的创建了数值型数据的统计性描述,但对于离散型数据就不能使用该方法了。我们在统计离散变量的观测数、唯一值个数、众数水平及个数,只需要使用describe方法就可以实现这样的统计了。
train_data= pd. read_csv( 'train_set.csv' )
train_data. head( )
ID age job marital education default balance housing loan contact day month duration campaign pdays previous poutcome y 0 1 43 management married tertiary no 291 yes no unknown 9 may 150 2 -1 0 unknown 0 1 2 42 technician divorced primary no 5076 yes no cellular 7 apr 99 1 251 2 other 0 2 3 47 admin. married secondary no 104 yes yes cellular 14 jul 77 2 -1 0 unknown 0 3 4 28 management single secondary no -994 yes yes cellular 18 jul 174 2 -1 0 unknown 0 4 5 42 technician divorced secondary no 2974 yes no unknown 21 may 187 5 -1 0 unknown 0
train_data[ 'job' ] . describe( )
count 25317
unique 12
top blue-collar
freq 5456
Name: job, dtype: object
test_data[ 'job' ] . describe( )
count 10852.000000
mean 5.593255
std 2.727318
min 1.000000
25% 3.000000
50% 6.000000
75% 8.000000
max 12.000000
Name: job, dtype: float64
除了以上简单的描述性统计之外,还提供了连续变量的相关系数(corr)和协方差(cov)的求解
df
x1 x2 x3 0 5.811077 0.181474 33.0 1 2.963418 0.482190 17.0 2 2.295078 0.425659 71.0 3 0.279647 0.102589 45.0 4 6.564293 0.552998 33.0 5 1.146455 0.108593 83.0 6 1.903623 0.669232 68.0 7 1.157710 1.185420 41.0 8 2.921304 0.120531 69.0 9 2.397009 4.641729 23.0
df. corr( )
x1 x2 x3 x1 1.000000 -0.075466 -0.393609 x2 -0.075466 1.000000 -0.437821 x3 -0.393609 -0.437821 1.000000
df. corr( 'spearman' )
x1 x2 x3 x1 1.000000 0.260606 -0.559273 x2 0.260606 1.000000 -0.510641 x3 -0.559273 -0.510641 1.000000
df. corr( 'pearson' )
x1 x2 x3 x1 1.000000 -0.075466 -0.393609 x2 -0.075466 1.000000 -0.437821 x3 -0.393609 -0.437821 1.000000
df. corr( 'kendall' )
x1 x2 x3 x1 1.000000 0.200000 -0.359573 x2 0.200000 1.000000 -0.359573 x3 -0.359573 -0.359573 1.000000
df. corrwith( df[ 'x1' ] )
x1 1.000000
x2 -0.075466
x3 -0.393609
dtype: float64
df. cov( )
x1 x2 x3 x1 4.027872 -0.208238 -18.029331 x2 -0.208238 1.890358 -13.738701 x3 -18.029331 -13.738701 520.900000
5 pandas实现SQL操作
pandas实现对数据的增删改查
dict = { 'Name' : [ 'LiuShunxiang' , 'Zhangshan' ] ,
'Sex' : [ 'M' , 'F' ] ,
'Age' : [ 27 , 23 ] ,
'Height' : [ 165.7 , 167.2 ] ,
'weight' : [ 61 , 63 ] }
print ( dict )
student1= pd. DataFrame( dict )
print ( student1)
dict = { 'Name' : [ 'Liu' , 'Zhang' ] ,
'Sex' : [ 'M' , 'F' ] ,
'Age' : [ 27 , 23 ] ,
'Height' : [ 165.7 , 167.2 ] ,
'weight' : [ 61 , 63 ] }
student2= pd. DataFrame( dict )
{'Name': ['LiuShunxiang', 'Zhangshan'], 'Sex': ['M', 'F'], 'Age': [27, 23], 'Height': [165.7, 167.2], 'weight': [61, 63]}
Name Sex Age Height weight
0 LiuShunxiang M 27 165.7 61
1 Zhangshan F 23 167.2 63
student3= pd. concat( [ student1, student2] , ignore_index= 'Ture' )
student3
Name Sex Age Height weight 0 LiuShunxiang M 27 165.7 61 1 Zhangshan F 23 167.2 63 2 Liu M 27 165.7 61 3 Zhang F 23 167.2 63
pd. DataFrame( student2, columns= [ 'Age' , 'Heught' , 'Name' , 'Sex' , 'weight' , 'Score' ] )
Age Heught Name Sex weight Score 0 27 NaN Liu M 61 NaN 1 23 NaN Zhang F 63 NaN
del student2
student2
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
<ipython-input-32-dbecb61e032f> in <module>()
----> 1 student2
NameError: name 'student2' is not defined
student3. drop( [ 0 ] )
Name Sex Age Height weight 1 Zhangshan F 23 167.2 63 2 Liu M 27 165.7 61 3 Zhang F 23 167.2 63
student3[ student3[ 'Age' ] < 25 ]
Name Sex Age Height weight 1 Zhangshan F 23 167.2 63 3 Zhang F 23 167.2 63
student3. drop( [ 'Height' , 'weight' ] , axis= 1 )
Name Sex Age 0 LiuShunxiang M 27 1 Zhangshan F 23 2 Liu M 27 3 Zhang F 23
不论删除行还是列,都可以通过drop方法实现,只需要设定好删除的轴即可,即调整drop方法中的axis参数。默认参数为0,即删除行观测数据,如果需要删除列变量,则需要设置为1.
student3
Name Sex Age Height weight 0 LiuShunxiang M 27 165.7 61 1 Zhangshan F 23 167.2 63 2 Liu M 27 165.7 61 3 Zhang F 23 167.2 63
student3. loc[ student3[ 'Name' ] == 'Liu' , 'Height' ] = 173
student3
Name Sex Age Height weight 0 LiuShunxiang M 27 165.7 61 1 Zhangshan F 23 167.2 63 2 Liu M 27 173.0 61 3 Zhang F 23 167.2 63
student3. groupby( 'Sex' ) . mean( )
Age Height weight Sex F 23 167.20 63 M 27 169.35 61
student3. groupby( [ 'Sex' , 'Age' ] ) . mean( )
Height weight Sex Age F 23 167.20 63 M 27 169.35 61
student3. drop( 'Age' , axis= 1 ) . groupby( 'Sex' ) . agg( [ np. mean, np. median] )
Height weight mean median mean median Sex F 167.20 167.20 63 63 M 169.35 169.35 61 61
series= pd. Series( np. array( np. random. randint( 1 , 20 , 10 ) ) )
series
0 7
1 19
2 7
3 19
4 19
5 14
6 7
7 18
8 3
9 5
dtype: int32
series. sort_values( )
8 3
9 5
0 7
2 7
6 7
5 14
7 18
1 19
3 19
4 19
dtype: int32
series. sort_values( ascending= False )
4 19
3 19
1 19
7 18
5 14
6 7
2 7
0 7
9 5
8 3
dtype: int32
student3. sort_values( by= [ 'Sex' , 'Age' ] )
Name Sex Age Height weight 1 Zhangshan F 23 167.2 63 3 Zhang F 23 167.2 63 0 LiuShunxiang M 27 165.7 61 2 Liu M 27 173.0 61
dict2= { 'Name' : [ 'Alfred' , 'Alice' , 'Barbara' , 'Carol' , 'Henry' , 'Jeffrey' , 'Judy' , 'Philip' , 'Robert' , 'william' ] ,
'Score' : [ 88 , 76 , 89 , 67 , 79 , 90 , 92 , 86 , 73 , 77 ] }
score= pd. DataFrame( dict2)
score
Name Score 0 Alfred 88 1 Alice 76 2 Barbara 89 3 Carol 67 4 Henry 79 5 Jeffrey 90 6 Judy 92 7 Philip 86 8 Robert 73 9 william 77
student3[ 'Name' ] = [ 'Alfred' , 'Alice' , 'Barbara' , 'Carol' ]
student3
Name Sex Age Height weight 0 Alfred M 27 165.7 61 1 Alice F 23 167.2 63 2 Barbara M 27 173.0 61 3 Carol F 23 167.2 63
stu_score1= pd. merge( student3, score, on= 'Name' )
stu_score1
Name Sex Age Height weight Score 0 Alfred M 27 165.7 61 88 1 Alice F 23 167.2 63 76 2 Barbara M 27 173.0 61 89 3 Carol F 23 167.2 63 67
stu_score1= pd. merge( student3, score, on= 'Name' , how= 'inner' )
stu_score1
Name Sex Age Height weight Score 0 Alfred M 27 165.7 61 88 1 Alice F 23 167.2 63 76 2 Barbara M 27 173.0 61 89 3 Carol F 23 167.2 63 67
使用how参数设置连接的方式,left为左连接,right为右连接,outer为外连接
stu_score2= pd. merge( student3, score, on= 'Name' , how= 'left' )
stu_score2
Name Sex Age Height weight Score 0 Alfred M 27 165.7 61 88 1 Alice F 23 167.2 63 76 2 Barbara M 27 173.0 61 89 3 Carol F 23 167.2 63 67
stu_score3= pd. merge( student3, score, on= 'Name' , how= 'right' )
stu_score3
Name Sex Age Height weight Score 0 Alfred M 27.0 165.7 61.0 88 1 Alice F 23.0 167.2 63.0 76 2 Barbara M 27.0 173.0 61.0 89 3 Carol F 23.0 167.2 63.0 67 4 Henry NaN NaN NaN NaN 79 5 Jeffrey NaN NaN NaN NaN 90 6 Judy NaN NaN NaN NaN 92 7 Philip NaN NaN NaN NaN 86 8 Robert NaN NaN NaN NaN 73 9 william NaN NaN NaN NaN 77
stu_score4= pd. merge( student3, score, on= 'Name' , how= 'outer' )
stu_score4
Name Sex Age Height weight Score 0 Alfred M 27.0 165.7 61.0 88 1 Alice F 23.0 167.2 63.0 76 2 Barbara M 27.0 173.0 61.0 89 3 Carol F 23.0 167.2 63.0 67 4 Henry NaN NaN NaN NaN 79 5 Jeffrey NaN NaN NaN NaN 90 6 Judy NaN NaN NaN NaN 92 7 Philip NaN NaN NaN NaN 86 8 Robert NaN NaN NaN NaN 73 9 william NaN NaN NaN NaN 77
6 对缺失值的处理
现实中的数据存在很多噪音的同时,缺失值也非常的常见。缺失值的存在会影响后期的数据分析或挖掘工作,那么缺失值的处理有哪些方法呢?
6.1 删除法
当数据中某个变量大部分值都会缺失值时,可以考虑删除该变量; 当缺失值时随机分布的,且缺失的数量并不是很多时,可以删除这些缺失的观测;
train_data. head( )
ID age job marital education default balance housing loan contact day month duration campaign pdays previous poutcome y 0 1 43 management married tertiary no 291 yes no unknown 9 may 150 2 -1 0 unknown 0 1 2 42 technician divorced primary no 5076 yes no cellular 7 apr 99 1 251 2 other 0 2 3 47 admin. married secondary no 104 yes yes cellular 14 jul 77 2 -1 0 unknown 0 3 4 28 management single secondary no -994 yes yes cellular 18 jul 174 2 -1 0 unknown 0 4 5 42 technician divorced secondary no 2974 yes no unknown 21 may 187 5 -1 0 unknown 0
offline_data= pd. read_csv( 'ccf_offline_stage1_train.csv' )
offline_data. head( )
User_id Merchant_id Coupon_id Discount_rate Distance Date_received Date 0 1439408 2632 NaN NaN 0.0 NaN 20160217.0 1 1439408 4663 11002.0 150:20 1.0 20160528.0 NaN 2 1439408 2632 8591.0 20:1 0.0 20160217.0 NaN 3 1439408 2632 1078.0 20:1 0.0 20160319.0 NaN 4 1439408 2632 8591.0 20:1 0.0 20160613.0 NaN
s= offline_data[ 'Coupon_id' ]
s. size
1754884
sum ( pd. isnull( s) )
701602
s. dropna( )
1 11002.0
2 8591.0
3 1078.0
4 8591.0
6 8591.0
7 7610.0
8 11951.0
9 1532.0
10 12737.0
13 1097.0
15 10698.0
16 9776.0
17 12034.0
18 5054.0
19 7802.0
20 7610.0
22 2366.0
23 7531.0
24 11002.0
25 13490.0
27 6704.0
31 2705.0
32 1495.0
33 11166.0
35 11166.0
36 11197.0
37 111.0
38 7531.0
39 111.0
41 2840.0
...
1754833 416.0
1754834 12097.0
1754835 11951.0
1754839 14031.0
1754843 10027.0
1754844 13118.0
1754845 2107.0
1754848 3469.0
1754849 10883.0
1754850 1807.0
1754851 7166.0
1754853 10050.0
1754854 4567.0
1754855 7379.0
1754856 13056.0
1754857 12034.0
1754858 1480.0
1754859 7751.0
1754860 17.0
1754861 3237.0
1754862 12034.0
1754866 111.0
1754869 4723.0
1754873 5686.0
1754876 5267.0
1754877 3739.0
1754878 5686.0
1754880 3739.0
1754882 1633.0
1754883 2705.0
Name: Coupon_id, Length: 1053282, dtype: float64
默认情况下,dropna会删除任何含有缺失值的行
df= pd. DataFrame( [ [ 1 , 1 , 2 ] , [ 3 , 5 , np. nan] , [ 13 , 21 , 34 ] , [ 55 , np. nan, 10 ] ,
[ np. nan, np. nan, np. nan] , [ np. nan, 1 , 2 ] ] , columns= [ 'x1' , 'x2' , 'x3' ] )
df
x1 x2 x3 0 1.0 1.0 2.0 1 3.0 5.0 NaN 2 13.0 21.0 34.0 3 55.0 NaN 10.0 4 NaN NaN NaN 5 NaN 1.0 2.0
df. dropna( )
x1 x2 x3 0 1.0 1.0 2.0 2 13.0 21.0 34.0
df= pd. DataFrame( [ [ 1 , 1 , 2 ] , [ 3 , 5 , np. nan] , [ 13 , 21 , 34 ] , [ 55 , np. nan, 10 ] ,
[ np. nan, np. nan, np. nan] , [ np. nan, 1 , 2 ] ] , columns= [ 'x1' , 'x2' , 'x3' ] )
df. dropna( how= 'all' )
x1 x2 x3 0 1.0 1.0 2.0 1 3.0 5.0 NaN 2 13.0 21.0 34.0 3 55.0 NaN 10.0 5 NaN 1.0 2.0
df= pd. DataFrame( [ [ 1 , 1 , 2 ] , [ 3 , 5 , np. nan] , [ 13 , 21 , 34 ] , [ 55 , np. nan, 10 ] ,
[ np. nan, np. nan, np. nan] , [ np. nan, 1 , 2 ] ] , columns= [ 'x1' , 'x2' , 'x3' ] )
df. dropna( how= 'any' )
x1 x2 x3 0 1.0 1.0 2.0 2 13.0 21.0 34.0
df= pd. DataFrame( [ [ 1 , 1 , 2 , np. nan] , [ 3 , 5 , np. nan, np. nan] , [ 13 , 21 , 34 , np. nan] , [ 55 , np. nan, 10 , np. nan] ,
[ np. nan, np. nan, np. nan, np. nan] , [ np. nan, 1 , 2 , np. nan] ] , columns= [ 'x1' , 'x2' , 'x3' , 'x4' ] )
print ( df)
df. dropna( how= 'all' , axis= 1 )
x1 x2 x3 x4
0 1.0 1.0 2.0 NaN
1 3.0 5.0 NaN NaN
2 13.0 21.0 34.0 NaN
3 55.0 NaN 10.0 NaN
4 NaN NaN NaN NaN
5 NaN 1.0 2.0 NaN
x1 x2 x3 0 1.0 1.0 2.0 1 3.0 5.0 NaN 2 13.0 21.0 34.0 3 55.0 NaN 10.0 4 NaN NaN NaN 5 NaN 1.0 2.0
df= pd. DataFrame( [ [ 1 , 1 , 2 , np. nan] , [ 3 , 5 , np. nan, np. nan] , [ 13 , 21 , 34 , np. nan] , [ 55 , np. nan, 10 , np. nan] ,
[ np. nan, np. nan, np. nan, np. nan] , [ np. nan, 1 , 2 , np. nan] ] , columns= [ 'x1' , 'x2' , 'x3' , 'x4' ] )
df. dropna( thresh= 3 )
x1 x2 x3 x4 0 1.0 1.0 2.0 NaN 2 13.0 21.0 34.0 NaN
df= pd. DataFrame( [ [ 1 , 1 , 2 , np. nan] , [ 3 , 5 , np. nan, np. nan] , [ 13 , 21 , 34 , np. nan] , [ 55 , np. nan, 10 , np. nan] ,
[ np. nan, np. nan, np. nan, np. nan] , [ np. nan, 1 , 2 , np. nan] ] , columns= [ 'x1' , 'x2' , 'x3' , 'x4' ] )
df. dropna( thresh= 1 )
x1 x2 x3 x4 0 1.0 1.0 2.0 NaN 1 3.0 5.0 NaN NaN 2 13.0 21.0 34.0 NaN 3 55.0 NaN 10.0 NaN 5 NaN 1.0 2.0 NaN
df= pd. DataFrame( [ [ 1 , 1 , 2 , np. nan] , [ 3 , 5 , np. nan, np. nan] , [ 13 , 21 , 34 , np. nan] , [ 55 , np. nan, 10 , np. nan] ,
[ np. nan, np. nan, np. nan, np. nan] , [ np. nan, 1 , 2 , np. nan] ] , columns= [ 'x1' , 'x2' , 'x3' , 'x4' ] )
df. dropna( thresh= 3 , axis= 1 )
x1 x2 x3 0 1.0 1.0 2.0 1 3.0 5.0 NaN 2 13.0 21.0 34.0 3 55.0 NaN 10.0 4 NaN NaN NaN 5 NaN 1.0 2.0
6.2 替补法
对于连续变量,如果变量的分布近似或就是正态分布的话,可以用均值替代那些缺失值; 如果变量是有偏的,可以使用中位数来代替那些缺失值; 对于离散型变量,一般使用众数去替换那些存在缺失的预测;
fillna函数的参数:
value:用于填充缺失值的标量值或者字典对象 method:插值方式,如果函数调用时,未指定其他参数的话默认值fill axis:待填充的轴默认值axis=0 inplace:修改调用这对象而不产生副本 limit:(对于前向和后项填充)可以连续填充的最大数量
df. fillna( 0 )
x1 x2 x3 0 1.0 1.0 2.0 1 3.0 5.0 0.0 2 13.0 21.0 34.0 3 55.0 0.0 10.0 4 0.0 0.0 0.0 5 0.0 1.0 2.0
df. fillna( method= 'ffill' )
x1 x2 x3 0 1.0 1.0 2.0 1 3.0 5.0 2.0 2 13.0 21.0 34.0 3 55.0 21.0 10.0 4 55.0 21.0 10.0 5 55.0 1.0 2.0
df. fillna( method= 'bfill' )
x1 x2 x3 0 1.0 1.0 2.0 1 3.0 5.0 34.0 2 13.0 21.0 34.0 3 55.0 1.0 10.0 4 NaN 1.0 2.0 5 NaN 1.0 2.0
df. fillna( { 'x1' : 1 , 'x2' : 2 , 'x3' : 3 } )
x1 x2 x3 0 1.0 1.0 2.0 1 3.0 5.0 3.0 2 13.0 21.0 34.0 3 55.0 2.0 10.0 4 1.0 2.0 3.0 5 1.0 1.0 2.0
x1_median= df[ 'x1' ] . median( )
x2_mean= df[ 'x2' ] . mean( )
x3_mean= df[ 'x3' ] . mean( )
print ( x1_median, x2_mean, x3_mean)
8.0 7.0 12.0
df. fillna( { 'x1' : x1_median, 'x2' : x2_mean, 'x3' : x3_mean} )
x1 x2 x3 0 1.0 1.0 2.0 1 3.0 5.0 12.0 2 13.0 21.0 34.0 3 55.0 7.0 10.0 4 8.0 7.0 12.0 5 8.0 1.0 2.0
使用填充法时,相对于常数填充或者前项、后项填充,使用各列众数,均值或中位数填充要更加合理些,这也是工作中常用的一个快捷手段。
7 实现excel的数据透视表功能
pivot_table(data,values=None,index=None,columns=None,aggfunc=‘mean’, fill_value=None,margins=False,dropna=True,margins_name=‘All’) #data:需要进行数据透视表操作的数据框 #values:指定需要聚合的字段 #index:指定某些原始变量作为行索引 #columns:指定哪些离散的分组变量 #aggfunc:指定相应的聚合函数 #fill_values:使用一个常数替代缺失值,默认不替换 #margins:是否进行行或列的汇总,默认不汇总 #dropna:默认所有观测为缺失的列 #margins_name:默认行汇总或列汇总的名称为‘ALL’
test_data. head( )
ID age job marital education default balance housing loan contact day month duration campaign pdays previous poutcome 0 25318 51 1 1 1 1 174 1 1 1 29 1 308 3 -1 0 1 1 25319 32 2 1 2 1 6059 2 1 2 20 2 110 2 -1 0 1 2 25320 60 3 1 3 1 0 1 1 1 30 1 130 3 -1 0 1 3 25321 32 4 2 2 1 64 1 1 2 30 3 598 4 105 5 2 4 25322 41 1 1 4 1 0 2 2 2 15 1 368 4 -1 0 1
pd. pivot_table( test_data, values= [ 'balance' ] , columns= [ 'marital' ] )
marital 1 2 3 balance 1455.797641 1313.231841 1251.767347
pd. pivot_table( test_data, values= [ 'balance' , 'housing' ] , columns= [ 'marital' ] )
marital 1 2 3 balance 1455.797641 1313.231841 1251.767347 housing 1.555051 1.529353 1.549388
pd. pivot_table( test_data, values= [ 'balance' , 'housing' ] , columns= [ 'marital' , 'job' ] )
marital job
balance 1 1 1228.493671
2 1785.391272
3 2353.951501
4 845.000000
5 1243.956701
6 977.805556
7 1364.432602
8 1243.585938
9 1579.081081
10 1952.578544
11 1270.040984
12 1749.975000
2 1 1355.333333
2 1501.268148
3 1890.900000
4 1205.809322
5 1438.189147
6 878.873606
7 1236.768448
8 1047.812500
9 1716.306931
10 1367.694737
11 2158.580000
12 814.833333
3 1 1166.673913
2 1801.176471
3 1454.990196
4 2946.000000
5 962.784141
6 940.809917
...
housing 1 7 1.601881
8 1.725361
9 1.459459
10 1.475096
11 1.573770
12 1.000000
2 1 1.393939
2 1.482963
3 1.300000
4 1.241525
5 1.533333
6 1.676580
7 1.587786
8 1.708333
9 1.257426
10 1.452632
11 1.540000
12 1.055556
3 1 1.239130
2 1.533088
3 1.235294
4 1.000000
5 1.550661
6 1.628099
7 1.657609
8 1.705521
9 1.513514
10 1.457143
11 1.571429
12 1.500000
Length: 72, dtype: float64
pd. pivot_table( test_data, values= [ 'balance' , 'housing' ] , columns= [ 'marital' , 'job' ] )
marital job
balance 1 1 1228.493671
2 1785.391272
3 2353.951501
4 845.000000
5 1243.956701
6 977.805556
7 1364.432602
8 1243.585938
9 1579.081081
10 1952.578544
11 1270.040984
12 1749.975000
2 1 1355.333333
2 1501.268148
3 1890.900000
4 1205.809322
5 1438.189147
6 878.873606
7 1236.768448
8 1047.812500
9 1716.306931
10 1367.694737
11 2158.580000
12 814.833333
3 1 1166.673913
2 1801.176471
3 1454.990196
4 2946.000000
5 962.784141
6 940.809917
...
housing 1 7 1.601881
8 1.725361
9 1.459459
10 1.475096
11 1.573770
12 1.000000
2 1 1.393939
2 1.482963
3 1.300000
4 1.241525
5 1.533333
6 1.676580
7 1.587786
8 1.708333
9 1.257426
10 1.452632
11 1.540000
12 1.055556
3 1 1.239130
2 1.533088
3 1.235294
4 1.000000
5 1.550661
6 1.628099
7 1.657609
8 1.705521
9 1.513514
10 1.457143
11 1.571429
12 1.500000
Length: 72, dtype: float64
很显然,这样的结果并不想Excel中预期的那样,该如何变成列联表的形式呢?很简单,只需将结果进行非堆叠操作即可。
pd. pivot_table( test_data, values= [ 'balance' , 'housing' ] , columns= [ 'marital' , 'job' ] ) . unstack( )
job 1 2 3 4 5 6 7 8 9 10 11 12 marital balance 1 1228.493671 1785.391272 2353.951501 845.000000 1243.956701 977.805556 1364.432602 1243.585938 1579.081081 1952.578544 1270.040984 1749.975000 2 1355.333333 1501.268148 1890.900000 1205.809322 1438.189147 878.873606 1236.768448 1047.812500 1716.306931 1367.694737 2158.580000 814.833333 3 1166.673913 1801.176471 1454.990196 2946.000000 962.784141 940.809917 859.793478 998.785276 803.864865 2890.657143 1379.485714 2940.500000 housing 1 1.295359 1.474112 1.219400 1.333333 1.544330 1.680556 1.601881 1.725361 1.459459 1.475096 1.573770 1.000000 2 1.393939 1.482963 1.300000 1.241525 1.533333 1.676580 1.587786 1.708333 1.257426 1.452632 1.540000 1.055556 3 1.239130 1.533088 1.235294 1.000000 1.550661 1.628099 1.657609 1.705521 1.513514 1.457143 1.571429 1.500000
这样的结果看起来更舒服一些
pd. pivot_table( test_data, values= [ 'balance' , 'housing' ] , columns= [ 'marital' ] ,
aggfunc= [ np. mean, np. median, np. std] )
mean median std marital 1 2 3 1 2 3 1 2 3 balance 1455.797641 1313.231841 1251.767347 479 431 372 3489.767929 2888.859422 3352.982857 housing 1.555051 1.529353 1.549388 2 2 2 0.496998 0.499220 0.497758
更多关于透视表的操作,大家可以参考《Pandas透视表(pivot_table)详解》
8 多层索引的使用
接下再讲一个Pandas中的重要功能,那就是多层索引。 序列的多层索引类似于Excel中如下形式。
s= pd. Series( [ 1 , 2 , 3 , 4 ] , index= [ [ '小张' , '小张' , '老王' , '老王' ] ,
[ '期中' , '期末' , '期中' , '期末' ] ] )
s
小张 期中 1
期末 2
老王 期中 3
期末 4
dtype: int64
s[ [ '小张' ] ]
小张 期中 1
期末 2
dtype: int64
s[ : , '期中' ]
小张 1
老王 3
dtype: int64
s. unstack( )
以上是对序列的多层次索引,接下来将对数据框的多层次索引,多层索引的形式类似excel中的如下形式。
df= pd. DataFrame( np. random. randint( 10 , 50 , 20 ) . reshape( 5 , 4 ) ,
index= [ [ 'A' , 'A' , 'A' , 'B' , 'B' ] , [ 1 , 2 , 3 , 1 , 2 ] ] ,
columns= [ [ 'x' , 'x' , 'x' , 'y' ] , [ 'x1' , 'x2' , 'x3' , 'y' ] ] )
df
x y x1 x2 x3 y A 1 45 45 17 39 2 14 23 41 46 3 36 43 36 39 B 1 42 13 18 17 2 20 46 48 24
df[ 'x' ]
x1 x2 x3 A 1 45 45 17 2 14 23 41 3 36 43 36 B 1 42 13 18 2 20 46 48
df. loc[ [ 'A' ] , : ]
x y x1 x2 x3 y A 1 45 45 17 39 2 14 23 41 46 3 36 43 36 39
在数据框中使用多层索引,可以将整个数据集控制在二维表结构中,这对于数据重塑和基于分组的操作(如数据透视表的生成)比较有帮助。
pd. pivot_table( test_data, index= [ 'marital' , 'loan' ] )
ID age balance campaign contact day default duration education housing job month pdays poutcome previous marital loan 1 1 30742.940724 43.724844 1585.893158 2.831504 2.219539 15.685876 1.012623 254.074094 3.113246 1.540249 5.640322 4.215514 39.616539 1.287596 0.535309 2 30756.275742 42.596859 835.289703 2.745201 2.215532 15.960733 1.034904 243.770506 3.314136 1.625654 6.091623 3.425829 34.813264 1.218150 0.445899 2 1 30742.904599 33.679970 1402.500190 2.667427 2.210946 15.686051 1.015964 270.407450 3.056632 1.528316 5.415431 4.501330 44.699734 1.331813 0.646142 2 30734.937500 33.416667 701.604167 2.901042 2.223958 14.864583 1.052083 253.484375 3.307292 1.536458 5.713542 3.427083 29.765625 1.195312 0.455729 3 1 30732.382937 46.012897 1380.368056 2.687500 2.275794 15.636905 1.014881 256.354167 3.129960 1.551587 5.200397 4.046627 39.643849 1.286706 0.523810 2 30764.129032 44.847926 654.396313 2.764977 2.239631 15.626728 1.050691 257.539171 3.331797 1.539171 5.543779 3.603687 37.571429 1.253456 0.414747
以上pandas模块的基本学习就完成了,大家若想更深一步的学习,可以参考《Pandas的官方文档》 ,本篇主要参考《Pandas数据分析之Pandas学习》