在特定列之间旋转，然后用NaN填充Pandas DataFrame -案例研究

Question

这个问题是关于旋转和填充栏，这两个非常频繁的活动在潘达。

我有个原始数据。我需要从long操作到wide，然后根据特定的规则填充NaN。

我的代码可以工作，但是我认为它不是高效和优雅的，而且它不能像我最后强调的那样泛化。

在来到这里并编写工作代码之前，我在堆栈上阅读了一些非常有价值的问题，如下所示：

• 如何扁平化列中的层次索引
• 我怎样才能把数据转换成支点？
• 从长到宽重塑大熊猫数据时的自定义变量名称

幸运的是，我达到了我的目标，但以一种非常糟糕的方式，这就是我来这里的原因。

但让我们开始展示这个丑陋的代码。

# Our df for this example
dict_df = {"Time":[1,1,1,1,2,2,2,2,3,3,3,3] ,
           "KPI":["A","B","C","D","A","B","C","D","A","B","C","D"],
           "SKU1":[10,1,0.1,0.01,40,4,0.4,0.04,90,9,0.9,0.09],
           "SKU2":[20,2,0.2,0.02,50,5,0.5,0.05,100,10,1,0.1],
           "SKU3":[30,3,0.3,0.03,60,6,0.6,0.06,110,11,1.1,0.11],
           "SKU4":[70,7,0.7,0.07,80,8,0.8,0.08,120,12,1.2,0.12]
           }

df_test = pd.DataFrame(dict_df)

    Time    KPI     SKU1    SKU2    SKU3    SKU4
   0    1   A      10.00    20.00   30.00   70.00
   1    1   B      1.00     2.00    3.00    7.00
   2    1   C      0.10     0.20    0.30    0.70
   3    1   D      0.01     0.02    0.03    0.07
   4    2   A      40.00    50.00   60.00   80.00
   5    2   B      4.00     5.00    6.00    8.00
   6    2   C      0.40     0.50    0.60    0.80
   7    2   D      0.04     0.05    0.06    0.08
   8    3   A      90.00    100.00  110.00  120.00
   9    3   B      9.00     10.00   11.00   12.00
   10   3   C      0.90     1.00    1.10    1.20
   11   3   D      0.09     0.10    0.11    0.12

我想过滤特定的KPI：

filter_kpi = ["A","B","C"]
df_test_filtered = df_test[df_test["KPI"].isin(filter_kpi)]
#Setting Time column as index
df_test_filtered.index = df_test_filtered.Time

#Pivoting the dataframe
pivot_test = df_test_filtered.pivot(values = ["SKU1","SKU2","SKU3","SKU4"],
                                    columns ="KPI", 
                                    index="Time")
print(pivot_test)

在此之后，我根据“自定义变量名(当重塑时)”的问题来扁平化列：

pivot_test.columns = [''.join(col) for col in pivot_test.columns]

得到了这个输出：

    SKU1A   SKU1B   SKU1C   SKU2A   SKU2B   SKU2C   SKU3A   SKU3B   SKU3C   SKU4A   SKU4B   SKU4C
Time                                                
1   10.0    1.0     0.1     20.0    2.0     0.2     30.0    3.0     0.3     70.0    7.0     0.7
2   40.0    4.0     0.4     50.0    5.0     0.5     60.0    6.0     0.6     80.0    8.0     0.8
3   90.0    9.0     0.9     100.0   10.0    1.0     110.0   11.0    1.1     120.0   12.0    1.2

现在我代码中丑陋的部分。目标是用3列Nan (但可以是任意固定的列数)填充每个SKU之间的空间。

index现在由3行组成，但可以是4,2或任何其他长度。

所以在集SKU1_，SKU2_等..。

# I initialize an empty DataFrame
df_empty = pd.DataFrame()

# I am defining the range that will define when to split the original dataframe
data_range = np.arange(0,len(pivot_test.columns),3)

#Creating the index for the empty dataframe that will be used for padding
df_nan_len =np.arange(1,len(pivot_test)+1,1)

df_nan = pd.DataFrame(np.nan, index=df_nan_len, columns=['0', '1','2'])


#loop for each element in the range
for i in data_range:
  #Splitting the DataFrame 
  filter = pivot_test.iloc[:,i:i+3]
  df_empty = pd.concat([df_empty,filter],
                             axis =1)
  df_empty = pd.concat([df_empty,df_nan],
                             axis =1)

