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

CodePudding user response：

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

CodePudding user response：

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.

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