0
\$\begingroup\$

I wrote the following prototype:

import xlsxwriter
import numpy as np
import pandas as pd
import random
import uuid

workbook = xlsxwriter.Workbook('test.xlsx')
worksheet = workbook.add_worksheet()

companyissuearray = ['3001', 'Test1', 'TestCat1', 'TesSubtCat1'],
['3002', 'Test2', 'TestCat1', 'TesSubtCat1'],
['3003', 'Test3', 'TestCat1', 'TesSubtCat1'],
['3011', 'Test4', 'TestCat1', 'TestSubCat2'],
['3012', 'Test5', 'TestCat1', 'TestSubCat2'],
['3013', 'Test6', 'TestCat1', 'TestSubCat2'],
['3021', 'Test7', 'TestCat1', 'TestSubCat3'],
['3022', 'Test8', 'TestCat1', 'TestSubCat3'],
['3023', 'Test9', 'TestCat1', 'TestSubCat3'],
['1001', 'Test10', 'TestCat2', 'TesSubtCat1'],
['1002', 'Test11', 'TestCat2', 'TesSubtCat1'],
['1003', 'Test12', 'TestCat2', 'TesSubtCat1'],
['1011', 'Test13', 'TestCat2', 'TestSubCat2'],
['1012', 'Test14', 'TestCat2', 'TestSubCat2'],
['1013', 'Test15', 'TestCat2', 'TestSubCat2'],
['1021', 'Test16', 'TestCat2', 'TestSubCat3'],
['1022', 'Test17', 'TestCat2', 'TestSubCat3'],
['1023', 'Test18', 'TestCat2', 'TestSubCat3'],
['2001', 'Test19', 'TestCat3', 'TesSubtCat1'],
['2002', 'Test20', 'TestCat3', 'TesSubtCat1'],
['2003', 'Test21', 'TestCat3', 'TesSubtCat1'],
['2011', 'Test22', 'TestCat3', 'TestSubCat2'],
['2012', 'Test23', 'TestCat3', 'TestSubCat2'],
['2013', 'Test24', 'TestCat3', 'TestSubCat2'],
['2021', 'Test25', 'TestCat3', 'TestSubCat4'],
['2022', 'Test26', 'TestCat3', 'TestSubCat4'],
['2023', 'Test27', 'TestCat3', 'TestSubCat4']


companyissueid = ['3001', '3002', '3003', '3011','3012', '3013','3021','3022','3023','1001','1002','1003','1011','1012','1013','1021','1022','1023','2001','2002','2003','2011','2012','2013','2021','2022','2023']
companyissuecat = ['Test1', 'Test2', 'Test3', 'Test4', 'Test5', 'Test6', 'Test7','Test8','Test9','Test10','Test11','Test12','Test13','Test14','Test15','Test16','Test17','Test18','Test19','Test20','Test21',
'Test22','Test23', 'Test24', 'Test25', 'Test26', 'Test27']
companyissuetype = ['TestCat1', 'TestCat1','TestCat1','TestCat1','TestCat1','TestCat1','TestCat1','TestCat1', 'TestCat2','TestCat2','TestCat2','TestCat2','TestCat2','TestCat2','TestCat2','TestCat2','TestCat2', 'TestCat3','TestCat3','TestCat3',
'TestCat3','TestCat3','TestCat3','TestCat3','TestCat3','TestCat3']
companyissuesubcat=['TesSubtCat1', 'TesSubtCat1', 'TestSubCat2', 'TestSubCat2', 'TestSubCat2', 'TestSubCat3', 'TestSubCat3', 'TestSubCat3', 'TesSubtCat1', 'TesSubtCat1', 'TesSubtCat1', 'TestSubCat2', 'TestSubCat2', 'TestSubCat2', 'TestSubCat3', 'TestSubCat3', 'TestSubCat3',
 'TesSubtCat1', 'TesSubtCat1', 'TesSubtCat1', 'TestSubCat2', 'TestSubCat2', 'TestSubCat2', 'TestSubCat4', 'TestSubCat4', 'TestSubCat4']

