How to handle date columns in a dataset with over 100+ date columns?

Question

Background: I have a dataset with around 85k rows and 320 columns.I have no formal domain knowledge and the columns ain't intuitive as well the dataset is not in a language that I speak or understand. Out of the 320 columns, 138 are date columns containing dates in various formats. I have managed to homogenize the date format to "YYYY-MM-DD". Now that I have sort of, for the lack of a better term, 'cleaned the data', I want to get ahead with treating missing data, encoding the data, normalising if needed and scaling the data if needed.

Issues:
1. Per treating the missing values, how do I treat missing date values? When I researched online I found a couple of strategies to fill in missing dates:

• You fill in the missing value based on either the value before or after. Eg. - If there exists a missing value in the date column, preceded by a value of lets us say '2000-09-09' we use the same value for the missing value as well or let's say the missing value succeeded by a value of '2000-09-10', we would use the same value for the missing value. I am apprehensive about this method because there are columns with more than 60% of data missing (I have already excluded columns with more than 75% of their data missing) and hence I am not sure of this method.
• We find the minimum and maximum of the date ranges, sort the rows accordingly and fill in the missing values by consecutive dates. I am apprehensive about this method because I have columns where the dates are not periodic or sorted and are present and missing at random.

2. Per encoding, how do I handle the date columns? Especially, 138 of them? When I researched online:

• Most frequently suggested method was to split the date column into Day, Month and Year and then use it for training the model to normalise this data, two columns would be created with sin and cosine values of cyclic features like Day and Month. I am not sure of this method because I already have 320 columns - creating 3 columns for every date column and then two extra columns for every day and month column would surge my dimension and would lead to a curse of dimensionality.

• I thought about a method where we convert all the date columns in the minutes (from origin), normalise if needed, scale the data and then use them in the model. I fear that it would lead to a loss of information/misinformation in the column.

What is the industry standard and best practice for imputing missing values and encoding them in such a situation, given the dimension, number of data columns, etc?. If the above methods are acceptable, what are the alternatives? Please advise and any external links for further knowledge and reference would be greatly appreciated.

Answer 1

There is no single 'standard' to treat any data. Data science is not about blindly hammering data then sucking into some algorithms which happen to take them. You must understand your goal and your data first. Never throw yourself into the sea of techniques before fully understand the problem to be solved.

Ask yourself:

1. Why do we clean the data in the first place? What do you want to do with it after being 'cleaned'?

2. Does these date columns contain any information which helps your objective?

3. If yes, how does each date column related to your objective? Is it possible that some of dates unrelated to your objective?

4. What does each missing date imply? Due to collection error, or is it intentional?

Start by answering these questions, to frame the problem and then brainstorm on potential approaches. How to handle e.g. missing dates etc. are technical details which should come last.

Never put the cart before the horse.

=======================

[Added as per OP's comment]

So if in the very unfortunate case where you have absolutely zero information (e.g. feature definition etc.), what to do?

Best bet: get more info. Talk to people who may know something. Decrypt the language. Sign up for a crash course of the problem domain.

Second best: if for whatever reason we cannot do the above, quit. There are numerous things in life we can do that are more meaningful than this.

Worst case: if you really have to do it due to e.g. pointed by a gun or poverty, God bless you. Since you have no info, there is nothing that can guide you. In fact, we don't even know the data is of sufficient quantity and quality to tackle the problem - let's pray it is.

The best thing we can do is to do a lot of EDA, observe and make assumptions on what they are, how they inter-related, and guess if/how they are relevant to the target. Then, try every combination of techniques (imputation, feature engineering, families of models) you can think of and wish for the best.

This isn't science; even if we luckily find a combo that works perfect on training data, it may fail miserably in the field ('if we look hard enough, we can always find some false pattern'). This is the price to pay for being non-scientific. If we know nothing, nothing can help us.