Creating excel sheet using python code

I want to create an excel sheet of permutations generated by the python code. My python code is:
‘’’’’’’’’’‘import random
letters = [1, 0, 0, 2]*33
for count in range(10000):
“Using this code, I am able to generate 10000 permutations comprising of 33 ‘1s’,33 ‘2s’ and 66 ‘0s’”.
How should I do that? What should be the code to transfer permutations produced by Python into excel sheet?

FYI, it looks like you’re trying to include Python code in a code block; this is the way to do that (the preview on the right will show you if you’ve got it right):


It depends on what form you want your permutations in…all in one Excel column, with the whole permulation per cell? Multiple columns, one for each position in the permutation (I assume most likely)? Or something else?

Huge arrays of numbers are not really Excel’s forte, compared to tabular data; unless your assignment requires it, I’d reconsidering the need to output it to an Excel spreadsheet to begin with. CSV is an open and generally interoperable (if simpler and more limited) interchange format (never mind JSON), assuming you don’t want to keep it inside Python for further processing.

To address your question, instead of printing each permutation to the screen, you can add them to a list, which you can then feed to pd.DataFrame and finally use .to_excel() to output it to an excel sheet. To get you started, you could do something like this:

permutations = []
for count in range(10000):

It would be simpler and more efficient to use a list comprehension, like this:

permutations = [random.shuffle(letters) for count in range(10000)]

Either way, you’d feed permutations to the pd.DataFrame constructor and call .to_excel() to the resulting dataframe.

It would be much more efficient to use a built-in Numpy or Pandas construct for generating an array, or itertools.product, etc., to avoid keeping the intermediate product all in memory at once, but those are more advanced techniques.

Best of luck!

Brother, I am now using this code to create an excel file
‘’’’’’’’’’’’’’’’’’‘import random
import pandas as pd
letters = [1, 0, 0, 2]*33
for count in range(1000):

but the problem is that when I put b=random.shuffle(letters)
the code does not give the same values as it was giving before.

As a preface, again, please place your code within the code block that I discussed above, as otherwise it is impossible to tell what levels your indents are, which can have critical consequences for what your code actually does and can very often be the source of bugs with are impossible for us to spot this way, as we must guess at what your code really looks like. Also, if you are not getting the behavior you expect from your code, please describe in specific detail, with actual examples, what behavior you see instead and how it differs from what you expect, as otherwise we essentially have to attempt to recreate it ourselves.

With that out of the way, this was my mistake here. Since I’m used to the Numpy functions for random number generation, I forgot that the standard library random.shuffle function shuffles the list in place. This means the existing letters object will be shuffled each time random.shuffle(letters) is called (and random.shuffle() returns None), rather than random.shuffle(letters) returning the new object and leaving the original unchanged.

To get the shuffled version in the above, for each loop iteration, you could shuffle letters, then append a copy of it to the list of permutations (which you can make the dataframe out of). If you didn’t make a copy, you’d just keep changing the same list. Like so:

permutations = []
for __ in range(1000):

However, you’ll end up creating one dataframe for each permutation, and if you write that out to CSV, each one will overwrite the next. Instead, it is both simpler and more efficient, as well as gets you more of what you actually want, to use the random.sample() function, which creates a new list with elements sampled from the one passed without replacement, set to get a sample the same size as the original list (which thus behaves as random.shuffle(), just returning a new list instead of modifying the existing one in place). Further, as noted above, doing this within a list comprehension allows the whole thing to be done in one line:

permutations = [random.sample(letters, len(letters)) for __ in range(1000)]

Then, create a DataFrame from permutations and write that to CSV, and you’re done.

Also, just to clarify, a CSV is not an “Excel file” (an XLS/XLSX; for that you’d use .to_excel()), but is indeed a better choice as it is a simple file format that is much easier to open and work with, and you can still open it in Excel if needed.

Thanks a lot for your help