1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

##(1) 通过index参数设置索引
import pandas as pd
data = pd.Series([1,2,3,4,5], index=['a','b','c','d','e'])
#a    1
#b    2
#c    3
#d    4
#e    5
#dtype: int64

##(2)如不设置index参数，则索引默认为 range(len)
data1 = pd.Series([1,2,3,4,5])

##(3)也可以以字典的形式创建，键值分别对应索引与值
data2 = pd.Series({'a':1,'b':2,'c':3,'d':4,'e':5})

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

import pandas as pd
data = pd.Series([1,2,3,4,5], index=['a','b','c','d','e'])
##(1) 提取Series的一维数组与索引标签
data.values
# array([1, 2, 3, 4, 5])
data.index
# Index(['a', 'b', 'c', 'd', 'e'], dtype='object')

##(2) 按显示索引取Series子集
data['a'] 
data['a':'c']
data[["a","c","e"]]
data[data>3]
data.loc["a":"c"]

##(3) 按隐式索引取Series子集
data[0]
data[0:3]
data[[0,2,4]]
data.iloc[1:3]

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

##(1) 单个Series转为DataFrame,通过columns参数设置列名
import pandas as pd
s1 = pd.Series({'a':1,'b':2,'c':3,'d':4,'e':5})
d1 = pd.DataFrame(s1, columns=["col_01"])
#   col_01
#a       1
#b       2
#c       3
#d       4
#e       5

##(2) 多个相同索引的Series对象构成的字典
s2 = pd.Series({'a':"Dog",'b':"Cat",'c':"Pig",'d':"Cow",'e':"Sheep"})
pd.DataFrame({"col_1":s1, "col_2":s2})

##(3) 通过字典列表创建：每个子字典代表一行；键名表示列名，可通过index设置行名
pd.DataFrame([{'a':1,'b':2,'c':3},
              {'a':2,'b':4,'c':6}], 
             index=["XiaoLi","XiaoWang"])
#          a  b  c
#XiaoLi    1  2  3
#XiaoWang  2  4  6

##(4) 将NumPy二维数组转为DataFrame
import numpy as np
pd.DataFrame(np.random.randint(0,10,(2,3)),
             columns=['a','b','c'],
             index=['sp1','sp2'])

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50

import pandas as pd
import numpy as np
s1 = pd.Series(np.random.choice(10,5,replace=False), 
               index=["stuA",'stuB','stuC','stuD','stuE'])
s2 = pd.Series(np.random.randint(60,100,5), 
               index=["stuA",'stuB','stuC','stuD','stuE'])
s3 = pd.Series(['class01','class01','class02','class02','class02'], 
               index=["stuA",'stuB','stuC','stuD','stuE'])
df = pd.DataFrame({'Grade':s1,"Score":s2,"Class":s3})
#      Grade  Score    Class
#stuA      3     95  class01
#stuB      9     96  class01
#stuC      1     85  class02
#stuD      5     76  class02
#stuE      8     70  class02

##(0) 初步探索
df.head()
df.info()    #表格概述信息
df.dtypes    #每列的数据类型
df.shape
df.size
df.values    #返回二维数组
df.T         #行列转换

##(1)获取行、列名（均为Index对象）以及值
df.index   #行名
df.columns #列名

##(2)返回列
df['Grade']
df.Grade
df[['Grade','Score']]
df.loc[:,'Grade':'Class']
df.iloc[:,0:3]
df['newCol'] = 0


##(3)返回行
df.loc['stuA':'stuC',:]
df.loc[df["Score"]>80,:]
df.loc[['stuA','stuC'],:]
df.iloc[1:3,:]
df[1:3]
df.iloc[[0,2,4],:]
df[df.Grade>5]  #筛选符合条件的行

##注意：如果仅取DataFrame的一行/一列，会返回为Serires对象；可加上中括号，返回为DataFrame
df[['Grade']]
df.loc[['stuC'],:]

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

import pandas as pd
idx = pd.Index([1,2,3,4,5])
# Int64Index([1, 2, 3, 4, 5], dtype='int64')

##(1) 数组相关操作
idx[0]    #支持一维数组的各种取值方式
list(idx) #转为普通列表

##(2) 集合相关操作
idx2 = pd.Index([4,5,6,7])
idx.intersection(idx2)
# Int64Index([4, 5], dtype='int64')
idx.union(idx2)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

import pandas as pd
import numpy as np
s1 = pd.Series(np.random.choice(10,3,replace=False), 
               index=["stuA",'stuB','stuC'])
