1、介绍
Pandas在实际工作中的应用：
pip install -i https://pypi.doubanio.com/simple pandas jupyter xlrd openpyxl pillow matplotlib seaborn
1、Pandas简介
pandas是?个开源的、BSD许可的库为Python编程语?提供?性能、易于使?的数据结构和数据分析?具。继承了numpy中良好的性质，然后又有易于使用的特点。
2、pandas的两个数据结构Series & DataFrame
DataFrame: 2维数据
Series: 1维数据
pandas: 没有3维以上的数据结构
3、创建Series
创建Series
1. 通过1维的list-like创建
2. 通过字典创建
3. 通过标量创建
以字典方式创建：
d = {'a': 1, 'b': 2, 'c': 3}
s = pd.Series(d)
4、创建DataFrame
创建DataFrame
1. 通过2维的list-like创建
2. 通过字典创建
3. 通过读取Excel表
通过字典方式创建：
d = {'A': [1, 3], 'B': [2, 4]}
df = pd.DataFrame(d, index=['x', 'y'])
读取Excel表：
df = pd.read_excel('goods_base.xlsx',index_col=0)
df.columns.name = 'col_name'
df.index.name = 'index_name'
df
2、读Excel-read_excel()
1、读Excel-sheet_name参数
io: 路径, StringIO, URL
import pandas as pd
df_dict = pd.read_excel('sheet_name.xlsx',sheet_name=[1, '3月'])
df_dict[1]
df_dict['3月']
2、读Excel-header参数
图表header.xlsx：
pd.read_excel('header.xlsx', header=None)
pd.read_excel('header.xlsx',sheet_name=3,header=[0, 1],index_col=0)
3、读Excel-usecols参数
usecols.xlsx表格样式为上图左下角。
pd.read_excel('usecols.xlsx',usecols='B:E')
pd.read_excel('usecols.xlsx',usecols=[0, 2])
# 代码的可读性比较好
pd.read_excel('usecols.xlsx',usecols=['AAA', 'CCC'])
pd.read_excel('usecols.xlsx', usecols=lambda x: (x=='AAA') | (x=='EEE'))
pd.read_excel('goods_base.xlsx', usecols=['货号', '商品名称', '品牌', '成本', '季节', '商品年份'])
4、读Excel-skiprows参数
pd.read_excel('skiprows.xlsx',skiprows=1)
pd.read_excel('skiprows.xlsx',sheet_name=1,skiprows=[0, 2])
pd.read_excel('skiprows.xlsx',sheet_name=2,skiprows=2,usecols='B:D')
对原始图形读取切片：
5、读Excel-names参数
df = pd.read_excel('header.xlsx')
df = pd.read_excel('header.xlsx',names=['月份', '销量', '销售额'])
df = pd.read_excel('header.xlsx',names=['月份', '销量', '销售额'],header=None)
6、读Excel-dtype参数
dtype: 字典, {'列名': '类型'}
设置列的类型
? int8/int16/int32/int64(默认): 整型
? float16/float32/float64(默认): 浮点型
? str/string: 字符串
? bool: 布尔
? category: 分类
? datetime64[ns]: 时间戳(纳秒)
? period[Y/M/D]: 时间周期(年/月/日)
? object: python对象混合类型
df = pd.read_excel('goods_base.xlsx')
df.dtypes
货号 object
商品代码 object
颜色代码 int64
商品名称 object
品牌 object
成本 int64
季节 object
商品年份 int64
dtype: object
通过下面的方式指定数据类型（注：和视频有点不太一样，str通过object类型进行存储，period[Y]无法识别）：
df = pd.read_excel(
'goods_base.xlsx',
dtype={
'货号': 'str',
'商品代码': 'str',
'颜色代码': 'str',
'季节': 'category',
'品牌': 'category',
# '商品年份': 'period[Y]'
}
)
# 转化为str,再转化为string
# df['颜色代码'] = df['颜色代码'].astype('string')
df.dtypes
结果：
货号 object
商品代码 object
颜色代码 object
商品名称 object
品牌 category
成本 int64
季节 category
商品年份 int64
dtype: object
7、读Excel-parse_dates参数
df = pd.read_excel('date_parser.xlsx')
df.dtypes
df = pd.read_excel(
'date_parser.xlsx',
parse_dates=[0],
date_parser=lambda x: pd.to_datetime(x, format='%Y年%m月%d日')
)
df.dtypes
df = pd.read_excel(
'parse_dates.xlsx',
sheet_name=1,
parse_dates={'日期': ['年', '月', '日']},
)
df
原始格式转化为：
df = pd.read_excel(
'date_parser.xlsx',
parse_dates=[0],
date_parser=lambda x: pd.to_datetime(x, format='%Y年%m月%d日')
)
df.dtypes
原始格式转化为（读成日期格式）：
8、读Excel-parse_na_values参数
# 下面的空格会被替换成NaN
df = pd.read_excel(
'na_values.xlsx',
na_values=['a', 0, ' ']
)
9、读Excel-converters参数
df = pd.read_excel(
'converters.xlsx',
converters={
# '货号': lambda x: x.strip(),
'货号': str.strip
}
)
10、读Excel-true_values参数
第二列只有d在false_value中，e、f不在其中，转换失败。（要成功都成功，否则失败）
df = pd.read_excel(
'true_false_values.xlsx',
true_values=['a', 'b'],
false_values=['c', 'd'],
)
df = pd.read_excel(
'true_false_values.xlsx',
true_values=['a', 'b'],
false_values=['c', 'd'],
dtype={'列3': bool}
)
11、读Excel-其他参数补充
第0列设置一个行索引。
3、写Excel
1、to_excel()
import pandas as pd
# 使用字典的方式来创建
df = pd.DataFrame({
'销量': [10, 20],
'售价': [100.123, None],
}, index=['aaa', 'bbb'])
df.index.name = '货号'
print(df)
df.to_excel('tb_.xlsx',
index=True,
sheet_name='tb1',
float_format='%.2f',
na_rep='我是空值')
写入表格时，货号格式正确：
2、ExcelWriter输出多个表
to_excel()函数
第一个参数使用ExcelWriter类的两个作用:
1: 设置datatime输出格式
2: 输出多个sheet
import pandas as pd
from datetime import datetime
df1 = pd.DataFrame(
{'日期': [datetime(2020, 1, 1), datetime(2020, 1, 2)],
'销量': [10, 20]}
)
df2 = pd.DataFrame(
{'日期': [datetime(2020, 2, 1), datetime(2020, 2, 2)],
'销量': [15, 25]}
)
print(df1)
print(df2)
with pd.ExcelWriter('tb.xlsx', datetime_format='YYYY-MM-DD') as writer:
df1.to_excel(writer, sheet_name='1月')
df2.to_excel(writer, sheet_name='2月')
控制台输出：
3、读写csv
import pandas as pd
df = pd.DataFrame(
{
'一': [1, 2],
'二': [3, 4],
}
)
print(df)
df.to_csv('tb1.csv', index=False, encoding='GBK')
df = pd.read_csv('tb1.csv', encoding='GBK')
print(df)
控制台输出：
4、Index介绍
1、识别
2、对齐
3、获取和设置
4、selecting
1、dict-like
注：. 点选不推荐使用
Series.index_name
DataFrame.columns_name
s.total
# 代码可读性良好
s['total']
2、loc
df = pd.DataFrame([
[1, 2, 3, 4],
[5, 6, 7, 8],
[9, 10, 11, 12]],
columns=list('ABCD'),
index=list('XYZ'))
df.loc[['X', 'Y'], ['B', 'D']]
原始数据：
df.loc[[True, False, True],
[True, False, True, False]]
df.loc[lambda df: [True, False, True],
lambda df: [True, False, True, False]]
3、iloc
选择单个值时
.loc == .at
.iloc == .iat
df.iloc[1, 2] == df.iat[1, 2]
5、数据运算
1、数据赋值
1、Series
2、DataFrame
2、加减乘除
+ ----- add()
- ----- sub()
* ----- mul()
/ ----- div()
需要注意的两个问题：
1. 对空值的处理
2. 对除数为0的处理
对空值的处理：
number +-*/ NaN == NaN
NaN +-*/ number == NaN
对除数为0的处理：
1 / 0 == inf
-1 / 0 == -inf
1、Series扩展
s1 = pd.Series([1, 2],index=['a', 'b'])
s2 = pd.Series([10, 20],index=['c', 'b'])
# 补上0，不至于出现NaN
s1.add(s2, fill_value=0)
2、DataFrame扩展
df1 = pd.DataFrame([
[1, 2],
[3, 4],
], columns=['A', 'B'],
index=['X', 'Y'])
df2 = pd.DataFrame([
[10, 20],
[30, 40],
], columns=['C', 'B'],
index=['Z', 'Y'])
df1.add(df2, fill_value=0)
3、MultiIndex
df = pd.read_excel(
'jd_tb.xlsx',
header=[0, 1]
)
df.columns
df[('京东', '销量')] + df[('淘宝', '销量')]
df_total = df['京东'] + df['淘宝']
df_total
6、字符串操作
1、字符串方法
strip, lstrip, rstrip:去除两边空?
'\t aaa \n'.strip() -> 'aaa'
'\t aaa \n'.lstrip() -> 'aaa \n'
'\t aaa \n'.rstrip() -> '\t aaa'
split：拆分
'a\t\nb c'.split() -> ['a','b','c']
'a:b:c'.split(':') -> ['a','b','c']
replace:替换
'abac'.replace('a','x') -> 'xbxc'
contains:包含
'ab'.__contains__('a') -> True
'ab'.__contains__('c') -> False
count:计数
'aba'.count('a') -> 2
'aba'.count('b') -> 1
'aba'.count('c') -> 0
index, find, in:查找
'abc'.index('c') -> 2
'abc'.find('c') -> 2
'c' in 'abc' -> True
'ab'.index('c') -> ValueError
'ab'.find('c') -> -1
'c' in 'ab' -> False
join:粘合
','.join('abc') -> 'a,b,c'
':'.join(['a','b','c']) -> 'a:b:c'
startswith, endswith:前缀, 后缀
'abc'.startswith('a') -> True
'abc'.endswith('c') -> True
lower, upper:?写, ?写
'Abc'.lower() -> 'abc'
'Abc'.upper() -> 'ABC'
2、正则表达式
findall 匹配到的全部以列表返回
search 返回第一个匹配对象
split 拆分
sub 替换
\d 数字
\w 字符, 包括: 中文, 英文, 数字
\s \t, \n, 空格
sub 替换
re.findall('\d', 'a1b2c')
re.findall('[a-z]', 'a1b2c')
3、StringDtype
能保存字符串的两个类型
1. object-dtype
2. StringDtype
df = pd.DataFrame([
['a', 'b'],
['c', 1],
], columns=['X', 'Y'])
df.dtypes
#%%
# 将没一列转化为最有可能的类型
df = df.convert_dtypes()
df.dtypes
# 选取类型为string的一列
df.select_dtypes(include='string')
4、Series.str 字符串访问器
Accessor 访问器
Series.str可用于以字符串的形式访问Series的值
并对其应用一些方法
df = pd.DataFrame([
['\na\n', '\nd\n', datetime(2020, 1, 1)],
['\nb\n', 1, datetime(2020, 1, 2)]
], columns=list('ABC'))
df.dtypes
df['A'].str.strip()
df['B'].str.strip()
通过下面方式解决：
# 此时1对应的是一个字符串
df['B'].astype('str').str.strip()
将日期格式拆分成列表：
df['C'].astype('str').str.split('-')
7、数据处理
1、缺失值
# 结果全为True
s.isna()
2、检测缺失值
# 效果相同
Series.isna(self)
Series.isnull(self)
DataFrame.isna(self)
DataFrame.isnull(self)
df = pd.read_excel(
'isna_notna.xlsx'
)
df.isna()
3、删除缺失值
axis=0 # 删除带有nan的行
how='any' # 只有那一行有nan值，无论有几个都会被删除
subset=None # 指定列
删除B这一列：
4、填充缺失值
ffill # 向前查找进行填充 f-> foword
轴方向不同，取值不同：
实际工作中最常见用法：
df['字符'] = df['字符'].fillna('')
df['数字'] = df['数字'].fillna(0)
df['时间'] = df['时间'].fillna(pd.NaT)
5、缺失值之线性插值
s = pd.Series(
[1,2,None,None,5,
100, None, 200]
)
s.interpolate()
df = pd.DataFrame([
[1,2,3,4],
[1,None,None,4],
[1,None,None,8],
[10,20,30,40],
])
df.interpolate(axis=0)
列的方向上操作。
8、时间处理
1、时间相关四个概念
1、pandas 时间相关四个概念
时间点VS时间段：
时间增量VS时间偏移量：
2、to_datetime()
转换第一个参数 arg 为 datetime 日期时间格式。
format 常用格式代码：
s = pd.to_datetime(pd.Series([
'2020-01-02',
'2020#01#02'
]), errors='coerce')
3、date_range()
生成时间戳范围。
返回固定频率的DatetimeIndex：
pd.date_range(
start='2020-01-01',
end='2020-01-05',
freq='D'
)
4、Datetimelike访问器
Datetimelike Accessor(访问器)
Series.dt 以日期时间格式的形式访问 Series 的属性/方法
Datetime 属性：
Datetime 方法：
df = pd.DataFrame(
{'A': [pd.Timestamp('2020-11-01 03:04:05')]}
)
df['B'] = df['A'].dt.date
df['B'] = df['A'].dt.time
5、Period 创建 和 属性访问器
Period 创建：
? Period() # 单个
? period_range() # 多个时间段范围
pandas.Period(
value=None, # 时间段的字符串
freq=None, # 频率
)
pandas.period_range(
start=None, # 开始时间
end=None, # 结束时间
periods=None, # 序列?度
freq=None, # 频率
)
Period 属性:
p = pd.period_range(
start='2020-01-01',
end='2020-01-03',
freq='D'
)
s = pd.Series(p)
6、Timetelta时间间隔
Timetelta 时间间隔 创建
? Timetelta()
? to_timetelta()
? timetelta_range()
s = pd.Series([
pd.Timedelta('1 days'),
pd.Timedelta('2 days'),
pd.Timedelta('3 days')
])
7、索引DatetimeIndex
时间序列索引优点：
1、获取日期范围(切片)非常快
df.loc['2020-01-02':'2020-01-04']
2、shift(), tshift()等移位操作快
3、相同频率的DatetimeIndex对象合并非常快
4、通过年份，月份等属性快速访问
9、数据透视表
1、pivot() & pivot_table()
pivot()无法处理重复数据, pivot_table()出场。
在其中引入聚合函数：
df1 = pd.DataFrame({
'年份': [2019, 2019, 2020, 2020, 2020],
'平台': ['京东', '淘宝', '京东', '淘宝', '淘宝'],
'销量': [100, 200, 300, 400, 500],
})
pd.pivot_table(df1,
index='年份',
columns='平台',
values='销量',
aggfunc='min')
拓展：
df = pd.read_excel('2019销售总表.xlsx').convert_dtypes()
pd.pivot_table(
df,
index='货号',
values='销量', # 不传入columns，传入values作为列名
aggfunc=[sum, 'mean']
)
10、排序
1、sort_index() & sort_values()
pandas 排序规则：
1. 数值类型, 根据大小
2. 字符, 0-9 < A-Z < a-z < 中文(拼音首字母)
4. 分类数据: CategoricalDtype, 可自定义大小关系
3. datetime, 过去 < 现在 < 未来
对A列中行进行排序：
CategoricalDtype：
作用1: 减少内存
作用2: 自定义排序
例如:
尺码: ['S', 'M', 'L', 'XL']
季节: ['春', '夏', '秋', '冬']
df = pd.DataFrame(
data,
index=['Y', 'Z', 'X'],
columns=['B', 'C', 'A']
)
df.sort_index()
#df.sort_index(ascending=False)
2、CategoricalDtype自定义排序
df['季节'].memory_usage() # 占用的内存
# 按季节分组
tb = pd.pivot_table(
df,
index='季节',
values='销量',
aggfunc=sum
)
tb.index.str.encode('utf-8')
my_type = pd.CategoricalDtype(
categories=['春季', '夏季', '秋季', '冬季', '四季'],
ordered=True # 按照我们设置的列表进行排序
)
my_type
# 转化为自定义的type
df['季节'] = df['季节'].astype(my_type)
df.dtypes
df['季节'].memory_usage()
tb1 = pd.pivot_table(
df,
index='季节',
values='销量',
aggfunc=sum
)
扩展：
df = pd.read_excel(
'2019销售总表.xlsx',
usecols=['货号', '平台']
).sample(12) # 抽取12条数据
df.sort_values(['货号', '平台'])
# 货号设置升序
df.sort_values(['货号', '平台'],ascending=[True, False])
11、分组
1、groupby()
1. 拆分成多个组
2. 分别对每个组应用函数
3. 合并
df = pd.DataFrame({
'货号': ['A','B','B','A','B'],
'平台': ['淘宝','淘宝','京东',
'京东','淘宝'],
'销量': [1, 2, 3, 4, 5]
})
gb = df.groupby('货号')
for name, group in gb:
print(name)
print(group)
分组之后通常可以做三件事情：
1: 聚合(aggregate)
2: 转换(transform)
3: 过滤(filter)
2、分组-聚合（groupby().agg()）
df = pd.DataFrame({
'货号': ['A', 'A', 'B', 'B', 'B'],
'销量': [1, 2, 3, 4, 5],
'售价': [10,20,30,40,50]
})
df.groupby('货号')
df.groupby('货号').first()
12、函数 & 方法
1、Series.apply()
在Series的值上调用函数.
Series.apply()
DataFrame.apply(axis)
GroupBy.apply()
Resample.apply()
Rolling.apply()
Expanding.apply()
s = pd.Series([1,2,3,4])
def f1(x):
return x * 10
s.apply(f1)
2、pct-change()百分比变化
3、query()查询并筛选行
#### DataFrame.query: 使用布尔表达式筛选行
import pandas as pd
df = pd.DataFrame([
[1, 6, 8],
[2, 4, 3],
[3, 2, 2]
], columns=['A', 'B', 'C'])
df
#%%
# 原本筛选的方式；同df.query('B>A')
df[df['B'] > df['A']]
#%%
# 可进行链式筛选
df.query('B>A').query('A*2==B')
#%%
df.query('B>C')
#%%
# 提取外部变量
x = 5
df.query('B<@x')
4、重置index
import pandas as pd
df = pd.DataFrame(
{'A': ['a','b','c']},
index=['x', 'y', 'z']
)
# df.index.name = 'B'
df
#%%
df.reset_index()
#%%
# 是否丢掉原来的索引
df.reset_index(drop=True)
#%%
df = pd.DataFrame(
{'A': ['a','b','c']},
index=pd.MultiIndex.from_tuples(
[
('x', 1),
('y', 2),
('z', 3),
]
)
)
df
#%%
df.reset_index()
#%%
df.reset_index(drop=True)
#%%
df.reset_index(level=0)
#%%
df.reset_index(level=1)
#%%
# level=1 层丢弃
df.reset_index(level=1, drop=True)
#%%
df = pd.DataFrame(
{
('A', 'B'): ['a','b','c'],
('A', 'C'): ['d','e','f'],
},
index=['x', 'y', 'z']
)
df
#%%
df.reset_index(col_level=0)
#%%
df.reset_index(col_level=1)
#%%
#
df.reset_index(col_level=0, col_fill='C')
5、使用结果符合指定的索引
import pandas as pd
#%%
df = pd.DataFrame(
[[1,2,3],
[4,5,6],
[7,8,9]],
columns=['A', 'B', 'C'],
index=['X', 'Y', 'Z']
)
df
#%%
df.reindex(['Z', 'Y'])
13、多表合并
1、merge联合
merge() 联接合并。
1:1情况：
M:1情况：
M:M情况：
import pandas as pd
#%%
df1 = pd.read_excel('sales.xlsx')
df1
#%%
df2 = pd.read_excel('goods_base.xlsx')
df2
#%%
# on 通过货号进行对齐后匹配；how='left' 多的在左边，使用左连接
pd.merge(df1, df2,
on='货号',
how='left',
validate='m:1')
其他情况分析：
（1）两个表连接的中间货号不同
（2）两个表格有重复列名称
（3）多对多可能出现问题
补充：
import pandas as pd
#%%
df1 = pd.read_excel(
'on_index.xlsx',
index_col=[0] # 货号设置成索引
)
df1
#%%
df2 = pd.read_excel(
'on_index.xlsx',
sheet_name=1, # 读取表格2
index_col=[0]
)
df2
#%%
# 通过索引连接
pd.merge(df1, df2,
left_index=True,
right_index=True,
how='outer')
#%%
# 和上面的方式相同
df1.join(df2, how='outer')
#%%
df3 = pd.read_excel(
'on_list.xlsx'
)
df3
#%%
df4 = pd.read_excel(
'on_list.xlsx',
sheet_name=1
)
df4
#%%
# 通过list连接（on参数）
pd.merge(df3, df4,on=['年份', '月份'])
14、openyxl操作excel
openpyxl 是对 pandas 的补充
? 读写图片
? 读写格式
1、基础
1、创建表格
from openpyxl import Workbook
from datetime import datetime
from openpyxl.drawing.image import Image
from openpyxl.styles import Font
#%%
wb = Workbook() # 实例化对象
wb
#%%
# 正在活动的工作表
ws = wb.active
ws
#%%
# 设置工作表
ws.title = 'sheet1'
#%%
# 左上角填入一和二
ws.append(['一', '二'])
ws.append([1, 2])
# 日期格式，时间戳
ws.append([datetime(2020,1,1),
datetime(2020,1,2)])
# A4 表示 excel中的位置
ws.add_image(Image('a.jpg'), 'A4')
ws.add_image(Image('b.jpg'), 'B4')
# 对A3字体更改一下颜色 16进制的RGB的三个值
ws['A3'].font = Font(color='FF0000')
wb.save('1.xlsx')
2、读取已有的表
from openpyxl import load_workbook
#%%
wb = load_workbook('1.xlsx')
wb
#%%
ws = wb.active
ws
#%%
c = ws['A2']
c.value
#%%
wb.save('2.xlsx')
#%%
# 读取1.xlsx -》并保存 3.xlsx
# index=False 索引不输出
import pandas as pd
pd.read_excel('1.xlsx').to_excel('3.xlsx', index=False)
使用pandas读取时，格式会丢失，输出结果如下：
2、sheet操作
1、创建
wb = Workbook()
wx = wb.active
wx.title = 'First'
#%%
wb.create_sheet()
wb.create_sheet(title='AAA')
wb.create_sheet(
title='BBB',
index=0
)
2、删除
# ['BBB', 'First', 'CCC', 'Sheet', 'DDD', 'AAA']
wb.sheetnames
wb['Sheet'].title
# <Worksheet "BBB">
# <Worksheet "First">
# <Worksheet "CCC">
# <Worksheet "Sheet">
# <Worksheet "DDD">
# <Worksheet "AAA">
for i in wb:
print(i)
# 移除表
wb.remove(wb['Sheet'])
wb.save('2.xlsx')
3、访问单元格
#### 访问单元格(cell)
from openpyxl import load_workbook
#%%
wb = load_workbook('1.xlsx')
ws = wb.active
#%%
# 'A1:D4'
ws.dimensions
#%%
c = ws['A2']
# 对单元格进行赋值，当对表格进行save时，才保存到表格里面
c.value = 100
c.value
#%%
# 取单元格
c = ws.cell(
row=3,
column=2,
value=200, # 赋值
)
c.value
#%%
# wb.save('2.xlsx')
#%%
# 获取第二行
ws[2]
ws['C']
#%%
# ((<Cell 'Sheet'.A2>, <Cell 'Sheet'.B2>, <Cell 'Sheet'.C2>, <Cell 'Sheet'.D2>),
# (<Cell 'Sheet'.A3>, <Cell 'Sheet'.B3>, <Cell 'Sheet'.C3>, <Cell 'Sheet'.D3>))
ws[2:3]
#%%
ws['C:D']
#%%
# ((<Cell 'Sheet'.B2>, <Cell 'Sheet'.C2>, <Cell 'Sheet'.D2>),
# (<Cell 'Sheet'.B3>, <Cell 'Sheet'.C3>, <Cell 'Sheet'.D3>),
# (<Cell 'Sheet'.B4>, <Cell 'Sheet'.C4>, <Cell 'Sheet'.D4>))
ws['B2':'D4']
#%%
gen = ws.iter_rows(
min_row=2,
max_row=4,
min_col=2,
max_col=3,
values_only=True)
list(gen)
#%%
gen = ws.iter_cols(
min_row=2,
max_row=4,
min_col=2,
max_col=3,
values_only=True)
list(gen)
#%%
tuple(ws.rows)
#%%
# (('AAA', 'BBB', 'CCC', 'DDD'), (100, 2, 3, 4), (5, 200, 7, 8), (9, 10, 11, 12))
tuple(ws.values)
更直观一些（按行来获取）：
对单元格数据进行遍历输出：
for row in ws.values:
for v in row:
print(v)
4、合并/拆分单元格
- 合并单元格: merge_cells()
- 拆分单元格: unmerge_cells()
#%%
from openpyxl import load_workbook
#%%
wb = load_workbook('1.xlsx')
ws = wb.active
ws.dimensions
#%%
# 合并单元，只会保留左上角数据
ws.merge_cells('B2:C3')
#%%
wb = load_workbook('1.xlsx')
ws = wb.active
ws.merge_cells(
start_row=2, # 开始的行列
start_column=2,
end_row=3,
end_column=3
)
wb.save('3.xlsx')
#%%
wb = load_workbook('2.xlsx')
ws = wb.active
ws.unmerge_cells('B2:C3')
wb.save('4.xlsx')
原始单元格：
合并单元格：
拆分单元格：
5、插入删除行/列
- Worksheet.insert_rows()
- Worksheet.insert_cols()
- Worksheet.delete_rows()
- Worksheet.delete_cols()
from openpyxl import load_workbook
#%%
wb = load_workbook('1.xlsx')
ws = wb.active
ws.dimensions
#%%
# idx=2 在第二行进行插入2列（amount=2）
# ws.insert_cols(idx=2, amount=2)
# wb.save('2.xlsx')
#%%
# 从第2删除2列
ws.delete_rows(idx=2, amount=2)
wb.save('2.xlsx')
6、add_image 插入图片
from openpyxl import load_workbook
from openpyxl.drawing.image import Image
import os
def insert_img_to_excel(filename, by_col, to_col, img_folder):
"""
插入图片到 excel
:param filename: 文件路径
:param by_col: 依靠列 (A, B, C ...)
:param to_col: 插入到列 (A, B, C ...)
:param img_folder: 图片源的文件夹
:return: None
"""
wb = load_workbook(filename)
ws = wb.active
# 从 1开始序号，原本是从0开始
for i, c in enumerate(ws[by_col], start=1):
# 1 货号
# 2 AGDH5500
# ...
# print(i, c.value)
# 拼接路径
img_ffn = os.path.join(img_folder, c.value + '.jpg')
print(i, img_ffn)
try:
ws.add_image(
img=Image(img_ffn),
anchor=to_col + str(i) # 'A1'
)
except:
print(c.value, '匹配不到图片')
wb.save(filename)
if __name__ == '__main__':
insert_img_to_excel(
'1.xlsx', 'A', 'B',
img_folder="D:\\03Enviroment\\23Pycharm\\02code\\python-study\\statistical_analysis\\src"
"\\module08_data_structrue_openyxl\\img"
)
7、Font字体
openpyxl 格式相关的类：
from openpyxl import load_workbook
from openpyxl.styles import Font
wb = load_workbook('tips.xlsx')
ws = wb.active
font = Font(
name='Apple SD Gothic Neo', # 字体名称
size=30, # 字体大小
bold=True, # 是否黑体
italic=True, # 是否斜体
underline='doubleAccounting', # 下划线 {'double', 'single', 'singleAccounting', 'doubleAccounting'}
color='AAFF0000' # 颜色
)
# 对一个单元格进行设置
# ws['A1'].font = font
# 对第一行进行设置
for c in ws[1]:
c.font = font
wb.save('tips_1.xlsx')
8、Fill填充
from openpyxl.styles import (
PatternFill, # 颜色填充
GradientFill, # 渐变填充
)
from openpyxl import load_workbook
wb = load_workbook('tips.xlsx')
ws = wb.active
# {'lightGrid', 'lightVertical', 'lightTrellis',
# 'darkDown', 'lightDown', 'mediumGray',
# 'gray125', 'darkHorizontal', 'gray0625',
# 'darkGray', 'lightUp', 'darkGrid',
# 'darkUp', 'lightGray', 'darkTrellis',
# 'solid', 'lightHorizontal', 'darkVertical'}
ws['A1'].fill = PatternFill(
patternType='solid',
fgColor='FDEB71'
)
ws['B1'].fill = PatternFill(
patternType='lightGrid',
fgColor='FDEB71'
)
ws['C1'].fill = GradientFill(
degree=0, # 水平
stop=('CE9FFC', '7367F0')
)
ws['D1'].fill = GradientFill(
degree=45,
stop=('CE9FFC', '7367F0')
)
ws['E1'].fill = GradientFill(
degree=90, # 垂直方向上填充
stop=('CE9FFC', '7367F0')
)
# 填充一列
for c in ws['B']:
c.fill = PatternFill(
patternType='solid',
fgColor='F55555'
)
wb.save('tips_2.xlsx')
9、Border和Side边框和边
四条边进行设置。
from openpyxl.styles import (
Border, # 边框
Side, # 边
)
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
# 将每个风格都绘制出来
style_list = ['dotted', 'dashDot', 'medium', 'mediumDashed', 'mediumDashDot', 'dashDotDot',
'mediumDashDotDot', 'slantDashDot', 'hair',
'thick', 'double', 'dashed', 'thin']
for i, style in enumerate(style_list, start=1):
cell = ws['B' + str(i)]
cell.value = style
cell.border = Border(
bottom=Side(border_style=style,
color='FF0000'),
left=Side(border_style='medium',
color='0000FF')
)
wb.save('border.xlsx')
9、设置对齐
from openpyxl.styles import (
Alignment, # 对齐
)
from openpyxl import load_workbook
wb = load_workbook('tips.xlsx')
ws = wb.active
ws['A1'].alignment = Alignment(
horizontal='center', # {'fill', 'right', 'general', 'left', 'justify', 'centerContinuous', 'distributed', 'center'}
vertical='bottom', # {'bottom', 'justify', 'distributed', 'top', 'center'}
wrap_text=True # 空值是否换行
)
ws['B1'].alignment = Alignment(
horizontal='right', # {'fill', 'right', 'general', 'left', 'justify', 'centerContinuous', 'distributed', 'center'}
vertical='top', # {'bottom', 'justify', 'distributed', 'top', 'center'}
wrap_text=False
)
wb.save('tips_4.xlsx')
10、列宽行高
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter
from openpyxl.utils import column_index_from_string
wb = load_workbook('tips.xlsx')
ws = wb.active
# 设置一列一行
# ws.column_dimensions['B'].width = 20
# ws.row_dimensions[2].height = 60
# 对所有的行列进行遍历
for i in range(ws.max_row):
# print(i)
ws.row_dimensions[i + 1].height = 60
for i in range(ws.max_column):
# print(i)
letter = get_column_letter(i + 1)
ws.column_dimensions[letter].width = 20
wb.save('tips_5.xlsx')
11、内置样式
from openpyxl import load_workbook
wb = load_workbook('tips.xlsx')
ws = wb.active
# 对第一行进行设置
for c in ws[1]:
c.style = "Pandas"
# 对 B 和 E列进行设置
for c in ws['B'][1:] + ws['E'][1:]:
c.style = "Good"
wb.save('builtin.xlsx')
12、openyxl与pandas相互转换
"""
openpyxl 与 pandas 相互转化
"""
from openpyxl import Workbook, load_workbook
import pandas as pd
from openpyxl.utils.dataframe import dataframe_to_rows
"""
pandas -> openpyxl
"""
df = pd.DataFrame(
[[1, 2, 3],
[4, 5, 6],
[7, 8, 9]],
columns=list('ABC'),
index=list('XYZ')
)
# print(df)
# 是否需要 index header
rows = dataframe_to_rows(df, index=False, header=True)
# <generator object dataframe_to_rows at 0x000001DB13396E58>
# print(rows)
wb = Workbook()
ws = wb.active
for row in rows:
ws.append(row)
wb.save('1.xlsx')
"""
openpyxl -> pandas
"""
wb = load_workbook('tips.xlsx')
ws = wb.active
data = list(ws.values)
# print(data)
# 第 0 行作为列名；从第2列作为数据进行切片
df = pd.DataFrame(data[1:], columns=data[0])
# total_bill tip sex smoker day time size
# 0 16.99 1.01 Female No Sun Dinner 2
# 1 10.34 1.66 Male No Sun Dinner 3
# 2 21.01 3.50 Male No Sun Dinner 3
# 3 23.68 3.31 Male No Sun Dinner 2
# 4 24.59 3.61 Female No Sun Dinner 4
print(df.head())
1.xlsx保存文件样式：
