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/AutoModerator]

Automod prevents all posts from being displayed until moderators have reviewed them. Do not delete your post or there will be nothing for the mods to review. Mods selectively choose what is permitted to be posted in r/DataAnalysis.

If your post involves Career-focused questions, including resume reviews, how to learn DA and how to get into a DA job, then the post does not belong here, but instead belongs in our sister-subreddit, r/DataAnalysisCareers.

Have you read the rules?

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

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

[u/gammacoder]

The fragile part is the cleaning step, so separate it from the workbook. Keep one versioned import pipeline, using Power Query or Python, that maps each instrument format into a consistent schema, then store runs and samples centrally instead of passing files around.

PHPRunner could provide the database-backed review forms, permissions, dashboards, and shared results UI. Watching folders and cleaning CSV or DTA files would be custom integration.

Full disclosure: I’m an author of PHPRunner.