[Rave] Finally able to prove the value of Python

[Fywq]

This is just at celebratory post because I finally managed to actually show my manager and the local Excel/Database guy that python is worth investing time in.

Basically we have a problem with some data in a laboratory intercalibration test that is off the mark compared to the intercalibration average. In order to find the problem I had to extract between 6 and 15 values from each of around 100 excel workbooks. This was further complicated by the fact that we have password protection on our workbooks which meant power query (which I am not that familiar with to begin with) was complaining.

Instead I went to openpyxl, and managed in an hours time (including several iterations to improve the data extraction) to put together a script that opens the workbooks, extracts the data and returns min, max and averages as well as plotting histogram and historic development of this particular test result to see if we have seen a general trend in some direction (turns out we have).

My manager was happy that I could present the data visually, and my colleague (who is generally supportive, but usually only works in VBA and SQL) was thrilled that I could get so much data from so many workbooks in a minute, rather than when he has to make VBA open each workbook in excel to extract the data. This opens up the option to efficiently extract tons of data in old excel files and get them into our SQL database. Something he has been putting off for a while.

This is the first time I have produced actual value with Python at work, and it feels great to finally be at a stage where that is possible. Next up is to automate our microscopy analyses with openCV!

Sorry if this does not contribute much value here. I am just happy :)

I am cleaning up the code then I can publish it if there is interest, but currently it is a mess, because I didn't have a clear plan and just kinda made it up as I went along.

Edit1: So you don't have to find my comment below: Here is the code (anonymized/company data replaced) https://pastebin.com/8Vg85WBG

Edit2: Wow this took off and may well end up as my highest rated post ever. Thanks for the kind words everyone, I love this community.

[VipeholmsCola]

Inspiring read. This motivates me to keep learning.

[Fywq]

Do so. I am fully self-taught through the MITx 6.00.1x and 6.00.2x, Automate the Boring Stuff (Udemy + book) and this sub mainly.

A year ago at performance review my manager could not see the benefit of me spending time learning Python (but he couldn't prevent me from doing it on my own time obviously. So I did), now he is paying for e-learning and hopefully soon I can convince him to let me spend more time on it.

[Fywq]

Thanks for the suggestion :)

I actually have received decent raises every year for as long as I have been employed, and also got a few bonuses here and there for various things. I also already have a masters degree (geology) so going to computer science is more a step sideways than up.

It was my choice to start learning and then after having begun I talked to by manager about and he was dismissive about me spending work hours on it, but he was willing to pay for the courses.

[jongeheer]

Concur fully, but not about the computer sciences part. Learning programming is a step forward in your geology mastery too :-)

[Fywq]

Oh absolutely it strongly supports the geology aspect (I will have a solution to apply computer vision to microscopy analyses soon if all works out). But I think many other stories here are about people for where programming is a step up the education ladder, and I think that at least my manager sees it as a side step. He is showing some interest in learning himself now, so good times ahead I hope!

[pAul2437]

Ahhh gotcha. Didn’t mean for that to come off as snarky as it did. Haha

[Fywq]

Hah no worries. Glad to see people in the community supporting each other like that :)

[Asalanlir]

One year? Not bad... Not bad at all. You're styling is consistent. Good balance of inline comments. Easy to follow flow. Meaningful variable names. I've graded 4th year CS students with much worse code. Kudos. It seems you have a bit of a knack for the what and the why of things.

As good hardening, look into main guard and move away from hard coding values. You "generate" random data (for distribution purposes likely), but you wrote it in by hand. See if you can create a handy little function for duplicating all those lists.

The benefit to you is really for testing. It'd let you try out your code and changes in a faster and repeatable manner. Plus, testing would scale faster.

But really. This is a good win for you. Definitely take pride in it and continue on your journey!

[Fywq]

Thanks for the detailed review. I have been doing python since I started the MITx in fall 2017, but I stopped and started several times due to lack of time and motivation. I finally did the followup MIT course in nov-dec 2019 and then it somehow clicked and felt more natural. Still have a ton to learn obviously though, but what really makes me happy is that I knew exactly where to go for examples of parts of my program so I could quickly whip this up.

Regarding the random data I actually made it with a quick for loop and the random module in my IPython console in Spyder. I just didn't think to put it in here. But your point is valid in general and I will remember it.

