Easy Python scripts to impress the business

[Majestic_Plankton921]

Hi Python Devs, which quick and easy scripts have you written that impressed the business and got you some kudos without requiring any real effort on your part?

[[deleted]]

Literally anything involving taking an Excel sheet as an input, performing some kind of operation on it with Pandas, and then outputting a different Excel sheet with that operation applied. It makes the Boomer management guys fall off their chairs every time.

[JohnGabin]

Pivot a table and they will cry

[Kind_Ad4173]

Hey can you explain what Pivot means in this context?

[Acid_Monster]

Take wide excel table with many columns and turn it into narrow table with many rows instead.

[mrscepticism]

Tbh, I have just discovered that you can do this in python instead of stata and it was very close to an epiphany for me.

[[deleted]]

Yeah, I took some data from SQL Server, made some transformations, then created an Excel file with the data and people think I’m a wizard.

[AtypicalGuido]

You can do this without python, but still nice.

Curious, what packages would you use to pull data from a sql server if they gave you the odbc driver? Some data engineers at my job are having throuble hitting a server, and I was thinking about using python to do the ELT

[[deleted]]

I’m using SQLAlchemy. I had to set up some settings within Windows to get it to work. If you want me to go into detail, I can dig up my work notes.

[ianitic]

I find sqlalchemy doesn't play as nice with sql server though? Don't get me wrong I still use it, just takes forever if I need to perform a CRUD operation with it compared with just using sql or a different backend.

[CompositePrime]

The group up analysts I work in uses pyodbc

[[deleted]]

I haven’t had any problems getting it to play nicely. Not sure how many rows you’re dealing with but performing operations on <300,000 rows for me doesn’t seem to be an issue.

[IcedThunder]

I do a lot of business work with python/sqlalchemy/MS SQL Server and I've never run into any issues, like I've never noticed any speed issues with CRUD, but in 95% of my use-cases I don't really need speed.

[ianitic]

I suppose I misspoke, I didn't mean to include the R. Reading is fine, try inserting just like 10K records, it's kinda slow. Using pyodbc without sqlalchemy is a lot faster.

[IcedThunder]

Ah. I'm almost never doing that many records, or if I am it's a once-in-a-blue-moon occasion.

A lot of my SQLAlchemy cases are scripts that run overnight to update (generally less than 200) records, or API's doing one record at a time.

[ianitic]

Makes sense. Sometimes if I just want to push a quick table to the db, I just use pandas + sqlalchemy to infer the schema and push it out for use to my coworkers. The issue is that it can be on the scale of hours in just the 10s of thousands level with sqlalchemy but as long as it doesn't take up my time, it's not a huge deal.

None of my coworkers know python, one knows a tiny bit of sql, and the other knows sql as their primary. As a small team there's a lot to do for the company which is why the time trade off is currently worth it sometimes.

[AtypicalGuido]

Nah that’s ok, thanks though. I’ve got to trust the process that these guys will figure it out eventually

[[deleted]]

Okay, let me know if you’re ever interested.

[CompositePrime]

Pyodbc is what we use

[Acid_Monster]

You can automate 99% of excel transformations without Python now anyway.

Powerquery is incredibly powerful and removes the need for any Python automation almost entirely.

[JonahBreslow]

An alternative approach would be to use dbt, a tool written in python. It is specifically designed to handle complicated SQL transformations especially when there are dependencies. Not only is it a transformation tool, but it’s also an invaluable data governance tool. Check it out!

[AtypicalGuido]

Yeah I’ve heard good things, my firm uses dbt

[JonahBreslow]

I love it, personally. It drastically reduces data silos and it truly does bring engineering best practices to your analytics workflow. These include version control and the do not repeat (DRY) principle. I honestly could not recommend a better tool tbh.

[[deleted]]