templateArray = ['3001', 'LA001', 'Test1 bah'],
['3001', 'LA002', 'Test1 bzh'],
['3001', 'LV001', 'Test1 adsf'],
['3002', 'LA003', 'afdgfdag'],
['3002', 'LA004', 'htrhesdfg'],
['3002', 'LA005', 'fasdfasfd'],
['3003', 'LA006', 'poigf'],
['0003', 'LA007', 'asfdcx'],
['0003', 'LA008', 'xyzc'],
['3011', 'LB001', 'cyxz'],
['3011', 'LB002', 'cyrek'],
['3011', 'LB003', 'yomai'],
['3012', 'LB004', 'maiyo'],
['3012', 'LB005', 'breakfast'],
['3012', 'LB006', 'thedaleksarecoming'],
['3013', 'LB007', 'mustfeedthemothership'],
['3013', 'LB008', 'withgreatresponsitribilities'],
['3013', 'LB009', 'comesgreat'],
['3021', 'LL001', 'stuff'],
['3021', 'LL002', 'this'],
['3021', 'LL003', 'should'],
['3022', 'LL004', 'be'],
['3022', 'LL005', 'random'],
['3022', 'LL006', 'but'],
['3023', 'LL007', 'it'],
['3023', 'LL008', 'aint'],
['3023', 'LL009', 'egsf'],
['1001', 'KA001', 'eggs'],
['1001', 'KA002', 'spoon'],
['1001', 'KA003', 'nuts'],
['1002', 'KA004', 'cereal'],
['1002', 'KA005', 'Frank'],
['1002', 'KA006', 'John'],
['1003', 'KA007', 'Doe'],
['1003', 'KA008', 'Dove'],
['1003', 'KA009', 'Johnny'],
# TRUNCATED
# there's more of this stuff...

templateArrayID = ['3001', '3001','3001','3002','3002','3002','3003','0003','0003','3011','3011','3011','3012','3012','3012','3013','3013','3013','3021','3021','3021','3022','3022','3022','3023','3023',
'3023','1001','1001','1001','1002','1002','1002','1003','1003','1003','1011','1011','1011','1012','1012','1012','1013','1013','1013','1021','1021','1021','1022','1022','1022','1023','1023','1023','2001',
'2001','2001','2002','2002','2002','2003','2003','2003','2011','2011','2011','2012','2012','2012','2013','2013','2013','2021','2021','2021','2022','2022','2022','2023','2023','2023']
templateArrayCode = ['LA001', 'LA002','LV001','LA003','LA004','LA005','LA006','LA007','LA008','LB001','LB002','LB003','LB004','LB005','LB006','LB007','LB008','LB009','LL001','LL002','LL003','LL004','LL005',
'LL006','LL007','LL008','LL009','KA001','KA002','KA003','KA004','KA005','KA006','KA007','KA008','KA009','KB001','KB002','KB003','KB004','KB005','KB006','KB007','KB008','KB009','KL001','KL002','KL003','KL004',
'KL005','KL006','KL007','KL008','KL009','SA001','SA002','SA003','SA004','SA005','SA006','SA007','SA008','SA009','SB001','SB002','SB003','SB004','SB005','SB006','SB007','SB008','SB009','SL001','SL002','SL003',
'SL004','SL005','SL006','SL007','SL008','SL009']
templateArrayTemplateName = ['truncatedforbrevity','seethearraysaboveforanexample']

#should I just np.array at this point?..
# what is this amateur stuff?
#LIMIT=2000000
# now we cooking with gas!
LIMIT=10000000
i=0
row=0
while i < LIMIT:
    """ col= random.randint(0,25)
    data= random.choice(GeVoArray)
    worksheet.write_column(row,col,data) """
    # print(random.choices(GeVoArrayZiffern))
    # print(random.choices(GeVoArrayKategorie))
    # print(random.choices(GeVoArrayArt))
    # print(random.choices(GeVoArraySubkategorie))
    
    i += 1

    uuid_list = [str(uuid.uuid4()) for _ in range(LIMIT)]
for row in range(0,LIMIT):
    #test=uuid.uuid4()
    worksheet.write_column(row, 0, random.choices(companyissueid))
    worksheet.write_column(row, 1, random.choices(companyissuecat))
    worksheet.write_column(row, 2, random.choices(companyissuetype))
    worksheet.write_column(row, 3, random.choices(companyissuesubcat))
    worksheet.write_column(row, 4, uuid_list)
    worksheet.write_column(row, 5, random.choices(templateArrayID))
    worksheet.write_column(row, 6, random.choices(templateArrayCode))
    worksheet.write_column(row, 7, random.choices(TemplateArrayTemplateName))
    row += 1

test= uuid.uuid4()
print(test)
print(type(test))
workbook.close()

It does basically the following:

It randomly picks values from the predefined arrays to generate a random data table containing those specific predefined values. Also one column is just random UUIDs which appears to be working but not especially fast. Credits to SSayan from https://stackoverflow.com/questions/71155509/python-save-a-column-in-excel-with-a-lot-of-rows-with-random-uuids

It does not have to truly random, pseudorandom is ok and hence I'm using UUIDv4 for the UUIDs and random.choices for picking the values.

The problem

It took way over 11 minutes (I stopped the execution) to generate 100k values and I need values in millions of rows. How do I optimize this?