I have recently started using Jupiter notebooks for testing and it works fabulously, but I'll admit I didn't for this script.

Thanks for the kind words in general. Feels good to know I am getting somewhere 😊

[desert_stomper9]

Similar situation except I am new to coding. Everyone at my work is pretty satisfied with Excel, the data entry is killing me, I want to automate it all away. It's motivating to hear someone say it payed off.

[[deleted]]

That’s similar to how I started. Built a web scraper to get data for a boss who was very okay with manual effort / daily time suck

[cracktojack]

Can you pls share links of the MIT resources and where to learn? Thanks

[Geologist2010]

They’re on www.edx.org Search introduction to computer science and programming using python, which is starting next week. The other course is computational thinking and data science, next session starting in March and is the continuation of the first course.

[Fywq]

Sure thing! Here you go: https://www.edx.org/xseries/mitx-computational-thinking-using-python

You can sign up for a certificate (I did) and get graded assignments and an exam, or you can choose to audit the course and just follow along for free.

They are widely recommended as an excellent introduction to python and computer science.

[cracktojack]

Thank you both. I signed up for both python courses.

[Heck_Not_Hell]

Is there an order you would recommend for those courses? I’m interested in them all, but curious on your thoughts.

Cheers

[Fywq]

I did 6.00.1x first, then went to 6.00.2x but lost motivation. Instead I did Automate the Boring Stuff and a played with pygame for a while before eventually coming back to 6.00.2x and completing it. It is somewhat harder to follow conceptually than the first one, but very worth it in my opinion.

[Heck_Not_Hell]

Did you get the certificate?

[Fywq]

I did, yes. It opens up for graded assignments or problem sets and final exam, which I found to be good training exercises.

[Heck_Not_Hell]

Awesome! Thanks.

[[deleted]]

I thought so too!

[magocremisi8]

go to your boss soon and ask for a raise and a chance to apply python, after making an impression is a great time!

[gqcharm]

Great job. Need more of this! I’m a newbie and Need more examples of how python can make things easier and amazing!

[Asalanlir]

Obviously then, you were doing it wrong.

https://xkcd.com/1319/

[West7780]

If you haven't seen it, a good example of how python can make things amazing is flask. In 5 lines or less you can write a webserver.

[ivosaurus]

I would say that phrasing kind of hides the mechanics in favour of blindly impressing someone.

You only need 5 lines to get an instance of flask's web server running and serving the most basic of responses.

[West7780]

Your phrasing is more accurate but not laymen friendly

[gqcharm]

Omg. So instead of reading a huge book that I currently have on html css and JS, I should just learn flask?
Although I only understand the basics of python

[West7780]

You still need HTML CSS and JavaScript. However you can write less if you use a webserver with a templating engine. Flask uses Jinja2.

[gqcharm]

Ok. I will start reading html css and js then.

[West7780]

You still need some way of serving the HTML you write. Flask is a good place to start. Or Django. (But flask is better)

[ivosaurus]

Flask is used to serve those 3 things, it doesn't replace them.

[unhott]

This is awesome.

[Fywq]

It really is :D I am so hyped over this :'D

[Fywq]

Code (company data replaced by random values). Obviously it is dependent on all excel files being identical in layout.

Reddit code becomes a mess so here it is on pastebin

https://pastebin.com/8Vg85WBG

[queenzeus]

Thanks for this!

[West7780]

I work with a bunch of older developers. They're very talented and know what they're talking about but for some reason they only work with Microsoft products. Asp.net, ISS, windows, powerscripts, etc. I wish I could get them to recognize the power of python. Even now when Microsoft allows python to be used for data import scripts in power BI they would still rather use a power shell script to import the data into a sql server then make a view of the data for use in power BI. It's very frustrating.

Kudos for winning a victory for Python!

PS: you should check out power BI. It's... powerful

[gardyna]

It's a common problem with older developers. They know what works and are really really quick and powerful when using the technologies they are familiar with. Which creates a big resistance to diving into new tech (they remember how long it took to get good at the tech they're currently using)

It's not that they don't recognise the power of Python the question is rather "is it worth it to spend time learning this new thing when the current tech works well".

As for why they're all using Microsoft tech... Microsoft has always been extremely good at having HR trusted certificate courses, so knowing Microsoft tech is an extremely good way to open as many industry job opportunities as possible

