1

I have a list of values in Python that I need to loop over and write to an excel file. The caveat here is that the values need to occupy column 1, 2, and 3 and then repeat again. This is what I have so far:

# Writing all information to Excel
workbook = xlsxwriter.Workbook('NEW DOCUMENT')
worksheet = workbook.add_worksheet()

worksheet.write(0,0,"Value 1")
worksheet.write(0,1,"Value 2")
worksheet.write(0,2,"Value 3")

row = 1
col = 0

for code in master_list_of_values:
  worksheet.write(row, col, code)
  col += 1

How do I go about stopping it at column 3 and then looping again?

Example:

List = [1,2,3,4,5,6,7,8,9]

In excel it'd look like:

Column 1   Column 2     Column 3
    1          2           3
    4          5           6
    7          8           9
wayoh22
  • 176
  • 9

1 Answers1

1

You can use Pandas framework to structure your data, and after this save in excel.

The code below can solution your problem:

import numpy as np
import pandas as pd
example_list = [1,2,3,4,5,6,7,8,9]
df = pd.DataFrame() #Create empty dataframe
N = 3 #Number of columns
split_list = np.array_split(example_list,N) #split the list in N frames

for i in range(N):
    df[f'Column_{i}'] = split_list[i] #Create a columns for each frame

print(df)
df.to_excel("./output.xlsx") #write excel output
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ailton Oliveira
  • 36
  • 5
  • array_split is probably not the optimal choice here; you can just reshape. You can also use `zip` in vanilla python. [See here for some options](https://stackoverflow.com/questions/42593104/convert-list-into-a-pandas-data-frame/72007753#72007753). Cheers –  Apr 26 '22 at 16:27
  • 1
    Worked perfectly, thanks! I didn't even think to use Numpy – wayoh22 Apr 26 '22 at 16:49