一道简单的电商数据分析笔试题：求组合商品的库存量

转自公众号可以叫我才哥

今天在粉丝交流群里有群友询问了一个已知每个货品的库存，求组合商品（就是多个货品捆绑组合成一个组合商品）的库存量。逻辑上讲，应该是组合商品里货品库存最低的那个货品存量决定整个组合商品的库存量（水桶原理），那么如何实现呢？

事实上，这个计算比较简单，但是里面还是有一些坑，大家可以阅读本例了解一下！

1. 需求分析

咱们先看看原始数据，然后再进行需求拆解。

货品库存数据（去掉了其他无关信息）

货品库存

组合商品数据（库存字段为待求数据）

组合商品

通过看两份原始数据，其实我们都能在脑海里有计算逻辑了，在excel里先通过vlookup匹配每个组成商品对应库存量，然后再按照组合商品进行透视分组计算库存量最小值即可。

基于以上思路，大家可以自己试试用excel透视表进行处理。不过，本文我们也会提供两种方式才进行处理，分别是python和excel数据合并计算。

2. 计算过程

我们先介绍用python实现本案例，同样的python实现的方式也有多种，核心思路就是先匹配每个组成商品的库存量，然后再求出分组里各商品库存量最小值即可。

2.1. python计算过程

核心：pandas的merge和transform函数方法

import pandas as pd

# 读取数据
df = pd.read_excel(r'案例数据.xlsx', sheet_name='组合商品')
df1 = pd.read_excel(r'案例数据.xlsx', sheet_name='总库存')

# 由于组合商品中存在空行，所以这里删除，并采用向上填充的方式填充组合商品字段的空值
temp = df.dropna(how='all').ffill()
temp.head()

组合商品预览

在组合商品中存在部分组合商品里的其实是同一种商品的N件组合，这种情况下算库存是需要除以N的，这也是本案例的一个可能踩到的坑。

N件组合装

所以，这里我们需要对组合商品字段进行简单的数据分列拆分（可以参考此前推文《一看就会的Pandas文本数据处理》）。

temp['货品编号'] = temp['组成商品'].str.split('*',expand=True)[0]
# 注意货品数量字段类型转换为 数值类型
temp['货品数量'] = temp['组成商品'].str.split('*',expand=True)[1].fillna(1).astype('int')
temp.head()

接着，我们将每个货品对应库存量匹配上来，用到merge函数。

# 货品库存预览
df1.sample(5)

temp = temp.merge(df1,how='left')
# 由于每个组合商品是多个商品按照一定数量组合的，所以实际库存量应该除以每个货品在组合里的数量
temp['库存量'] = temp['库存量']//temp['货品数量']
temp.head()

到这里，我们就好处理组合商品的库存了（就是其对应组成商品的库存量最小值）。大家会怎么来计算呢？首先想到的可能是分组或透视的形式，当然这里用到的是transform，大家可以用别的试试哈。

temp['库存'] = temp.groupby('组合商品')['库存量'].transform('min')
temp.head()

2.2. excel操作过程

这里也是简单介绍一种操作技巧抛砖引玉，大家可以补充更多方案哈。

先去掉空行

然后将组合商品字段填充

=IF(B3="",A2,B3)

接着对组合商品表中组成商品分列（按照）*

分列

再用vlookup获取每个组成商品的的库存量

计算对组合商品而言实际库存（除以单组成商品数量）

=ROUNDDOWN(IFERROR(E2/D2,E2),0)

最后，菜单栏 数据->合并计算 来求值

合并计算

计算设置

结果预览

以上就是一种excel的操作技巧，这里用到的是数据->合并计算！！

3. 总结

其实，本案例实际上并没有什么难度，而且我们的解决方案也有很多种，核心思路就是匹配+分组的思想。大家可以基于此，结合自己对工具的理解，试试更多种解法来强化哈！

案例数据大家可以扫描下方二维码添加好友，并回复：电商笔试，获取数据。

长按识别二维码