视频地址 http://sa.mentorx.net/course/89/task/403/show
一、创建Excel文件
import pandas as pd
df=pd.DataFrame({'ID':[1,2,3],'Name':['Tim','Victor','Nick']})
df=df.set_index('ID')
print(df)
df.to_excel('output.xlsx')
print('done!')
DataFrame 数据帧 (class类)
DataFrame()文件里没有任何数据
一个DataFrame相当于一个work sheet
df.to_excel('路径') (method方法)
如果没有指定索引
结果:
指定索引:
df.set_index('ID')(method方法)
df=df.set_index('ID')#产生一个新的DataFrame,再用df引用
结果:
二、读取Excel文件
1、pd.read_excel('路径')
people=pd.read_excel('people.xlsx')#自动生成索引
print(people.shape)#读取行列
读取为DataFrame
2、df.shape
结果:
(19972, 6) #19972行,6列
3、df.columns
print(people.columns)#列名
结果:
Index(['ID', 'Type', 'Title', 'FirstName', 'MiddleName', 'LastName'], dtype='object')
4、df.head()
people.head()
Out[4]:
ID Type Title FirstName MiddleName LastName
0 1 Employee NaN Ken J Sánchez
1 2 Employee NaN Terri Lee Duffy
2 3 Employee NaN Roberto NaN Tamburello
3 4 Employee NaN Rob NaN Walters
4 5 Employee Ms. Gail A Erickson
5、df.tail()
people.tail(3)
Out[5]:
ID Type Title FirstName MiddleName LastName
19969 20775 Individual Customer NaN Crystal S He
19970 20776 Individual Customer NaN Crystal NaN Zheng
19971 20777 Individual Customer NaN Crystal NaN Hu
(1)在excel第一行不是header的情况下
pd.read_excel('路径',header=1)
people=pd.read_excel('路径',header=1)#可读取到header
(2)没有header的情况
pd.read_excel('路径',header=None)
people=pd.read_excel('people.xlsx',header=None)#不设置header
print(people.columns)
结果:
Int64Index([0, 1, 2, 3, 4, 5], dtype='int64')
(3)设置header
df.columns=[' ',' ',' ']
people=pd.read_excel('People.xlsx',header=None)
people.columns=['ID','Type','Title','FirstName','MiddleName','LastName']
print(people.columns)
people=people.set_index['ID']#代替原来的DataFrame
#或者people.set_index('ID',inplace=True)
people.to_excel('People.xlsx')
结果:'ID'不再columns里面
Index(['Type', 'Title', 'FirstName', 'MiddleName', 'LastName'], dtype='object')
(6)在read_excel()中,index_col='ID'就不会再自动生成索引(read_excel()的属性)
df=pd.read_excel('People.xlsx',index_col='ID')
print(df.head())
Type Title FirstName MiddleName LastName
ID
1 Employee NaN Ken J Sánchez
2 Employee NaN Terri Lee Duffy
3 Employee NaN Roberto NaN Tamburello
4 Employee NaN Rob NaN Walters
5 Employee Ms. Gail A Erickson