第1节 Python基础知识
1-Python简介
2-数据类型
-
数字型a
int、float、complex(复数型)、bool(布尔型)
-
字符串型
基础分析
str_a = "Certified_Data_Analyst" type(str_a) len(str_a) #长度 str_a[0] #输出第1个字符 str_a[:9] #输出前10个字符 str_a[10:14] #输出第11-15个字符 str_a*2 #字符串复制字符串 str_a+"_Exam" str_a.upper() str_a.lower() int("1024") #转化为数字 str(3.14) #转化为字符串 "Certified_Data_Analyst".split("_") #分隔符拆分字符串 "Certified_Data_Analyst".replace("_","") #替换字符串 "7".zfill(3) #左边补0
-
容器型
- 可以容纳多个元素的对象叫容器(放不同的数据类型)
- 列表list():[]可以创建一个list变量,[2,3,5,7]
- 元组tuple():()可以创建一个tuple变量,(2,3,5,7)
- 集合set():{}可以创建一个tuple变量,{2,3,5,7}
- 字典dict():{}、: 可以创建一个字典变量,{'a':2,'b':3,'c':5,'d':7}
3-流程控制
-
分支
x=-10 if x > 0: print("x是正数") elif x<0: print("x是负数") else:print("x是零")
-
循环
for i in range(1,6): #生成1个1到5的序列(不包括6),然后for循环来迭代序列中的每个元素 print(i) #每个迭代中都打印i值
i=1 #初始化1 while i <= 10: #while循环在1-10内执行 print(i) #每次打印i值 i +=1 #j接下来,i值递增 if i==6: #i=6时终止循环 break
4-函数
x=-2
y=2
abs(x)
round(x)
pow(x,y) #返回x的y次方
#sqrt(x) #返回x的平方根
max(x,y)
min(x,y)
sum([x,y])
x=2.5
y=0.4
from math import *
sqrt(x)
log(x)
log2(x)
log10(x)
exp(x) #x的e次幂
modf(x) #返回x的小数部分和整数部分
floor(x) #向下取整
ceil(x) #向上取整
divmod(x,y) #接受2个数字,返回商和余数的元组(x/y,x%y)
# sin\\cos\\tan\\asin\\acos\\atan
x=10
y=4
from itertools import *
product() #序列中的元素进行排列,相当于使用嵌套循环组合
permutation(p,[,r]) #从序列p中取r个元素,组成全排列
combinations(p,r) #从序列p中取出r个元素组成全组合,元素不允许重复
combinations_with_replacement(p,r)#从序列p中取出r个元素组成全组合,元素允许重复
describe() #描述性统计
count() #非空观测数量
sum() #所有值之和
mean() #平均值
mode() #值的模值
std() #标准差
var() #方差
prod() #数组元素的乘积
corr() #相关系数矩阵
sorted() #需要用一个变量进行接受,不会修改原有列表
#min\\max\\abs\\
intersection() #交集
union() #并集
difference() #补集
symmetric_difference() #异或集(不相交部分)
isdisjoint() #两集合有无相同元素
issuperset() #是不是超集合
#缺失值问题
#自定义函数
def linear(x):
y=2*x+4
return y
第2节 Pandas基础知识
1-数据类型\查看
pandas除了Python有的数据类型外,还有2种独有的数据结构
-
Series:放置各种数据类型
-
Time-Series:时间索引DatetimeIndex
import pands as pd s=pd.Series([2,3,5,7,11],name='A') dts1=pd.DatetimeIndex(['2024-01-01 00:00:00','2024-01-01 00:03:00','2024-01-01 06:00:00']) dts2=pd.date_range(start='2024-01-01',periods=6,freq='3H') dts3=pd.date_range('2024-01-01',periods=6,freq='d')
-
-
DataFrame:二维的表格型数据结构,Series的容器
- 行索引:index 列索引:columns
- 如何创建DataFrame
-
字典创建DataFrame
import pandas as pd d={ 'A':[1,2,3], 'B':[4,5,6], 'C':[7,8,9] } df=pd.DataFrame(data=d) df
-
列表创建DataFrame
import pandas as pd d=[[1,2,3],[4,5,6],[7,8,9]] df1=pd.DataFrame(data = d, index=['a','b','c'], columns=['A','B','C'] ) df1
-
数组创建DataFrame
import pandas as pd import numpy as np d=np.array([[81,28,24,25,96], [82,35,56,98,39], [13,39,55,36,36], [70,54,69,48,12], [63,80,97,25,70]]) df2=pd.DataFrame(data = d,columns=list('abcde')) #[]\\iloc\\iat\\at #取列 df2 df2.head(2) df2.tail(2) #取最后2行 df2.sample(2) df2['a'] df2.a df2.iloc[0:3,1] df2.iloc[:,[0,2,3]] #取行 df2[0:1] df2.loc[0:0] df2.iloc[[1,3],] df2.iloc[1:4,:] #取指定值 df2.iat[2,2] #55 根据行列索引 df2.at[2,'c'] #55 根据行列名称 df2.iloc[[2,3],[1,4]] #取指定区域
-
2-条件查询
import pandas as pd
import numpy as np
d=np.array([[81,28,24,25,96],
[82,35,56,98,39],
[13,39,55,36,36],
[70,54,69,48,12],
[63,80,97,25,70]])
df=pd.DataFrame(data = d,columns=list('abcde'))
#单一条件
df[df['a']>60] #a列大于60的行
df.loc[df['a']>60]
df.loc[(df['a']>60),['a','b','d']]
#多条件
df[(df['a']>60)&(df['b']>60)]
df.loc[(df['a']>60)&(df['b']>60),['a','b','d']]
3-常用函数
(1)常见数学计算(描述性统计)
import pandas as pd
import numpy as np
d=np.array([[81,28,24,25,96],
[8,35,56,98,39],
[13,39,56,36,3],
[70,54,69,48,12],
[63,80,97,25,70]])
df=pd.DataFrame(data=d,columns=list('abcde'))
df
df['a'].mean() #均值
df['a'].sum() #求和
df['a'].max() #最大值
df['a'].min() #最小值
df['a'].count() #计数
df['a'].median() #中位数
df['a'].var() #方差
df['a'].skew() #偏度
df['a'].kurt() #峰度
df['a'].cumsum() #累计求和
df['a'].cumprod() #累计求积
df['a'].diff() #差分
#df['a'].mad() #平均绝对偏差
df.sum(axis=0) #按列求和汇总到最后一行
df.sum(axis=1) #按行求和汇总到最后一列
df.describe() #描述性统计
df.agg(['sum','mean','max','min','median']) #对整个DataFrame批量使用多个函数
df.agg({'a':['max','min'],'b':['max','sum','mean'],'c':['median']}) #对整个DataFrame批量使用多个函数
#apply()函数可以在DataFrame或Series上应用自定义函数,可以在行或列上进行操作
#applymap()函数只能用于DataFrame,可以在每个元素上应用自定义函数
#map()函数只能用于Series,用于将每个元素映射到另一个值
df.apply(lambda x :x.max()-x.min(),axis=1)
df.apply(lambda x :x.max()-x.min(),axis=0)
df.applymap(lambda x :1 if x>60 else 0)
df['a'].map(lambda x :1 if x>60 else 0)
(2)表的合并&连接
import pandas as pd
df1 =pd.DataFrame({'A':['a','b'],
'B':[1,2]})
df2 =pd.DataFrame({'A':['b','c','d'],
'B':[2,3,4]})
df1
df2
#df1.append(df2,ignore_index=True) #追加
pd.concat([df1,df2],axis=0) #上下拼接
pd.concat([df1,df2],axis=1) #左右拼接
pd.merge(df1,df2,how='inner') #inner
pd.merge(df1,df2,how='outer') #outer
pd.merge(df1,df2,how='left',on='A') #left join
pd.merge(df1,df2,how='right',on='A') #right join
pd.merge(df1,df2,how='inner',on='A') #inner join
pd.merge(df1,df2,how='outer',on='A') #outer join
df1[df1['A'].isin(df2['A'])] #df1中列‘A’的值在df2中也存在的行
df1[~df1['A'].isin(df2['A'])] #df1中列‘A’的值在df2中不存在的行
(3)分组聚合
#group by 先分组再聚合
import pandas as pd
import numpy as np
import random
df=pd.DataFrame({'A':['a','b','a','b','a','b'],
'B':['L','L','M','N','M','M'],
'C':[107,177,139,3,52,38],
'D':[22,59,38,50,60,82]})
df.groupby('A').sum() #单列分组后聚合
df.groupby('A').agg({'C':'min'}).rename(columns={'C':'C_min'}) #A列各元素在C列的最小值
df.groupby('A').agg({'C':'max','D':'min'}).rename(columns={'C':'C_max','D':'D_min'}) #A列各元素在C列的最大值,D列的最小值
df.groupby(['A','B']).sum() #多列分组后聚合
df.groupby(['A','B']).agg({'C':'max'}).rename(columns={'C':'C_max'}) #A、B列各元素在C列的最大值
df.groupby(['A','B']).agg({'C':'max','D':'min'}).rename(columns={'C':'C_max','D':'D_min'}) #A列各元素在C列的最大值,D列的最小值
def custom_agg(x):
return x.max()-x.min()
result=df[['B','C']].groupby('B').agg({'C':custom_agg}) #B列的元素,对于C列的最大值-最小值
result
df['B_C_std']=df[['B','C']].groupby('B')['C'].transform('mean') #L、M、N各元素在D列的均值
df
df=pd.DataFrame({
'姓名':['张三','张三','张三','李四','李四','李四'],
'科目':['语文','数学','英语','语文','数学','英语']
})
(df.astype(str) #将数据转化为字符
.groupby('姓名') #分组
.agg(lambda x: ','.join(x)))['科目'] #join连接元素
4-数据重塑(数据变形)
-
df.pivot数据变形:根据索引(index)、列(column),长表转宽表
- 作用:将数据从长格式(long format)转换为宽格式(wide format)。通过指定索引、列和值,将某些列的值作为新的列名,并使用相应的值填充新的列。适用于将多个类别的数据展示在一张表中。
- 关系:数据变形是数据透视表的前提操作之一。可以使用 df.pivot_table 函数进行更复杂的数据汇总和分析。
import pandas as pd import numpy as np df=pd.DataFrame( { '姓名':['张三','张三','张三','李四','李四','李四'], '科目':['语文','数学','英语','语文','数学','英语'], '成绩':[91,80,100,80,100,96] }) df.pivot(index='姓名',columns='科目',values='成绩') #长转宽 df1=pd.pivot(df,index='姓名',columns='科目',values='成绩').reset_index() df1.melt(id_vars=['姓名'],value_vars=['数学','英语','语文']) #宽表变长表
-
df.pivot_table数据透视表
- 作用:根据指定的行、列和值,计算数据的透视表。透视表可以对数据进行汇总和分析,以便更好地理解数据的关系和特征。
- 关系:数据透视表可以通过 df.pivot 函数实现,但 df.pivot_table 函数提供了更多的功能选项,如聚合函数(aggfunc)、缺失值处理(fill_value)、边际汇总(margins)等。
import random random.seed(1024) df=pd.DataFrame({ '专业':np.repeat(['数学与应用数学','计算机','统计学'],4), '班级':['1班','1班','2班','2班']*3, '科目':['高数','线代']*6, '平均分':[random.randint(60,100) for i in range(12)], '及格人数':[random.randint(30,50) for i in range(12)] }) pd.pivot_table(df,index=['专业','科目'], values=['及格人数','平均分'], aggfunc={'及格人数':np.sum,'平均分':np.mean}) #将长表转宽表 适合于长表中存在重复情况
-
df.stack/unstack数据堆叠
- 作用:将列标签堆叠成行索引(stack),或将行索引堆叠成列标签(unstack)。这两个函数通常用于处理多级索引的数据,以方便数据的操作和分析。
- 关系:数据堆叠是数据融合的前提操作之一。通过堆叠操作,可以将数据从宽格式转换为长格式,以便进行进一步的分析和处理。
df=pd.DataFrame({ '专业':np.repeat(['数学与应用数学','计算机','统计学','物理学'],6), '班级':['1班','2班','3班']*8, '科目':['高数','线代']*12, '平均分':[random.randint(60,100) for i in range(24)], '及格人数':[random.randint(30,50) for i in range(24)] }) df2=pd.pivot_table(df,index=['专业','科目'],values=['及格人数','平均分'], aggfunc={'及格人数':np.sum,"平均分":np.mean}) df2 stacked=df2.stack() #透视结果堆叠到一起 stacked.unstack() #stack的逆操作 stacked.unstack(level=1) stacked.unstack(level=0)
-
df.cross数据交叉表
- 作用:创建数据的交叉表,用于统计两个或多个因素之间的频数。通常用于探索分类变量之间的关系,以及计算各组别的频率分布情况。
- 关系:数据交叉表是一种特殊的数据透视表。通过 pd.crosstab 函数,可以快速创建数据的交叉表,而无需手动进行数据的变形和透视。
df=pd.DataFrame({'High':["高","高","高","中","中","中","低","低","低","高","低"], 'Weight':["重","轻","中","中","轻","重","重","轻","中","重","轻"]}) pd.crosstab(df['High'],df['Weight']) df=pd.DataFrame({'High':["高","高","高","中","中","中","低","低","低","高","低"], 'Weight':["重","轻","中","中","轻","重","重","轻","中","重","轻"], 'Size':["大","中","小","中","中","大","中","小","小","大","小"]}) pd.crosstab(df['High'],[df['Weight'],df['Size']],rownames=['High'],colnames=['Weight','Size'])
5-常用文本函数
import pandas as pd
import numpy as np
d=pd.DataFrame(['a','b','c'],columns=['A'])
d['A'].str.cat(['A','B','C'],sep=',') #cat()拼接字符串
d['A'].str.cat(sep=',') #将一列元素合并成一个字符串
h=pd.DataFrame(['a_b_c','c_d_e',np.nan,'f_g_h'],columns=['B'])
h['B'].str.split('_')
h['B'].str.get(2)
h['B'].str.join("!")
h['B'].str.contains('d') #contains是否包含表达式
h.fillna('0')[h['B'].fillna('0').str.contains('d|f')]
h.fillna('0')[h.fillna('0')['B'].str.contains('d.*e')]
#replace\\repeat
h['B'].str.pad(10,side="right",fillchar="0")
#pad左右补齐 center()中间补齐 ljust右补齐 rjust左补齐 zfill左边补0
#wrp()在指定位置加回车符合
#slice()按指定的开始结束位置切割字符串
#slice_replace()使用给定的字符串,替换指定位置的字符
#startswith()判断是否以给定的字符串开头
#endswith()判断是否以字符串结束
#findall()查找所有符合正则表达式的字符,以数组形式返回
#match()检查是否全部匹配给定的字符串或者表达式
#extract()抽取匹配的字符串出来,注意要加上括号,把你想要的抽取的东西标注上
#strip()去除前后的空白字符 rstrip()去除后面的空白字符 lstrip()去除前面的空白字符
#partition()把字符串分隔成DataFrame,切割为三部分,分隔符前,分隔符,分隔符后
#lower\\upper\\caplitalize()首字符大写\\swapcase()大小写转换\\find\\rfind
#index()查找给定字符串所在的位置 ,如果不存在,会报错
#isalnum()是否全部是数字和字母成\\isalpha()是否全部是字母\\isdigit()是否全部都是数字\\isspace()是否是空格
#islower()\\isupper()\\istitle()\\isnumeric()是否是数字\\isdecimal()
6-常用时间函数
import pandas as pd
import numpy as np
from datetime import datetime
#创建时间戳
date_range=pd.to_datetime(['2024-01-02','2024-01-03','2024-01-06'])
date_range=pd.date_range(start='2024-01-01',end='2024-12-31',freq='D')
timestamp=pd.Timestamp(year=2023,month=1,day=1,hour=12,minute=30,second=45)
timestamp=datetime(2023,1,1,12,30,45)
#计算时间差
start_time=pd.Timestamp('2024-01-01 12:00:00')
end_time=pd.Timestamp('2024-01-02 14:30:00')
time_diff=end_time-start_time
start_time+time_diff #开始时间+时间差=结束时间
#时间索引
data=[1,2,3,4,5]
dates=pd.date_range(start='2024-01-01',periods=5,freq='D') #periods长度 freq频率
ts=pd.Series(data,index=dates)
ts['2024-01-02']
ts['2024-01-01':'2024-01-03']
ts[1:4]
ts.resample('W').mean() #W按周进行重采样,mean计算每周日期的平均值
ts.rolling(window=3).mean() #窗口大小为3 今天昨天前天日期的平均值
ts.shift(0) #将索引序号向前或者向后偏移
#时间访问器dt
timestamps=pd.Series(pd.date_range(start='2024-01-01',periods=5,freq='D'))
year=timestamps.dt.year #month\\day\\hour\\minute\\second\\quarter\\day_name(星期几)
#isocalendar().week(周数)\\dayofyear(一年中的第几天)
#dayofweek+1(一周中的第几天)\\dayinmonth(所在月的最后一天)
#时长转换
ts=pd.Series(pd.to_timedelta(np.arange(10),unit='m'))
seconds=ts.dt.seconds
seconds=ts.dt.to_pytimedelta()
seconds
7-Pandas 窗口函数
在数据上执行滑动窗口操作,可以对数据进行滚动计算、滑动统计等操作。
import pandas as pd
import numpy as np
data={'column':[1,2,3,4,5,6,7,8,9,10]}
df=pd.DataFrame(data)
df['MA']=df['column'].rolling(window=3).mean() #移动平均值Moving Average
df['Sum']=df['column'].rolling(window=5).sum() #滚动求和Rolling Sum
df['Max']=df['column'].rolling(window=5).max() #滚动最大值 最小值min 滑动标准差(Rolling Standard Deviation)std
#自定义窗口函数:rolling().apply()
def custom_function(data):
return max(data)/sum(data)
df['Result']=df['column'].rolling(window=3).apply(custom_function)
df
第3节 Pandas数据读写
Python是一种高级编程语言,具有简洁、高效、易读、易学等特点,并且能够轻松处理各种数据类型,包括CSV、JSON和Excel等。其中,to_excel()方法是pandas库中提供的一个函数,能够非常方便地将Python数据导出到Excel表格中。
Python的to_excel方法优势如下:
-
Python的to_excel方法能够快速轻松地生成Excel文件,并且可以自由控制数据的导出格式。
-
Python的to_excel方法支持多种数据源的导出,包括CSV文件、SQL数据库等。
-
Python的to_excel方法支持生成多张Excel表格,并且能够控制Excel表格内的字段名和数据内容的排版格式。
-
CSV
import pandas as pd import numpy as np data=np.random.rand(10,10) #生成一个10*10的矩阵 columns=['col'+str(i) for i in range(10)] #列名为col1\\col2....... df=pd.DataFrame(data,columns=columns) df.to_csv('./output/foo.csv') #请注意:要在你的代码文件夹目录下建一个\\output文件夹才能写入 pd.read_csv('./output/foo.csv')
-
Excel
df.to_excel('./output/foo.xlsx',sheet_name='Sheet1',index=None) pd.read_excel('./output/foo.xlsx',index_col=None,na_values=['NA'])
import pandas as pd df = pd.read_csv('data.csv') #read_csv()读取CSV文件中的数据到DataFrame对象中 df.to_excel('output.xlsx', index=False) #index=False表示Excel导出后不包含索引列
import pandas as pd import openpyxl df = pd.read_csv('data.csv') #read_csv()读取CSV文件中的数据到DataFrame对象中 writer = pd.ExcelWriter('output.xlsx', engine='openpyxl') #输出Excel文件 df.to_excel(writer, sheet_name='Sheet1', index=False) #输出Sheet1 df.to_excel(writer, sheet_name='Sheet2', index=False)#输出Sheet2 #设置Sheet1中两列的宽度 sheet1 = writer.sheets['Sheet1'] sheet1.column_dimensions["A"].width = 20 sheet1.column_dimensions["B"].width = 15 #设置Sheet2中数据输出区域的样式 sheet2 = writer.sheets['Sheet2'] cell_range = sheet2['A1':'C11'] for row in cell_range: for cell in row: cell.value=cell.value.title() cell.alignment=openpyxl.styles.Alignment(horizontal='center',vertical='center') writer.save()
-
HDF
df.to_hdf('./output/foo.h5','df') pd.read_hdf('./output/foo.h5','df').head()
-
MySql
from sqlalchemy import create_engine import pandas as pd mysql_engine=create_engine("mysql+pymysql://root:password@localhost/test") df.to_sql(pust_table_name,mysql_engine,if_exists='replace',index=False) #mysql_engine一定要正确配置 df=pd.read_sql(""" select a,b from pust_table_name; """,mysql_engine) df
1、OS模块
- listdir():获取某一个文件夹下所有的子文件以及子文件夹的名字
- os.path.normcase('c:/windows\\\\system32\\\\')
- 在Linux和Mac平台上,该函数会原样返回path,在windows平台上会将路径中所有字符转换为小写,并将所有斜杠转换为反斜杠。
- os.path.normpath('c://windows\\\\System32\\\\../Temp/')
- 规范化路径,如..和/
- os.path.getsize() # 获取文件大小
- os.remove():删除一个文件
- os.rename("oldname","newname")重命名文件/目录
- os.system(r"ls /")//告诉操作系统运行这条命令
- os.environ():环境变量(key与value都必须是字符串),配置之后,程序就可以看到这条路径
- os.abspath():根据系统转换路径分隔符.
- os.split():将路径分割为文件名和文件名所在路径.
2、HANA SQL文件正常导出
import requests
import json
import os
from urllib3 import encode_multipart_formdata
import time
import pyhdb
import pandas as pd
import datetime
from copy import copy
# 获取media_id
def upload_file(file_path, wx_upload_url):
file_name = file_path.split("/")[-1]
with open(file_path, 'rb') as f:
length = os.path.getsize(file_path)
data = f.read()
headers = {"Content-Type": "application/octet-stream"}
params = {
"filename": file_name,
"filelength": length,
}
file_data = copy(params)
file_data['file'] = (file_path.split('/')[-1:][0], data)
encode_data = encode_multipart_formdata(file_data)
file_data = encode_data[0]
headers['Content-Type'] = encode_data[1]
r = requests.post(wx_upload_url, data=file_data, headers=headers)
print(r.text)
media_id = r.json()['media_id']
return media_id
# 发送文件
def qi_ye_wei_xin_file(wx_url, media_id):
headers = {"Content-Type": "text/plain"}
data = {
"msgtype": "file",
"file": {
"media_id": media_id
}
}
r = requests.post(
url=wx_url,
headers=headers, json=data)
print(r.text)
# 数据库读取模块, 读取并存储数据
def read_hanaData(sql_select1, outputpath):
datatime = datetime.datetime.now().strftime("%Y-%m-%d")
filename = str(datatime) + outputpath
# filename = 'D:\\output_data\\\\test\\\\'+str(datatime)+ outputpath #\\t和\\u、\\'都会进行转义,所以需要多加一个\\
# filename = r'D:\\output_data\\test\\{}{}'.format(str(datatime),outputpath)
# 当前目录下创建一个excel对象
writer = pd.ExcelWriter(filename)
# 创建数据库连接
connection = pyhdb.connect(
host="192.168.71.199",
port=30015,
user="BWPCON",
password="Cass@!@1214"
)
print("数据1读取中,请稍后...")
# 创建游标
cursor = connection.cursor()
# 执行第一个sql查询
cursor.execute(sql_select1)
# 获取查询结果
res = cursor.fetchall()
# 获取查询字段名称
col = cursor.description
# 获取字段名,以列表形式保存
columns = []
for i in range(len(col)):
columns.append(col[i][0])
# print("字段名", i, col[i][0])
# 按行插入查询到的数据
df = pd.DataFrame(columns=columns)
for i in range(len(res)):
df.loc[i] = list(res[i])
# 关闭数据库连接
cursor.close()
df.to_excel(writer, sheet_name='Sheet1', index=False, header=True)
print("数据读取完成,保存地址为:" + filename)
writer.save()
# writer.close()
return filename
if __name__ == "__main__":
###path = 'E:\\output_data' ###############本地运行路径
path ='/tmp' ###############海豚里面的路径
os.chdir(path)
##################################################修改###############################################################
sql_select1 = """
SELECT
a."ZGARAGE" as "维修厂CODE",
a."ZCC00174" as "维修厂名称",
a."ZMGTRGIN_T" as "连队",
a."ZMGTCITY_T" as "网格",
a."TYRE_CUSTYPE" as "轮胎客户分级",
sum(a."ZCURM_GTV_MP") AS "当月马牌GTV",
sum(a."ZCURM_INQGTV_MP") AS "当月马牌询价GTV",
sum(a."ZCURM_GTV_MPQJD") AS "当月马牌旗舰店GTV",
sum(a."ZCURM_INQGTV_MQL") AS "当月米其林GTV",
sum(a."ZCURM_INQGTV") AS "当月米其林询价GTV",
sum(a."ZCURM_GTV_BNL") AS "当月倍耐力GTV",
sum(a."ZCURM_INQGTV_BNL") AS "当月倍耐力询价GTV",
sum(a."ZCURM_REINQC") AS "当月复询客户数",
sum(a."ZCURM_REORDC") AS "当月复购客户数",
sum(a."ZBEF3M_REINQC") AS "前三月复询客户数",
sum(a."ZBEF3M_REINQOC") AS "前三月询价复购客户数",
sum(a."ZCURM_TINQCUS") AS "当月仅询价下单客户"
FROM "_SYS_BIC"."CassTime.ROBOT.BAQ/ZQCV_ROBOT_002_TYRE_GARAGE5M_ZLR" as a
"""
####################################################################################################################
outputpath = "维修厂维度近5月交易分析.xlsx" ####################################修改 文件名称
filename = read_hanaData(sql_select1, outputpath)
wx_api_key = "07b79772-16bc-4f81-b256-12bec2a6941e" ####################修改 这个地方写webhook中key=的值
#<https://qyapi.weixin.qq.com/cgi-bin/webhook/send?key=07b79772-16bc-4f81-b256-12bec2a6941e>
wx_upload_url = "<https://qyapi.weixin.qq.com/cgi-bin/webhook/upload_media?key={}&type=file>".format(wx_api_key)
wx_url = '<https://qyapi.weixin.qq.com/cgi-bin/webhook/send?key={}>'.format(wx_api_key)
media_id = upload_file(filename, wx_upload_url)
print('开始发送文件')
qi_ye_wei_xin_file(wx_url, media_id)
print('文件发送结束')
第4节 EXCEL处理工具-xlsxwriter
1、简介
Xlsxwriter 是一个用于编写 Excel 文件格式文件的 Python 模块,xlsxwriter 可以用来写文本,数字,公式和超链接到多个工作表,它支持的功能也有很多,譬如格式化、单元格合并、图标功能等
2、表格样式
import xlsxwriter
from datetime import datetime
# 创建工作簿
workbook = xlsxwriter.Workbook('测试文件.xlsx')
# 创建工作表
worksheet1 = workbook.add_worksheet('这是sheet1')
worksheet2 = workbook.add_worksheet('这是sheet2') # Sheet2
worksheet3 = workbook.add_worksheet('test-sheet2')
worksheet4 = workbook.add_worksheet() # Sheet4
# 设置单元格样式
format = {
'font_size': 10, # 字体大小
'bold': True, # 是否粗体
# 'bg_color': '#101010', # 表格背景颜色
'fg_color': '#00FF00',
'font_color': '#0000FF', # 字体颜色
'align': 'center', # 水平居中对齐
'valign': 'vcenter', # 垂直居中对齐
# 'num_format': 'yyyy-mm-dd H:M:S',# 设置日期格式
# 后面参数是线条宽度
'border': 1, # 边框宽度
'top': 1, # 上边框
'left': 1, # 左边框
'right': 1, # 右边框
'bottom': 1 # 底边框
}
style = workbook.add_format(format) # 设置样式format是一个字典
worksheet2.set_row(0,60) # 设置行宽
worksheet2.set_column(1,2,30) # 设置列宽
worksheet2.set_column('D:F',40)# 设置列宽
worksheet2.set_tab_color('#0000FF') #设置设置sheet页标签颜色
#worksheet2.insert_image('F3', '00.jpg', {'url': '<https://image.baidu.com>'})
# 写入数据 使用行列方式,下标从0开始
worksheet1.write(0, 0, '未合并') # 第一行第一列,A1写入'未合并'
worksheet1.write(2, 0, '会被覆盖') # 第三行第一列,A3写入'会被覆盖'
worksheet1.write(4, 0, '待合并') # 第五行第一列,A5写入'待合并'
worksheet1.merge_range(1,5,3,6,'合并01') # 合并第二行-四行,第三列-五列,即:C2:E4
worksheet1.merge_range('A3:B3','覆盖它') # 合并A3:B3,并写入'覆盖它'
worksheet1.merge_range('A5:B5','') # 合并A5:B5,'待合并'并不被覆盖
worksheet2.write(2, 0, 100) # 第3行第1列(即A3) 写入100
worksheet2.write(3, 0, 99.8) # 第4行第1列(即A4) 写入99.8
worksheet2.write(4, 0, '=SUM(A3:A4)') # 写上excel公式
worksheet2.write_number(5, 0, 1001) # worksheet2.write_number:写入数字Int or float
worksheet2.write_blank(6, 0,'xx') # worksheet2.write_blank:写入空格不管第三个参数是什么,都写入空格
worksheet2.write_formula(7, 0, '=SUM(A3:A4)') # worksheet1.write_formula:写入公式
worksheet2.write_boolean(9, 0,False) # worksheet2.write_boolean:写入逻辑数据
worksheet2.write_url(10, 0,'www.baidu.com') # worksheet2.write_url:写入链接地址
from datetime import datetime # worksheet2.write_datetime:写入时间格式
date_format = workbook.add_format({'num_format': 'yyyy-mm-dd H:M:S'})
worksheet2.write_datetime(8, 0, datetime.today(),date_format )
lst=['12','34','56','78']
worksheet2.write_row(13, 0, lst) # write_row 写入 把数据写到第13行的,从第1列开始到第4列上
worksheet2.write_column(6, 3,lst) # write_column 写入 把数据写到第4列,从第7行开始到第10行上
worksheet2.write('C1',200,style)
worksheet2.write(2,2,900,style)
#读取数据
import pandas as pd
import numpy as np
df=df=pd.DataFrame(
{'A':['孙云','郑成','冯敏','王忠','郑花','孙华','赵台','王花','黄成','钱明','孙宇'],
'B':[79,70,39,84,87,26,29,47,32,22,99],
'C':[28,77,84,26,29,47,32,22,99,76,44],
'D':[18,53,78,41,36,88,79,49,54,25,14]})
df.style.set_caption("三年级二班学习成绩表") #添加标题
#1、把出成绩超过80的分数用红色标注出来
def color_negative_red(val):
color='red' if val>80 else 'black'
return 'color:%s' %color
df.style.applymap(color_negative_red)
#将科目分数小于60的,用红色进行高亮显示
df.style.applymap(lambda x:'background-color:red' if x<60 else '',subset=pd.IndexSlice[:,['B','C','D']])
#2、背景高亮
df1=df.copy()
df1.iloc[1,1]=np.NaN
df1.iloc[2,1]=np.NaN
df1.style.highlight_null(null_color='yellow') #.highlight_null空值高亮显示 null_color参数指定高亮颜色
df1.set_index('A').style.highlight_max(color='yellow')#每个科目的最高/低分高亮
df1.set_index('A').style.highlight_max(color='yellow',subset=['B']) #subset只对一列高亮展示
df1.set_index('A').style.highlight_max(color='green').highlight_min(color='red')
df1.set_index('A').style.highlight_max(color='green',axis=1).highlight_min(color='red',axis=1)
#4、背景渐变
df.style.background_gradient() #颜色越深,成绩越高
df.style.background_gradient(subset=['B'],cmap='BuGn') #指定列、指定颜色
df.style.background_gradient(low=0.5,high=0) #低百分比和高百分比范围,更换颜色时避免使用所有色域,避免深色太深
df.style.background_gradient(vmin=60,vmax=100) #60-100的数值渐变
#标记总分低于120的分数
(df.set_index('A').assign(sum_s=df.set_index('A').sum(axis=1))).style.applymap(lambda x:'backgrounp-color:yellow' if x<50 else '',subset=pd.IndexSlice[:,['sum_s']]).format({'avg':"{:.2f}"})
3、数据图表
# 三:数据准备
row_data=['姓名','数学','英语','语文']
col_data = [
['张三','李四','老王','德华','赵四'],
[90, 85, 120, 130, 99],
[70, 65, 120, 109, 110],
[60, 95, 130, 120, 79]
]
worksheet3.write_row('A1',row_data)
worksheet3.write_column('A2',col_data[0])
worksheet3.write_column('B2',col_data[1])
worksheet3.write_column('C2',col_data[2])
worksheet3.write_column('D2',col_data[3])
# 四:创建chart对象
chart = workbook.add_chart({'type':'line'}) # 线条样式的图表
# chart = workbook.add_chart({'type':'column'}) # 柱状图
# chart = workbook.add_chart({'type':'area'}) # 面积样式的图表
# chart = workbook.add_chart({'type':'stock'}) # 股票样式的图表
# chart = workbook.add_chart({'type':'bar'}) # 条形图
# chart = workbook.add_chart({'type':'pie'}) # 饼形图
# chart = workbook.add_chart({'type':'scatter'}) # 散点图
# chart = workbook.add_chart({'type':'radar'}) # 雷达样式的图表
#categories:设置图表类别标签范围;
#values:设置图表数据范围;
#line:设置图表线条属性,包括宽度、颜色等;
chart.add_series({
'name':'=test-sheet2!$B$1',
'categories': '=test-sheet2!$A$2:$A$6',
'values': '=test-sheet2!$B$2:$B$6',
'line': {'color': 'red'},
'pie': {'color': 'red'},
})
chart.add_series({
'name':'=test-sheet2!$C$1',
'categories': '=test-sheet2!$A$2:$A$6',
'values': '=test-sheet2!$C$2:$C$6',
'line': {'color': 'yellow'},
'pie': {'color': 'yellow'},
})
chart.add_series({
'name':'=test-sheet2!$D$1',
'categories': '=test-sheet2!$A$2:$A$6',
'values': '=test-sheet2!$D$2:$D$6',
'line': {'color': 'blue'},
'pie': {'color': 'blue'},
})
chart.set_title({'name':'测试'})
chart.set_x_axis({'name':"x轴"})
chart.set_y_axis({'name':'y轴'}) #设置图表表头及坐标轴
chart.set_style(1)
# chart.set_style(37)
worksheet3.insert_chart('A3',chart,{'x_offset':25,'y_offset':10}) #放置图表位置
workbook.close()