数据分析Pandas库应用

读入输出

import pandas as pd
people=pd.read_excel('*.xlsx') #读入excel
people.columns=['ID','','','',''] #设置列名
people.set_index('ID',inplace=True)#设置ID作为参考指标数
people.to_excel('*.xlsx')

Series使用——输出table

import pandas as pd

s1=pd.Series([1,2,3],index=[1,2,3],name='A')
s2=pd.Series([10,20,30],index=[1,2,3],name='B')
s3=pd.Series([100,200,300],index=[1,2,3],name='C')
df=pd.DataFrame({s1.name:s1,s2.name:s2,s3.name:s3})
#index有对齐关系
print(df)

单元格数据运算

#方法一
books['Price']=books['ListPrice']*books['Discount']  #重载，单元格对应相乘
#方法二
for i in books.index:
  books['Price'].at[i]=books['ListPrice'].at[i]*books['Discount'].at[i] 

#apply() 用于间接调用函数 
books['ListPrice']=books['ListPrice'].apply(add_2)  

数据排序

products.sort_values(by=['Worthy','Price'],inplace=True,ascending=[True,False]) #按列为参考进行排序

条件筛选

students=students.loc[students.Age.apply(lambda a:18<=a<=30)].loc[students['Score'].apply(level_a)] 
#loc条件筛选

绘图

#方法一
students.plot.bar(x='Field',y='Number',color='orange',title='international students by field')
#方法二
plt.bar(students.Field,students.Number)
plt.xticks(students.Field,rotation=90)
plt.xlabel('Field')
plt.ylabel('Number')
plt.title('International Students By Field',fontsize=16)
plt.tight_layout()
plt.show()

Vlookup匹配

table=students.join(scores,how='left').fillna(0)  #类似与vlookup的数据匹配

分割split

df=employees['Full Name'].str.split(expand=True)

简易运算

temp=students[['Test_1','Test_2','Test_3']]
row_sum=temp.sum(axis=1) #从左到右求和
row_mean=temp.mean(axis=1)
students['Total']=row_sum
students['Average']=row_mean
col_mean=students[['Test_1','Test_2','Test_3','Total','Average']].mean()

去重

#方法一
students.drop_duplicates(subset='Name',inplace=True,keep='last') #去重
#方法二
dupe=students.duplicated(subset='Name')
dupe=dupe[dupe==True]