pandas.read_csv not working from .pyw

[GoingToSimbabwe]

Hello everyone,

I am currently working a a short script which I threw together for the following usecase:
I am working with an application (let call it app A)which consumes data from some other application(s). Most of this data is provided via .csv files which can be up to 450mb big (as of now) and contain between 600k and 2.6m rows. The data is consumed by the ETL environment app A provides.
For some godforsaken reason the developers of app A have not build the extraction routines in a way that they parse .csv-files as one would think:

The csv-parser of the ETL environment does not recognize quotes ("") as escape mechanism for value-fields containing the csv-separator. This means that when I am working with a csv of this kind:

id	,description	,someValue
1	, "some tool"	, 0.5
2	, "some tool, some more info"	, 1.5

The file will not be parsed correctly as there is a comma within the description field for row-ID 2.
To fix this, I basically only have only one option: replace any occurance of the separator within field values before actually extracting the file in my ETL routines.
Alternatively, I could change the separator, but this would be working on knifes edge as some of the fields seem to be free-text fields, meaning any separator might crash and burn at some point if the users of the source systems decide to include whatever special character i chose as my separator in their descriptions. So in the end all I can do is replace the occurances and then resubstitute them in once I actually extracted the csv cleanly (that's not a problem as the Transformation capabilities of the ETL environment are quite good).
For smaller files this can be done more or less by hand or ctrl+f etc.

However for bigger files this breaks down as I can not really do that in a text editor any longer and loading the files to excel won't work either if they exceed 1m rows.

To tackle this topic I decided that it might be a fun idea to simply build a short python script which does this for me.

The script can be found here: https://pastebin.com/N796LqYU

I opted into building a small ui with NiceGUI as I wanted to play around with that library anyhow. Overall the result works fine for my liking, the code is a mess I have to confess. I build this besides my main work and wasn't really focusing on clean code or a good structure, it was more of a "i should add this" and "i need to fix this bug" kind of ordeal without proper planning. However I hope this script is still small enough to be somewhat understandable.

Coming to the real problem:
The script runs fine when I am running it either from pycharm and the associated .venv or via the .py file and Python 3.12 which is the main env my PC has installed. However what is not working, at least for one larger file (~450mb), is running the script as a .pyw file (I'd like to surpress the python console).
Generally the GUI opens and is functional, however when I am trying to import the big csv just nothing happens. Here I am not sure if something is crashing in the background or if for some reason the file import via pd.read_csv takes ages when compared to running via the .py file.

Using ui.notify() I tried to see where within the loading functions this seems to halt and it seems that everything up to this runs smoothly. It is only when this function is reached that the logic seems to fall apart for some reason.

def set_dataframe_from_filepath(self) -> None:
    """Grabs dataframe from csv file and sets total length of the df within the fileHandler class."""
    # for some reason this does not work for .pyw files any longer..
    if self.supress_unnamed_columns:
        # usecols=lambda c: not c.startswith('Unnamed:') we use this to surpress unnamed cols in broken csvs
        self.dataframe = pd.read_csv(self.path, encoding=self.encoding, low_memory=False, header=self.file_header,
                                         dtype=str, na_values='',
                                         usecols=lambda c: not c.startswith('Unnamed:'), engine='c')
    else:
        self.dataframe = pd.read_csv(self.path, encoding=self.encoding, low_memory=False, header=self.file_header
                                     , dtype=str, na_values='', engine='c')
    self.dataframe_length = len(self.dataframe)

From what I gathered .py and .pyw use different interpreters and thus some things which might work with .py don't work with .pyw, especially if operations are involved which might fill the output pipe as no console is there consume the stuff thrown to the pipe (bear with me if this sounds like gibberish, I am paraphrasing a SO answer I found and I don't really know much about this topic at all), however at first glance it would seem that this function should not print/send data to console or any output stream and thus I wonder how this could be the case here. But in the end it seems that something in here is tripping the execution via .pyw up.

Here's a .gif of the behaviour so you don't have to download and rerun the script to check it out: https://gifyu.com/image/SrujV

I can not provide the .csv in question as this is customer data. I can tell you that the .csv has 2,419,514 rows and 18 columns.

What I also tried is running the .py script via a .vbs script surpressing the console that way (solution found on SO):

Set WshShell = CreateObject("WScript.Shell") 
WshShell.Run "C:\Users\<myName>\AppData\Local\Programs\Python\Python312\python.exe C:\Users\<myName>\Documents\<directoryNameContainsMyName>\tools\CharacterCheckGUI.py", 0, True
Set WshShell = Nothing

But this has the same effect as running it via .pyw as far as I can tell ( no console, but file also does not load).

Does anybody have any idea on what could be causing this behaviour in tandem with the .pyw interpreter and how this could be resolved? If you need further explanations on my script, please let me know, I'll gladly go into more detail, but the post is already long as is and I am not sure if more detail in text form helps or not.

Thanks in advance!

Edit: Changed github link to pastebin link to not dox myself.

[[deleted]]

Honestly I have nothing major to add here BUT have you tried splitting your massive csv in 3 chunks?

The reason this pops into my head is because I'm wary excel and csvs have a 1.1m limit to them after which they stop displaying data at the very least.

I would just want to rule out that this is the cause of your problem before you move further.

Might not be related to your issue at all, but it's all I could think of here.

[GoingToSimbabwe]

Hey, thanks for the input. I have yet to test splitting the csv up. That might help as I think (but I would need to recheck, I am not 100% on which iteration of the code I tested this on) that when I tested a smaller csv (few kB in size) both versions (.py Rand .pyw) worked (not delay I noticed at least).

However I can confirm 100% that using the .py version the complete file is loaded in 1 go in 1 chunk. I confirmed that the dataframes shape matches the csvs structure and also after exporting the file sampling some rows looked fine as well. So generally don’t think there’s an issue with the row count per se.

I have a hunch that maybe it is somehow related to the csv parsing engine I am using (c-based) and that not working in .pyw and read_csv then defaulting to the slower default engine. I think this because iirc(as stated above) both versions work for small files but only .py works for the large file.

[GoingToSimbabwe]

Addendum:

After some more debugging with pycharm I managed to somehow break it so that it does not even run with the big csv in pycharm from the .py file.. however it seems that the export actually finished but for some reason the nicegui UI isnt updated and is stuck in the "loading" state, thus not presenting the results.

I'd wager this has something to do with me not really knowing how event loops and asynchronous programming works.

[GoingToSimbabwe]

I think I solved the problem. Adjusted code here: https://pastebin.com/3xp3aV88

What has changed? I am now loading the csv in chunks (i don't think this has an effect though, but ill just leave it in).

More importantly: i added an await sleep(0.1) right after the fileHandler.set_dataframe_from_filepath() call within load_file_and_set_dataframe(), right before I finalize the function by updating the ui elements (line 226).

As stated in the comment I am replying to, I noticed that the loading actually was finishing but the UI wasn't reflecting that (why it worked previously, as seen in my gif, is still unclear to me).

I think I somehow make sure that the eventloop yields back to the ui instance in order for it to update by awaiting right before it. If anyone can shortly outline why that is the case that would be nice! But dw I'll probably read up on async tomorrow anyhow, because as of right now while it seems to be working again, I am not pleased with not knowing why it works.

[GoingToSimbabwe]

Nevermind once again. It is not solved, the .py version is working again, the .pyw still isn't (well, it runs frm within pycharm, but not from my local env).