Iterate appending Python List output to rows in excel

Question

As output of my python code I am getting the marks of Randy and Shaw everytime I run my program. I run this program couple of times every month for many years. I am storing their marks in a list in python. but how do I save it following format? I am getting output in following format[Output in a row for two different persons]

import pandas
from openpyxl import load_workbook

#These lists I am getting from a very complicated code so just creating new lists here
L1=('7/6/2016', 24,24,13)

L2=('5/8/2016', 25,24,16)

L3=('7/6/2016', 21,16,19)

L4=('5/8/2016', 23,24,21)

L5=('4/11/2016', 13, 12,17)


print('Randy's grades')
print(L1)
print(L2)
print(L3)

print('Shaw's grades')
print(L4)
print(L5)


book = load_workbook('C:/Users/Desktop/Masterfile.xlsx')
writer = pandas.ExcelWriter('Masterfile.xlsx', engine='openpyxl')







Output at run no 1:

For Randy


7/6/2016, 24,24,13

5/8/2016, 25,24,16


For Shaw

7/6/2016, 21,16,19

5/8/2016, 23,24,21

4/11/2016, 13, 12,17

Output at run no 2:

For Randy


7/8/2016, 24,24,13

5/9/2016, 25,24,16


 For Shaw

 7/8/2016, 21,16,19

 5/9/2016, 23,24,21

I will have many such output runs for couple of years so I want to save the data by appending in the same document.

I am using OpenPyxl to open doc and I know I need to use append() operation but I am having hard time to save my list as row. I am new here. Please help me with Syntax!I understand the logic but difficulty with syntax! Thank you!

Have you considered saving as a CSV to open in excel? That would make this much, much easier — bendl
– bendl, Commented Jul 10, 2017 at 13:00
There's not much code which I could do successfully. yet I uploaded all what I could. CSV will also work for me.Appreciate your help. — Analyst
– Analyst, Commented Jul 10, 2017 at 13:50
BTW your line print('Randy's grades') should throw an error for mismatched quotes... it should be print("Randy's grades") — bendl
– bendl, Commented Jul 10, 2017 at 14:09
This is an odd situation. How do you know that L1, L2 and L3 are related to Randy and L4, L5 are related to Shaw? And why are they separate variables? It would make a lot more sense if you had a dictionary like {'Randy': [L1, L2, L3], 'Shaw': [L4, L5]}. Then you could simply search the first row for the student's name and add the grades in those columns. — Aran-Fey
– Aran-Fey, Commented Jul 10, 2017 at 15:33

bendl · Accepted Answer · 2017-07-10 14:08:10Z

1

Since you said that you are willing to use csv format, I will show a csv solution.

with open('FileToWriteTo.csv', 'w') as outFile:
   outFile.write(','.join([str(item) for item in L1]))     # Take everything in L1 and put commas between them then write to file
   outFile.write('\n')                                     # Write newline
   outFile.write(','.join([str(item) for item in L2]))
   outFile.write('\n')
   outFile.write(','.join([str(item) for item in L3]))
   outFile.write('\n')
   outFile.write(','.join([str(item) for item in L4]))
   outFile.write('\n')
   outFile.write(','.join([str(item) for item in L5]))
   outFile.write('\n')

If you keep a list of lists instead of separate lists, this becomes easier with a for loop:

with open('FileToWriteTo.csv', 'w') as outFile:
    for row in listOfLists:
        outFile.write(','.join([str(item) for item in row]))
        outFile.write('\n')

answered Jul 10, 2017 at 14:08

bendl

1,6302 gold badges19 silver badges45 bronze badges

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

2 Comments

Analyst Over a year ago

Hi,Thank you so much for help! But I want to store list for Randy in separate column and list for Shaw in separate column but without blanks and separate rows

bendl Over a year ago

So you want a single element to contain a list? This can be done but I don't see a good reason for it

Collectives™ on Stack Overflow

Iterate appending Python List output to rows in excel

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related