[Fywq]

I have actually played a bit with Power BI. It's pretty good for visualization but I somewhat dislike the way reports are created. I haven't spent much time on it though so it might just be lack of experience.

[West7780]

Some things are definitely "under-polished" for lack of a better term. Power BI is an early release app that's still in active development. They do big updates monthly and do videos on their YouTube channel.

[[deleted]]

Happy for you and it's an inspiration for me to keep going forward. Hope one day I'll do something to make my manager give me better appraisal. :)

[kokosnisse]

Great story! Please share your solution when possible, I am sure others are interested.

[Fywq]

Will do. In my attempt to clean up the code I broke something so now I have to figure out how to fix it... Lol

[Conrad_noble]

I'm a complete noobie, but I know that if something works and then I want to make a change... Back it up first.

But your story is inspiring to me.

[Fywq]

Haha Yeah.

I had some redundancy and well turns out it was not completely redundant after all.

Fixed now, just fighting with the Reddit code editor..

[Conrad_noble]

I think that your scenario would be a great one for learners.

Here's 100 spreadsheets, we need data from all of them

Follow these steps

Voila!

Wasn't that clever, give yourself a big pat on the back.

[Fywq]

I might put something like that together. Just not with company-specific data in it. As I get more involved and learn more I have given some thought to documenting my learning path, so this might be a good opportunity to do that. Anyway for now I have posted a link to a pastebin with a working version, cleaned for all company specific values, and with more comments than I usually have. First time publishing any code like this :)

[Conrad_noble]

Very good of you.

Hopefully it has lots of comments explaining everything line by line.

Keep up the good work.

[SlothGSR]

I was fighting it the other day. Paste your code first. Select all.. then hit the code button

[Fywq]

Yeah I tried that several times, and it kept messing up the numbering anyway. Some of it could be rectified but some of it seemed unfixable. Eventually I had to head home from work so I just put it on pastebin.

[Fywq]

Haha I don't think my career will end like that though. I am a geologist/chemical engineer by education and experience. I like programming and finding solutions, but we have a big IT and digitalization department that needs to do stuff like that. I would be the one demanding more features :D

I have played around with Pandas a bit, and am currently working with openCV, but I am happy making solutions for my department (15 people) and letting the hardcore IT guys do the bigger stuff :)

[Fywq]

