This question is about pivoting and padding columns, two very frequent activities in Pandas.

I have a raw dataframe. I need to manipulate from `long` to `wide` and then pad `NaN` based on a specific rule.

My code works, but I think is not efficient and elegant, plus it is not able to generalize as I highlighted at the end.

Before coming here and having working code I read some very valuable questions on stack like the following ones:

* [How to flatten a hierarchical index in columns](https://stackoverflow.com/questions/14507794/how-to-flatten-a-hierarchical-index-in-columns)
* [How can I pivot a dataframe?](https://stackoverflow.com/questions/47152691/how-can-i-pivot-a-dataframe)
* [Custom variable names when reshaping pandas dataframe from long to wide](https://stackoverflow.com/questions/60155889/custom-variable-names-when-reshaping-pandas-dataframe-from-long-to-wide)

And fortunately I reached my goal, but in a very bad way and that's why I am here.

But let's start showing this ugly code.

```
# 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

```

I want to filter specific KPIs:

```
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)

```

After that I flatten the columns based on the question "Custom variable names when reshaping":

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

```

And got this output:

```
    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

```

And now the ugly part of my code.
The goal is to pad with 3 columns of `Nan` (but can be any fixed number of columns) the space between each SKU.

The `index` now is made by 3 rows but can be of 4,2 or any other length.

So between the set `SKU1_`, `SKU2_` etc...

```
# 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)

```

And from that I get my final output:

```
     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

```

I am already aware that if I had a different number of KPIs for example 4 or 2 this code will not work properly.

The code works, but I think the loop, how I pad the `NaN`, it is something I can improve.

Thank you for your time and patience!

More information and context
----------------------------

The data come from the [IRI data source](https://www.iriworldwide.com/en-gb).
KPI1, KP2, KP3 are just a way to describe some of the information that I get:

* Price Euro/Quantity
* Weighted distribution selling
* Standardized sales per business Euro
* Standardized sales per business quantity
* Standardized sales per business piece

And so on, they are 8 but I wanted to create a [working minimum reproducible](https://stackoverflow.com/help/minimal-reproducible-example) example and that's why I choose only 3 variables.

Why I need to pad? Because I inherited and excel file where this information are padded in that way and I need to attach each month the new data to the "master excel data book", after saving in `*xlsx` format the `dataframe`

This is just to give you an example.
[![enter image description here](https://i.stack.imgur.com/WDKXz.png)](https://i.stack.imgur.com/WDKXz.png)

Why I can't use just a join operation, opening the excel file and reading it? It's in the plan, my final goal. I had to fix some date issue first, because the data I receive are ordered with this time notation `KW 14/2022` that I have to change in order to use as a key with the final notation `21/03/2022`

How they handled the "updating process" until now? They used an excel table where in one tab you paste the data, in one tab you extend the rows and it returns the final result already padded.

My goal is to create a Data Pipeline in Python to automatically handle this monthly data update.

This padding solution helps me to copy and paste quickly this data for the next deadline.

It is this process data quality inefficient? Yes, I am working really hard to fix it, but it is not something I decided at the beginning.

Thank you again for the suggestions and time.  

Hopes it help.