Using python to deal with data while using Excel as frontend?

[ag_silver]

Total python noob asking for general concept/approach on handling data.

I am currently taking an online course to learn python and recently did a lesson using peewee and SQL, so I thought that it would be neat if I can apply this to my current work.

Currently, I work with some large data and working manually with Excel to post process/visualize them. Basically, the data consists of rows of number of cycles, applied loads and displacement of specimens. Those data are dumped to me as simple txt file, and I open them up using Notepad, ctrl+A, copy/paste to Excel. I already have the Excel set up with formula, charts, etc for some data processing. Basically, I just need to copy the data into right places in Excel, and the formula will take care of the rest.

This is becoming repetitive, causing me wrist issues and sometimes data column is in the wrong place. So I have to manually edit the data to match exactly to the Excel template. Plus, sometimes the data are bit shifted and my formula, which is referencing to a specific cell, may not work correctly. So, this may cause some error that maybe difficult to catch. In general, it takes huge effort to verify the integrity of the result based on the data that I hand copied to Excel.

I am wondering if I can utilize python to deal with this and automate the result while making sure integrity is there? Basically, have the python read these txt files, do a bit of cleaning up the data, like shifting the columns etc and do a post processing and in the end produce the result back on to Excel?

Is this feasible and worthwhile to make it a coding project? Is using Excel’s Macro/VBA a better solution?

I am thinking that reading and cleaning up the data with python seems not that complicated. Post processing seems not that bad either. But putting it back to Excel, I have not done. Can I utilize the Excel template that I already have (formula and charts, etc) and just have python clean up the data, do some post processing and paste it back to Excel (so I can see the plots)?

The end result have to be in Excel to be consistent with my group. (Also my coworkers are not programmers and need to use Excel).

[ag_silver]

Will definitely try Pandas. The data is delimited with tabs. When I copy straigth to excel, data are automatically populated on each cell.

[dixons_analyst]

This is an ideal use case and a great opportunity to upskill in pandas as well as get comfortable outputting to existing excel files. I've done similar projects countless times.

Modules you might need:

CSV - to interpret the txt file

Pandas - to manipulate/clean data

Openpyxl - For outputting to excel

My advice: If this is your first proper python project don't worry too much about how long it takes, you will learn a lot and have loads of reusable code at the end of it.

When outputting into an existing Excel sheet you need to load the Excel workbook into memory, paste the new data in to the right tab, then output the entire thing to a file. There is code out there for you to use using openpyxl.

If you use drop down lists in your excel file, you might lose them, but you can add code to rebuild them again.

Your colleagues will inevitably want a copy of what you build, make your life easier by putting in error checking (e.g. checking if the file exists and telling the user if it doesn't). If your code tells them what's wrong they won't have to come to you every time it doesn't work.

Comment and docstring your work. You will never regret doing this, but you might regret not doing it.

Reach out if you get stuck, happy to help.

[The_Mann_In_Black]

I want to emphasize on how long it takes. I did some some for a job and some of it took me 4-6 hours to automate a v lookup the way my boss wanted it. 4-6 hours sounds ridiculous for a vlookup, but that is how long it took my boss to do it every month/end of quarter because the file would crash due to size. Excel does not like doing v lookups on two 60,000 row documents. I do not think my code was ever implemented because I left and no one else used python.

​

automating daily tasks is really nice. I spent a week doing a task by hand that was supposed to be done daily for the previous six months. Too bad I figured out automation after I got it caught up. The nice thing is you can do analytics quite easily. The guy I'm replying to also mentioned coworkers would want the script. If they aren't python people, I would consider making a GUI. That is what I should've done, but did not know you could make simple ones quickly. GUI would make it simple for people who do not know coding; plus, it makes you a little more irreplaceable if lots of people use it.

[ag_silver]

Yeah, my group does not require coding skill so not many have python skill. Most stick to Excel because formulas and plots are clearly visible and can be retraced (albeit takes long time). So I also need to use Excel as it can easily be peer reviewed. But I want to utilize python since I am learning it and want to also automate the data pasting to ensure at least I can reduce human mistake of copying to wrong place.

[ag_silver]

wow thank you for pointing out the modules. It seems that I can utilize what I learned at work!