Please guide me the process if we can use some pandas functionality like melt/stack to convert into that format.
I have reviewed that there are some functionalities like using Pandas melt function, however, I am unable to crack the right code for the same.
Here is a proposition using some pandas reshaping functions you mentionned and pivot_table :
out = (
pd.read_excel("/tmp/Untitled spreadsheet.xlsx", sheet_name="Input")
.pipe(lambda df: df.assign(**{col: df[col].ffill() for col in ["Product", "Tier Type"]}))
.rename(columns={"Tier Type": "tier_type", "Unnamed: 2": "Type"})
.melt(id_vars=['Product','tier_type','Type'], value_vars=['Unnamed: 3','Unnamed: 4'], value_name='Value')
.pivot_table(index=['Product','tier_type'], columns='Type', values='Value', aggfunc=lambda x: x)
.explode(["Cost", "Velocity"])
.reset_index()
.rename_axis(None, axis=1)
)
Output :
print(out)
Product tier_type Cost Velocity
0 A Retro 10 0-600
1 A Retro 20 601+
2 B Retro 30 0-1000
3 B Retro 40 1000+
4 C Retro 50 0-10
.. ... ... ... ...
13 G Retro NaN NaN
14 H Retro NaN NaN
15 H Retro NaN NaN
16 I Retro NaN NaN
17 I Retro NaN NaN
[18 rows x 4 columns]