目录
1.导入excel文件
import pandas as pd
pd.read_excel(io, sheet_name=0, header=0, names=None, index_col=None,
usecols=None, squeeze=False,dtype=None, engine=None,
converters=None, true_values=None, false_values=None,
skiprows=None, nrows=None, na_values=None, parse_dates=False,
date_parser=None, thousands=None, comment=None, skipfooter=0,
convert_float=True, **kwds)
参数 | 说明 |
---|---|
io | excel存储路径-尽量使用英文路径 |
sheet_name | 可以是整型数字、列表名或SheetN(整型数字:sheet表从0为起点,例sheet_name=0,第一个工作表;SheetN代表第N个sheet,S要大写) |
header | 作为列名的行,可设置多行,默认0行,header=None表示无列名 |
names | 重新定义列名(必须与excel列数相同) |
index_col | 用作索引的列(可用列名或位置表示) |
use_cols | 需要读取的列(列名、数字[列1、列2…]) |
converters | 规定列的类型(converts={“商品名”:str,“价格”:float}) |
skiprows | 跳过某些行(skiprows= n, 跳过前n行; skiprows = [a, b, c],索引从0开始,列名有被跳过的风险 |
nrows | 需要读取的行数 |
skipfooter | 跳过末尾n行 |
import numpy as np
import pandas as pd
df=pd.read_excel("priceindex.xlsx",header=1,converters={
"商品名称":str,"原价":float,"现价":float})
1.1批量导入指定文件及汇总
由于每份文件需处理后做拼接,目前用了一种笨方法
citylist=["GZ","SZ"]
datelist=["0111","0112","0113","0114","0115","0116","0117"]
df_output = pd.DataFrame()
for city in citylist:
for day in datelist:
file=city+"缺货"+day+'.xlsx'
df=pd.read_excel(file)
df["日期"]=day
df["城市"]=city
df_output=pd.concat([df_output, df], axis=0)
df_output.to_excel("广深缺货.xlsx",index=False)
1.2批量导入多sheet的excel文件
import xlrd
import pandas as pd
from pandas import DataFrame
excel_name = "北京历史天气2011-2021.xlsx"
wb = xlrd.open_workbook(excel_name)
# print(wb)
# 获取workbook中所有的表格
sheets =