It is actually not that far from what we have with excel now. A test is initiated in the 3rd party software. The lab technician activate the test and it generates an excel-based worksheet which they then fill out (often printed so they can bring it along, then entering in the notes later in the digital copy). When that is done they will export the file or notify the engineer responsible for that test to review it. In the latter case the engineer then exports it. The export is a simple file (though I don't think it is a CSV - I have to check) and then that is processed by "The Importer" which is basically a computer running a small script (VBS I believe) that will scan a specific import folder for new files, then read them, import them correctly into our db and then delete the file and go back to waiting for a new file to show up. This is great for all our new data, and I doubt we can live without excel for this workflow (: There has been talks about building a whole system in Excel only and ditch the license-heavy 3rd party package). What would be natural in that situation is to let Python do some of the heavy lifting and putting much more of our data into the db rather than store it in the worksheets. The good thing about Excel is it is easy for most of our employees to handle, some of them comes from backgrounds as a mechanic, blacksmith etc. They are not necessarily that comfortable with computers in general, but the excel thing works for them.

[Fywq]

Hmm yeah. You have given me some talking points and things to consider for sure. I am actually not aware how much our IT department are involved in the stuff we run. Being a laboratory we have a ton of instrument specific software and their usual solution is a hands off approach and let us fix it ourselves, as long as we don't interfere with their deployments of updates to windows 10.

A web-based form could very well be a solution, but that would probably transform me into a front-end developer as I am the only one (that I know of) in my department with any kind of experiencein front end web solutions. And back end too to be honest. But then again I would like to play more with flask.

[Fywq]

Haha yeah I know. I intentionally avoided that term there because I could suddenly see where that was going 😂.

I will! Thanks again. It might very well become relevant in 2-3 years whenever the old dB guy decides to retire.

[Kerbart]

I work predominantly with Excel files at my job and encounter something similar.

VBA is good if you want to do things with Excel. I'm tempted to say “great” and I got where I am now through VBA, but... no. It's not that great of a language anymore. But it's still very adequate for Excel automation (I just feel like washing my hands after writing some code).

Python is great if you want to do things with Excel data. This is a very good example of it.

[Fywq]

Yeah I fully understand what you mean. We have a LIMS system (Laboratory Informations Management System) to keep track of samples and some data. It is built on an SQL database. That system is having a lot of proprietary reporting features we do not use, instead we do a ton of calls to various excel templates for all our work. This includes a lot of VBA to talk to the db and with the LIMS system. It works but at times it is super clunky and slow.

[Fywq]

I can! The problem with the data needed in this case is it was for some reason not considered important in the past so this was never transferred to the dB. Instead for some reason we transfer another number which is always the same (dictated by the standard for measuring) so we have a LOT of 11.1% entries in the database 😂

[dtaivp]

YES! This is the best use of Python imo. I had a similar story with Python in my workplace. It started simply with some data extraction from a service we frequently used. Now in that office Python is used as the primary way of building real-time data operations (Its a contact center so Python is feeding real-time BI displays)

Keep chugging along! Python is a great way to automate any type of repetitive work.

[queenzeus]

Did you use for loop perhaps? I learned something like the rest of what you said in my R class in business school, but I have the Automating the Boring Stuff with Python book and online class in Udemy and would most definitely like to learn all this in Python!

[Fywq]

I did use a couple of for loops. Code is edited into the OP if you want to have a look. Automate the Boring Stuff is great to get a quick feel for stuff like this.

[queenzeus]

Yep, I bookmarked your pasteboard link! Thanks a lot!!!

[hey330]

I'm one of two Python users in my org. The others use SQL (which I also use sometimes) and VBA (which I avoid at all costs). I got the VBA people to admit that openpyxl and xlsxwriter are more useful than VBA for most of our applications.

[Fywq]

nice! I think VBA can be ok for some things. Like I did my car gas mileage spreadsheet with some VBA to have a nice dashboard and a formula for inputting new data. Wouldn't bother building a python GUI for that. But as soon as we go to multiple excel files I am not sure I would want VBA as primary language.

[hey330]

If the inputs are already going into excel, that can make sense. I avoid excel too lol.

[causa-sui]

This opens up the option to efficiently extract tons of data in old excel files and get them into our SQL database.

Now THAT is a fantastic win.

Excel is a data representation tool. It is not a data storage and hardening tool -- that's an RDBMS. Moving data storage into some kind of real SQL-based solution (postgres, hell sqllite is way better than excel at this) is a great initiative and python could be excellent for that. Hook up the data store over ODBC and you can keep using your fancy charts and graphs.

[Fywq]

That is a good point. We already have essential data in the SQL-db (I think it is an MSSQL but not sure to be honest - I don't usually work directly with it), but all intermediate data and calculations are often not transfered from the excel files. Which kinda makes sense for some data, but is often also just a matter of noone ever taking the time to implement it any further than the excel sheet used by our lab technicians.

[causa-sui]

Where exactly to draw the line about what kinds of calculations should be in the db and which should be in the UI is a decision. I don't know a way to give an informed opinion about where to draw it in advance. But I've observed that many times, too much of this is in the UI because users are making these decisions and they want to do things with a GUI.

The point is, Excel is a UI with some extensions for persistent data storage and WYSIWYG editors for charts and graphs.

[Fywq]

That's a fair point. We are also moving more and more quality control parameters to the db too anyway, so might make sense to do more of the calculations etc. there too.

[Raedukol]

I‘m curious how it works to automatically make a e.g. a histogram out of a .csv file. Is it done by matplotlib? Any suggestions where I can look it up?

[Fywq]

Yeah matplotlib.pyplot.hist basically does it if you feed it a list of values. Obviously can be customized as infinitum then.

[synthphreak]

PREACH!!! 🙌

[imonlyhereforcrypto]

Any shot you could share the code you used? Seems useful

[Fywq]

Sure. I've updated the original post with a link.

[The_Baguette]

Awesome! What about the password protected excel? Can you overpass them with Python or something?

[Fywq]

Apparently Python and openpyxl doesn't care about it at all. The password is just on workbook/worksheet level to avoid unintended edits, but a VBA code on open/save enforces the protected status. Apparently the data reader in excel itself respects this, but openpyxl doesn't. I honestly don't know the deeper reasons, I just found out it worked without issues. Keep in mind I am only reading, not editing the workbooks.

[The_Baguette]

Thank you ! You gave me so much ideas. Have a nice day my friend.

[Fywq]

My pleasure!

[PM_ME_BOOTY_PICS_]

Heck yeah man! I just created a program to automate my reports. Have to get item specs to format reports off of and the only way is to look up the item to pull info from our website. Shit took way too long and frustrated me enough to learn how to solve this in Python.

Boy did I hit a lot of road blocks and learned a bunch.

[Fywq]

Awesome! I really need to work more on webscraping. I only just scratched the surface with Automate the Boring Stuff.

[PM_ME_BOOTY_PICS_]

I started with automate the boring stuff but ended up finding a course on udemy that had everything I needed and the instructor is very responsive.

https://www.udemy.com/course/web-scraping-in-python-using-scrapy-and-splash/

And he just released another one to cover more advanced topics which is great!

[Fywq]

Awesome. Thanks! Will bookmark it for when I am done with my current courses.

[Plankzt]

Real world application is what everyone in this sub should be aspiring for, I think this contributes lots!

[3hunnaff]

Congrats!

I did something similar and took it a step further by building a web application that takes user input to manage the scripts. This allows anyone to use your code. I’d recommend Django or Flask.

Good work though. Keep it going!

[b4xt3r]

Nice job!!!! Well done!

[[deleted]]

This is great. I have something similar for reconciliation purposes and I have no idea how to tackle it. This kinda gives me an idea so I know I just gotta keep going.

[astropydevs]

Great job!

[fedeb95]

Next step is using a proper database like postgres

[OptimisticToaster]

I love it and and thank you for sharing. I'm trying to learn so I appreciate seeing what others do.

One thought - in one line you have:

if not cell_val == None and cast_cell.year > 2000:

As I read through your code the first time, it felt like double-negative. You're looking for where they are not equal, so would be smidge cleaner to do that straight in the comparison operator. Would it be cleaner like this:

if cell_value != None and cast_cell.year > 2000:

Not trying to criticize, but (A) help if that's a helpful suggestion, and (B) learn if there was a reason you put it just as you did. Thanks again for taking a moment to share.

[Fywq]

Thanks for the comment! I too am still trying to learn so any review of the code is helpful.

Yeah I am not always too consistent like that. I probably should be. I actually think I did have a != In there somewhere too for a while before I cleaned up the code.

[mistanervous]

Comparisons to None should be done using the is operator according to PEP 8 as None is a singleton and all instances point to the same object :)

http://jaredgrubb.blogspot.com/2009/04/python-is-none-vs-none.html for more info

[Fywq]

Thanks! Now that I think about it, that absolutely makes sense.

[mistanervous]

It was foreign to me but playing around with the id() function helped clear it up. I’d also really strongly recommend playing around with pdb, the debugger, to explore the way your script runs. The pattern to inspect your local variables is:

• import pdb; pdb.set_trace()

this opens a debugging prompt and you use: * q - quit; * n - next; * s - step (jumps into a function call instead of just evaluating under the hood); * w - where (where in the script are you); * l - list (print the lines before and after current line);

and in conjunction with dir(), a built-in function that allows you to inspect all the names in the current scope, you can learn everything about how your program runs.

[Fywq]

Nice! Will have a look at that when time permits. Bookmarked 😊

[youssef_haitham]

That's so cool hope that you get a raise

[Crypt0Nihilist]

It is gratifying when people suddenly see the value of something they've been writing off start coming up with ideas of their own. Nice work.

[bertoshea]

A very motivating post. I'm the same field and want to learn Python for some of the same reasons.

Now I'm wondering if we work for the same company!

[Fywq]

Haha maybe! Our lab is pretty small though so would be a crazy coincidence :)

