reformat numbers stored in array

Question

the input is:

need output as:

is this possible in just XL format or a python script is required. if yes can you help with python script? thanks.

my script: my logic is read each df.col and compare value from 1 to 20, if equal write it out or write blank.

import pandas as pd
df = pd.read_excel(r"C:\Users\my\scripts\test-file.xlsx")

print(df)

for column in df.columns[0:]:
    print(df[column])

Answer 1

Here's one approach:

Sample df

import pandas as pd
import numpy as np

np.random.seed(0) # for reproducibility

df = pd.DataFrame({f'col_{i}': np.random.choice(range(1, 10), 
                                                size=4, 
                                                replace=False) 
                   for i in range(1, 4)})

df

   col_1  col_2  col_3
0      6      3      6
1      3      7      8
2      4      8      5
3      5      6      7

Code

max_value = df.values.max()

out = df.stack()

idx = pd.MultiIndex.from_arrays([out.index.get_level_values(1), 
                                 out.values])

out = (out
       .set_axis(idx)
       .unstack(0)
       .reindex(range(1, max_value + 1))
       )

out

   col_1  col_2  col_3
1    NaN    NaN    NaN
2    NaN    NaN    NaN
3    3.0    3.0    NaN
4    4.0    NaN    NaN
5    5.0    NaN    5.0
6    6.0    6.0    6.0
7    NaN    7.0    7.0
8    NaN    8.0    8.0

# write away to excel with `df.to_excel`

Explanation

• Create a variable (max_value) for the max value in the df (to be used later for reindexing).
• Apply df.stack to get all your columns as one stacked pd.Series (here: out).
• Now, create a new index (idx) using pd.MultiIindex.from_arrays. For the first array pick the second level from the index (use: pd.MultiIndex.get_level_values); for the second array, use the values.
• Now, set idx as the index with Series.set_axis, apply Series.unstack, and use Series.reindex to add the missing index values (with np.nan values).