Python 操作 Excel
目录1 安装 pandas
pip install openpyxl -i https://pypi.douban.com/simple
pip install pandas -i https://pypi.douban.com/simple
2 pandas中操作Excel的函数
# creating a sample dataframe
data = pd.DataFrame({'Brand': ['Maruti', 'Hyundai', 'Tata',
'Mahindra', 'Maruti', 'Hyundai',
'Renault', 'Tata', 'Maruti'],
'Year': [2012, 2014, 2011, 2015, 2012,
2016, 2014, 2018, 2019],
'Kms Driven': [50000, 30000, 60000,
25000, 10000, 46000,
31000, 15000, 12000],
'City': ['Gurgaon', 'Delhi', 'Mumbai',
'Delhi', 'Mumbai', 'Delhi',
'Mumbai', 'Chennai', 'Ghaziabad'],
'Mileage': [28, 27, 25, 26, 28,
29, 24, 21, 24]})
# 格式化输出
Brand Year Kms Driven City Mileage
0 Maruti 2012 50000 Gurgaon 28
1 Hyundai 2014 30000 Delhi 27
2 Tata 2011 60000 Mumbai 25
3 Mahindra 2015 25000 Delhi 26
4 Maruti 2012 10000 Mumbai 28
5 Hyundai 2016 46000 Delhi 29
6 Renault 2014 31000 Mumbai 24
7 Tata 2018 15000 Chennai 21
8 Maruti 2019 12000 Ghaziabad 24
2.1 loc()
loc：通过行标签索引行数据，包含所传递的范围的最后一个元素，接受布尔数据。
2.1.1 根据某些条件选择数据
print(data.loc[(data.Brand == 'Maruti') & (data.Mileage > 25)])
# 输出
Brand Year Kms Driven City Mileage
0 Maruti 2012 50000 Gurgaon 28
4 Maruti 2012 10000 Mumbai 28
2.1.2 选择一个行的范围
print(data.loc[2:4])
# 输出
Brand Year Kms Driven City Mileage
2 Tata 2011 60000 Mumbai 25
3 Mahindra 2015 25000 Delhi 26
4 Maruti 2012 10000 Mumbai 28
2.1.3 根据条件更新列的值
data.loc[(data.Year < 2015), ['Mileage']] = 22
print(data)
# 输出
Brand Year Kms Driven City Mileage
0 Maruti 2012 50000 Gurgaon 22
1 Hyundai 2014 30000 Delhi 22
2 Tata 2011 60000 Mumbai 22
3 Mahindra 2015 25000 Delhi 26
4 Maruti 2012 10000 Mumbai 22
5 Hyundai 2016 46000 Delhi 29
6 Renault 2014 31000 Mumbai 22
7 Tata 2018 15000 Chennai 21
8 Maruti 2019 12000 Ghaziabad 24
2.1.4 选择行
# 按索引查找
# 指定单行
print(data.loc[0])
# 指定多行
print(data.loc[[0, 1]])
## 输出
Brand Year Kms Driven City Mileage
0 Maruti 2012 50000 Gurgaon 28
1 Hyundai 2014 30000 Delhi 27
# 按标签查找
data.set_index("Brand", inplace=True)
# 单标签查找
print(data.loc["Maruti"])
# 多标签查找
print(data.loc[["Maruti", "Tata"]])
2.1.5 选择多行和特定列
# 指定特定行
print(data.loc[[2, 3], ["Brand", "Year"]])
# 输出
Brand Year
2 Tata 2011
3 Mahindra 2015
# 选择所有具有某些特定列的行
print(data.loc[:, ["Brand", "Year"]])
# 输出
Brand Year
0 Maruti 2012
1 Hyundai 2014
2 Tata 2011
3 Mahindra 2015
4 Maruti 2012
5 Hyundai 2016
6 Renault 2014
7 Tata 2018
8 Maruti 2019
# 选择特定行标签的特定列
data.set_index("Brand", inplace=True)
print(data.loc[["Maruti", "Tata"], ["City", "Year"]])
# 输出
City Year
Brand
Maruti Gurgaon 2012
Maruti Mumbai 2012
Maruti Ghaziabad 2019
Tata Mumbai 2011
Tata Chennai 2018
2.2 iloc()
iloc：通过行号索引行数据，需要指定一个整数索引来选择一个特定的行/列，不包括所传递范围的最后一个元素，接受布尔数据。
2.2.1 使用整数索引选择行
print(data.iloc[[0, 2, 4]])
# 输出
Brand Year Kms Driven City Mileage
0 Maruti 2012 50000 Gurgaon 28
2 Tata 2011 60000 Mumbai 25
4 Maruti 2012 10000 Mumbai 28
2.2.2 同时选择一个列和行的范围
print(data.iloc[1:4,2:5])
# 输出
Kms Driven City Mileage
1 30000 Delhi 27
2 60000 Mumbai 25
3 25000 Delhi 26
2.2.3 选择多行和特定列
# 选择单行
print(data.iloc[0])
# 选择多行
print(data.iloc[0:2])
print(data.iloc[[0, 1]])
# 输出
Brand Year Kms Driven City Mileage
0 Maruti 2012 50000 Gurgaon 28
1 Hyundai 2014 30000 Delhi 27
# 选择特定行的特定列
print(data.iloc[[0, 1], [0, 3]])
# 输出
Brand City
0 Maruti Gurgaon
1 Hyundai Delhi
# 选择所有特定列的行
print(data.iloc[:, [0, 3]])
# 输出
Brand City
0 Maruti Gurgaon
1 Hyundai Delhi
2 Tata Mumbai
3 Mahindra Delhi
4 Maruti Mumbai
5 Hyundai Delhi
6 Renault Mumbai
7 Tata Chennai
8 Maruti Ghaziabad
2.3 使用[]按名称选择列
# 选择一个单列
print(data["Brand"])
# 输出
0 Maruti
1 Hyundai
2 Tata
3 Mahindra
4 Maruti
5 Hyundai
6 Renault
7 Tata
8 Maruti
# 选择多列
print(data[["Brand", "Year"]])
# 输出
Brand Year
0 Maruti 2012
1 Hyundai 2014
2 Tata 2011
3 Mahindra 2015
4 Maruti 2012
5 Hyundai 2016
6 Renault 2014
7 Tata 2018
8 Maruti 2019
3 操作Excel表单
3.1 读取Excel文件内容
import pandas as pd
# 方法一：默认读取第一个表单
df = pd.read_excel('net.xlsx')
# 默认读取前5行的数据
data = df.head()
print("读取前5行的数据:\n{0}".format(data))
# 方法二：通过制定表单名的方式读取
df = pd.read_excel('net.xlsx', sheet_name='Sheet2')
# 默认读取前5行的数据
data = df.head()
print("读取前5行的数据:\n{0}".format(data))
# 方法三：通过表单索引来指定要访问的表单，0表示第一个表单
df = pd.read_excel('net.xlsx',sheet_name=0)
# 默认读取前5行的数据
data = df.head()
print("读取前5行的数据:\n{0}".format(data))
# 方法四：指定多个表单数据
df = pd.read_excel('net.xlsx', sheet_name=['没有东东', 1, 0])
# 注意此处不能使用head()方法啦
data = df.values()
print("读取所有表单数据:\n{0}".format(data))
# 输出
主IP地址 所属系统 子系统 地址对象名称
0 192.168.40.37 系统1 APP APP_192.168.40.37/32
1 192.168.40.19 系统2 APP APP_192.168.40.19/32
2 192.168.10.33 系统3 DB DB_192.168.10.33/32
3 192.168.0.3 系统4 APP APP_192.168.0.3/32
4 192.168.10.113 系统5 WEB WEB_192.168.10.113/32
5 192.168.1.3 系统6 DB DB_192.168.1.3/32
6 192.168.2.1 系统7 APP APP_192.168.2.1/32
pd.read_excel(io, sheet_name=0, header=0, names=None, index_col=None, usecols=None)：io:excel文件的路径+名字字符串sheet_name:返回指定的sheet，如果将sheet_name指定为None，则返回全表；如果需要返回多个表, 可以将sheet_name指定为一个列表, 例如['sheet1', 'sheet2']；- name:如果没有表头, 可用此参数传入列表做表头
- header:指定数据表的表头,默认值为0, 即将第一行作为表头
- index_col:用作行索引的列编号或者列名，如果给定一个序列则有多个行索引。一般可以设定
index_col=False指的是pandas不适用第一列作为行索引。 - usecols：读取指定的列, 也可以通过名字或索引值
3.2 读取Excel表单
3.2.1 获取行号
print(df.index.values)
# 输出
[ 0 1 2 3 4 5 6]
3.2.2 获取列名
print(df.columns.values)
# 输出
['主IP地址' '所属系统' '子系统' '地址对象名称']
3.2.3 获取指定行数的值
print(df.sample(3).values)
# 输出
[['192.168.40.19' '系统2' 'APP' 'APP_192.168.40.19/32']
['192.168.10.113' '系统5' 'WEB' 'WEB_192.168.10.113/32']
['192.168.1.3' '系统6' 'DB' 'DB_192.168.1.3/32']]
3.2.4 获取指定列的值
print(df["主IP地址"])
# 输出
0 192.168.40.37
1 192.168.40.19
2 192.168.10.33
3 192.168.0.3
4 192.168.10.113
5 192.168.1.3
6 192.168.2.1
Name: 主IP地址, dtype: object
3.2.5 处理Excel数据成为字典
test_data = []
for i in df.index.values:
row_data = df.loc[i, ['主IP地址', '所属系统', '子系统', '地址对象名称']].to_dict()
test_data.append(row_data)
print(test_data)
# 输出
[{'主IP地址': '192.168.40.37', '所属系统': '系统1', '子系统': 'APP', '地址对象名称': 'APP_192.168.40.37/32'}, {'主IP地址': '192.168.40.19', '所属系统': '系统2', '子系统': 'APP', '地址对象名称': 'APP_192.168.40.19/32'}, {'主IP地址': '192.168.10.33', '所属系统': '系统3', '子系统': 'DB', '地址对象名称': 'DB_192.168.10.33/32'}, {'主IP地址': '192.168.0.3', '所属系统': '系统4', '子系统': 'APP', '地址对象名称': 'APP_192.168.0.3/32'}, {'主IP地址': '192.168.10.113', '所属系统': '系统5', '子系统': 'WEB', '地址对象名称': 'WEB_192.168.10.113/32'}, {'主IP地址': '192.168.1.3', '所属系统': '系统6', '子系统': 'DB', '地址对象名称': 'DB_192.168.1.3/32'}, {'主IP地址': '192.168.2.1', '所属系统': '系统7', '子系统': 'APP', '地址对象名称': 'APP_192.168.2.1/32'}]
3.3 增加Excel表单数据
3.3.1 增加一列数据
df["NewColumn"] = None
print(df)
# 输出
主IP地址 所属系统 子系统 地址对象名称 NewColumn
0 192.168.40.37 系统1 APP APP_192.168.40.37/32 None
1 192.168.40.19 系统2 APP APP_192.168.40.19/32 None
2 192.168.10.33 系统3 DB DB_192.168.10.33/32 None
3 192.168.0.3 系统4 APP APP_192.168.0.3/32 None
4 192.168.10.113 系统5 WEB WEB_192.168.10.113/32 None
5 192.168.1.3 系统6 DB DB_192.168.1.3/32 None
6 192.168.2.1 系统7 APP APP_192.168.2.1/32 None
3.3.2 增加一行数据
# 若行索引不存在则新增，存在则修改
df.loc[7] = ["1.1.1.1", "系统8", "DB", "DB_1.1.1.1/32"]
print(df)
# 输出
主IP地址 所属系统 子系统 地址对象名称
0 192.168.40.37 系统1 APP APP_192.168.40.37/32
1 192.168.40.19 系统2 APP APP_192.168.40.19/32
2 192.168.10.33 系统3 DB DB_192.168.10.33/32
3 192.168.0.3 系统4 APP APP_192.168.0.3/32
4 192.168.10.113 系统5 WEB WEB_192.168.10.113/32
5 192.168.1.3 系统6 DB DB_192.168.1.3/32
6 192.168.2.1 系统7 APP APP_192.168.2.1/32
7 1.1.1.1 系统8 DB DB_1.1.1.1/32
# 针对已存在的行
df.loc[5] = ["1.1.1.1", "系统8", "DB", "DB_1.1.1.1/32"]
print(df)
# 输出
主IP地址 所属系统 子系统 地址对象名称
0 192.168.40.37 系统1 APP APP_192.168.40.37/32
1 192.168.40.19 系统2 APP APP_192.168.40.19/32
2 192.168.10.33 系统3 DB DB_192.168.10.33/32
3 192.168.0.3 系统4 APP APP_192.168.0.3/32
4 192.168.10.113 系统5 WEB WEB_192.168.10.113/32
5 1.1.1.1 系统8 DB DB_1.1.1.1/32
6 192.168.2.1 系统7 APP APP_192.168.2.1/32
3.4 删除Excel表单数据
# 删除"所属系统"列，需要指定axis为1，
# 当删除行时，axis为0
data = df.drop("所属系统", axis=1)
print(data)
# 输出
主IP地址 子系统 地址对象名称
0 192.168.40.37 APP APP_192.168.40.37/32
1 192.168.40.19 APP APP_192.168.40.19/32
2 192.168.10.33 DB DB_192.168.10.33/32
3 192.168.0.3 APP APP_192.168.0.3/32
4 192.168.10.113 WEB WEB_192.168.10.113/32
5 192.168.1.3 DB DB_192.168.1.3/32
6 192.168.2.1 APP APP_192.168.2.1/32
# 删除第3,4行，这里下表以0开始，并且标题行不算在类
data = data.drop([2, 3], axis=0)
# 输出
主IP地址 所属系统 子系统 地址对象名称
0 192.168.40.37 系统1 APP APP_192.168.40.37/32
1 192.168.40.19 系统2 APP APP_192.168.40.19/32
4 192.168.10.113 系统5 WEB WEB_192.168.10.113/32
5 192.168.1.3 系统6 DB DB_192.168.1.3/32
6 192.168.2.1 系统7 APP APP_192.168.2.1/32
3.5 修改Excel表单数据
df.loc[0, ["所属系统"]] = "system01"
print(df)
df.iloc[0,1] = "system01"
print(df)
# 输出
主IP地址 所属系统 子系统 地址对象名称
0 192.168.40.37 system01 APP APP_192.168.40.37/32
1 192.168.40.19 系统2 APP APP_192.168.40.19/32
2 192.168.10.33 系统3 DB DB_192.168.10.33/32
3 192.168.0.3 系统4 APP APP_192.168.0.3/32
4 192.168.10.113 系统5 WEB WEB_192.168.10.113/32
5 192.168.1.3 系统6 DB DB_192.168.1.3/32
6 192.168.2.1 系统7 APP APP_192.168.2.1/32
3.6 保存修改后的表单数据
pd.DataFrame(data).to_excel('test.xlsx', sheet_name="sheet1", index=False, header=None)