Convert List of list of list to dataframe then export to excel multiple sheet

user_14724837 0
Viewed 43

Yeah, it's kind of INTERESTING but annoying that I couldn't figure it out. Hope someone could help me, i appreciate it!!

I have a list like this

people_lst = [[[0.04184746, 0.029883996, 0.0009178036, ],
  [0.014391203, 0.016383031, 0.005761528, ],
  [0.010505916, 0.0038218796, 0.0056757517, ]],
 [[0.029479079, 0.023749327, 0.0013516274, ],
  [0.017781705, 0.026531693, 0.003175055,],
  [0.0323914, 0.02072367, 0.006535718, ]]]

column = ["apple", "banana", "candy"]

I want to convert this listt to a dataframe and then to excel with different sheet names.

So my expected result is

[0.04184746, 0.029883996, 0.0009178036 ] is the first sheet first column
[0.014391203, 0.016383031, 0.005761528, ] is the first sheet second column
[0.029479079, 0.023749327, 0.0013516274, ] is the second sheet first column
[0.017781705, 0.026531693, 0.003175055,] is the second sheet second column
.
.
.

I have tried something like this

df = pd.DataFrame()
for i in range(2):
    dfs = pd.DataFrame(people_lst[i]).T
    df = pd.concat([df, dfs])
df

But it shows single sheet with 0,1,2,0,1,2 index.

By the way, I hope the sheet_name could be written in a range, because I have 40 sheets...

Thx

dataframe list excel python
2 Answers

So I understand, that your first row, becomes in the first column of your new data frame and from there you need to export to excel.

import pandas as pd
df1 = pd.DataFrame(listt[0]) # Convert to Pandas DataFrame
df1 = df1.transpose() # Convert rows to Columns
df1.columns = ["apple", "banana", "candy"] # Name your columns
df1.to_excel("output.xlsx", sheet_name='Sheet_name_1')  #Export the sheet 1

The output of your first element of the list, after conversions.

apple banana candy
0.041847 0.014391 0.010506
0.029884 0.016383 0.003822
0.000918 0.005762 0.005676

So, to finish the answer. This transform the dataframes:

data_frames = []
for i in range(len(listt)):
    df_i = pd.DataFrame(listt[i])
    df_i = df1.transpose()
    df_i.columns = ["apple", "banana", "candy"]
    data_frames.append(df_i)

And this code, export each file to excel:

j=1
for i in data_frames:
    i.to_excel(f"output{j}.xlsx", sheet_name=f'Sheet_name_{j}')  
    j=j+1
user_155923630

You can create a list of dataframes and then loop the list when writing the dataframes to the Excel file with ExcelWriter:

import pandas as pd

people_lst = [[[0.04184746, 0.029883996, 0.0009178036, ],
  [0.014391203, 0.016383031, 0.005761528, ],
  [0.010505916, 0.0038218796, 0.0056757517, ]],
 [[0.029479079, 0.023749327, 0.0013516274, ],
  [0.017781705, 0.026531693, 0.003175055,],
  [0.0323914, 0.02072367, 0.006535718, ]]]

column = ["apple", "banana", "candy"]

dfs = [pd.DataFrame(i).T.set_axis(column, axis=1) for i in people_lst]

with pd.ExcelWriter("filename.xlsx") as writer:
    for nr, df in enumerate(dfs):
        df.to_excel(writer, sheet_name=f"Sheet{nr}")
user_113807950
Related