# 在运算中自动对齐不同索引的数据,当索引没有对应的值时,可能出现缺失数据显示NaN(not a number)的情况# 使得两个Series进行相加
s1 = Series([1,2,3],index=['a','b','c'])
s2 = Series([1,2,3],index=['a','d','c'])
s = s1 + s2
s
输出:
a 2.0
b NaN
c 6.0
d NaN
dtype: float64
# 可以使用pd.isnull(),pd.notnull()或s.isnull(),notnull()函数检测缺失数据
s[[0,1]]
输出:
a 2.0
b NaN
dtype: float64
s[['a','b']]
输出:
a 2.0
b NaN
dtype: float64
s[[True,False,True,False]]
输出:
a 2.0
c 6.0
dtype: float64
s.isnull()
输出:
a False
b True
c False
d True
dtype:bool
s.notnull()
输出:
a True
b False
c True
d False
dtype:bool# 打印出不为空值的数据
s[s.notnull()]
输出:
a 2.0
c 6.0
dtype: float64
# 使用列表创建
df = DataFrame(data=[['a',1],['y',2],['z',3]],columns=['k','v'])
df
输出:
k v
0 a 11 y 22 z 3# 使用ndarray创建DataFrame
np.random.seed(10)
df = DataFrame(data=np.random.randint(0,100,size=(3,4)),index=['a','b','c'],columns=['A','B','C','D'])
df
输出:
A B C D
a 9156428
b 8993298
c 7304036# DataFrame以字典的键作为每一【列】的名称,以字典的值(一个数组)作为每一列。
data ={'k':['a','b','c'],'v':[1,2,3]}
df = pd.DataFrame(data)
df
输出:
k v
0 a 11 y 22 z 3
df1 = DataFrame(data=np.random.randint(0,100,size=(3,4)))
df2 = DataFrame(data=np.random.randint(0,100,size=(3,3)))
df1
输出:
0123
a 8639429.0
b 97813784.0
c 84787611.0
df2
输出:
012
a 831761
d 94953
c 344684# 外连接:补NaN(默认模式)
pd.concat((df1,df2))
输出:
0123
a 8639429.0
b 97813784.0
c 84787611.0
a 831761 NaN
d 94953 NaN
c 344684 NaN
# 内连接:只连接匹配的项
df1 = DataFrame(data=np.random.randint(0,100,size=(3,4)),index=['a','b','c'])
df2 = DataFrame(data=np.random.randint(0,100,size=(3,3)),index=['a','d','c'])
pd.concat((df1,df2),axis=0,join='inner')
输出:
012
a 86394
b 978137
c 847876
a 831761
d 94953
c 344684
# 一对一匹配合并
df1 = DataFrame({'employee':['Bob','Jake','Lisa'],'group':['Accounting','Engineering','Engineering'],})
df2 = DataFrame({'employee':['Lisa','Bob','Jake'],'hire_date':[2004,2008,2012],})
df1
输出:
employee group
0 Bob Accounting
1 Jake Engineering
2 Lisa Engineering
df2
输出:
employee hire_date
0 Lisa 20041 Bob 20082 Jake 2012
pd.merge(df1,df2)
输出:
employee group hire_date
0 Bob Accounting 20081 Jake Engineering 20122 Lisa Engineering 2004# 多对一不合并:指左表单行匹配右表多行数据
df3 = DataFrame({'employee':['Lisa','Jake'],'group':['Accounting','Engineering'],'hire_date':[2004,2016]})
df4 = DataFrame({'group':['Accounting','Engineering','Engineering'],'supervisor':['Carly','Guido','Steve']})
df3
输出:
employee group hire_date
0 Lisa Accounting 20041 Jake Engineering 2016
df4
输出:
group supervisor
0 Accounting Carly
1 Engineering Guido
2 Engineering Steve
pd.merge(df3,df4)
输出:
employee group hire_date supervisor
0 Lisa Accounting 2004 Carly
1 Jake Engineering 2016 Guido
2 Jake Engineering 2016 Steve
# 多对多匹配:指左表多行匹配右表多行数据
df1
输出:
employee group
0 Bob Accounting
1 Jake Engineering
2 Lisa Engineering
df4
输出:
group supervisor
0 Accounting Carly
1 Engineering Guido
2 Engineering Steve
pd.merge(df1,df4)
employee group supervisor
0 Bob Accounting Carly
1 Jake Engineering Guido
2 Jake Engineering Steve
3 Lisa Engineering Guido
4 Lisa Engineering Steve
# key的规范化
df1 = DataFrame({'employee':['Jack',"Summer","Steve"],'group':['Accounting','Finance','Marketing']})
df2 = DataFrame({'employee':['Jack','Bob',"Jake"],'hire_date':[2003,2009,2012],'group':['Accounting','sell','ceo']})
df1
输出:
employee group
0 Jack Accounting
1 Summer Finance
2 Steve Marketing
df2
输出:
employee hire_date group
0 Jack 2003 Accounting
1 Bob 2009 sell
2 Jake 2012 ceo
# 使用key的交集
pd.merge(df1,df2,how='inner')
pd.merge(df1,df2)
输出:
employee group hire_date
0 Jack Accounting 2003# 联合key
pd.merge(df1,df2,how='outer')
输出:
employee group hire_date
0 Jack Accounting 2003.01 Summer Finance NaN
2 Steve Marketing NaN
3 Bob sell 2009.04 Jake ceo 2012.0# 当有多个列名称相同时,需要使用on=来指定哪一个列作为key,配合suffixes指定冲突列名
pd.merge(df1,df2,on='group',how='outer')
输出:
employee_x group employee_y hire_date
0 Jack Accounting Jack 2003.01 Summer Finance NaN NaN
2 Steve Marketing NaN NaN
3 NaN sell Bob 2009.04 NaN ceo Jake 2012.0# 当两张表没有可进行连接的列时,可使用left_on和right_on手动指定merge中左右两边的哪一列列作为连接的列
df1 = DataFrame({'employee':['Bobs','Linda','Bill'],'group':['Accounting','Product','Marketing'],'hire_date':[1998,2017,2018]})
df2 = DataFrame({'name':['Lisa','Bobs','Bill'],'hire_dates':[1998,2016,2007]})
df1
输出:
employee group hire_date
0 Bobs Accounting 19981 Linda Product 20172 Bill Marketing 2018
df2
输出:
name hire_dates
0 Lisa 19981 Bobs 20162 Bill 2007
pd.merge(df1,df2,left_on='employee',right_on='name',how='outer')
输出:
employee group hire_date name hire_dates
0 Bobs Accounting 1998.0 Bobs 2016.01 Linda Product 2017.0 NaN NaN
2 Bill Marketing 2018.0 Bill 2007.03 NaN NaN NaN Lisa 1998.0
pd.merge(df1,df2,left_index=True,right_index=True,how='outer')
输出:
employee group hire_date name hire_dates
0 Bobs Accounting 1998 Lisa 19981 Linda Product 2017 Bobs 20162 Bill Marketing 2018 Bill 2007
3.3 使用tushare包获取某股票的历史行情数据。
import tushare as ts
ts.set_token('your token here')
pro = ts.pro_api()
df = ts.get_k_data('600519',start='1900-01-01')
df.to_csv('./maotai.csv')
# 行采样
df.take([1,0,2])
输出:
A B C
10.0215810.4008540.98608100.1586780.8886940.49816320.9748510.9258640.147149# 列采样
df.take([1,0,2],axis=1).head(2)
输出:
B A C
00.8886940.1586780.49816310.4008540.0215810.986081# 随机抽样# 随机列展示,再随机抽样10个数据
df.take(np.random.permutation(3),axis=1).take(np.random.permutation(10),axis=0)
4.6 数据分类处理
4.6.1 数据分组
df = DataFrame({'item':['Apple','Banana','Orange','Banana','Orange','Apple'],'price':[4,3,3,2.5,4,2],'color':['red','yellow','yellow','green','green','green'],'weight':[12,20,50,30,20,44]})
df
输出:
item price color weight
0 Apple 4.0 red 121 Banana 3.0 yellow 202 Orange 3.0 yellow 503 Banana 2.5 green 304 Orange 4.0 green 205 Apple 2.0 green 44# 使用groupby实现分组# 使用groups查看分组情况
df.groupby('item',axis=0)
df.groupby('item',axis=0).groups
输出:
{'Apple':[0,5],'Banana':[1,3],'Orange':[2,4]}
4.6.2 操作分组后的数据
分组后的成员中可以被进行运算的值会进行运算,不能被运算的值不进行运算
# 默认如果不指定列的话,会计算所有列的平均值,此处指定计算price列# 以item中的项目分组计算每个项目价格的平均值
s = df.groupby('item').mean()['price']
s.to_dict()
df['mean_price']= df['item'].map(s.to_dict())
输出:
item price color weight mean_price
0 Apple 4.0 red 123.001 Banana 3.0 yellow 202.752 Orange 3.0 yellow 503.503 Banana 2.5 green 302.754 Orange 4.0 green 203.505 Apple 2.0 green 443.00# 以color中的项目分组计算每个颜色价格的平均值
s = df.groupby('color',axis=0).mean()['price'].to_dict()
df['color_mean_price']= df['color'].map(s)
item price color weight mean_price color_mean_price
0 Apple 4.0 red 123.004.0000001 Banana 3.0 yellow 202.753.0000002 Orange 3.0 yellow 503.503.0000003 Banana 2.5 green 302.752.8333334 Orange 4.0 green 203.502.8333335 Apple 2.0 green 443.002.833333