从那我得到了我的最后输出：

     SKU1A  SKU1B   SKU1C   0       1       2   SKU2A   SKU2B   SKU2C       0   ...     SKU3C       0       1       2   SKU4A   SKU4B   SKU4C       0       1       2
1   10.0    1.0     0.1     NaN     NaN     NaN     20.0    2.0     0.2     NaN     ...     0.3     NaN     NaN     NaN     70.0    7.0     0.7     NaN     NaN     NaN
2   40.0    4.0     0.4     NaN     NaN     NaN     50.0    5.0     0.5     NaN     ...     0.6     NaN     NaN     NaN     80.0    8.0     0.8     NaN     NaN     NaN
3   90.0    9.0     0.9     NaN     NaN     NaN     100.0   10.0    1.0     NaN     ...     1.1     NaN     NaN     NaN     120.0   12.0    1.2     NaN     NaN     NaN

我已经知道，如果我有不同数量的KPI(例如4或2)，那么这段代码将不能正常工作。

代码工作，但我认为循环，我如何垫NaN，这是我可以改进的东西。

感谢您的时间和耐心！

更多信息和上下文

数据来自IRI数据源。KPI1，KP2，KP3只是描述我得到的一些信息的一种方式：

• 价格欧元/数量
• 加权分配销售
• 每个企业欧元的标准化销售
• 每个业务数量的标准化销售
• 单件标准化销售

以此类推，它们是8，但我想创建一个工作最小重复性示例，这就是为什么我只选择了3个变量。

我为什么要垫？因为我继承和使用了excel文件，其中这些信息是以这种方式填充的，并且在以*xlsx格式保存dataframe格式之后，我每个月都需要将新数据附加到“主excel数据簿”中。

这只是给你举个例子。

为什么我不能只使用一个联接操作，打开excel文件并读取它？在计划里，我的最终目标。我必须首先修复一些日期问题，因为我接收到的数据是用这个时间表示法KW 14/2022排序的，为了使用最后的符号21/03/2022，我必须更改它作为键。

到目前为止，他们是如何处理“更新过程”的？他们使用一个excel表，在其中一个选项卡中粘贴数据，在一个选项卡中扩展行，它返回已经填充的最终结果。

我的目标是在Python中创建一个数据管道，以自动处理每月的数据更新。

这个填充解决方案可以帮助我在下一个截止日期快速复制和粘贴这些数据。

这是过程数据质量的低效吗？是的，我真的很努力去修复它，但这不是我在一开始就决定的事情。

再次感谢您的建议和时间。希望能帮上忙。

Answer 1

我相信有人会想出一个比这更好的解决方案，但希望这里的一些想法是有帮助的。从这个数据文件开始：

    SKU1A   SKU1B   SKU1C   SKU2A   SKU2B   SKU2C   SKU3A   SKU3B   SKU3C   SKU4A   SKU4B   SKU4C
Time                                                
1   10.0    1.0     0.1     20.0    2.0     0.2     30.0    3.0     0.3     70.0    7.0     0.7
2   40.0    4.0     0.4     50.0    5.0     0.5     60.0    6.0     0.6     80.0    8.0     0.8
3   90.0    9.0     0.9     100.0   10.0    1.0     110.0   11.0    1.1     120.0   12.0    1.2

我们希望与SKU一起工作，因此可能最简单的方法是将其转换并重置索引，这样我们就可以访问列中的SKU。所以我们可以用

df = df.T
df.index.name = "SKUs"
df = df.reset_index()

要获得

     SKUs     1     2      3
0   SKU1A  10.0  40.0   90.0
1   SKU1B   1.0   4.0    9.0
2   SKU1C   0.1   0.4    0.9
...

接下来，我们希望将每个SKU按初始SKU部件分组，我们可以使用

group = df.groupby(df["SKUs"].str.extract("(SKU\d+)", expand=False))

在这里，正则表达式"(SKU\d+)"表示要捕获字母SKU，后面跟着任意数目的连续数字。这意味着SKU11A将如您所期望的那样被处理。现在，我们可以通过以下方式获得各个组：

groups = [group.get_group(x) for x in group.groups]

正如您所预期的那样，它为每个SKU提供了一个单独的数据(例如，用于SKU1、SKU2等的数据)。

现在我来了一个丑陋的部分。

最终，我们希望在组中的元素和所需结构的空数据之间交替使用。我们可以创建如下所示的空数据：

