pandas数据处理--莫烦教程
(其实相当于 字典 或者说表格的操作)
1.pandas基本介绍
import numpy as np #相当于矩阵
import pandas as pd #相当于字典
#生成简单字典
s=pd.Series([1,3,6,np.nan,44,1])
print(s)
'''
0 1.0
1 3.0
2 6.0
3 NaN
4 44.0
5 1.0
dtype: float64
'''
#日期列表
dates=pd.date_range('20200101',periods=6)
print(dates)
'''DatetimeIndex(['2020-01-01', '2020-01-02', '2020-01-03', '2020-01-04',
'2020-01-05', '2020-01-06'],
dtype='datetime64[ns]', freq='D')'''
#第一种方法生成DataFrame
#行列未标识的DataFrame
df1=pd.DataFrame(np.arange(12).reshape(3,4))
print(df1)
#行列标识的DataFrame
df=pd.DataFrame(np.random.randn(6,4),index=dates,columns=['a','b','c','d'])
print(df)#每一列:columns 每一行:index
'''
0 1 2 3
0 0 1 2 3
1 4 5 6 7
2 8 9 10 11
a b c d
2020-01-01 -0.063706 0.078455 -1.533434 0.051476
2020-01-02 1.670585 0.692318 -1.391377 0.912161
2020-01-03 -0.778981 -1.274842 -0.777923 1.479659
2020-01-04 0.115147 -0.641083 -1.303042 0.229925
2020-01-05 1.463493 1.332047 -0.858546 -1.638755
2020-01-06 -0.765973 1.150096 0.201085 2.172077
'''
#第二种方法生成DataFrame
df2=pd.DataFrame({'A':1.,
'B':pd.Timestamp('20200101'),
'C':pd.Series(1,index=list(range(4)),dtype='float32'),
'D':np.array([3]*4,dtype='int32'),
'E':pd.Categorical(["test","train","test","train"]),
'F':'foo'})
print(df2)
'''
A B C D E F
0 1.0 2020-01-01 1.0 3 test foo
1 1.0 2020-01-01 1.0 3 train foo
2 1.0 2020-01-01 1.0 3 test foo
3 1.0 2020-01-01 1.0 3 train foo'''
输出类型 名字 转置 排序
print(df2.dtypes)#查看每一列的类型
'''
A float64
B datetime64[ns]
C float32
D int32
E category
F object
dtype: object'''
print(df2.index)#输出每一行的名字 Int64Index([0, 1, 2, 3], dtype='int64')
print(df2.columns)#输出每一列的名字 Index(['A', 'B', 'C', 'D', 'E', 'F'], dtype='object')
print(df2.values)#输出所有值
'''
[[1.0 Timestamp('2020-01-01 00:00:00') 1.0 3 'test' 'foo']
[1.0 Timestamp('2020-01-01 00:00:00') 1.0 3 'train' 'foo']
[1.0 Timestamp('2020-01-01 00:00:00') 1.0 3 'test' 'foo']
[1.0 Timestamp('2020-01-01 00:00:00') 1.0 3 'train' 'foo']]'''
3print(df2.describe())#输出数字列 的每一列的 方差 平均数 最大值最小值
''' A C D
count 4.0 4.0 4.0
mean 1.0 1.0 3.0
std 0.0 0.0 0.0
min 1.0 1.0 3.0
25% 1.0 1.0 3.0
50% 1.0 1.0 3.0
75% 1.0 1.0 3.0
max 1.0 1.0 3.0'''
rint(df2.T)#输出其转置
'''' 0 ... 3
A 1 ... 1
B 2020-01-01 00:00:00 ... 2020-01-01 00:00:00
C 1 ... 1
D 3 ... 3
E test ... train
F foo ... foo
[6 rows x 4 columns]'''
print(df2.sort_index(axis=1,ascending=False))#按列 倒序 排列
''' F E D C B A
0 foo test 3 1.0 2020-01-01 1.0
1 foo train 3 1.0 2020-01-01 1.0
2 foo test 3 1.0 2020-01-01 1.0
3 foo train 3 1.0 2020-01-01 1.0'''
print(df2.sort_index(axis=0,ascending=False))
''' A B C D E F
3 1.0 2020-01-01 1.0 3 train foo
2 1.0 2020-01-01 1.0 3 test foo
1 1.0 2020-01-01 1.0 3 train foo
0 1.0 2020-01-01 1.0 3 test foo'''
print(df2.sort_values(by='E'))#按某一列的值进行排序 test test train train
'''
A B C D E F
0 1.0 2020-01-01 1.0 3 test foo
2 1.0 2020-01-01 1.0 3 test foo
1 1.0 2020-01-01 1.0 3 train foo
3 1.0 2020-01-01 1.0 3 train foo'''
2 Pandas 选择数据
import numpy as np #相当于矩阵
import pandas as pd #相当于字典
dates=pd.date_range('20200101',periods=6)
df=pd.DataFrame(np.arange(24).reshape(6,4),index=dates,columns=['A','B','C','D']) #先生成矩阵 再给其加 行列名称
print(df)
'''
A B C D
2020-01-01 0 1 2 3
2020-01-02 4 5 6 7
2020-01-03 8 9 10 11
2020-01-04 12 13 14 15
2020-01-05 16 17 18 19
2020-01-06 20 21 22 23'''
print(df['A'],df.A)
'''2020-01-01 0
2020-01-02 4
2020-01-03 8
2020-01-04 12
2020-01-05 16
2020-01-06 20
Freq: D, Name: A, dtype: int32'''
print(df[0:3])
print(df['20200101':'20200103'])
'''
A B C D
2020-01-01 0 1 2 3
2020-01-02 4 5 6 7
2020-01-03 8 9 10 11'''
#select by label:loc 依标签选择
print(df.loc['20200101'])
'''
A 0
B 1
C 2
D 3
Name: 2020-01-01 00:00:00, dtype: int32'''
print(df.loc['20200103',['A','B']])
'''
A 8
B 9
Name: 2020-01-03 00:00:00, dtype: int32'''
#select by position:iloc 依位置选择
print(df.iloc[3:5,1:3])
''' B C
2020-01-04 13 14
2020-01-05 17 18'''
print(df.iloc[[1,3,5],1:3])
'''
B C
2020-01-02 5 6
2020-01-04 13 14
2020-01-06 21 22'''
#Boolean indexing
print(df[df.A<8])
''' A B C D
2020-01-01 0 1 2 3
2020-01-02 4 5 6 7'''
3 Pandas 设置值
import numpy as np #相当于矩阵
import pandas as pd #相当于字典
dates=pd.date_range('20200101',periods=6)
df=pd.DataFrame(np.arange(24).reshape(6,4),index=dates,columns=['A','B','C','D'])
print(df)
'''
A B C D
2020-01-01 0 1 2 3
2020-01-02 4 5 6 7
2020-01-03 8 9 10 11
2020-01-04 12 13 14 15
2020-01-05 16 17 18 19
2020-01-06 20 21 22 23'''
df.loc['20200103','B']=555
df.iloc[2,2]=233
print(df)
''' A B C D
2020-01-01 0 1 2 3
2020-01-02 4 5 6 7
2020-01-03 8 555 233 11
2020-01-04 12 13 14 15
2020-01-05 16 17 18 19
2020-01-06 20 21 22 23'''
df.B[df.A>4]=0#df.A大于4的元素 所在行的B这一列的元素都改成0
print(df)
df[df.A>4]=1#df.A大于4的元素 所在行的所有元素都改成1
print(df)
''' A B C D
2020-01-01 0 1 2 3
2020-01-02 4 5 6 7
2020-01-03 8 0 233 11
2020-01-04 12 0 14 15
2020-01-05 16 0 18 19
2020-01-06 20 0 22 23
A B C D
2020-01-01 0 1 2 3
2020-01-02 4 5 6 7
2020-01-03 1 1 1 1
2020-01-04 1 1 1 1
2020-01-05 1 1 1 1
2020-01-06 1 1 1 1'''
df['F']=np.nan
df['E']=pd.Series([1,2,3,4,5,6],index=pd.date_range('20200101',periods=6))
print(df)
''' A B C D F E
2020-01-01 0 1 2 3 NaN 1
2020-01-02 4 5 6 7 NaN 2
2020-01-03 1 1 1 1 NaN 3
2020-01-04 1 1 1 1 NaN 4
2020-01-05 1 1 1 1 NaN 5
2020-01-06 1 1 1 1 NaN 6'''
4 Pandas 处理丢失数据
import numpy as np #相当于矩阵
import pandas as pd #相当于字典 表格操作
dates=pd.date_range('20200101',periods=6)
df=pd.DataFrame(np.arange(24).reshape(6,4),index=dates,columns=['A','B','C','D'])
df.iloc[2,2]=np.nan
df.iloc[3,3]=np.nan
print(df)
''' A B C D
2020-01-01 0 1 2.0 3.0
2020-01-02 4 5 6.0 7.0
2020-01-03 8 9 NaN 11.0
2020-01-04 12 13 14.0 NaN
2020-01-05 16 17 18.0 19.0
2020-01-06 20 21 22.0 23.0'''
print(df.dropna(axis=0,how='any'))#0行1列
##how = any 表示其所在列或行中只要含有nan就丢掉
##how = all 表示其所在列或行的所有元素都是nan就丢掉
''' A B C D
2020-01-01 0 1 2.0 3.0
2020-01-02 4 5 6.0 7.0
2020-01-05 16 17 18.0 19.0
2020-01-06 20 21 22.0 23.0'''
print(df.isnull())#判断表格中那些是nan 是nan则在其元素位置上返回True
print(np.any(df.isnull()) ==True)#如果表格很大,不能排查,只要表格中有nan则返回True
print(df.fillna(0))#将nan的元素值改为0
''' A B C D
2020-01-01 False False False False
2020-01-02 False False False False
2020-01-03 False False True False
2020-01-04 False False False True
2020-01-05 False False False False
2020-01-06 False False False False
True
A B C D
2020-01-01 0 1 2.0 3.0
2020-01-02 4 5 6.0 7.0
2020-01-03 8 9 0.0 11.0
2020-01-04 12 13 14.0 0.0
2020-01-05 16 17 18.0 19.0
2020-01-06 20 21 22.0 23.0'''
5 Pandas 导入导出 文件读写
import pandas as pd #相当于字典 表格操作
##import pickle
data=pd.read_csv('student.csv')
print(data)
#写入sudent.pickle文件中
data.to_pickle('student.pickle')
#用pickle的方式读出 pickle.dump()写入 pickle.load()读出
##file=open('student.pickle','rb')
##datanew=pickle.load(file)
##print(datanew)
##file.close()
datanew=pd.read_pickle('student.pickle')
print(datanew)
6 Pandas 合并 concat (按行按列合并)
import pandas as pd #相当于字典 表格操作
import numpy as np
#concatenating 合并
df1=pd.DataFrame(np.ones((3,4))*0,columns=['a','b','c','d'])
df2=pd.DataFrame(np.ones((3,4))*1,columns=['a','b','c','d'])
df3=pd.DataFrame(np.ones((3,4))*2,columns=['a','b','c','d'])
#print(df1,df2,df3)
#合并 法一:concat
res=pd.concat([df1,df2,df3],axis=0,ignore_index=True) #忽略每一行的坐标并对其重新排列
print(res)
#合并 法二:append
result_4=df1.append([df2,df3],ignore_index=True)
print(result_4)
'''
a b c d
0 0.0 0.0 0.0 0.0
1 0.0 0.0 0.0 0.0
2 0.0 0.0 0.0 0.0
3 1.0 1.0 1.0 1.0
4 1.0 1.0 1.0 1.0
5 1.0 1.0 1.0 1.0
6 2.0 2.0 2.0 2.0
7 2.0 2.0 2.0 2.0
8 2.0 2.0 2.0 2.0'''
#添加一列元素
s=pd.Series([1,2,3,4],index=['a','b','c','d'])
result_5=df1.append(s,ignore_index=True)
print(result_5)
''' a b c d
0 0.0 0.0 0.0 0.0
1 0.0 0.0 0.0 0.0
2 0.0 0.0 0.0 0.0
3 1.0 2.0 3.0 4.0'''
#join,['inner','outer']
#outer 相当于并集
#inter 相当于交集
df4=pd.DataFrame(np.ones((3,4))*0,columns=['a','b','c','d'],index=[1,2,3])
df5=pd.DataFrame(np.ones((3,4))*1,columns=['b','c','d','e'],index=[2,3,4])
print(df4)
print(df5)
result_1=pd.concat([df4,df5],axis=0,join='outer',ignore_index=True) #默认
print(result_1)
result_2=pd.concat([df4,df5],axis=0,join='inner',ignore_index=True)
print(result_2)
''' a b c d e
0 0.0 0.0 0.0 0.0 NaN
1 0.0 0.0 0.0 0.0 NaN
2 0.0 0.0 0.0 0.0 NaN
3 NaN 1.0 1.0 1.0 1.0
4 NaN 1.0 1.0 1.0 1.0
5 NaN 1.0 1.0 1.0 1.0
b c d
0 0.0 0.0 0.0
1 0.0 0.0 0.0
2 0.0 0.0 0.0
3 1.0 1.0 1.0
4 1.0 1.0 1.0
5 1.0 1.0 1.0
'''
#依照df4的行坐标进行合并 1 2 3
result_3=pd.concat([df4,df5.reindex(df4.index)],axis=1)
print(result_3)
''' a b c d b c d e
1 0.0 0.0 0.0 0.0 NaN NaN NaN NaN
2 0.0 0.0 0.0 0.0 1.0 1.0 1.0 1.0
3 0.0 0.0 0.0 0.0 1.0 1.0 1.0 1.0'''
7 Pandas 合并 merge
import pandas as pd
# merging two df by key/keys. (may be used in database)
# simple example
left = pd.DataFrame({'key': ['K0', 'K1', 'K2', 'K3'],
'A': ['A0', 'A1', 'A2', 'A3'],
'B': ['B0', 'B1', 'B2', 'B3']})
right = pd.DataFrame({'key': ['K0', 'K1', 'K2', 'K3'],
'C': ['C0', 'C1', 'C2', 'C3'],
'D': ['D0', 'D1', 'D2', 'D3']})
print(left)
print(right)
res = pd.merge(left, right, on='key') #基于共有的‘key’这一列进行合并
print(res)
'''key A B
0 K0 A0 B0
1 K1 A1 B1
2 K2 A2 B2
3 K3 A3 B3
key C D
0 K0 C0 D0
1 K1 C1 D1
2 K2 C2 D2
3 K3 C3 D3
key A B C D
0 K0 A0 B0 C0 D0
1 K1 A1 B1 C1 D1
2 K2 A2 B2 C2 D2
3 K3 A3 B3 C3 D3
'''
import pandas as pd
# consider two keys
left = pd.DataFrame({'key1': ['K0', 'K0', 'K1', 'K2'],
'key2': ['K0', 'K1', 'K0', 'K1'],
'A': ['A0', 'A1', 'A2', 'A3'],
'B': ['B0', 'B1', 'B2', 'B3']})
right = pd.DataFrame({'key1': ['K0', 'K1', 'K1', 'K2'],
'key2': ['K0', 'K0', 'K0', 'K0'],
'C': ['C0', 'C1', 'C2', 'C3'],
'D': ['D0', 'D1', 'D2', 'D3']})
print(left)
print(right)
# how = ['left', 'right', 'outer', 'inner']
res_1 = pd.merge(left, right, on=['key1', 'key2'], how='inner') # 并集 default for how='inner'
print(res_1)
res_2 = pd.merge(left, right, on=['key1', 'key2'], how='left')
print(res_2)
'''key1 key2 A B
0 K0 K0 A0 B0
1 K0 K1 A1 B1
2 K1 K0 A2 B2
3 K2 K1 A3 B3
key1 key2 C D
0 K0 K0 C0 D0
1 K1 K0 C1 D1
2 K1 K0 C2 D2
3 K2 K0 C3 D3
key1 key2 A B C D
0 K0 K0 A0 B0 C0 D0
1 K1 K0 A2 B2 C1 D1
2 K1 K0 A2 B2 C2 D2
key1 key2 A B C D
0 K0 K0 A0 B0 C0 D0
1 K0 K1 A1 B1 NaN NaN
2 K1 K0 A2 B2 C1 D1
3 K1 K0 A2 B2 C2 D2
4 K2 K1 A3 B3 NaN NaN
'''
import pandas as pd
# indicator
df1 = pd.DataFrame({'col1':[0,1], 'col_left':['a','b']})
df2 = pd.DataFrame({'col1':[1,2,2],'col_right':[2,2,2]})
print(df1)
print(df2)
res_1 = pd.merge(df1, df2, on='col1', how='outer', indicator=True)
print(res_1)
# give the indicator a custom name
res_2 = pd.merge(df1, df2, on='col1', how='outer', indicator='indicator_column')
print(res_2)
'''col1 col_left
0 0 a
1 1 b
col1 col_right
0 1 2
1 2 2
2 2 2
col1 col_left col_right _merge #_mearge是indicator的默认名字
0 0 a NaN left_only
1 1 b 2.0 both
2 2 NaN 2.0 right_only
3 2 NaN 2.0 right_only
col1 col_left col_right indicator_column #indicator='indicator_column'
0 0 a NaN left_only
1 1 b 2.0 both
2 2 NaN 2.0 right_only
3 2 NaN 2.0 right_only
'''
import pandas as pd
# merged by index
left = pd.DataFrame({'A': ['A0', 'A1', 'A2'],
'B': ['B0', 'B1', 'B2']},
index=['K0', 'K1', 'K2'])
right = pd.DataFrame({'C': ['C0', 'C2', 'C3'],
'D': ['D0', 'D2', 'D3']},
index=['K0', 'K2', 'K3'])
print(left)
print(right)
# left_index and right_index
res_1 = pd.merge(left, right, left_index=True, right_index=True, how='outer')
res_2 = pd.merge(left, right, left_index=True, right_index=True, how='inner')
print(res_1)
print(res_2)
''' A B
K0 A0 B0
K1 A1 B1
K2 A2 B2
C D
K0 C0 D0
K2 C2 D2
K3 C3 D3
A B C D
K0 A0 B0 C0 D0
K1 A1 B1 NaN NaN
K2 A2 B2 C2 D2
K3 NaN NaN C3 D3
A B C D
K0 A0 B0 C0 D0
K2 A2 B2 C2 D2
'''
import pandas as pd
# handle overlapping suffixes函数
boys = pd.DataFrame({'k': ['K0', 'K1', 'K2'], 'age': [1, 2, 3]})
girls = pd.DataFrame({'k': ['K0', 'K0', 'K3'], 'age': [4, 5, 6]})
res = pd.merge(boys, girls, on='k', suffixes=['_boy', '_girl'], how='inner')
print(boys)
print(girls)
print(res)
''' k age
0 K0 1
1 K1 2
2 K2 3
k age
0 K0 4
1 K0 5
2 K3 6
k age_boy age_girl
0 K0 1 4
1 K0 1 5'''
8 Pandas plot 出图
首先,要安装 pip3 install matplotlib库
import pandas as pd
import numpy as np #生成数据
import matplotlib.pyplot as plt
##法1:
##rand 0-1之间均匀随机分布数 randn 均值为0方差为1的 正态分布随机数
y=np.random.randn(1000)
x=np.arange(1000)
plt.plot(x,y)
plt.title('method1:generate 1000 random numbers')
plt.show ()
##法2:
# plot data
# Series 线性数据
data = pd.Series(np.random.randn(1000), index=np.arange(1000))
print(data.head(5))#输出前五行数据
data.plot(title='method2:generate 1000 random numbers')
##data.title('法二:1000个随机数据')
plt.show()
'''
0 -0.395516
1 -0.214216
2 1.387715
3 -0.361872
4 0.903104
dtype: float64
'''
##累加
data = data.cumsum()
data.plot(title='add up to 1000 random numbers')
plt.show()
# DataFrame
data = pd.DataFrame(np.random.randn(1000, 4),
index=np.arange(1000),
columns=list("ABCD"))
print(data.head(5))#输出前五行数据 5*4 1000*4
data = data.cumsum()
data.plot(title='add up to 1000*4 random numbers')
plt.show()
''' A B C D
0 -0.358835 -0.887334 1.623515 0.592971
1 -0.681534 -0.552056 1.809367 -0.171675
2 0.498227 1.354632 -0.556342 -1.009500
3 -0.575301 -1.656798 -1.010952 -0.226274
4 0.282341 -1.038344 1.455043 1.513783'''
# plot methods:
# 'bar'条形图, 'hist', 'box', 'kde', 'area', 'scatter', hexbin', 'pie'
ax = data.plot.scatter(x='A', y='B', color='DarkBlue', label="Class 1")
data.plot.scatter(x='A', y='C', color='LightGreen', label='Class 2', ax=ax)
#label是名称 ax=ax 是将这两个图打印在一张图中 深蓝 绿
plt.show()