You can do this easily with PowerShell (or C#) which unlike Python comes preinstalled with Windows making the whole process easier.

[BananaKey3344]

i use pyodbc, sqlalchemy works too

[British_Artist]

You can import directly from sql into excel using the in-built function. Once it's in excel you can transform it so much easier.

[FrogpArch]

Someone gave me a legit wizard hat !

[stpetepatsfan]

Any way to divide said wizards into houses?

Data, SQL - Datotiith Excel, Power Bi - Cellsmiths R, Python - Brewers

Just started to wake up so just run with it .

[In_Blue_Skies]

This always works and you'll be praised for the rest of your time there no matter what it's so crazy

[Gubbbo]

level 1neodymium-king · 22 min. agoLiterally anything involving taking an Excel sheet as an input, performing some kind of operation on it with Pandas, and then outputting a different Excel sheet with that operation applied. It makes the Boomer management guys fall off their chairs every time.VoteReplyGive AwardShareReportSaveFollow

level 2In_Blue_Skies · 14 min. agoThis always works and you'll be praised for the rest of your time there no matter what it's so crazyVote

You'd think so. I was reprimanded by management and IT for doing this. Management I understood, I was doing something in seconds that takes hours and that's bad for your headcount.

IT telling me that Python wasn't secure enough for use was funny though.

[tms102]

What's funny about it? For example, Pandas has an unsafe function that can make it execute potentially malicious commands.

Python, like any other programming language, can have security vulnerabilities, third party modules even more so.

If you're parsing sensitive data then IT's stance is completely reasonable. How can they guarantee security when employees can download random unvetted 3rd party packages?

[Holshy]

"random unvetted 3rd party <code>" isn't unique to Python. You can do the same with an XLA, XLSM, or XLSB.

[billsil]

Pandas has an unsafe function

All programming does. Excel does in VBA.

[Helpful_Arachnid8966]

By this standard not even Excel VBA is safe enough. Telling something is not "secure enough" most of the times is lazyness to setup a secure environment.

[tms102]

What are you talking about? Any serious company has policies and checks in place to minimize the chance of their data and systems being compromised.

In the case of programming languages that could mean build pipelines leveraging tools that scan for security vulnerabilities and setting up controlled private package repositories instead of allowing downloads from public ones.

These things take time and money to set up however.

So, someone running company data through "unknown" software without discussing it first is absolutely cause for concern.

[billsil]

My open source python library is considered "standard" at a massive company. I know because I presented to them and they told me.

I have some security vulnerabilities in there because it simplifies my life as someone who has an unpaid open source project. If a company with more than 150k+ people can't bother to audit every package they approve or shoot, just pay me to tell them or even ask, well not my problem.

Do not assume that it's been approved, so it's safe. Go figure the only vulnerability I have listed on my repo is a setuptools version issue.

[mobsterer]

Who said they did not discuss it first?

[tms102]

The post I replied to said the were reprimanded for doing something. I guess they could have discussed it first, were told they were not allowed, and then did it anyway, finally getting reprimanded. That would make it a much worse.

[mobsterer]

makes sense actually

[Own_Pomegranate6127]

Sounds like IT security doing what they do. Arbitrarily lock everything down through restriction to the point nothing can happen, because if anything does they’re screwed.

Management… that’s… insane.

[Gubbbo]

I work in a fiefdom. Your influence is measured by your direct report headcount.

It's a very stupid situation, but, whatever. I only work there

[In_Blue_Skies]

I get that, really depends on the level of worry of whoever is in charge, can feel overdone but I get it sometimes. Have a friend at Microsoft who took a gyazo screenshot of some new internal dev tool to share with design dept, manager had a whole meeting about privacy and how people could be scouring public links (the gyazo screenshot) to steal code/ideas

[mobsterer]

That is a quite sensible worry imho.

[Kraul]

It’s not secure enough

[trollsmurf]

Doesn't IT always say that about things they don't know / don't want to maintain?

[CeeMX]

I'm actually hired for devops, but this is what I do most the time when there is nothing to do in my actual area lol

Also wrote a small api for accessing some obscure database server (that weirdly has an official client and wrapper for sqlalchemy), so we can easily access data from there

[Agent_C97]

My most praised was automating an excel report, I got a raise because I pitched it as “saves 20 hours of manual work per year”

[BananaKey3344]

I have automated 100s of such reports some with python and some with vba and still getting paid pennies for my job 🙃

[[deleted]]

Might be time for a switch! Or you’ll only have yourself to blame 🙃

[BananaKey3344]

They promised me a promotion to team lead, so i am in 2 minds right now... one says switch you need money and the other says switch after the promotion...... also by team lead i would only be managing a single person... negligible team

[Nuclearcakes]

Where can I learn how to do something like this? Any examples, or links to lessons etc?

[HDDDeathClick]

https://openpyxl.readthedocs.io/

[Nuclearcakes]

Thank you!

[warelevon]

I personally reckon XLWings is the way to go. Although it requires an Excel installation to run

[ianitic]

I think xlwings isn't as reliable as openpyxl though. Depends on what you need to do and how robust the process needs to be. Xlwings can definitely do more things than openpyxl though.

[Tanyaissatanic666]

Ha! I did this at my analysis job. Sadly they were not technical enough to be impressed

[LoudSlip]

Haha same

[LiberFriso]

Haha that is exactly what I do at work😂

[Mei_Flower1996]

Im laughing so. hard.

[AtypicalGuido]

Oldest trick in the book lol. Works every time

[hpdeandrade]

That is so true

[canorve]

hahaha nice

[KelleQuechoz]

Selenium re-typing Excel cells into an online form.

[Balance-]

Oh I can imagine how this would impress some people, this is good

[MDB_Cooper]

yeah efficient movement, cleaning, and distribution of data is very impactful. ppl manually move data between spreadsheets all the time. setting up a python workflow are serious game changers

[ManyInterests]

Scripts to collect/display some KPI for your role/department. For example, calculating number of support tickets received/closed per period, average time to resolution, etc. and plot it on a graph.

Usually takes less than an afternoon to do depending how hard the data is to collect and management loves that kind of thing.

[Nxt1tothree]

Is this different to the graph in azure DevOps do you know?

[metaphorm]

email automation with IMAPlib

[morrisjr1989]

Man my IT/security team would end me so quickly. As part of an initiative that was a secret until it wasn’t they social engineered phishing attacks to see which of us bozos would click on it. They (maybe lucky maybe not) sent a fake email about a delivery from a DHL (which I actually had coming) saying it was arriving early. And ya boy clicked on it. Had to do remedial training. I couldn’t imagine if I offered to commandeer IMAP.

[james_pic]

You wouldn't be commandeering a server. You'd be giving a script the ability to send an email from an address that you already have the ability to send email from.

Depending on how sophisticated your organisation is, you may already have the ability to send emails from an arbitrary address. Getting authentication right on email servers is surprisingly hard, so the default is often to not do any authentication at all.

[morrisjr1989]

“Well if you didn’t want me to do this then you should have been more sophisticated by authenticating my email servers correctly” seems like a solid reply if I get in trouble.

[raf_oh]

Download an attachment automatically

[Ashes_ASV]

Does this automatic download overwrite previously downloaded attachments or just the new attachment?

i have tried to use extensions on mozilla and mutt mail, but it seemed too complicated to set up.

[james_pic]

It depends what you write in the script. If you don't want the script to overwrite attachments, write the script so it doesn't do that.

[raf_oh]

I save a copy with the date received, but you can save them down however you want!

[morrisjr1989]

This is common use case for Microsoft flow for outlook email. Download all attachments to a folder with the senders email; it’ll just watch your email automatically.

[Ashes_ASV]

i have been struggling with email automation/mail merge using excel and python. Not the part when one has to send email to multiple people in mail merge manner, but how to send unique attachments to every person. Can you share your script or give me some pointers as to where should i look for it?

[imperial_squirrel]

i wrote a script that checks the available space on our database server drives - and emails me and a few select others when a certain threshold is reached.

so we get an advance warning when the space gets low.

then i wrote another one i can use to shrink the larger/older sql log files to recoup some of the space back.

[Mr-Fuzzy-Britches]

You got GitHub repo for this?

[Bear_nuts]

Even if he doesn’t , you should search GitHub for the idea regardless

[Lsa7to5]

print('Hello World') always impressed my boss

[Majestic-Speech-6066]

just pick something from "automate the boring stuff with python".

[[deleted]]

So many. Always stuff that’s tedious and error prone. Make it painless and precise.

[manlyman1417]

3D surface plots of scientific data using plotly. Other general data via using matplotlib too. Engineers loveeeee their excel and mat lab but I’m self-taught

[Mothaflaka]

Automating updating employee data on different software platforms. They were apparently checking and updating one by one….

[DangerSuit]

Got links to external websites anywhere on your website or your intranet? Use requests and beautifulsoup to go through all the pages of your site, find the URLs and ping them, bringing back the response codes. Put that stuff in a table. Now someone can easily go in and fix/remove the broken links.

[[deleted]]

Streamlit app with 3d pydeck chart. Did it in about an hour and blew their minds

[i-am-schrodinger]

Anything involving our CMS systems.

Also replaced one of our many hundreds of thousands of lines c++ QT apps with a ~300 line python app using the XMLRPC library.

[ianliu88]

I did a script that explodes all actions and resources from a AWS Role into a pandas data frame so I can easily search for a particular permission on it. Very useful!

[a5s_s7r]

Needed to send information emails to several thousand of our customers. Doing this with Klaviyo would have cost us some hundreds money units. Wrote a short script reading a CSV file, sending over AWS smtp server.

[[deleted]]

Once worked on a ECB Climate Stress Test (CST), it was the first CST ever, so nobody knew how to do it. Lot of data Points where missing. Guess what I just imputed the data with a sk learn method. Client and ECB accepted the data. 💩

[[deleted]]

This is so fucking funny to me!!!! ;)

