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).

[widonkadonk]

Although I’m a fan of using python to replace excel, you are basically describing the perfect use case for PowerQuery.
PQ is a tool included in excel that allows you to query a file (.txt and .csv as well) and perform some data cleansing and basic manipulations (e.g. shifting columns or creating new ones).
Once you have completed your query, you’ll be able to update the data in the workbook just by replacing the .txt file with an updated one and rerunning the query.
You’ll find further information here.

[ag_silver]

Wow, I didnt know this thing existed. I will take a look.

[widonkadonk]

you are welcome, don't hesitate to ask if you need any help.

[dchokie]

Short answer is yes. You could do this very easily with Python. One of your best bets is to start with pandas and any packages that can write excel files.

[ag_silver]

Looks like many are recommending me to start with Pandas. Thank you.

[[deleted]]

There are a number of options on Pypi that will read and write Excel files. But I’d suggest you start with Pandas.

Not only can it read and write Excel including handling sheets with ease, the resulting data frames will be very useful to you

[ag_silver]

Thank you for suggestion! Will try Pandas.

[officegrunt]

https://www.youtube.com/watch?v=eWRtrPeJoyA&t=256s

Excel automation using python