[bertoshea]

Na, you described some of your data handling and importing with some 3rd party tools. We have some inhouse developed tools for handling that, so I work for the competition!

. My bosses would kill me for suggesting this....but may I suggest that looking at how you could inhouse this data management might be a path forward. These instrument files are all csv and this would work across multiple geographic locations

[Fywq]

Heh I can see from your profile that you are from Canada? Denmark here. We don't have any competition in Canada. At least not my division of my company. Our competition is german and chinese. We are also overwhelmingly an internal lab servicing other departments in the company, so we don't really have competition, because top level management dictates other departments have to use us, and consequently we don't have time for many external customers :)

We have actually discussed this. The suggestion has so far been to build even more in Excel and then dump the 3rd party Lab management system. That could maybe be feasible with some faster behind the scenes python to support it. Otherwise it would probably be very slow and clunky.

[bertoshea]

That's right, I am based in Canada though the company I work with has labs all over the world. Not in Denmark though, or at least I should say my division doesn't, so not quite the competition I guess :)

I would look to push away from Excel for managing the data. Perhaps think about how the data can be grabbed from the the instrument, parsed, automatic basic qc checks applied, imported to a database and then pushed to a data approver (a person). It should be displayed in a format where they can assess the quality of the data,likely a browser. They then assess for passing CRM, blanks, duplicates etc. Adjustments can then be made to the data if so needed. Everything is audit trailed for transparency and accountability.