desired_num_cols = 3
empty_df = pd.DataFrame(
    [[pd.NA] * groups[0].shape[1]] * desired_num_cols,
    columns=groups[0].columns
)
empty_df['SKUs'] = list(range(desired_num_cols))

现在，我们只需要以一种交替的方式将其与来自组的元素连接起来，我们可以这样做：

df = pd.concat(
    itertools.chain(
        *zip(
            *[
                groups,
                itertools.cycle([empty_df]),
            ]
        )
    )
)

分解：

• itertools.cycle([empty_df])只会给出一个empty_df，直到groups耗尽为止。
• 内部zip将(group, empty_df)的元组打包在一起。
• 然后，itertools.chain(*...)解压缩这些元组，这样我们就有了[group1, empty_df, group2, empty_df, ...]。

现在将索引设置为SKUs和transpose：

df = df.reset_index(drop=True).set_index("SKUs")
df.T

我们得到了你的最后答案：

SKUs  SKU1A  SKU1B  SKU1C   0   1   2  SKU2A  SKU2B  SKU2C   0   1   2  SKU3A  SKU3B  SKU3C   0   1   2  SKU4A  SKU4B  SKU4C   0   1   2
1      10.0    1.0    0.1 NaN NaN NaN   20.0    2.0    0.2 NaN NaN NaN   30.0    3.0    0.3 NaN NaN NaN   70.0    7.0    0.7 NaN NaN NaN
2      40.0    4.0    0.4 NaN NaN NaN   50.0    5.0    0.5 NaN NaN NaN   60.0    6.0    0.6 NaN NaN NaN   80.0    8.0    0.8 NaN NaN NaN
3      90.0    9.0    0.9 NaN NaN NaN  100.0   10.0    1.0 NaN NaN NaN  110.0   11.0    1.1 NaN NaN NaN  120.0   12.0    1.2 NaN NaN NaN

Answer 2

您的问题的前提假设了您不应该使用的数据框架的结构，至少在数据处理方面是如此。您似乎对需要导出的Excel文件的格式没有任何影响。

想想你的画框描述中的“每”S:每一次，每KPI，每SKU，你以一些未知的浮点值开始(也许是一个价格；谁知道)。无论何时写"per"，都应该成为多层索引中的一个级别。这将您的数据压缩为一列，即"Price“。

考虑到您的"pad“操作，您实际上只是添加了第二列(更不清楚的用途)：

import pandas as pd

# Our df for this example
dict_df = {
    "Time": [1,1,1,1,2,2,2,2,3,3,3,3],
    "KPI":  ["A","B","C","D","A","B","C","D","A","B","C","D"],
    "SKU1": [10,1,0.1,0.01,40,4,0.4,0.04,90,9,0.9,0.09],
    "SKU2": [20,2,0.2,0.02,50,5,0.5,0.05,100,10,1,0.1],
    "SKU3": [30,3,0.3,0.03,60,6,0.6,0.06,110,11,1.1,0.11],
    "SKU4": [70,7,0.7,0.07,80,8,0.8,0.08,120,12,1.2,0.12]
}
df_test = pd.DataFrame(dict_df)

# Reshape data to be in stacked, multi-index form
df_test.set_index(['Time', 'KPI'], inplace=True)
df_test.columns = df_test.columns.str.slice(3).astype(int)
df_test = df_test.stack()
df_test.index.set_names('SKU', level=-1, inplace=True)
df_test = pd.DataFrame({'Price': df_test})

filter_kpi = {"A", "B", "C"}
df_test = df_test[df_test.index.get_level_values('KPI').isin(filter_kpi)]

df_test['Mystery'] = pd.NA
print(df_test)

              Price Mystery
Time KPI SKU               
1    A   1     10.0    
         2     20.0    
         3     30.0    
         4     70.0    
     B   1      1.0    
         2      2.0    
         3      3.0    
         4      7.0    
     C   1      0.1    
         2      0.2    
         3      0.3    
         4      0.7    
2    A   1     40.0    
         2     50.0    
         3     60.0    
         4     80.0    
     B   1      4.0    
         2      5.0    
         3      6.0    
         4      8.0    
     C   1      0.4    
         2      0.5    
         3      0.6    
         4      0.8    
3    A   1     90.0    
         2    100.0    
         3    110.0    
         4    120.0    
     B   1      9.0    
         2     10.0    
         3     11.0    
         4     12.0    
     C   1      0.9    
         2      1.0    
         3      1.1    
         4      1.2