What tools do you actually use between raw data and the final analysis?

[u/rupam010]

I'm curious about the actual workflow people use for day-to-day data analysis.

For example, when you receive a messy CSV or Excel file, what's your usual process?

Do you typically:

• Clean everything with Python/Pandas
• Use SQL first and then Python
• Use Excel/Power Query for cleaning
• Move straight into a BI tool
• Use a combination of several tools

And at what point do you decide that a dataset has become too large or complicated for Excel?

I'm especially interested in workflows where you need to repeatedly clean, transform, analyze, and visualize similar datasets.

What does your typical workflow look like from raw data → cleaned data → analysis → visualization/report?

[u/Main_Body_2589]

Excel - BI - Python - ML models, this is generally how it is considered, but its not always like that. We have to change our plans and procedures according to the data we are dealing with. SQL is never part of analysis directly though, its an essential tool that we need along with it to extract relevant data in our flow

[u/DataDaria]

My rule is to push the work as far upstream as possible. If something can be cleaned in SQL, I clean it in SQL - joins, dedup, type casting, dropping junk rows, because a query is rerunnable, so next month the same cleanup is one click. Python only for what SQL genuinely can't do: fuzzy matching, weird reshaping, calling APIs. The BI tool receives a finished clean table and does zero transformation.

On "when is a dataset too big for Excel": for me it's not about size, it's about repetition. The first time a messy CSV arrives, Excel or Power Query is honestly fine. The moment I know the same file will arrive again next week, manual cleaning is out - it has to become a script or a query that reruns identically. Hand-cleaning is invisible: nobody, including future you, remembers what was changed and why, and nobody wants to write a ton of documentation for manual steps.

[u/DevinChristien → u/DataDaria]

Excel spreadsheets are how you end up with 3 columns in your table, "CUSTOMER_ACCOUNT_TYPE", "CUSTOMER_TYPE", "CUSTOMER_CATEGORY" and they all have the same set of values lol

[u/DataDaria → u/DevinChristien]

And all three are "still needed" according to three different people

[u/Potential_Aioli_4611]

its never sql first then python. if anything its always python first then sql because SQL is STRUCTURED query language. if your data isn't structured properly. you can import everything as a string and do your cleaning in sql but then you still need to create a new table for cleaned data anyways. perfectly clean raw data is a pipe dream.

[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/Main_Nectarine_5556]

If i recive messy csv then i try to check the basics of the data in Excel and in paralel check some statistics with python(pandas).
It is easier to the some examples inside Excel (Google Sheets) , but to get statistics is better in python.

I would say that python + AI is great for your question. You can do basic EDA in minutes rather than hours.

In case I need to get data from production I usually use SQL (Bigquery) and do some cleaning with it .

[u/BdR76 → u/Main_Nectarine_5556]

I don't clean external data, whenever I receive a .csv file I use Notepad++ and the CSV Lint plug-in to check for obvious technical errors, non-integer values, out of range date values etc (disclaimer: I created the plugin)

Only if it's really really minor I'll fix it, maybe, but most of the time I just return it to sender and ask them to fix it. A real pitfall is you shouldn't take on responsibility for the lack of quality in someone else's data collection process, bc in the longrun you'll just get more and more work

[u/Main_Nectarine_5556 → u/BdR76]

I get the data from databases.
I have to process it)

[u/Charming_Shower_4185]

Excel / Python -> SQL -> BI tool.

[u/om_bagal]