【月度总结】数据库&Python&Excel_202207

Excel

1、maxifs()函数

maxifs函数返回一组给定条件或标准指定的单元格中的最大值。

maxifs(数据区域，条件区域，条件)

技巧：

选取某个分类中占比最大的值作为这个分类的唯一值。比如：选取sku编码，单价占比最大的，作为sku编码的唯一价格。

1、使用EXcel来解决：

• 首先数据清洗，计算sku编码占比。只选择某个店铺某个月份的数据，并提取sku编码，单价，将所有数据去重，使用countifs()函数计数，再计算占比，字段呈现：sku编码，单价，个数，个数占比。
• 其次找出sku占比的最大值。提取sku编码去重，使用maxifs()函数，找出最大的占比值，字段呈现：sku编码，最大占比。
• 找出sku编码占比最大的单价。字段：使用vlookup()函数，合并sku编码&最大占比，查找单价，字段呈现：sku编码，单价。

2、使用Python来解决

数据库

1、取今日十一点这个时间，使用dateadd()函数

select  DATEADD(hour,11,convert(varchar(100),GETDATE(),23))  as '时间'

Python

1、取某个字段中的某部分

        以取商品名称中【】里面的内容为例，商品名称的格式例如：成品台历【A236-未来可期】

• 使用正则匹配   

data1['商品名称'].map(lambda x:re.findall("【(.*?)】",x)[0] if len(re.findall("【(.*?)】",x)) > 0 else '无')

• 使用split()拆分

data1['商品名称'].map(lambda x :(x.split('【')[1]).split('】')[0] if len((x.split('【')[1]).split('】')) >0 else '无')

2、使用np.where()函数对字段判断

np.where(condition,x,y)

满足条件(condition)，输出x，不输出满足y。

有以下两种写法：

其一：

data1['商品分类'] =np.where(data1['商品最小分类'] =='环装台历' ,'定制台历',

np.where(data1['商品最小分类'] == '无环台历','定制台历',

np.where(data1['商品最小分类'] == '商务台历','定制台历','成品台历')))

其二：

data1['商品分类'] = np.where(data1['商品最小分类'].isin(['环装台历','无环台历','商务台历']) ,'定制台历','成品台历')

3、对数据中的某些行进行处理

data1.loc[data1['商品分类'] == '定制台历','款式'] = '无'

4、根据两列数据，对另外一列赋值

在修改邮件过程中遇到的最大问题：是对于款式的定义

最终实现的代码为（注意：写自定义的函数用来判断值就可以了）：

def style(x,y):

    if x =='成品台历':

        if len(re.findall("【(.*?)】",y)[0]) >0:

            return  re.findall("【(.*?)】",y)[0]

        else:

            return '无'

    else:

        return '无'

data1['款式'] = data1.apply(lambda x:style(x.商品分类,x.商品名称),axis=1)

5、将各数据源合并在一起（纵向）

Data_y = pd.concat([Data1, Data3])  # 将各来源的数据合并在一起

6、将几个DataFrame横向拼接

data_re = data_zy.join(data_dy).join(data_['name']) # 将几个DataFrame横向拼接

7、构建空的DataFrame

data2 = pd.DataFrame(columns = [['name','导演','主演']])  # 定义一个空的DataFrame,并定义列名

8、重命名列名

df.columns = ['name','导演','主演']

9、不输出，警告信息

import warnings

warnings.filterwarnings('ignore')

10、四分位数

q1 = data[col].quantile(q = 0.25)  # 下四分位数

q3 = data[col].quantile(q = 0.75)  # 上四分位数

q = q3 - q1  #四分位差

11、header = 0

header = 0 表示，第一行数据，而不是文件的第一行。

12、箱线图构成

    箱线图是由5部分组成，分别是最小值、中位数、最大值和两个四分位数。第一个”四分位数Q1“：又称下四分位数，等于该样本中所有数值由小到大排列后第25%的数字。中位数F：又称第二四分位数（Q2），又称“中位数”，等于该样本中所有数值由小到大排列后第50%的数字。第三四分位数：又称“上四分位数”，等于该样本中所有数值由小到大排列后第75%的数字。

13、bokeh的官方参考网站Configuring plot tools — Bokeh 2.4.3 Documentation

#图表的画法

p  = figure(plot_width = 600,plot_height = 400)

p.circle(df.index,df['A'],color = 'green',size = 10,alpha = 0.5)

#### 框线

p.outline_line_width = 7 #边框线宽

p.outline_line_alpha = 0.3 #边框透明度

p.outline_line_color = 'navy'   #边框线颜色

# 绘图背景

p.background_fill_color = 'yellow'   # 绘图背景颜色

p.background_fill_alpha = 0.5   # 绘图背景透明度

#外边界设置

p.border_fill_color = "red"  # 外边界背景

p.min_border_left = 80  # 外边界背景 -----左边宽度

p.min_border_right= 80  # 外边界背景 -----右边宽度

p.min_border_top = 10  # 外边界背景 -----上宽度

p.min_border_bottom = 10  # 外边界背景 -----下宽度

#轴线的设置

# 设置X轴线：标签，线宽，轴线颜色

p.xaxis.axis_label  ='Temp'

p.xaxis.axis_label_text_font_style ="FangSong"  #设置字体

p.xaxis.axis_label_standoff = 30 # 设置偏移距离

p.xaxis.axis_line_width = 3

p.xaxis.axis_line_color = "red"

# 设置y轴线：标签，字体颜色,字体角度

p.yaxis.axis_label  ='PPPP'

p.yaxis.major_label_text_color = "red"

p.yaxis.major_label_orientation = "vertical"

p.axis.minor_tick_in = 5   # 刻度往绘图区域内延伸长度

p.axis.minor_tick_out = 3  # 刻度往绘图区域外延伸长度

p.xaxis.bounds =(2,4)  #设置轴线范围

####格网设置

p.xgrid.grid_line_color  =None # 颜色设置，None时则不显示

p.ygrid.grid_line_alpha = 0.8  # 设置透明度

p.ygrid.grid_line_dash  = [6,4]  # dash--->通过设置间隔来做虚线

#设置次轴线  minor_line

p.xgrid.minor_grid_line_color = 'navy'

p.xgrid.minor_grid_line_alpha = 0.5  

####格网颜色填充

p.ygrid.band_fill_alpha = 0.1

p.ygrid.band_fill_color = 'navy'

shw(p)

p  = figure(plot_width = 600,plot_height = 400)

#图例设置

x = np.linspace(0,4*np.pi,100)

y = sin(x)

14、bokeh辅助矩形：

# 辅助标注 - 矩形

from bokeh.models.annotations import BoxAnnotation

# 导入BoxAnnotation模块

center = BoxAnnotation(top=0.6, bottom=-0.3, left=7, right=12,  # 设置矩形四边位置

                       fill_alpha=0.1, fill_color='navy'        # 设置透明度、颜色

                      )

p.add_layout(center)

# 绘制辅助矩形

show(p)

15、转字典格式：

exports = {'fruits' : fruits,

           '2015'   : [2, 1, 4, 3, 2, 4],

           '2016'   : [5, 3, 4, 2, 4, 6],

           '2017'   : [3, 2, 4, 4, 5, 3]}