s2 = pd.Series(np.random.randint(60,100,3), 
               index=["stuA",'stuB','stuC'])
s3 = pd.Series(['class01','class01','class02'], 
               index=["stuA",'stuB','stuC'])
s4 = pd.Series(['Zhang','Li','Liu'], 
               index=["stuA",'stuB','stuC'])
df = pd.DataFrame({'Grade':s1,"Score":s2,"Class":s3,"Name":s4})
#      Grade  Score    Class   Name
#stuA      0     60  class01  Zhang
#stuB      8     65  class01     Li
#stuC      2     80  class02    Liu

##(1)修改列名
df.rename(columns = {"Grade":"Level"})  # {"Old_name":"New_name"}
##(2)将特定列变成行名
df.set_index("Name")
##(3)将行名变为列
df["ID"] = df.index

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

import numpy as np
import pandas as pd

##(1) None缺失值
n1 = np.array([1,2,3,None])
# array([1, 2, 3, None], dtype=object)


n1.min()  # TypeError
n1.astype(np.float64)
#array([ 1.,  2.,  3., nan])


##(2) NaN缺失值
n2 = np.array([1,2,3,np.nan])
# array([ 1.,  2.,  3., nan])
# dtype('float64')

#含nan的一般统计运算都会返回nan值
n2.min()      # nan
#可使用针对处理含有nan的numpy数据
np.nanmin(n1) # 1.0

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

pd.Series([1,np.nan,3, None])
#0    1.0
#1    NaN
#2    3.0
#3    NaN
#dtype: float64

#当pandas整型数据中出现一个缺失值时，会自动转为浮点型数据
s1 = pd.Series(np.random.choice(10,5,replace=False), 
               index=["stuA",'stuB','stuC','stuD','stuE'])
s2 = pd.Series(np.random.randint(60,100,5), 
               index=["stuA",'stuB','stuC','stuD','stuE'])
s3 = pd.Series(['class01','class01','class02','class02','class02'], 
               index=["stuA",'stuB','stuC','stuD','stuE'])
df = pd.DataFrame({'Grade':s1,"Score":s2,"Class":s3})
df.iloc[2,0:2]=None
df.iloc[4,1]=np.nan
#       Grade  Score    Class
# stuA    1.0   91.0  class01
# stuB    3.0   61.0  class01
# stuC    NaN    NaN  class02
# stuD    0.0   94.0  class02
# stuE    7.0    NaN  class02

##(1)判断缺失值
df.isnull()   #是否为缺失值
df.notnull()  #是否为非缺失值

##(2)去除缺失值
df.dropna()       #删除含有缺失值的整行数据
# axis=1 :删除含有缺失值的整列数据
# how    :默认为'any';'all'表示全为缺失值才删除

##(3)填补缺失值
df.fillna(10)             #固定值填充
df.fillna(method='ffill') #使用缺失值前面的数据填充
df.fillna(method='bfill') #使用缺失值后面的数据填充

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

import pandas as pd
import numpy as np
##(1)整体算数运算：适用于介绍的np算法运算, 不影响原有结构
s1 = pd.Series({'a':1,'b':2,'c':3,'d':4,'e':5})  
#a    1
#b    2
#c    3
#d    4
#e    5
#dtype: int64

df1 = pd.DataFrame(np.random.randint(0,10,(2,3)),
                   columns=['a','b','c'],
                   index=['sp1','sp2'])
#     a  b  c
#sp1  5  9  4
#sp2  6  3  4

np.log(s1 + 1)
df1 / 10

##(2)Series之间索引对齐运算：即使顺序不同，按照索引一一对应计算
s2 = pd.Series({'a':1,'d':4,'e':5,'b':2,'c':3})   
s1 + s2
#如果索引不一致，按全集处理，用NaN填充
s3 = pd.Series({'a':1,'d':4,'e':5})   
s1 + s3

##(3)DataFrame与Series(行/列)之间运算
#按行
ss1 = pd.Series({'a':1,'b':2,'c':3})  
df1 - ss1                                   #所有行相减
df1.loc[["sp1"],:]=df1.loc[["sp1"],:]-ss1   #特定行相减 
#按列
ss2 = pd.Series({'sp1':10,'sp2':20})  
df1.subtract(ss2, axis=0)                   #所有列相减
df1.loc[:,"a"] = df1.loc[:,"a"] - ss2       #特定列相减

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

import pandas as pd
import numpy as np
df = pd.DataFrame(np.random.randint(0,100,(10,6)),
             index= ["stu%s" % (x) for x in range(10)],
             columns=["col1","col2","col3","col4","col5","col6"])
