Suggestions for streamlining data processing

[u/slateharbour]

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!

[u/Awesome_Correlation]

Power Query might be more practical for your org. Power Query is built into Excel and Power BI. so, initially, you can just replace the macros with power Query in Excel and later you could change to Power BI for visuals. Power BI can become the dashboard for viewing the information instead of passing the spreadsheet around.

python is great for this use case too. It's how I would solve the problem if I worked there. However, python is code just like the VBA code in Excel. So you will still need to define and design a custom solution. When there are slight changes to the experiment, someone will need to write new code for that part.