how to convert string to datatable excel using pandas?

Question

Following my previous question, now i'm trying to put data in a table and convert it to an excel file but i can't get the table i want, if anyone can help or explain what's the cause of it, this is the final output i want to get

this the data i'm printing

Hotel1 : chambre double - {'lpd': ('112', '90','10'), 'pc': ('200', '140','10')}

and here is my code

import pandas as pd
import ast  
s="Hotel1 : chambre double - {'lpd': ('112', '90','10'), 'pc': ('200', '140','10')}"

ds = []

for l in s.splitlines():
        d = l.split("-")

        if len(d) > 1:
            df = pd.DataFrame(ast.literal_eval(d[1].strip()))
            ds.append(df)
for df in ds:
        df.reset_index(drop=True, inplace=True)

df = pd.concat(ds, axis= 1)

cols = df.columns

cols = [((col.split('.')[0], col)) for col in df.columns]

df.columns=pd.MultiIndex.from_tuples(cols)

print(df.T)
df.to_excel("v.xlsx")

but this is what i get

How can i solve the probleme please this the final and most important part and thank you in advance.

Answer 1

Within the for loop, the value "Hotel1 : chambre double" is held in d[0] (try it by yourself by printing d[0].)

In your previous question, the "Name3" column was built by the following line of code:

cols = [((col.split('.')[0], col)) for col in df.columns]

Now, to save "Hotel1 : chambre double", you need to access it within the first for loop.

import pandas as pd
import ast  
s="Hotel1 : chambre double - {'lpd': ('112', '90','10'), 'pc': ('200', '140','10')}"
ds = []
cols = []
for l in s.splitlines():
        d = l.split("-")

        if len(d) > 1:

            df = pd.DataFrame(ast.literal_eval(d[1].strip()))
            ds.append(df)
            cols2 = df.columns
            cols = [((d[0], col)) for col in df.columns]
        
for df in ds:
        df.reset_index(drop=True, inplace=True)

df = pd.concat(ds, axis= 1)

df.columns=pd.MultiIndex.from_tuples(cols)

print(df.T)
df.T.to_csv(r"v.csv")

This works, because you are taking the d[0] (hotel name) within the for loop, and creating tuples for your column names whilst you have access to that object.

you then create a multi index column in the line of code you already had, outside the loop:

df.columns=pd.MultiIndex.from_tuples(cols)

Finally, to answer the output to excel query you had, please add the following line of code at the bottom:

df.T.to_csv(r"v.csv")