开启今日学习之旅~
用Python读取Excel表，看看有些什么方法呢
我们先来看一下Excel表长什么样
然后我们来读取文件
import pandas as pd books=pd.read_excel(‘E:/PycharmProjects/pythonProject/Hellopython/Books.xlsx‘) print(books)
运行结果：
读出来的好像不是我们想要的，那么如何处理呢，就需要我们对参数进行设置
import pandas as pd books=pd.read_excel(‘E:/PycharmProjects/pythonProject/Hellopython/Books.xlsx‘,usecols="C:F",skiprows=3) print(books)
运行结果：
增加了两个参数，首先跳过前面三行，然后使用C D E F列
可以看一下ID这列的数据类型
print(books[‘ID‘])
运行结果：
可以看到ID是float类型，因为表中为空的地方系统会自动填充为NaN，NaN为float类型，所以如果想要填充整数，可以将dtype设为int，但是系统会报错，不允许float转换为int，先将float转换为str。
import pandas as pd from datetime import date,timedelta //导入时间 books=pd.read_excel(‘E:/PycharmProjects/pythonProject/Hellopython/Books.xlsx‘,usecols="C:F",skiprows=3,dtype={‘ID‘:str,‘InStore‘:str,‘Date‘:str}) start=date(2018,1,1) for i in books.index: books[‘ID‘].at[i]=i+1 books[‘InStore‘].at[i]=‘Yes‘ if i%2==0 else ‘No‘ books[‘Date‘].at[i]=start+timedelta(days=i)//timedelta中只有days没有年月 print(books)
运行结果：
这里的Date是将天数加一，接下来看一下如何将年加一呢
import pandas as pd from datetime import date,timedelta books=pd.read_excel(‘E:/PycharmProjects/pythonProject/Hellopython/Books.xlsx‘,usecols="C:F",skiprows=3,dtype={‘ID‘:str,‘InStore‘:str,‘Date‘:str}) print(books[‘ID‘]) start=date(2018,1,1) for i in books.index: books[‘ID‘].at[i]=i+1 books[‘InStore‘].at[i]=‘Yes‘ if i%2==0 else ‘No‘ books[‘Date‘].at[i]=date(start.year+i,start.month,start.day) print(books)
运行结果：
import pandas as pd from datetime import date,timedelta books=pd.read_excel(‘E:/PycharmProjects/pythonProject/Hellopython/Books.xlsx‘,usecols="C:F",skiprows=3,dtype={‘ID‘:str,‘InStore‘:str,‘Date‘:str}) def add_month(d,md): yd=md//12 m=d.month+md%12 if m!=12: yd+=m//12 m=m%12 return date(d.year+yd,m,d.day) //月增加时的小算法 start=date(2018,1,1) for i in books.index: books[‘ID‘].at[i]=i+1 books[‘InStore‘].at[i]=‘Yes‘ if i%2==0 else ‘No‘ books[‘Date‘].at[i]=add_month(start,i) print(books)
运行结果：
books.set_index(‘ID‘,inplace=True) books.to_excel(‘E:/PycharmProjects/pythonProject/Hellopython/output.xlsx‘) print(‘done!‘)
输出新的Excel表格
好啦，今天的学习之旅结束啦~
原文：https://www.cnblogs.com/kannei/p/14768104.html