how to parse merge colume to dataframe

Question

have a CSV that produces a Excel sheet in the following format enter image description here When inform dataframe, it looks like this enter image description here What I would like to Do is, Group country name in row together and convert to colume to columns. So, the final dataframe should look like this: enter image description here I have tried df.pivot() but it doesn't give the required format

I have tried df.pivot() but it doesn't give the required format

Answer 1

Try this after read your file like dataframe (df):

df.columns = list(pd.DataFrame(df.columns).replace(
    'Unnamed\.?\d?', np.nan, regex=True).ffill(limit=1)[0])
df = df.set_index(['Product SKU', 'Product Name'])
df.columns = pd.MultiIndex.from_arrays([df.columns, df.iloc[0].values])
df = df.iloc[1:]
df = pd.DataFrame(df.stack(level=0).to_records())