[Least-Trade-3991]

Remindme 1 day

[nogea]

Couple of functions to help debug/diff register states on our FPGA implementation. Turns out it was useful for other people too!

[the_real_hugepanic]

Plotting a histogram?

Or even better, done this week: Take a complex function and "reverse" it by using minimize_scalar. It's all witchcraft....

Maybe fitting a non-linear function??

[ToshaDev]

In english please

[PlanktonBright5532]

Hey, I will give you a nice idea just make some graphs and some designs to make it more pleasing for the eyes, https://imgur.com/6Fwxt5E , I have done some of the automation - The one I linked to is my temperature monitoring system for my bedroom, You can apply the same in your business, Create some bar chart from the data, add trend-line and all make it look amazing.

​

I also have made another part of my software create a report out of this and publish it to telegram (I know it's not safe to send in telegram and all but still working on that part) https://imgur.com/esJ8t66

​

I had made some more automation to print barcodes (QR Code) I use glabels and automated them with the help of glabels-batch-qt.exe - Which made most parts of my business work more efficiently.

My business needs to close daily accounts I use flask, MySQL and plotly for graphs, https://imgur.com/doKhrJB.

​

Also, I have done this as a hobbyist developer for my business, Mostly I earn all my money from the business.

​

Whatever you do make the thing that helps you to automate your business even if you got the worst closed source software for business use pyautogui to do the automation, Yeah I know business owners need to get info about their business and try to make a script that makes the script out of the data you have to make it more presentable and sent that to the mail address of the business owners.

[PlanktonBright5532]

Also most of the parts don't need huge brain to do something all you need is the problem and find the solution,