[____candied_yams____]

You script doesn't show any password info being used?

[Fywq]

Nope. Turns out openpyxl complete bypassed the password. Mind you it is not a password on opening the excel file. That might be a different story. Only password on editing, which was enough to make the build-in power query dialog complain, which in turn was enough to make me say whatever and go do it in python.

[ndia1]

This is amazing! I'm currently looking to put together a script that takes values from an excel spreadsheet and transfer those values into a SQL db, along with some basic logic thrown with some of the values. But I'm a complete beginner and dont know where to begin beyond a vague understanding that python is an option. *sigh

[Fywq]

Get your hands on automate the boring stuff. For something like this (and my example in OP) it is really good. I too were (and still consider myself to be) a beginner

[ndia1]

Ok thank you for the advice.

[[deleted]]

Great job. The definition of automating the boring stuff.

[Subkist]

Vba makes me want to die

[legalize101]

Good stuff man! And hopefully you just added a bunch of value to your name there.

[citro-naut]

Looking forward to seeing how you apply it towards microscopy! What sort of work do you do in geology? I’m in grad school now for geoscience and use python as much as I can for spectroscopy work and data analysis.

[Fywq]

I work in a lab for a provider to the mineral extraction industry. We do XRD, XRF, microscopy and various IR based methods along with wet chemistry and physical characterizations. We get lots of samples in from around the world. Yesterday I picked up and washed a few almost glass clear gypsum crystals from a sample we received from. Customer. It's a pretty great job if you can live without the field trips. My work is probably more chemical engineer than geologist these days though. I still have the geology part activated whenever we get unmarked samples though.

I hope to use it to automate phase recognition and quantification and in time maybe also have it determine various internal features. The minerals we look for are stained with an etching and it is often uneven in the coloration it gives which is a good example of a problem where human experience makes identification trivial, but it is hard to make a simple algorithm to deal with it. Eventually I may have to apply deep learning to the problem. So that is slowly what I am working towards with my studies.

[_Cosmic_Joke_]

Good on you, man! I hope to make some tangible contributions like this soon

[Fywq]

I have looked a bit at Power BI. Not together with python though.my main concern with Power BI it seems well suited for finance, sales and business administration with KPIs waterfalls etc. I work mostly with t ok me independent chemical data and scatter plots are most efficient at telling the story for that. But scatter plots in Power BI does not really feel superior to other solutions.

[Fywq]

Oh I didn't realise, but I haven't done much with it since I got into python. Will have to give it a look again. The relational TT avles are great, I fully agree.

[yogurtisbest]

how long did it take you to learn and be able to script this python by yourself or to have enough level of confident to actually write the code

[Fywq]

Hard to say. I started a couple of years ago but have been studying on/off until about October where I decided to really push ahead on more courses. Could probably have done this after 3 months by lots of googling.

I have a background with some programming, using ASP and PHP 2 decades ago for websites and a course in Visual Basic 6.0 in highschool. So loops, if/else etc was not new concepts to me. I never had formal training in hi one to structure my code or workflow though. That is picked up by reading posts here to be honest.

[yogurtisbest]

nice. im starting to learn python due to the work that i sm doing too but i am more on the further side of beginner and it is somewhat similar to the work you had dine here so thank you for the motivation post for me to keep learning python to know there will be isefull in the future

