Pandas —— 数据分析处理库
安装Pandas: pip install pandas
import pandas as pd
pd.show_versions() # 显示当前版本信息
读取数据
读数据
pd.read_csv() : 读取csv类型数据
df = pd.read_csv('./data/titanic.csv')
df.head(n) : 显示前n条数据
df.head(6) # head()可以读取前几条数据,指定前几条都可以
df.info() : 返回数据的总体信息
df.info()
'''
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 891 entries, 0 to 890
Data columns (total 12 columns):
PassengerId 891 non-null int64
Survived 891 non-null int64
Pclass 891 non-null int64
Name 891 non-null object
Sex 891 non-null object
Age 714 non-null float64
SibSp 891 non-null int64
Parch 891 non-null int64
Ticket 891 non-null object
Fare 891 non-null float64
Cabin 204 non-null object
Embarked 889 non-null object
dtypes: float64(2), int64(5), object(5)
memory usage: 83.6+ KB
'''
数据项的索引
df.index : 数据项
df.columns ： 指标
df.index # RangeIndex(start=0, stop=891, step=1)
df.columns
'''
Index(['PassengerId', 'Survived', 'Pclass', 'Name', 'Sex', 'Age', 'SibSp',
'Parch', 'Ticket', 'Fare', 'Cabin', 'Embarked'],
dtype='object')
'''
df.dtypes : 数据类型
df.dtypes
'''
PassengerId int64
Survived int64
Pclass int64
Name object
Sex object
Age float64
SibSp int64
Parch int64
Ticket object
Fare float64
Cabin object
Embarked object
dtype: object
'''
索引
索引单个column
age = df['Age']
age[:5]
'''
age = df['Age']
age[:5]
age = df['Age']
age[:5]
0 22.0
1 38.0
2 26.0
3 35.0
4 35.0
Name: Age, dtype: float64
'''
索引多个column
df[['Age','Fare']][:5]
set_index: 默认index是0，1，2…, 也可以用set_index来指定某一个指标为index
df = df.set_index('Name')
df['Age'][:5]
'''
Name
Braund, Mr. Owen Harris 22.0
Cumings, Mrs. John Bradley (Florence Briggs Thayer) 38.0
Heikkinen, Miss. Laina 26.0
Futrelle, Mrs. Jacques Heath (Lily May Peel) 35.0
Allen, Mr. William Henry 35.0
Name: Age, dtype: float64
'''
age = df['Age']
age['Allen, Mr. William Henry'] #35.0
age=age+10
age[:5]
'''
Name
Braund, Mr. Owen Harris 32.0
Cumings, Mrs. John Bradley (Florence Briggs Thayer) 48.0
Heikkinen, Miss. Laina 36.0
Futrelle, Mrs. Jacques Heath (Lily May Peel) 45.0
Allen, Mr. William Henry 45.0
Name: Age, dtype: float64
'''
iloc:用position 来定位
df.iloc[0] # 取出第一条数据
df.iloc[0:5] # 展示前5行的信息
df.iloc[0:5,1:3] # 展示前5行，第2，3列的信息
loc :用lable 来定位
df = df.set_index('Name')
df.loc['Heikkinen, Miss. Laina'] #展示Heikkinen, Miss. Laina的所有信息
df.loc['Heikkinen, Miss. Laina','Fare'] #展示Heikkinen, Miss. Laina的fare信息
df.loc['Heikkinen, Miss. Laina':'Allen, Mr. William Henry',:] # 展示Heikkinen, Miss. Laina到Allen, Mr. William Henry的所有信息
df.loc['Heikkinen, Miss. Laina','Fare'] = 1000 # 修改信息
bool类型的索引
df['Fare'] > 40 # 对于每一条数据返回True或者False
df[df['Fare'] > 40][:5] #返回fare大于50的前5条数据
df[df['Sex'] == 'male'][:5] #返回男性的前5条数据
df.loc[df['Sex'] == 'male','Age'].mean() #返回男性年龄的平均值
(df['Age'] > 70).sum()#年龄大于70的人数
数据处理
数值运算
import pandas as pd
df = pd.DataFrame([[1,2,3],[4,5,6]],index = ['a','b'],columns = ['A','B','C'])
df.sum() : 求和运算
# 默认按照列求和
df.sum()
'''
A 5
B 7
C 9
dtype: int64
'''
# axis=1 按照行求和
df.sum(axis = 1)
'''
a 6
b 15
dtype: int64
'''
# axis = 'columns' 按照行求和
df.sum(axis = 'columns')
'''
a 6
b 15
dtype: int64
'''
df.mean() :均值
# 默认按照列求均值
df.mean()
'''
A 2.5
B 3.5
C 4.5
dtype: float64
'''
# axis=1 按照行求均值
df.mean(axis = 1)
'''
a 2.0
b 5.0
dtype: float64
'''
df.min(),df.max(): 求最大值，最小值
df.min()
df.max()
'''
A 4
B 5
C 6
dtype: int64
'''
统计特性
df.describe() ； 描述性统计
df.describe() # 描述性统计
median: 求中位数
df.median()
'''
A 2.5
B 3.5
C 4.5
dtype: float64
'''
df.cov() :返回一个协方差矩阵
df.cov()
df.corr(): 返回一个相关系数矩阵
df.corr()
value_counts() : 按照统计数量进行排序
#默认降序
df['Age'].value_counts()
'''
24.00 30
22.00 27
18.00 26
19.00 25
...
0.42 1
Name: Age, Length: 88, dtype: int64
'''
#升序
df['Age'].value_counts(ascending = True)
'''
0.42 1
...
18.00 26
22.00 27
24.00 30
'''
# 划分区间
df['Age'].value_counts(ascending = True,bins = 5)
'''
(64.084, 80.0] 11
(48.168, 64.084] 69
(0.339, 16.336] 100
(32.252, 48.168] 188
(16.336, 32.252] 346
Name: Age, dtype: int64
'''
dataframe结构
创
pd.DataFrame(data= ,index= ,columns = ) : 指定data, index , columns
data = [[1,2,3],[4,5,6]]
index = ['a','b']
columns = ['A','B','C']
df = pd.DataFrame(data=data,index=index,columns = columns)
df = pd.DataFrame(data,index = ) : 将columns嵌入data中
import numpy as np
data = {'animal': ['cat', 'cat', 'snake', 'dog', 'dog', 'cat', 'snake', 'cat', 'dog', 'dog'],
'age': [2.5, 3, 0.5, np.nan, 5, 2, 4.5, np.nan, 7, 3],
'visits': [1, 3, 2, 3, 2, 3, 1, 1, 2, 1],
'priority': ['yes', 'yes', 'no', 'yes', 'no', 'no', 'no', 'yes', 'no', 'no']}
labels = ['a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j']
df = pd.DataFrame(data,index = labels)
df.head()
'''
age animal priority visits
a 2.5 cat yes 1
b 3.0 cat yes 3
c 0.5 snake no 2
d NaN dog yes 3
e 5.0 dog no 2
'''
查
df[] : 索引
df['A']
'''
a 1
b 4
Name: A, dtype: int64
'''
df.iloc[] : 通过位置索引
df.iloc[0]
'''
A 1
B 2
C 3
Name: a, dtype: int64
'''
df.loc[] : 通过index，columns索引
df.loc['a']
'''
A 1
B 2
C 3
Name: a, dtype: int64
'''
df.loc['a']['A'] #1
改
# 修改数据项
df.loc['a']['A'] = 150
'''
A B C
a 150 2 3
b 4 5 6
'''
# 修改index
df.index = ['f','g']
'''
A B C
f 150 2 3
g 4 5 6
'''
增
增添一行数据
df.loc['c'] = [1,2,3]
'''
A B C
f 150 2 3
g 4 5 6
c 1 2 3
'''
增添一列指标
data = [[1,2,3],[4,5,6]]
index = ['j','k']
columns = ['A','B','C']
df2 = pd.DataFrame(data=data,index=index,columns = columns)
'''
A B C
j 1 2 3
k 4 5 6
'''
df2['Q'] = [10,11]
'''
A B C Q
j 1 2 3 10
k 4 5 6 11
'''
pd.concat(): 拼接两组数据
# axis = 0 竖着拼接
df3 = pd.concat([df,df2],axis = 0)
'''
A B C
f 150 2 3
g 4 5 6
c 1 2 3
j 1 2 3
k 4 5 6
'''
df4 = pd.DataFrame([[10,11],[12,13]],index=['j','k'],columns=['D','E'])
'''
D E
j 10 11
k 12 13
'''
# axis = 1 横着拼接
df5 = pd.concat([df2,df4],axis = 1)
'''
A B C Q D E
j 1 2 3 10 10 11
k 4 5 6 11 12 13
'''
删
df.drop() : 删除一行或者一列的数据
df5.drop(['j'],axis=0,inplace = True)
'''
A B C Q D E
k 4 5 6 11 12 13
'''
del ：删除一列数据
del df5['Q']
'''
A B C D E
k 4 5 6 12 13
'''
df5.drop(['A','B','C'],axis = 1,inplace = True)
'''
D E
k 12 13
'''
groupby操作
合并
构建一个key值有重复的df结构
df = pd.DataFrame({'key':['A','B','C','A','B','C','A','B','C'],
'data':[0,5,10,5,10,15,10,15,20]})
比较麻烦的方法进行分组
for key in ['A','B','C']:
print (key,df[df['key'] == key].sum())
用groupby 进行分组
df.groupby('key').sum() #key值相同的数据项相加合并
用groupy 可以进行单个或者多个依据分组
import pandas as pd
import numpy as np
df = pd.DataFrame({'A' : ['foo', 'bar', 'foo', 'bar',
'foo', 'bar', 'foo', 'foo'],
'B' : ['one', 'one', 'two', 'three',
'two', 'two', 'one', 'three'],
'C' : np.random.randn(8),
'D' : np.random.randn(8)})
'''
A B C D
0 foo one -0.785250 1.010458
1 bar one 2.549941 1.704677
2 foo two -0.255153 -0.603249
3 bar three -0.954625 0.117662
4 foo two -0.548512 0.648127
5 bar two -0.642762 -1.111568
6 foo one 0.870697 0.556371
7 foo three 0.839937 0.798669
'''
# 单个分组依据
grouped = df.groupby('A')
grouped.count()
'''
B C D
A
bar 3 3 3
foo 5 5 5
'''
#多个分组依据
grouped = df.groupby(['A','B'])
grouped.count()
'''
C D
A B
bar one 1 1
three 1 1
two 1 1
foo one 2 2
three 1 1
two 2 2
'''
groupy 中可以传入自定义函数
def get_letter_type(letter):
if letter.lower() in 'aeiou':
return 'a'
else:
return 'b'
grouped = df.groupby(get_letter_type,axis = 1)
grouped.count().iloc[0]
'''
a 1
b 3
Name: 0, dtype: int64
'''
有关groupy的其他操作
first() ,last(): 显示第一次或者最后一次出现各个组的对应值
s = pd.Series([1,2,3,1,2,3],[8,7,5,8,7,5])
'''
8 1
7 2
5 3
8 1
7 2
5 3
dtype: int64
'''
grouped = s.groupby(level = 0)
grouped.first() #自动升序分组：5，7，8 显示第一次出现5，7，8的值
'''
5 3
7 2
8 1
dtype: int64
'''
grouped.last() #显示最后次出现5，7，8的值
'''
5 3
7 2
8 1
dtype: int64
'''
grouped = s.groupby(level = 0,sort =False)
grouped.first()
'''
8 1
7 2
5 3
dtype: int64
'''
grouped.sum() : 同组内的数值求和
grouped.sum()
'''
5 6
7 4
8 2
dtype: int64
'''
get_group() : 得到该组的所有数据
df2 = pd.DataFrame({'X':['A','B','A','B'],'Y':[1,2,3,4]})
'''
X Y
0 A 1
1 B 2
2 A 3
3 B 4
'''
df2.groupby(['X']).get_group('A')
'''
X Y
0 A 1
2 A 3
'''
df2.groupby(['X']).get_group('B')
'''
X Y
1 B 2
3 B 4
'''
pd.MultiIndex.from_arrays(arrays,names = ) : 多重索引分组
arrays = [['bar', 'bar', 'baz', 'baz', 'foo', 'foo', 'qux', 'qux'],
['one', 'two', 'one', 'two', 'one', 'two', 'one', 'two']]
index = pd.MultiIndex.from_arrays(arrays,names = ['first','second']) # 多重索引
s = pd.Series(np.random.randn(8),index = index)
'''
first second
bar one -0.877562
two -1.296007
baz one 1.026419
two 0.445126
foo one 0.044509
two 0.271037
qux one -1.686649
two 0.914649
dtype: float64
'''
#level =0 按照一级标签进行分组
grouped = s.groupby(level =0)
grouped.sum()
'''
first
bar -2.173569
baz 1.471545
foo 0.315545
qux -0.772001
dtype: float64
'''
#level =1 按照二级标签进行分组
grouped = s.groupby(level = 1)
grouped.sum()
'''
second
one -1.493284
two 0.334805
dtype: float64
'''
#level =first 按照一级标签进行分组
grouped = s.groupby(level = 'first')
grouped.sum()
'''
first
bar -2.173569
baz 1.471545
foo 0.315545
qux -0.772001
dtype: float64
'''
普通方法 对已经分好的组进行按组操作
df = pd.read_csv('./data/titanic.csv')
df.groupby('Sex')['Age'].mean() # 分别计算男性和女性的的年龄平均值
aggregate() : 对已经分好的组进行按组单个操作
import numpy as np
df.groupby('key').aggregate(np.mean) # key值相同的数据项以均值合并
'''
df=
A B C D
0 foo one -0.785250 1.010458
1 bar one 2.549941 1.704677
2 foo two -0.255153 -0.603249
3 bar three -0.954625 0.117662
4 foo two -0.548512 0.648127
5 bar two -0.642762 -1.111568
6 foo one 0.870697 0.556371
7 foo three 0.839937 0.798669
'''
# 多重依据分组
grouped = df.groupby(['A','B'])
grouped.aggregate(np.sum)
'''
C D
A B
bar one 2.549941 1.704677
three -0.954625 0.117662
two -0.642762 -1.111568
foo one 0.085447 1.566829
three 0.839937 0.798669
two -0.803665 0.044878
'''
# as_index = False
grouped = df.groupby(['A','B'],as_index = False)
grouped.aggregate(np.sum)
'''
A B C D
0 bar one 2.549941 1.704677
1 bar three -0.954625 0.117662
2 bar two -0.642762 -1.111568
3 foo one 0.085447 1.566829
4 foo three 0.839937 0.798669
5 foo two -0.803665 0.044878
'''
# reset_index()
df.groupby(['A','B']).sum().reset_index()
'''
A B C D
0 bar one 2.549941 1.704677
1 bar three -0.954625 0.117662
2 bar two -0.642762 -1.111568
3 foo one 0.085447 1.566829
4 foo three 0.839937 0.798669
5 foo two -0.803665 0.044878
'''
#组合个数
grouped = df.groupby(['A','B'])
grouped.size()
'''
A B
bar one 1
three 1
two 1
foo one 2
three 1
two 2
dtype: int64
'''
agg() : 对已经分好的组进行按组多个操作
grouped = df.groupby('A')
grouped['C'].agg([np.sum,np.mean,np.std])
'''
sum mean std
A
bar 0.952553 0.317518 1.939613
foo 0.121719 0.024344 0.781542
'''
grouped['C'].agg({'res_sum':np.sum,'res_mean':np.mean,'res_std':np.std})
'''
res_std res_mean res_sum
A
bar 1.939613 0.317518 0.952553
foo 0.781542 0.024344 0.121719
'''
索引
创建数据
s = pd.Series(np.arange(5),index = np.arange(5)[::-1],dtype='int64')
'''
4 0
3 1
2 2
1 3
0 4
dtype: int64
'''
isin : 判断数组中的值是否在列表中
# 返回值是布尔类型
s.isin([1,3,4])
'''
4 False
3 True
2 False
1 True
0 True
dtype: bool
'''
# 通过mask索引
s[s.isin([1,3,4])]
'''
3 1
1 3
0 4
dtype: int64
'''
pd.MultiIndex.from_product : 多重依据分类
s2 = pd.Series(np.arange(6),index = pd.MultiIndex.from_product([[0,1],['a','b','c']]))
'''
0 a 0
b 1
c 2
1 a 3
b 4
c 5
dtype: int32
'''
iloc: 通过位置索引
s2.iloc[s2.index.isin([(1,'a'),(0,'b')])]
'''
0 b 1
1 a 3
dtype: int32
'''
其他方法索引
s
'''
4 0
3 1
2 2
1 3
0 4
dtype: int64
'''
s[s>2]
'''
1 3
0 4
dtype: int64
'''
df.select : 传入lambda 函数进行索引
dates = pd.date_range('20171124',periods=8)
df = pd.DataFrame(np.random.randn(8,4),index=dates,columns=['A','B','C','D'])
'''
A B C D
2017-11-24 -1.690231 -0.338101 -1.071022 1.084637
2017-11-25 1.292291 0.449885 0.468264 0.637102
2017-11-26 0.602494 -0.591658 0.301893 -1.050524
2017-11-27 -1.132170 1.310110 0.552812 0.370947
2017-11-28 -0.113234 -0.859983 0.381977 0.371730
2017-11-29 0.616029 0.209225 1.879964 0.179152
2017-11-30 0.554969 -0.656240 2.449274 -0.302113
2017-12-01 0.700342 1.068990 -0.572698 0.577581
'''
df.select(lambda x:x=='A',axis='columns')
'''
A
2017-11-24 -1.690231
2017-11-25 1.292291
2017-11-26 0.602494
2017-11-27 -1.132170
2017-11-28 -0.113234
2017-11-29 0.616029
2017-11-30 0.554969
2017-12-01 0.700342
'''
df.where() : 通过设置条件进行索引, 并进行相关操作
df.where(df < 0) # 小于0的位置被置为NaN
df.where(df < 0,-df) # 小于0的值被置为相反数
df.query(): 通过设置条件进行索引
df = pd.DataFrame(np.random.rand(10,3),columns = list('abc'))
'''
a b c
0 0.760971 0.910097 0.480540
1 0.242792 0.778342 0.852441
2 0.147953 0.449719 0.539780
3 0.519164 0.936192 0.402399
4 0.365343 0.148621 0.176917
5 0.837852 0.283028 0.527734
6 0.729312 0.066871 0.747968
7 0.502851 0.462246 0.116735
8 0.472404 0.517753 0.945877
9 0.962282 0.300276 0.258252
'''
df.query('(a<b)') #列出a<d的数据项
df.query('(a<b) & (b<c)')
serise
创
pd.Series(data = ,index = )
data = [10,11,12]
index = ['a','b','c']
s = pd.Series(data = data,index = index)
'''
a 10
b 11
c 12
dtype: int64
'''
查
# 用[]进行切片和索引
s[0] #10
s[0:2]
'''
a 10
b 11
dtype: int64
'''
# 用mask进行索引
mask = [True,False,True]
s[mask]
'''
a 10
c 12
dtype: int64
'''
# 用loc和iloc 进行索引
s.loc['b'] #11
s.iloc[1] # 11
改
# 直接通过索引[] 赋值修改
s1 = s.copy()
s1['a'] = 100
'''
a 100
b 11
c 12
dtype: int64
'''
# 通过replace 进行修改
s1.replace(to_replace = 100,value = 101,inplace = True)
'''
a 101
b 11
c 12
dtype: int64
'''
# 直接赋值对索引进行修改
s1.index # Index(['a', 'b', 'c'], dtype='object')
s1.index = ['a','b','d']
'''
a 101
b 11
d 12
dtype: int64
'''
# 通过rename进行修改
s1.rename(index = {'a':'A'},inplace = True)
'''
A 101
b 11
d 12
dtype: int64
'''
增
s.append() : 对两个serise进行合并
data = [100,110]
index = ['h','k']
s2 = pd.Series(data = data,index = index)
'''
h 100
k 110
dtype: int64
'''
s3 = s1.append(s2)
s3['j'] = 500
'''
A 101
b 11
d 12
j 500
h 100
k 110
dtype: int64
'''
s1.append(s2,ignore_index = True)
'''
0 101
1 11
2 12
3 500
4 100
5 110
dtype: int64
'''
删
用del 或者drop 进行删除操作
s1
'''
A 101
b 11
d 12
j 500
dtype: int64
'''
del s1['A']
'''
b 11
d 12
j 500
dtype: int64
'''
s1.drop(['b','d'],inplace = True)
'''
j 500
dtype: int64
'''
合并
merge
import pandas as pd
left = pd.DataFrame({'key': ['K0', 'K1', 'K2', 'K3'],
'A': ['A0', 'A1', 'A2', 'A3'],
'B': ['B0', 'B1', 'B2', 'B3']})
'''
A B key
0 A0 B0 K0
1 A1 B1 K1
2 A2 B2 K2
3 A3 B3 K3
'''
right = pd.DataFrame({'key': ['K0', 'K1', 'K2', 'K3'],
'C': ['C0', 'C1', 'C2', 'C3'],
'D': ['D0', 'D1', 'D2', 'D3']})
'''
C D key
0 C0 D0 K0
1 C1 D1 K1
2 C2 D2 K2
3 C3 D3 K3
'''
res = pd.merge(left, right)
'''
A B key C D
0 A0 B0 K0 C0 D0
1 A1 B1 K1 C1 D1
2 A2 B2 K2 C2 D2
3 A3 B3 K3 C3 D3
'''
res = pd.merge(left, right, on = 'key')
'''
A B key C D
0 A0 B0 K0 C0 D0
1 A1 B1 K1 C1 D1
2 A2 B2 K2 C2 D2
3 A3 B3 K3 C3 D3
'''
left = pd.DataFrame({'key1': ['K0', 'K1', 'K2', 'K3'],
'key2': ['K0', 'K1', 'K2', 'K3'],
'A': ['A0', 'A1', 'A2', 'A3'],
'B': ['B0', 'B1', 'B2', 'B3']})
'''
A B key1 key2
0 A0 B0 K0 K0
1 A1 B1 K1 K1
2 A2 B2 K2 K2
3 A3 B3 K3 K3
'''
right = pd.DataFrame({'key1': ['K0', 'K1', 'K2', 'K3'],
'key2': ['K0', 'K1', 'K2', 'K4'],
'C': ['C0', 'C1', 'C2', 'C3'],
'D': ['D0', 'D1', 'D2', 'D3']})
'''
C D key1 key2
0 C0 D0 K0 K0
1 C1 D1 K1 K1
2 C2 D2 K2 K2
3 C3 D3 K3 K4
'''
#默认交集
res = pd.merge(left, right, on = ['key1', 'key2'])
'''
A B key1 key2 C D
0 A0 B0 K0 K0 C0 D0
1 A1 B1 K1 K1 C1 D1
2 A2 B2 K2 K2 C2 D2
'''
# how = 'outer'并集
res = pd.merge(left, right, on = ['key1', 'key2'], how = 'outer')
'''
A B key1 key2 C D
0 A0 B0 K0 K0 C0 D0
1 A1 B1 K1 K1 C1 D1
2 A2 B2 K2 K2 C2 D2
3 A3 B3 K3 K3 NaN NaN
4 NaN NaN K3 K4 C3 D3
'''
# indicator = True 添加提示信息
res = pd.merge(left, right, on = ['key1', 'key2'], how = 'outer', indicator = True)
'''
A B key1 key2 C D _merge
0 A0 B0 K0 K0 C0 D0 both
1 A1 B1 K1 K1 C1 D1 both
2 A2 B2 K2 K2 C2 D2 both
3 A3 B3 K3 K3 NaN NaN left_only
4 NaN NaN K3 K4 C3 D3 right_only
'''
# 按照'left'的进行合并
res = pd.merge(left, right, how = 'left')
'''
A B key1 key2 C D
0 A0 B0 K0 K0 C0 D0
1 A1 B1 K1 K1 C1 D1
2 A2 B2 K2 K2 C2 D2
3 A3 B3 K3 K3 NaN NaN
'''
# 按照'left'的进行合并
res = pd.merge(left, right, how = 'right')
'''
A B key1 key2 C D
0 A0 B0 K0 K0 C0 D0
1 A1 B1 K1 K1 C1 D1
2 A2 B2 K2 K2 C2 D2
3 NaN NaN K3 K4 C3 D3
'''
join
left = pd.DataFrame({'A': ['A0', 'A1', 'A2', 'A3'],
'B': ['B0', 'B1', 'B2', 'B3'],
'key': ['K0', 'K1', 'K0', 'K1']})
'''
A B key
0 A0 B0 K0
1 A1 B1 K1
2 A2 B2 K0
3 A3 B3 K1
'''
right = pd.DataFrame({'C': ['C0', 'C1'],
'D': ['D0', 'D1']},
index=['K0', 'K1'])
'''
C D
K0 C0 D0
K1 C1 D1
'''
result = left.join(right, on='key')
'''
A B key C D
0 A0 B0 K0 C0 D0
1 A1 B1 K1 C1 D1
2 A2 B2 K0 C0 D0
3 A3 B3 K1 C1 D1
'''
显示格式设置
pd.set_option : 控制显示的格式，如最大行数，列数，宽度，小数位数
# display.max_rows 显示的最大行数
pd.get_option('display.max_rows') #60
pd.set_option('display.max_rows',6)
pd.Series(index = range(0,100))
'''
0 NaN
1 NaN
2 NaN
..
97 NaN
98 NaN
99 NaN
Length: 100, dtype: float64
'''
#display.max_columns 显示的最大列数
pd.get_option('display.max_columns') #20
pd.set_option('display.max_columns',30)
pd.DataFrame(columns = range(0,30))
# display.max_colwidth 显示的最大宽度
pd.get_option('display.max_colwidth') #50
pd.set_option('display.max_colwidth',100)
pd.Series(index = ['A'],data=['t'*70])
# display.precision 设置显示的小数位数
pd.get_option('display.precision') #6
pd.Series(data = [1.23456789236546])
'''
0 1.234568
dtype: float64
'''
pd.set_option('display.precision',5)
pd.Series(data = [1.23456789236546])
'''
0 1.23457
dtype: float64
'''
特殊表格
数据透视表
数据透视表用来做数据透视，可以通过一个或多个键分组聚合DataFrame中的数据，通过aggfunc参数决定聚合类型，是groupby的高级功能。
pd.pivot_table（data, values = None，index = None，columns = None，aggfunc =‘mean’，fill_value = None，margin = False，dropna = True，margins_name =‘All’ ）
data：DataFrame对象
values：要聚合的列或列的列表
index：数据透视表的index，从原数据的列中筛选
columns：数据透视表的columns，从原数据的列中筛选
aggfunc：用于聚合的函数，默认为numpy.mean，支持numpy计算方法
fill_value: 用于替换缺失值的值
margin: 添加所有行/列
dropna: 不包括条目为 NaN的列，默认为True
margin_name: 当margin为True时，将包含总计的行/列的名称
import pandas as pd
example = pd.DataFrame({'Month': ["January", "January", "January", "January",
"February", "February", "February", "February",
"March", "March", "March", "March"],
'Category': ["Transportation", "Grocery", "Household", "Entertainment",
"Transportation", "Grocery", "Household", "Entertainment",
"Transportation", "Grocery", "Household", "Entertainment"],
'Amount': [74., 235., 175., 100., 115., 240., 225., 125., 90., 260., 200., 120.]})
pivot(index =,columns=,values =) : 返回数据透视表
example_pivot = example.pivot(index = 'Category',columns= 'Month',values = 'Amount')
'''
Month February January March
Category
Entertainment 125.0 100.0 120.0
Grocery 240.0 235.0 260.0
Household 225.0 175.0 200.0
Transportation 115.0 74.0 90.0
'''
sum :求和
# axis = 1 按照行求和
example_pivot.sum(axis = 1)
'''
Category
Entertainment 345.0
Grocery 735.0
Household 600.0
Transportation 279.0
dtype: float64
'''
# axis = 0 按照列求和
example_pivot.sum(axis = 0)
'''
Month
February 705.0
January 584.0
March 670.0
dtype: float64
'''
pivot_table: 对数据透视表进行相关操作
# 默认值求平均
df = pd.read_csv('./data/titanic.csv')
df.pivot_table(index = 'Sex',columns='Pclass',values='Fare')
'''
Pclass 1 2 3
Sex
female 106.125798 21.970121 16.118810
male 67.226127 19.741782 12.661633
'''
# aggfunc='max' 求最大值
df.pivot_table(index = 'Sex',columns='Pclass',values='Fare',aggfunc='max')
'''
最大值
Pclass 1 2 3
Sex
female 512.3292 65.0 69.55
male 512.3292 73.5 69.55
'''
# aggfunc='count' 统计个数
df.pivot_table(index = 'Sex',columns='Pclass',values='Fare',aggfunc='count')
'''
统计数目
Pclass 1 2 3
Sex
female 94 76 144
male 122 108 347
'''
交叉表
默认情况下，crosstab是用来计算因子的频率表。
pd.crosstab()参数：
pd.crosstab(index, columns, values=None, rownames=None, colnames=None, aggfunc=None, margins=False, dropna=True, normalize=False)
index：要在行中分组的值
columns：要在列中分组的值
values：根据因子聚合的值数组，需指定aggfunc
aggfunc：如指定，还需指定value
normalize：将所有值除以值的总和进行归一化 ，为True时候显示百分比
pd.crosstab() :
# 默认计算因子的频率表。
pd.crosstab(index = df['Sex'],columns = df['Pclass'])
'''
Pclass 1 2 3
Sex
female 94 76 144
male 122 108 347
'''
# aggfunc='mean' 求平均值
df.pivot_table(index = 'Pclass',columns='Sex',values='Survived',aggfunc='mean')
'''
Sex female male
Pclass
1 0.968085 0.368852
2 0.921053 0.157407
3 0.500000 0.135447
'''
# 设置条件进行筛选
df['Underaged'] = df['Age'] <= 18
df.pivot_table(index = 'Underaged',columns='Sex',values='Survived',aggfunc='mean')
'''
Sex female male
Underaged
False 0.760163 0.167984
True 0.676471 0.338028
'''
时间操作
时间转换
datetime.datetime(year=,month=,day=,hour=,minute=)
%matplotlib notebook
import datetime
dt = datetime.datetime(year=2017,month=11,day=24,hour=10,minute=30)
'''
datetime.datetime(2017, 11, 24, 10, 30)
'''
pd.Timestamp ： 设置时间
pd.Timedelta : 时间增量
pd.to_datetime : 时间格式转换
import pandas as pd
ts = pd.Timestamp('2017-11-24')
ts.month
ts.day
ts + pd.Timedelta('5 days')# Timestamp('2017-11-29 00:00:00')
pd.to_datetime('2017-11-24') #Timestamp('2017-11-24 00:00:00')
pd.to_datetime('24/11/2017')# Timestamp('2017-11-24 00:00:00')
时间序列
to_datetime() : 传入的参数是时间序列
s = pd.Series(['2017-11-24 00:00:00','2017-11-25 00:00:00','2017-11-26 00:00:00'])
'''
0 2017-11-24 00:00:00
1 2017-11-25 00:00:00
2 2017-11-26 00:00:00
dtype: object
'''
ts = pd.to_datetime(s)
'''
0 2017-11-24
1 2017-11-25
2 2017-11-26
dtype: datetime64[ns]
'''
dt.hour : 通过小时进行序列的索引
dt.weekday : 通过星期进行序列的索引
ts.dt.hour
'''
0 0
1 0
2 0
dtype: int64
'''
ts.dt.weekday
'''
0 4
1 5
2 6
dtype: int64
'''
pd.date_range(start=,periods = ,freq = ) :生成时间序列
pd.Series(pd.date_range(start='2017-11-24',periods = 10,freq = '12H'))
'''
0 2017-11-24 00:00:00
1 2017-11-24 12:00:00
2 2017-11-25 00:00:00
3 2017-11-25 12:00:00
4 2017-11-26 00:00:00
5 2017-11-26 12:00:00
6 2017-11-27 00:00:00
7 2017-11-27 12:00:00
8 2017-11-28 00:00:00
9 2017-11-28 12:00:00
dtype: datetime64[ns]
'''
时间序列的截取的方式
data = pd.read_csv('./data/flowdata.csv')
data.head()
'''
Time L06_347 LS06_347 LS06_348
0 2009-01-01 00:00:00 0.137417 0.097500 0.016833
1 2009-01-01 03:00:00 0.131250 0.088833 0.016417
2 2009-01-01 06:00:00 0.113500 0.091250 0.016750
3 2009-01-01 09:00:00 0.135750 0.091500 0.016250
4 2009-01-01 12:00:00 0.140917 0.096167 0.017000
'''
data['Time'] = pd.to_datetime(data['Time'])
data = data.set_index('Time')
'''
L06_347 LS06_347 LS06_348
Time
2009-01-01 00:00:00 0.137417 0.097500 0.016833
2009-01-01 03:00:00 0.131250 0.088833 0.016417
2009-01-01 06:00:00 0.113500 0.091250 0.016750
2009-01-01 09:00:00 0.135750 0.091500 0.016250
...
'''
# 时间序列截取方式
data[pd.Timestamp('2012-01-01 09:00'):pd.Timestamp('2012-01-01 19:00')] #截取一段数据
data[('2012-01-01 09:00'):('2012-01-01 19:00')]
data.tail(10) #截取最后10个数据
data[data.index.month == 1]
data[(data.index.hour > 8) & (data.index.hour <12)]
data.between_time('08:00','12:00')
时间抽样
data.resample : 对时间进行抽样
data.resample('D').mean().head() #间隔一天取平均值
data.resample('D').max().head()
data.resample('3D').mean().head()
data.resample('M').mean().head()
data.resample('M').mean().plot()
排序
sort_values
import pandas as pd
data = pd.DataFrame({'group':['a','a','a','b','b','b','c','c','c'],
'data':[4,3,2,1,12,3,4,5,7]})
'''
data group
0 4 a
1 3 a
2 2 a
3 1 b
4 12 b
5 3 b
6 4 c
7 5 c
8 7 c
'''
#按照group降序的基础上，data升序
data.sort_values(by=['group','data'],ascending = [False,True],inplace=True)
'''
data group
6 4 c
7 5 c
8 7 c
3 1 b
5 3 b
4 12 b
2 2 a
1 3 a
0 4 a
'''
data = pd.DataFrame({'k1':['one']*3+['two']*4,
'k2':[3,2,1,3,3,4,4]})
'''
k1 k2
0 one 3
1 one 2
2 one 1
3 two 3
4 two 3
5 two 4
6 two 4
'''
# 根据关键字进行排序
data.sort_values(by='k2')
'''
2 one 1
1 one 2
0 one 3
3 two 3
4 two 3
5 two 4
6 two 4
'''
去重
drop_duplicates() : 去重操作
# k1, k2 的值都相等时才除去
data.drop_duplicates()
'''
k1 k2
0 one 3
1 one 2
2 one 1
3 two 3
5 two 4
'''
# 只针对k1去重
data.drop_duplicates(subset='k1')
'''
k1 k2
0 one 3
3 two 3
'''
映射
apply
data = pd.DataFrame({'food':['A1','A2','B1','B2','B3','C1','C2'],'data':[1,2,3,4,5,6,7]})
'''
data food
0 1 A1
1 2 A2
2 3 B1
3 4 B2
4 5 B3
5 6 C1
6 7 C2
'''
def food_map(series):
if series['food'] == 'A1':
return 'A'
elif series['food'] == 'A2':
return 'A'
elif series['food'] == 'B1':
return 'B'
elif series['food'] == 'B2':
return 'B'
elif series['food'] == 'B3':
return 'B'
elif series['food'] == 'C1':
return 'C'
elif series['food'] == 'C2':
return 'C'
data['food_map'] = data.apply(food_map,axis = 'columns')
'''
data food food_map
0 1 A1 A
1 2 A2 A
2 3 B1 B
3 4 B2 B
4 5 B3 B
5 6 C1 C
6 7 C2 C
'''
map
food2Upper = {
'A1':'A',
'A2':'A',
'B1':'B',
'B2':'B',
'B3':'B',
'C1':'C',
'C2':'C'
}
data['upper'] = data['food'].map(food2Upper)
'''
data food food_map upper
0 1 A1 A A
1 2 A2 A A
2 3 B1 B B
3 4 B2 B B
4 5 B3 B B
5 6 C1 C C
6 7 C2 C C
'''
assign : 添加一列
import numpy as np
df = pd.DataFrame({'data1':np.random.randn(5),
'data2':np.random.randn(5)})
df2 = df.assign(ration = df['data1']/df['data2'])
'''
data1 data2 ration
0 -1.069925 -0.186540 5.735617
1 0.636127 0.020425 31.143814
2 0.366197 -0.102836 -3.560992
3 -0.975327 0.451201 -2.161624
4 -1.562407 -2.436845 0.641160
'''
drop : 去除一行
df2.drop('ration',axis='columns',inplace=True)
'''
data1 data2
0 -1.069925 -0.186540
1 0.636127 0.020425
2 0.366197 -0.102836
3 -0.975327 0.451201
4 -1.562407 -2.436845
'''
分类
pd.cut: 返回每个元素对应的区间
ages = [15,18,20,21,22,34,41,52,63,79]
bins = [10,40,80]
bins_res = pd.cut(ages,bins)
'''
[(10, 40], (10, 40], (10, 40], (10, 40], (10, 40], (10, 40], (40, 80], (40, 80], (40, 80], (40, 80]]
'''
lables : 返回每个元素对应区间的标号
bins_res.labels
'''
array([0, 0, 0, 0, 0, 0, 1, 1, 1, 1], dtype=int8)
'''
value_counts: 返回每个区间对应的个数
pd.value_counts(bins_res)
'''
(10, 40] 6
(40, 80] 4
dtype: int64
'''
pd.cut(ages,[10,30,50,80])
'''
[(10, 30], (10, 30], (10, 30], (10, 30], (10, 30], (30, 50], (30, 50], (50, 80], (50, 80], (50, 80]]
'''
group_names = ['Yonth','Mille','Old']
pd.value_counts(pd.cut(ages,[10,20,50,80],labels=group_names))
'''
Mille 4
Old 3
Yonth 3
dtype: int64
'''
判空
nan : 表示数据为空
df = pd.DataFrame([range(3),[0, np.nan,0],[0,0,np.nan],range(3)])
'''
0 1 2
0 0 1.0 2.0
1 0 NaN 0.0
2 0 0.0 NaN
3 0 1.0 2.0
'''
isnull() : 返回值为布尔类型，若为nan则返回true, 否则返回false
df.isnull()
'''
0 1 2
0 False False False
1 False True False
2 False False True
3 False False False
'''
isnull().any() : 返回值为布尔类型，若该条数据全为nan则返回true, 否则返回false
# 默认判断每行数据
df.isnull().any()
'''
0 False
1 True
2 True
dtype: bool
'''
# axis = 1 判断每一列的数据
df.isnull().any(axis = 1)
'''
0 False
1 True
2 True
3 False
dtype: bool
'''
df.fillna(n) : 将值为nan的数据用n填充
df.fillna(5)
'''
0 1 2
0 0 1.0 2.0
1 0 5.0 0.0
2 0 0.0 5.0
3 0 1.0 2.0
'''
df[df.isnull().any(axis = 1)]
'''
0 1 2
1 0 NaN 0.0
2 0 0.0 NaN
'''
字符串
import pandas as pd
import numpy as np
s = pd.Series(['A','b','B','gaer','AGER',np.nan])
'''
0 A
1 b
2 B
3 gaer
4 AGER
5 NaN
dtype: object
'''
大小写转换
str.lower(), str.upper() : 转换为小写，大写字母格式
s.str.lower()
'''
0 a
1 b
2 b
3 gaer
4 ager
5 NaN
dtype: object
'''
s.str.upper()
'''
0 A
1 B
2 B
3 GAER
4 AGER
5 NaN
dtype: object
'''
长度
str.len(): 求字符串的长度
s.str.len()
'''
0 1.0
1 1.0
2 1.0
3 4.0
4 4.0
5 NaN
dtype: float64
'''
去除空格
str.strip(): 去除字符串左右两边的空格。
str.lstrip(): 去除字符串左边的空格。
str.rstrip(): 去除字符串右边的空格。
index = pd.Index([' tang',' yu ','di'])
Index([' tang', ' yu ', 'di'], dtype='object')
index.str.strip()
'''
Index(['tang', 'yu', 'di'], dtype='object')
'''
index.str.lstrip()
'''
Index(['tang', 'yu ', 'di'], dtype='object')
'''
index.str.rstrip()
'''
Index([' tang', ' yu', 'di'], dtype='object')
'''
替换
str.replace() : 将字符串中的某一个字符用另外一个字符填充
df = pd.DataFrame(np.random.randn(3,2),columns = ['A a','B b'],index = range(3))
'''
A a B b
0 -1.392628 1.020082
1 0.866707 0.654731
2 -0.320871 1.360513
'''
df.columns = df.columns.str.replace(' ','_')
'''
A_a B_b
0 -1.392628 1.020082
1 0.866707 0.654731
2 -0.320871 1.360513
'''
分割
str.split(): 按照分隔符对字符串进行分割
s = pd.Series(['a_b_C','c_d_e','f_g_h'])
'''
0 a_b_C
1 c_d_e
2 f_g_h
dtype: object
'''
# 将字符串按照分隔符分割为list
s.str.split('_')
'''
0 [a, b, C]
1 [c, d, e]
2 [f, g, h]
dtype: object
'''
# expand = True 将字符串按照分隔符分割为多个columns
s.str.split('_',expand = True)
'''
0 1 2
0 a b C
1 c d e
2 f g h
'''
# 指定分割的次数
s.str.split('_',expand = True,n=1)
'''
0 1
0 a b_C
1 c d_e
2 f g_h
'''
str.contains() : 检查字符串中是否包含某个子串，返回值为布尔类型
s = pd.Series(['A','Aas','Afgew','Ager','Agre','Ager'])
'''
0 A
1 Aas
2 Afgew
3 Ager
4 Agre
5 Ager
dtype: object
'''
s.str.contains('Ag')
'''
0 False
1 False
2 False
3 True
4 True
5 True
dtype: bool
'''
str.get_dummies(sep = ) : 按照分割符sep 将字符串分割成多个columns
s = pd.Series(['a','a|b','a|c'])
'''
0 a
1 a|b
2 a|c
dtype: object
'''
s.str.get_dummies(sep = '|')
'''
a b c
0 1 0 0
1 1 1 0
2 1 0 1
'''
绘图
折线图
单条折线图
%matplotlib inline
import pandas as pd
import numpy as np
s = pd.Series(np.random.randn(10),index = np.arange(0,100,10))
s.plot()
多条折线图
df = pd.DataFrame(np.random.randn(10, 4).cumsum(0),
index = np.arange(0, 100, 10),
columns = ['A', 'B', 'C', 'D'])
df.plot()
柱状图
水平和竖直柱状图
import matplotlib.pyplot as plt
fig,axes = plt.subplots(2,1) #子图
data = pd.Series(np.random.rand(16),index=list('abcdefghijklmnop'))
data.plot(ax = axes[0],kind='bar') #竖直柱状图
data.plot(ax = axes[1],kind='barh') #水平柱状图
多个指标柱状图
df = pd.DataFrame(np.random.rand(6, 4),
index = ['one', 'two', 'three', 'four', 'five', 'six'],
columns = pd.Index(['A', 'B', 'C', 'D'], name = 'Genus'))
df.plot(kind='bar')
tips = pd.read_csv('tips.csv')
tips.total_bill.plot(kind='hist',bins=50)
'''
total_bill tip sex smoker day time size
0 16.99 1.01 Female No Sun Dinner 2
1 10.34 1.66 Male No Sun Dinner 3
2 21.01 3.50 Male No Sun Dinner 3
3 23.68 3.31 Male No Sun Dinner 2
4 24.59 3.61 Female No Sun Dinner 4
'''
直方图
tips = pd.read_csv('tips.csv')
tips.head()
tips.total_bill.plot(kind='hist',bins=50)
散点图
macro = pd.read_csv('macrodata.csv')
macro.head()
data = macro[['quarter','realgdp','realcons']]
data.plot.scatter('quarter','realgdp')
pd.scatter_matrix(data,color='g',alpha=0.3)
大数据处理
向下类型转换
import pandas as pd
gl = pd.read_csv('game_logs.csv')
gl.head()
gl.info(memory_usage='deep') # 占用内存大小
for dtype in ['float64','object','int64']: #查看每种类型平均占用内存大小
selected_dtype = gl.select_dtypes(include=[dtype])
mean_usage_b = selected_dtype.memory_usage(deep=True).mean()
mean_usage_mb = mean_usage_b / 1024 ** 2
print("Average memory usage for {} columns: {:03.2f} MB".format(dtype,mean_usage_mb))
import numpy as np
int_types = ["uint8", "int8", "int16","int32","int64"]
for it in int_types:
print(np.iinfo(it)) #查看每种类型表示数的范围大小
def mem_usage(pandas_obj): #计算内存占用
if isinstance(pandas_obj,pd.DataFrame):
usage_b = pandas_obj.memory_usage(deep=True).sum()
else: # we assume if not a df it's a series
usage_b = pandas_obj.memory_usage(deep=True)
usage_mb = usage_b / 1024 ** 2 # convert bytes to megabytes
return "{:03.2f} MB".format(usage_mb)
gl_int = gl.select_dtypes(include=['int64'])
converted_int = gl_int.apply(pd.to_numeric,downcast='unsigned') #转换数据类型
print(mem_usage(gl_int)) # 7.87 MB
print(mem_usage(converted_int))# 1.48 MB
去重
gl_obj = gl.select_dtypes(include = ['object']).copy()
gl_obj.describe() #重复值过多
dow = gl_obj.day_of_week
dow.head()
dow_cat = dow.astype('category')
dow_cat.head()
'''
0 Thu
1 Fri
2 Sat
3 Mon
4 Tue
Name: day_of_week, dtype: category
Categories (7, object): [Fri, Mon, Sat, Sun, Thu, Tue, Wed]
'''
dow_cat.head(10).cat.codes#object类型转换为category
'''
0 4
1 0
2 2
3 1
4 5
5 4
6 2
7 2
8 1
9 5
dtype: int8
'''
时间压缩
date = optimized_gl.date
date[:5]
'''
0 18710504
1 18710505
2 18710506
3 18710508
4 18710509
Name: date, dtype: uint32
'''
print (mem_usage(date)) #0.66MB
optimized_gl['date'] = pd.to_datetime(date,format='%Y%m%d')
print (mem_usage(optimized_gl['date'])) #1.31 MB
optimized_gl['date'][:5]
'''
0 1871-05-04
1 1871-05-05
2 1871-05-06
3 1871-05-08
4 1871-05-09
Name: date, dtype: datetime64[ns]
'''
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
