CODEWITHSUNDEEP
pythonexcellist
titusflex
titusflex

Reputation: 107

How do I export a two dimensional list in Python to excel?

I have a list that looks like this:

[[[u'example', u'example2'], [u'example', u'example2'], [u'example', u'example2'], [u'example', u'example2'], [u'example', u'example2']], [[5.926582278481011, 10.012500000000001, 7.133823529411763, 8.257352941176471, 7.4767647058823545]]]

I want to save this list to an Excel file in the following way:

Column 1: [example, example, ..., example]

Column 2: [example2, example2, ..., example2]

Column 3: [5.926582278481011, 10.012500000000001, ..., 7.4767647058823545]

Upvotes: 1

Views: 4537

Answers (3)

Mark Colan
Mark Colan

Reputation: 491

  1. I revised your data layout for clarity by adding spaces and newlines, though the result, in memory, is exactly the same as your input data. I assigned it to a variable to work with it.
  2. Your data is not two dimensional: it has three dimensions. We can call the dimensions sheet, row, and column. Your specified result is one sheet of five rows and three columns. The data from the second sheet (the numbers) needs to be put into columns of the first sheet. The first for loop does that. Then, the variable sheet is assigned the values from the first of the two sheets in your data.
  3. The sheet you are building in Python has only data, so you don't need the Excel package to create an XLS file. It's easier to create a CSV file if there is only data.
  4. Run the program.

  5. To open the resulting file in Excel, double-click on the export.csv file created by this code. Excel 2013 (and presumably later) have no import on the File menu, so open the file separately, select it, copy, and paste into the sheet where you want it to go.

    import csv

yourdata = [    
        [   
            [u'example', u'example2'], 
            [u'example', u'example2'], 
            [u'example', u'example2'], 
            [u'example', u'example2'], 
            [u'example', u'example2']
        ], 
        [
            [   5.926582278481011, 
                10.012500000000001, 
                7.133823529411763, 
                8.257352941176471, 
                7.4767647058823545
            ]
        ]
]

for i in range(len(yourdata[0])):
    yourdata[0][i].append(yourdata[1][0][i])

sheet0 = yourdata[0]

newFile = open('export.csv','w',newline='')
newWriter = csv.writer(newFile, dialect='excel')
for i in range(len(sheet0)):
    newWriter.writerow(sheet0[i])
newFile.close()

Upvotes: 1

Burhan Khalid
Burhan Khalid

Reputation: 174624

You just need to rearrange the data a bit:

import csv

col1 = [i[0] for i in s[0]]
col2 = [i[1] for i in s[0]]
col3 = s[1][0]

with open('results.csv', 'w') as o:
    writer = csv.writer(o, delimiter=',')
    writer.writerows(zip(col1, col2, col3))

You can open this file and import it in Excel; create a new workbook and then click on the data tab and then insert from file.

Upvotes: 1

Amit Dhar
Amit Dhar

Reputation: 31

Please use below link to explore different ways:

http://www.python-excel.org/

xlwt is one of the ways:

http://xlwt.readthedocs.io/en/latest/

https://yuji.wordpress.com/2012/04/19/python-xlwt-writing-excel-files/

If you want to use xlwt then below is the code:

import xlwt
workbook = xlwt.Workbook()
sheet = workbook.add_sheet("Sheet")

for i in range(len(rows)):
    for j in range(len(rows[i])):
        sheet.write(i, j, rows[i][j])

workbook.save("test.xls")

You have to install xlwt first if you want to use above code. For more information please refer xlwt documentation.

Upvotes: 0

Related Questions