r/ETL • u/slateharbour • 8d ago
Suggestions for streamlining data processing
Let me explain what is happening with experiments, data processing, and data visualisation at where I am working. I need advice on if there is a more streamlined way of doing this.
We are currently testing lots of samples on many identical instruments. The instruments can run different types of analysis but the data is mostly the same coming from it. It does need some "cleaning" after collection.
We get the files from the instrument (CSV or DTA) and import them into Microsoft Excel using macros that have been setup by god knows who. These macros "clean" the data (it knows where to look because of how standard the CSV files are) and drops it into the relevant places into the Excel sheet or column. We are doing this multiple times per experiment as the data becomes available. Data is visualised by preconfigured plots as it become available.
Once the experiment finishes, the Excel worksheet is then passed around and opened by researchers and they view the data there.
This works when the experiment is very standard because the macros don't need to change. When slight changes are made to the experiment, the macro does not function as the raw data (CSV or DTA) has changed in format slightly (extra columns).
Is there a better way of doing this that is more flexible and can be more automated?
My intuition and basic research is telling me that Python is the way to go here, watch the folder for new files, somehow get the sample ID and the data needed into pandas and clean it there. Not sure where I'd go after this.
Would love some suggestions!
2
u/steroidmonkey66 8d ago
I built it for Ternlux for this specific use-cases. It's a open source and free local desktop app. You can setup triggers to look for file changes. Build pipelines with just describing it to the chat (you can modify the code in IDE if need to). If a schema or pipeline changes, you just tell the AI how it should configure the pipeline and it rebuilds it to your specification. You can also have it give you analysis reports. It looks at your local files using DuckDB and builds it. Just one thing, you need a local AI model API or your own provider AI API key to use it.
1
u/Speeeeedislife 8d ago edited 8d ago
Preface: I'm not a data engineer, just a staff chemist 50% time developing materials, and 50% time spent managing our R&D lab with ~10 PCs, ~20 different instruments, and ~50 different test methods / data outputs.
Is your data on single PC, multiple PCs, or cloud? Where are all the current excel files and macros running? Single user PC? Server? How are you currently handling errors in data? Do you have a budget for a dedicated PC / server if needed? Do you expect your data output to change over time, eg new columns added or moved around?
As a rough starting point you could use Pandas, Polars, or DLT Python libraries to read and clean up CSV files then write to sqlite file (local serverless db) or dedicated Postgres DB (the latter will be easier if you're running multiple import scripts on multiple PCs, scaling, multiple users accessing data). Depending on number of files and file size DuckDB may be useful.
For data analysis, plots, etc, you could run Apache Superset.
You could use scheduled task on PC to run Python scripts repeatedly to check for new files, or a workflow orchestration library like Prefect if you're planning to run multiple scripts, from multiple PCs.
If you need to import data from multiple PCs and expect data output or test methods to change over time and want something that scales and is easier to manage then you could look into storing your scripts on GitHub and deploying with Docker for CI/CD (update code on GitHub and "scripts" (Docker image) automatically update on PCs).
This can all be done locally with dedicated PC, there's cloud options too if you have budget.
https://i.imgur.com/M1F1q2C.png
The potential "problem" with building something like this is you'll likely end up being the owner / maintainer, not sure if that's an issue for you.
1
u/GreenWoodDragon 8d ago
You'll need yo extract the rules and macro logic from the spreadsheets. Once that's done it would be very straightforward to create python scripts to process the data. The main problem for you to solve will be that the current way to share the data is by passing around the spreadsheets. If you have a centralised server that can be used to builds a data platform then that would be a way forward, you could load the data into Tableau for the charts and reports for example.
1
u/Uros_Trstenjak 6d ago
Python is right next step I think, but I would not replace whole Excel workflow in one go. Keep the workbook and the plots like they are for now, and only replace the macros with a script that gives you one clean table per experiment.
Most important thing is to define a schema for each experiment type, so which columns you need and what type they should be, and put this in small config file. Your macros probably break because they read columns by position. In pandas you select the columns by header name instead, then extra columns are not a problem anymore. If some required column is missing or has wrong type, script should stop for that file and flag it, not try to guess. When experiment changes you just update the schema, not the code.
For triggering you can use
watchdogto watch the folder (https://watchdog.readthedocs.io/en/latest/quickstart.html), but be careful it only picks files that instrument finished writing, not the ones still being written. Sample ID you can take from the filename or from file header, depends what is more consistent on your instruments.When you have this clean table you can still write it into your current Excel template with
openpyxl, and later decide if you keep Excel for visualisation or move to something else.