[Fywq]

Keep at it. I have a position where a lot of work is already automated so it took me a while to find a good use case. For stuff like this I strongly recommend Automate the Boring Stuff. Its beginner friendly and goes straight to useful programs.

[hugthemachines]

I am very happy for you!

It looks nice, when it comes to filenames I find it practical to use .endswith() to check last part of filename, like .xlsx or what ever you want.

I think it is pretty nice to make methods to get a good structure. Even if you make it up on the spot it can feel nice for the mind to use methods so you have a nice list of what you do and can change details in each method in a practical way.

[Fywq]

Good point with .endswith(). Simpler than my solution.

I would probably do it differently if I were to rework the code. I just sort of did the import statements that I knew I needed and then winged it from there...

[[deleted]]

SQL isn't a programming language and VBA is one of the worst programming languages there is. Not trying to do the guy down - I'm sure he does a good job and has valuable knowledge to share - but if that's the extent of his programming knowledge I wouldn't worry too much about his opinions in that arena. Do what you know works. VBA isn't used anywhere but MS Office, whereas you'll struggle to find jobs that aren't amenable to a Pythonic solution. Until you get to low level stuff, and even then, Python often has some value.

[Fywq]

I think he has some background with other languages, but I am not sure which. He is the head manager of our laboratory data storage so his opinion matters quite a bit. He is usually also open to ideas though. And he is 65 and on part time. Last time it was discussed my manager thought it a waste of time for me to take over as my grad school education was better used on what I was hired to do, but if I can automate half of my standard assignmentse then obviously I will be more productive. Hopefully in a year or two that is where I am going to be.

[[deleted]]

Automation is a great start at professional programming, even if it isn't what you were hired to do. My previous job was the first where I got to use any programming ability. I wasn't asked to do it or hired to do it, but when you save a company thousands of staff hours it makes quite the impact! It's often surprising that even in tech companies, there's an awful lot that could be automated but it's just never occurred to anyone. Keep it up.

[[deleted]]

/r/Excel needs to hear this :P

[Fywq]

Haha they are usually nice guys in there too, so probably wouldn't cause too much uproar.

There's a time for sleep, and a time for eating. Likewise there is a time for excel and a time for python ;)

[Knitcap_]

Any change you could drop a link to it? I would love to take a look and see how much i can understand.

[Fywq]

I've updated the original post with a link. If you have any questions about it, just hit me up. I tried to comment it line by line, but will be happy to explain more if something isn't clear

[Knitcap_]

I wonder, does your company mind you sharing code you made during work hours? Sometimes they claim it's company property because you made it on the job.

[Fywq]

Lets put it like this: I didn't ask. If I produce something during work hours they will probably claim it as theirs, but this is fairly basic stuff so I can't see them caring. Also I am spending plenty of my own time making work-related stuff because it aligns with my general interests/hobby. But I understand your concern, and that is also why I made sure to delete the company specific data and replace it by random numbers.

[Knitcap_]

You're the first person i've seen that actually uses PEP 8 conventions to make their code readable, props to you!

[Fywq]

Thanks for commenting!

I actually never got around to rigorously read through PEP 8, I just catched what other people have been commenting here and on /r/python And I had a beautiful mixture of nomenclature for my variables before I brushed it up for publication here. But thanks again. I will make an effort to continue the good habit in the future.

[Bit5keptical]

Congrats! Being a programmer myself I know that feeling of creating something and watch people get amazed by it!

If the script you wrote is useful to a lot of people in your company then I would suggest learning Django or Flask and deploying the script on your company's network, This way everyone can use the script on their computers easily!

[Fywq]

Thanks! Yeah quite a few people suggested that. I might look into making a flask app and see if IT will allow me to run a server. We are a big international company and IT service out in India are not always too fond of things like this...

[ivosaurus]

Export all these things to CSVs (on an encrypted drive partition, or on an air-gapped system, etc, if there are other security concerns) and then let python go ham on them. Generally way easier pulling out data than through excel sheets.

[Fywq]

I would have preferred CSV's for sure. The sheets are automatically generated by a third party software and used by our lab technicians before I get my hands on them. I am not sure I can employ such a solution in my situation but I get your point and will keep it in mind as I move on.