df["Group"] = ["Group%s" % (x) for x in list("A"*5 + "B"*5)]
df.head()
#       col1  col2  col3  col4  col5  col6   Group
# stu0     0    30    31    72    20     2  GroupA
# stu1    11    47    47    53    92    80  GroupA
# stu2    47    39    11    25    78    57  GroupA
# stu3    15    90    57    28    90    54  GroupA
# stu4    30     9    92    85    40     7  GroupA

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

##(1)数值列统计方法默认只会对数值列统计
df.col1.sum()
# 349

df.mean()
# col1    34.9
# col2    39.8
# col3    54.2
# col4    34.9
# col5    64.4
# col6    43.5
# dtype: float64

df.describe()
#             col1       col2       col3       col4       col5     col6
# count  10.000000  10.000000  10.000000  10.000000  10.000000  10.0000
# mean   34.900000  39.800000  54.200000  34.900000  64.400000  43.5000
# std    23.811528  24.548365  31.688764  27.404176  31.885908  26.7426
# min     0.000000   9.000000   2.000000   1.000000  11.000000   2.0000
# 25%    17.250000  23.250000  35.000000  19.000000  42.250000  28.2500
# 50%    31.000000  36.000000  56.500000  28.500000  78.000000  50.0000
# 75%    50.750000  47.000000  78.500000  48.250000  89.250000  56.2500
# max    70.000000  90.000000  92.000000  85.000000  99.000000  80.0000

##(2)统计分类
df.Group.value_counts()                #频数
df.Group.value_counts(normalize=True)  #百分比
df.Group.unique()                      #唯一值
df.describe(include=["O"])             #分别统计所有字符串列
pd.crosstab(df.Group, df.Group)        #类似R语言的table()

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

##(1) 指定DataFrame表格中的表示分组信息的列，进行分组统计
df.groupby('Group')["col2"].mean()                            #根据分组列，分组统计1列的值
# Group
# GroupA    43.0
# GroupB    36.6
# Name: col2, dtype: float64

df.groupby('Group', as_index=False).mean()                    #根据分组列，分组统计所有列的值
df.groupby('Group')["col2"].agg(["min","max"])                #根据分组列，分组统计1列的多个指标
df.groupby('Group')["col2"].describe()                        #根据分组列，分组统计1列的多个指标

##(2) 指定其它能够表示分组信息的，列表、Serises、字典等进行分组统计
Class = ["Class01","Class01","Class01","Class02","Class02","Class02",
         "Class03","Class03","Class03","Class03"]
df.groupby(Class)["col2"].mean()  
# Class01    38.666667
# Class02    55.333333
# Class03    29.000000
# Name: col2, dtype: float64

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

import pandas as pd
import numpy as np
df = pd.DataFrame(np.random.randint(0,100,(10,6)),
             index= ["stu%s" % (x) for x in range(10)],
             columns=["col1","col2","col3","col4","col5","col6"])
df["Group"] = ["Group%s" % (x) for x in list("A"*5 + "B"*5)]
 
df.sort_values("col1")                  #按照指定列升序排列
df.sort_values("col1", ascending=False) #按照指定列降序排列
df.sort_values(["col1", "col2"], 
               ascending=[True, False])

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

import pandas as pd
import numpy as np
s1 = pd.Series(list("ABBAC"), 
               index=["stuA",'stuB','stuC','stuD','stuE'])
s2 = pd.Series(np.random.randint(60,100,5), 
               index=["stuA",'stuB','stuC','stuD','stuE'])
s3 = pd.Series(['class01','class01','class02','class02','class02'], 
               index=["stuA",'stuB','stuC','stuD','stuE'])
df = pd.DataFrame({'Grade':s1,"Score":s2,"Class":s3})
#      Grade  Score    Class
# stuA     A     84  class01
# stuB     B     68  class01
# stuC     B     84  class02
# stuD     A     78  class02
# stuE     C     74  class02

##(1) Python处理字符串方法
"level " + df.Grade 
df.Grade.str.len()
df.Class.str.upper()
df.Class.str.split("0")
# stuA    [class, 1]
# stuB    [class, 1]
# stuC    [class, 2]
# stuD    [class, 2]
# stuE    [class, 2]
# Name: Class, dtype: object

##(2) 正则化处理字符串，类似 re 模块
df.Class.str.match('([A-Za-z]+)')      #返回逻辑值
df.Class.str.extract('([A-Za-z]+)')    #提取匹配子字符串

