2021-03-31

# -*- codeing =utf-8 -*-
# @Time : 2021/3/22 21:00
# @Author : David
# @File : pandas.py
# @Software :PyCharm


import pandas as pd
import numpy as np
s=pd.Series([1,3,6,np.nan,44,1]) #相当于一维的dataframe
print(s)
t=pd.Series(1,index=list(range(4)))#表示长度为4
print(t)


dates=pd.date_range('20210101',periods=6)   #生成6个以基准时间开始的时间序列
print(dates)
df=pd.DataFrame(np.random.randn(6,4),index=dates,columns=['a','b','c','d'])
#生成一个6行4列的dataframe，index表示行，给一个时间索引，colums表示列，给四列用abcd来命名
print(df)
df1=pd.DataFrame(np.arange(12).reshape(3,4))
#默认序列由0，1，2，3.....表示
print(df1)
df2=pd.DataFrame({'a':1.,'b':pd.Timestamp('20130102'),'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'})
#可以用字典形式来定义dataframe
print(df2)
print(df2.dtypes) #输出所有列的类型
print(df2.index)  #输出所有行的名称(属性)
print(df2.columns)  #输出所有列的名称(属性)
print(df2.values)   #输出每一行的值
print(df2.describe())   #输出每一列的一些数学描述，只能针对数字类型，其余会跳过
print(df2.T)   #输出dataframe的转置
print(df2.sort_index(axis=0,ascending=False))  #对列的索引进行排序，体现为行变化，ascending为假表示逆序
print(df2.sort_values(by='e'))   #对某一列值进行排序

#选择数据

import pandas as pd
import numpy as np
dates=pd.date_range('20210101',periods=6)
df=pd.DataFrame(np.arange(24).reshape((6,4)),index=dates,columns=['A','B','C','D'])
print(df['A'],df.A) #选择数据集中的A列，两种方法一样
print(df[0:3],df['20210101':'20210103'])  #选中数据集中的1-3行，两种方法也一样


#select by label:loc
print(df.loc['20210101'])  #根据标签来选数据
print(df.loc[:,['A','B']])  #复杂查询，先选中所有行，然后选择列
print(df.loc['20210101':'20210106','A':'C'])  #切片不需要加额外的[]，但是选中某字符需要加
print(df.loc['20210101',['A','B']])  #如果输出为一行的数据，则将列序号作为输出的行序号

#select by position:iloc
import pandas as pd
import numpy as np
dates=pd.date_range('20210101',periods=6)
df=pd.DataFrame(np.arange(24).reshape((6,4)),index=dates,columns=['A','B','C','D'])
print(df)
print(df.iloc[3])  #输出第四行数据
print(df.iloc[3,1])  #输出第四行第二列的数字
print(df.iloc[3:5,1]) #输出第四行至第五行的第二列数字
print(df.iloc[3:5,0:3])  #输出第四行至第五行的第一至三列数字
print(df.iloc[[0,1,3],[0,1,3]])  #输出第一行、第二行、第四行的第一列、第二列、第四列数字

#mixed selection : ix  (1.0.0版本以后失效)
import pandas as pd
import numpy as np
dates=pd.date_range('20210101',periods=6)
df=pd.DataFrame(np.arange(24).reshape((6,4)),index=dates,columns=['A','B','C','D'])
print(df.ix[:3,['A','B']])

#Boolean indexing
import pandas as pd
import numpy as np
dates=pd.date_range('20210101',periods=6)
df=pd.DataFrame(np.arange(24).reshape((6,4)),index=dates,columns=['A','B','C','D'])
print(df)
print(df[df.A>8])   #选择A列大于8的所有数据
print(df>8)   #查询df数据集中大于8的数，返回结果为逻辑
print(df.loc[:,'A'][df.B>5])   #复杂查询，结合loc和iloc和比大小来精确定位所选数据


#pandas设置值
import pandas as pd
import numpy as np
dates=pd.date_range('20210101',periods=6)
df=pd.DataFrame(np.arange(24).reshape((6,4)),index=dates,columns=['A','B','C','D'])
df.iloc[2,2]=1111  #选择第三行第三列的值，将其改为1111
print(df)
df.loc['20210101','B']=2222  #同理，选中位置的值将其更改,不过需要注意，如果没有搜索到该标签，则将新建
print(df)
df.loc[:,'A'][df.A>0]=0
print(df)
df['F']=np.nan  #新建一列，值为空
print(df)
df['E']=pd.Series([1,2,3,4,5,6],index=df.index)  #新建一列，注意行序号要和本体一样
print(df)

#pandas处理缺失值
import pandas as pd
import numpy as np
dates=pd.date_range('20210101',periods=6)
df=pd.DataFrame(np.arange(24).reshape((6,4)),index=dates,columns=['A','B','C','D'])
df.iloc[0,1]=np.nan
df.iloc[1,2]=np.nan
print(df)
print(df.dropna(axis=1,how='any')) #how={'any','all'}  对列进行压缩，等于删去行  #另一种how表示这一行当中全为none才删除
print(df.fillna(value=0))  #将数据集中为空值的元素变为0
print(df.isnull())   #判断数据集中是否有元素为空值
print(np.any(df.isnull())==True)  #如果数据集特别庞大，可以通过返回numpy的逻辑函数来判断数据集中是否有元素为空值，但无法获取具体某个元素的位置

