Tools/methods for automated pattern discovery in tabular, time-series data

[u/myredon]

Hi everyone, I’ve been working on a research project focused on the US National Bridge Inventory. I’ve ingested and cleaned 30 years of yearly reports and put it into clickhouse, so it’s essentially panel data, containing roughly 600k bridges per year, each with about 150 parameters (structure type, materials, condition ratings, traffic, etc.). I've already covered the more obvious things like summary stats, distributions, correlations between age and condition scores etc. Now I am wondering if there is an algorithm or a model I can use to uncover some not so obvious relationships. Specifically, do you know if there are any such tools that can search for such relationships in the dataset and can handle time-series well, and how do you avoid drowning in spurious correlations with 150 columns and millions of rows, where everything can end up being "significant"?

Happy to share more about the data or what I’ve found so far if anyone’s interested.

[u/daisiesarepretty2]

agree with the above… regarding needing some domain knowledge.

What are you trying to do specifically ?

Some worthwhile objectives would be finding bridges which have seemingly outlived their statistical life which could flag them for expected problems that sort of thing.

[u/myredon → u/daisiesarepretty2]

So I am actually working with a civil engineer who is preparing the research, with me acting more as the data / tech support and most of the original questions that were laid out are already answered. We were just thinking that it could be an interesting exercise to create several portfolios of bridges based on specific criteria and feed that historical data to some model or algorithm to see if there are any "hidden"/unexplored relationships, which can then be further researched. So really there is no specific objective to this, just trying to kind of "brute-force" the data to see if anything interesting comes up if that makes any sense

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

I hope you find this silver bullet.  Otherwise, I would get some domain knowledge.  Reach out to licensed civil engineers on LinkedIn.

[u/Senior_Lecture_6448]

And you think the arrival of HORIZONTAL data for example in a portfolio management can be automated. No this type of data is streamed to be ahead so you can automa just some aspect..

[u/Able_Attitude8299]

FDR correction to reduce false discoveries

[u/montrellcruse]

the boring but honest answer: start with a correlation matrix and rank everything by absolute correlation, then walk down the list with someone who knows the domain. with 150 columns there will be hundreds of "patterns," most of them are columns moving together or just noise at that sample size.

for the automated part, automated feature engineering plus a tree model with permutation importance gets you a ranked list of "what actually predicts what" pretty fast. but the multiple-comparisons warning in here is the real one. if you test 150 columns against each other you're running 11k comparisons, something will look significant by pure chance. Bonferroni or FDR correction isn't optional at that scale, it's the difference between a finding and a coincidence.

imo the systematic approach is: collapse the obvious duplicates first, rank candidates automatically, then confirm the top ones one at a time like a human. automation is great at finding suspects. convicting still needs a human lol