##(3) 切片取值等
df.Class.str[0:3]
df.Class.str.split("0").str.get(0)
df.Class.str.split("0").str[0]

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

import pandas as pd
import numpy as np
df = pd.DataFrame(np.random.randint(0,100,(10,6)),
             index= ["stu%s" % (x) for x in range(10)],
             columns=["col1","col2","col3","col4","col5","col6"])
df["Group"] = ["Group%s" % (x) for x in list("A"*3 + "B"*3 + "C"*4)]
df.head()
      # col1  col2  col3  col4  col5  col6   Group
# stu0    13    24    72    45    58    77  GroupA
# stu1    93    29    67    38    95     7  GroupA
# stu2    32    85    54    34    51    84  GroupA
# stu3    16    92    36    10    51    67  GroupB
# stu4    78    69    96    32     1    87  GroupB
               
#### 下述默认是指筛选到符合条件的行               
               
##(1) 数值列筛选
df[df.col1>80]
               
##(2) 字符串列筛选
##可结合正则表达式匹配筛选
df[df.Group=="GroupA"]

##(3) 多个条件筛选
df[(df.col1>50) & (df.Group=="GroupA")]               
               
##(4) 筛选特定范围的值               
df[df.Group.isin(["GroupA","GroupB"])]               
df[~df.Group.isin(["GroupA","GroupB"])]     # ~ 表示逻辑 非    

##(5) query语句
df.query('col1>50 and Group=="GroupC"')
               
##(6) 去重
df.drop_duplicates(["Group"])

##直接删除指定列
df.drop([column], axis=1)
##直接删除指定行
df.drop([index])               

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

##(1) replace替换
df.Group.unique()
# array(['GroupA', 'GroupB', 'GroupC'], dtype=object)
##一对一替换
df["Group"].replace("GroupA","GroupC").unique()
# array(['GroupC', 'GroupB'], dtype=object)
##多对一替换
df["Group"].replace(["GroupA","GroupB"],"GroupC").unique()
# array(['GroupC'], dtype=object)

##(2) map替换
df["Group"].map({"GroupA":"class01", "GroupB":"class02", "GroupC":"class03"}).unique()
# array(['class01', 'class02', 'class03'], dtype=object)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50

import numpy as np
import pandas as pd

df1 = pd.DataFrame(np.random.randint(0, 20, (3,4)),
                  columns=['col1','col2','col3','col4'],
                  index=['sp1','sp2','sp3'])
#      col1  col2  col3  col4
# sp1    11    10    19    13
# sp2     6    13    18    10
# sp3     5    14     6    10
df2 = pd.DataFrame(np.random.randint(0, 20, (2,4)),
                  columns=['col1','col2','col3','col4'],
                  index=['sp4','sp5'])
#      col1  col2  col3  col4
# sp4    11     2     0     2
# sp5    11    19    12     4
df3 = pd.DataFrame(np.random.randint(0, 20, (2,4)),
                  columns=['col1','col2','col3','col4'],
                  index=['sp3','sp4'])
#      col1  col2  col3  col4
# sp3    13     5     3    15
# sp4     7     2     2    13
df4 = pd.DataFrame(np.random.randint(0, 20, (2,4)),
                  columns=['col3','col4','col5','col6'],
                  index=['sp4','sp5'])
#      col3  col4  col5  col6
# sp4    11     1     2    15
# sp5    18     2     5    18
df5 = pd.DataFrame(np.random.randint(0, 6, (3,2)),
                  columns=['col5','col6'],
                  index=['sp1','sp2','sp3'])
#      col5  col6
# sp1     0     5
# sp2     5     3
# sp3     2     0

##(1)普通合并--列名相同，行名不同，上下合并
pd.concat([df1, df2])
##(2)普通合并--行名相同，列名不同，左右合并
pd.concat([df1, df5], axis=1)

##(3)上下合并时，忽略索引重复
pd.concat([df1, df3])                           #允许存在重复行名
pd.concat([df1, df3]).reset_index()
pd.concat([df1, df3], verify_integrity=True)    #有重复行名会报错
pd.concat([df1, df3], ignore_index=True)        #重命名所有列名去重

##(4)上下合并时，列名不完全一致
pd.concat([df1, df4])                           #默认保留全集列名
pd.concat([df1, df4], join='inner')             #仅保留交集索引

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

df1 = pd.DataFrame([{'Stu':"XiaoLi",'Grade':2,'Class':'C01'},
                    {'Stu':"XiaoSun",'Grade':1,'Class':'C01'},
                    {'Stu':"XiaoAi",'Grade':3,'Class':'C02'}])
