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.

GPRSmith · Accepted Answer · 2021-08-30 15:53:05Z

2

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

edited Aug 30, 2021 at 15:53

answered Aug 29, 2021 at 18:31

GPRSmith

2971 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

HiFAR Over a year ago

thank you ! but it still showing the data with wrong structure not like as i wanted it returns back to line i've added an image explaining the output in the xlsx file

GPRSmith Over a year ago

Hi HiFAR, could you explain what you get now versus what you want to get? I can't see a difference sorry!

HiFAR Over a year ago

the table i want is in the first image the data should be next to each other, they are all relied to the name of the hotel, did i explained ?

GPRSmith Over a year ago

Hi HiFAR, the reason this is happening is because the line of code you added df.to_excel("v.xlsx") Is not providing the same output as what you had initially asked for, which is an output aligned to df.T. I've amended my answer to reflect what you're looking for, along with an image of my csv output.

HiFAR Over a year ago

yes with changing df.to_excel("v.xlsx") to df.T.to_excel("v.xlsx") it workod ! thank you !! one more question if i can ask how can i display all the data in the excel file when i have list of hotels ? its working just for the last hotel in the list.

|

Collectives™ on Stack Overflow

how to convert string to datatable excel using pandas?

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related