#pandas储存or调用数据
#先创建一个csv格式的excel文件
import pandas as pd
import os
data=pd.read_csv('D:/demo/student.csv')   #设置路径
print(data)
data.to_pickle('student.pickle')   #保存为什么格式


#pandas合并concat

#concat
import pandas as pd
import numpy as np
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)
print(df2)
print(df3)
res1 = pd.concat([df1,df2,df3],axis=0)  #竖向合并   #行序号仍然为012012
res2 = pd.concat([df1,df2,df3],axis=1)  #横向合并
print(res1)
print(res2)
res3 = pd.concat([df1,df2,df3],axis=0,ignore_index=True) #行序号重新排序
print(res3)

#concatenating函数
#join,['inner','outer']
import pandas as pd
import numpy as np
df1=pd.DataFrame(np.ones((3,4))*0,columns=['a','b','c','d'],index=[1,2,3])
df2=pd.DataFrame(np.ones((3,4))*1,columns=['b','c','d','e'],index=[2,3,4])
print(df1)
print(df2)
#不标注axis，默认皆为axis=0
res1=pd.concat([df1,df2])   #行列序号不完全一致，合并结果多并少空。默认：res1=pd.concat([df1,df2],join='outer') 求并集
print(res1)
res2=pd.concat([df1,df2],join='inner',ignore_index=True)  #求交集
print(res2)

#新版本pandas已经删除join_axes
import pandas as pd
import numpy as np
df1=pd.DataFrame(np.ones((3,4))*0,columns=['a','b','c','d'],index=[1,2,3])
df2=pd.DataFrame(np.ones((3,4))*1,columns=['b','c','d','e'],index=[2,3,4])
res1=pd.concat([df1,df2],axis=1,join_axes=[df1.index])  #直接套用df1的行序号，多删少空
print(res1)

#append
import pandas as pd
import numpy as np
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))*1,columns=['a','b','c','d'])
res1=df1.append([df2,df3],ignore_index=True)  #相当于concat函数中axis=0，且只能竖向添加
print(res1)
s1=pd.Series([1,2,3,4],index=['a','b','c','d'])
print(s1)
res2=df1.append(s1,ignore_index=True)
print(res2)

#merge
import pandas as pd
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)
res1=pd.merge(left,right,on='key')  #通过key这一列让两个数据集合并
print(res1)

#consider two keys
import pandas as pd
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,inner,outer]
res1=pd.merge(left,right,on=['key1','key2'],how='inner')  #不标注默认为inner
res2=pd.merge(left,right,on=['key1','key2'],how='outer')
res3=pd.merge(left,right,on=['key1','key2'],how='left')
res4=pd.merge(left,right,on=['key1','key2'],how='right')
#类似sql的left join,right join,inner join,outer join
print(res1)
print(res2)
print(res3)
print(res4)

#indicator
import pandas as pd
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)
res1=pd.merge(df1,df2,on='col1',how='outer',indicator=True) #显示合并后的数据集这一行哪些数据集参与合并，哪些没有
#res1=pd.merge(df1,df2,on='col1',how='outer',indicator='indicator_column')  可将合并的数据集中的indicator这一列的显示为indicator_column,也可以显示为其他自定义列名
print(res1)

#index合并
import pandas as pd
left = pd.DataFrame({'A':['A0','A1','A2'],'B':['B0','B1','B2']},index=['K0','K1','K2'])
right = pd.DataFrame({'C':['C0','C1','C2'],'D':['D0','D1','D2']},index=['K0','K2','K3'])
print(left)
print(right)
res1=pd.merge(left,right,left_index=True,right_index=True,how='outer')
#根据index来合并 how同理
print(res1)

#handle overlapping
import pandas as pd
boys=pd.DataFrame({'k':['k0','k1','k2'],'age':[1,2,3]})
girls=pd.DataFrame({'k':['k0','k0','k3'],'age':[4,5,6]})
res1=pd.merge(boys,girls,on='k',suffixes=['_boys','_girls'],how='outer')
#如果遇到两个数据集列名相同，则使用suffixes函数加一个后缀来区分该两组数据集的相同列名
print(res1)



#matplotlib ploy data
#Series
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
data1 = pd.Series(np.random.randn(1000),index=np.arange(1000))
data1=data1.cumsum()
data1.plot()
plt.show()
#DataFrame
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
data2=pd.DataFrame(np.random.randn(1000,4),index=np.arange(1000),columns=list("ABCD"))
data2=data2.cumsum()
print(data2.head())
data2.plot()
plt.show()


#plot methods
#'bar','hist','box','kde','aera','scatter','hexbin','pie'
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
data2=pd.DataFrame(np.random.randn(1000,4),index=np.arange(1000),columns=list("ABCD"))
data2=data2.cumsum()
ax=data2.plot.scatter(x='A',y='B',label='Class1')
data2.plot.scatter(x='A',y='C',label='Class2',ax=ax)#图对象，不写ax=ax会产生两张图
plt.show()


















```python
在这里插入代码片