#        Stu  Grade Class
# 0   XiaoLi      2   C01
# 1  XiaoSun      1   C01
# 2   XiaoAi      3   C02
df2 = pd.DataFrame([{'Class':"C01",'Teacher':"Mr.Zhou"},
                    {'Class':"C02",'Teacher':"Mr.Wu"}])
#   Class  Teacher
# 0   C01  Mr.Zhou
# 1   C02    Mr.Wu

##如上会自动按照Class列合并两个DataFrmae
pd.merge(df1, df2)
#等价于
pd.merge(df1, df2, on="Class")

1
2
3
4
5
6
7

df1 = pd.DataFrame([{'Stu':"XiaoLi",'Grade':2,'Class':'C01'},
                    {'Stu':"XiaoSun",'Grade':1,'Class':'C01'},
                    {'Stu':"XiaoAi",'Grade':3,'Class':'C02'}])
df2 = pd.DataFrame([{'Class':"C01",'Teacher':"Mr.Zhou",'Grade':'A'},
                    {'Class':"C02",'Teacher':"Mr.Wu",'Grade':'B'}])
pd.merge(df1, df2, on="Class")
pd.merge(df1, df2, on="Class", suffixes=["_stu","_class"])

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

##(1) 指定列名合并
df1 = pd.DataFrame([{'Stu':"XiaoLi",'Grade':2,'Class':'C01'},
                    {'Stu':"XiaoSun",'Grade':1,'Class':'C01'},
                    {'Stu':"XiaoAi",'Grade':3,'Class':'C02'}])
df2 = pd.DataFrame([{'Room':"C01",'Teacher':"Mr.Zhou"},
                    {'Room':"C02",'Teacher':"Mr.Wu"}])  

#如上df1的Class列与df2的Room列表示相同的含义
pd.merge(df1, df2, left_on="Class", right_on="Room")
pd.merge(df1, df2, left_on="Class", right_on="Room").drop("Room", axis=1)

##(2)指定索引合并
df2.set_index("Room", inplace=True)
#       Teacher
# Room
# C01   Mr.Zhou
# C02     Mr.Wu
pd.merge(df1, df2, left_on="Class", right_index=True)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

##以其中的how="left"为例
df1 = pd.DataFrame([{'Stu':"XiaoLi",'Grade':2,'Class':'C01'},
                    {'Stu':"XiaoSun",'Grade':1,'Class':'C01'},
                    {'Stu':"XiaoAi",'Grade':3,'Class':'C02'},
                    {'Stu':"XiaoHe",'Grade':3,'Class':'C03'},])
df2 = pd.DataFrame([{'Class':"C01",'Teacher':"Mr.Zhou"},
                    {'Class':"C02",'Teacher':"Mr.Wu"}])  
pd.merge(df1, df2, how="left")
#        Stu  Grade Class  Teacher
# 0   XiaoLi      2   C01  Mr.Zhou
# 1  XiaoSun      1   C01  Mr.Zhou
# 2   XiaoAi      3   C02    Mr.Wu
# 3   XiaoHe      3   C03      NaN

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

import numpy as np
import pandas as pd

df = pd.DataFrame(np.random.randint(0, 20, (3,4)),
                  columns=['col1','col2','col3','col4'],
                  index=['sp1','sp2','sp3'])
#      col1  col2  col3  col4
# sp1    11    10    19    13
# sp2     6    13    18    10
# sp3     5    14     6    10

1
2
3
4
5
6
7
8
9

df.to_csv('test.csv')
df.to_csv('test.txt', sep="\t")       #指定分隔符
df.to_csv('test.csv', index=False)    #不保存行名
df.to_csv('test.csv', header=False)   #不保存列名

df.to_excel('test.xlsx')                       #需要提前安装openpyxl模块
df.to_excel('test.xlsx',sheet_name='Sheet1')   #自定义保存的sheet名称

df.

1
2
3
4
5
6
7
8
9

df0 = pd.read_csv('./test.txt')                      #默认第一行为列名，且没有行名
df0 = pd.read_csv('./test.txt', sep="\t")            #指定分隔符

df0 = pd.read_csv('./test.csv', header=None)         #不设置列名
df0 = pd.read_csv('./test.csv', names=list("ABCD"))  #自定义列名
df0 = pd.read_csv('./test.csv', index_col=0)         #指定第一列(索引为0)作为行名
df0 = pd.read_csv('./test.csv', index_col="col1")    #指定列作为行名

df0 = pd.read_excel('test.xlsx')