Would using numpy's array make my code way more efficient?

I need to make my code at least 100x faster considering the run time above...

\$\endgroup\$
2
  • \$\begingroup\$ Code review is about working code and your code is absolutely not working, your while loop does nothing useful, your csv will contain a whole list inside a column each row, you for loop will only run half long as intended, your script contains no functions and your data can be generated using loops... \$\endgroup\$ Feb 20 at 14:34
  • \$\begingroup\$ @XeнεiΞэnвϵς the code actually IS functional... Yes, it's not very clean, but it is functional... \$\endgroup\$
    – Munchkin
    Feb 21 at 7:29

1 Answer 1

5
\$\begingroup\$

I think there is a misunderstanding in what this does:

uuid_list = [str(uuid.uuid4()) for _ in range(LIMIT)]

This is what is called a list comprehension in Python. It is a compact way of doing a for loop. It is equivalent to this:

uuid = []
for i in range(LIMIT):
    uuid_list.append(str(uuid.uuid4()))

So what your code does with the while i < LIMIT is computing a list of a million UUIDs, a million times.

Same for the writing part: you are writing the columns a million times. Remember the previous post, the write_column() is already a loop somehow. The code should just be:

LIMIT=1_000_000

for row in range(0,LIMIT):
    #test=uuid.uuid4()
    worksheet.write(row, 0, random.choice(companyissueid))
    worksheet.write(row, 1, random.choice(companyissuecat))
    worksheet.write(row, 2, random.choice(companyissuetype))
    worksheet.write(row, 3, random.choice(companyissuesubcat))
    worksheet.write(row, 4, str(uuid.uuid4()))
    worksheet.write(row, 5, random.choice(templateArrayID))
    worksheet.write(row, 6, random.choice(templateArrayCode))
    worksheet.write(row, 7, random.choice(TemplateArrayTemplateName))
 

workbook.close()

NOTE: for a million lines it took 81s on my system. Be careful - the limit in your example code is ten million lines. You can use the underscores as I do to see how many zeros you have: 1_000_000 is the same as 1000000 or 10**6.

PS: you should not increment (row+=1) in a for loop.

Pandas version

This pandas version takes less than 15s (on the same system) for a million lines :).

import pandas as pd
from tqdm import tqdm
import time

start_time = time.time()
columns = {f"column{i}":[] for i in range(8)}
for _ in tqdm(range(LIMIT)):
    columns["column0"].append(random.choice(companyissueid))
    columns["column1"].append(random.choice(companyissuecat))
    columns["column2"].append(random.choice(companyissuetype))
    columns["column3"].append(random.choice(companyissuesubcat))
    columns["column4"].append(str(uuid.uuid4()))
    columns["column5"].append(random.choice(templateArrayID))
    columns["column6"].append(random.choice(templateArrayCode))
    columns["column7"].append(random.choice(templateArrayTemplateName))

data_generation_end = time.time()
print(f"Data Generation: {data_generation_end  - start_time:.2f}s")
data_df = pd.DataFrame(columns)
data_df.to_csv("test_million.csv")
print(f"Write to CSV: {time.time() - data_generation_end :.2f}s")
print(f"Overall time: {time.time() - start_time :.2f}s")
100%|██████████| 1000000/1000000 [00:08<00:00, 122234.30it/s]
Data Generation: 8.20s
Write to CSV: 4.56s
Overall time: 12.76s
\$\endgroup\$
4
  • \$\begingroup\$ Weird for me on a tad older hardware it took 3+ min for the code to finish, is it possible to make the process multithreaded? Also maybe exporting to excel (here you export to csv) is more time demanding? \$\endgroup\$
    – Munchkin
    Feb 18 at 10:20
  • \$\begingroup\$ I am really surprised. I would think writing to csv is faster than to Excel as it is just text. You should time both steps to see which one makes sense to improve (I'm pretty sure its not the writing. I have added the timing and a progress bar (requires an external package "tqdm") on the pandas solution so you can see which part needs work. Finally, as I mentioned in your code, LIMIT is ten millions, not 1 which would explain the much longer time. \$\endgroup\$
    – Ssayan
    Feb 18 at 17:11
  • \$\begingroup\$ I see. And is it possible to make this code multithreaded without much effort? As I see, only one core is being used on my PC. \$\endgroup\$
    – Munchkin
    Feb 19 at 14:30
  • \$\begingroup\$ I don't really know that part well. I know you can map a function on a Pool and basically cut your operations in different sets and run them in parallel. I couldn't make it efficient on my computer though so I would leave someone else help on that. minimal example \$\endgroup\$
    – Ssayan
    Feb 21 at 11:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.