Thanks for taking the time to check out my question - have learned a lot from this sub!
I work in a financial reporting role in a very small manufacturing company. Like many here, I fell into Python as I was the go-to guru for using Excel to 'automate' so reporting features. I've fallen into the Python rabbit hole & have enjoyed moving my workflow from 95-5% Excel-Python to choosing Python as my main tool. As a self-trained (mostly Google/StackOverFlow) 'programmer', I'm looking for ways to improve my skills & am unsure what the best way forward is. My skills are nowhere near production ready & I'm often duct-taping together code I find online to get the end-result I'm looking for. Up until now, I have been exclusively been using JupyterLab to make & run my Python scrips.
My issue is I'm struggling with organization & unsure what's my best course of action. I've probably written 50 different scripts, but end up reusing the same code over & over again & often can't remember how I completed a similar task earlier or what script that's in. I've tried to organize across different reporting functions (Sales, Inventory, General Ledger Analysis, etc), but it's getting unruly.
I'm looking to improve my workflow & organization. For example, my typical workflow is to manually pull a report out of our accounting system (Sage100) that contains all sales for the last 25 years. This is an ugly .CSV file that's ~200MB. From there, I have one script that 'cleans' the file - deleting empty columns, normalizing customer names, etc. I then dump that to a pickle file as it's much faster to load than a CSV.
I then have ~15 scripts that perform different manipulations related to sales analysis. One creates an automated sales report for each product / customer, one breaks out commissions paid, one looks at price per pound, etc. My issue is that in each of those files, I'm reusing a ton of code. For example, there are many times when I'm creating sub-totals of pandas groupbys - I often have to look back at what I did before to get it to work. It seems like I should be able to call these functions across my different files / projects. Another thing that I redo continuously is exporting to excel. I export the reports in Excel as that's what my managers need, but inside of each script I'm writing functions to output to Excel to try to auto-format it / write to multiple tabs inside. Another excample I'm re-doing in each tab is utilizing matplotlib / seaborn to generate graphs. Multiply this effort across 10 different reporting functions & I have a hard time finding the 'good code' when I want to perform a similar task.
I realize this is coming off as a complete amateur (which I am!), but I'm hoping for some guidance as to go from here. I'm worried that the tasks I'm performing with Python/Pandas are becoming as ad-hoc as what I was doing in Excel prior. I want to step up my game & begin to create projects that can grow in a manageable way. Every time I update an analysis, I'm concerned I'm going to break something & not realize it. Right now I'm doing way too much adjusting on the fly.
Should I be looking at moving away from JupyterLab & more towards a proper IDE? I've downloaded PyCharm, but it takes me much longer to write something in PyCharm as I have to run the entire analysis everytime I want to test out a new piece of code as opposed to running it in the browser line by line. I haven't even begun to work with a VCS.
Has anyone faced similar issues & made it out? If so, what helped you out? These are the times I wish I had take a CS course to better understand structure. Appreciate any advice anyone has on how to improve!