<jupyter_start><jupyter_text>DATE COLUMN DETECTION - <NAME> There are two functions :1. **getDateColumns()**2. **featureDateDifferences()**1. **getDateColumns() - Explanation**This method finds columns containing 'DATE' values based on calculating the possibilities. Firstly, the column name is checked, if it contains the string 'date' in it, the possibility is more. But, sometimes we get features like 'price as of date', in which case, it might be a numeric column.Secondly, all non_numerical columns are checked to have two **'/ '** or two **'white spaces'** or two **' - '** or two **' . '** . Also, we check if there's any name of month present in it, like **' jan ' , ' dec '** and so on. As these criteria get satisfied, the possbility is increased by a small fraction , else, possibility of that non-numerical column is reduced. At the end of iteration, we definitely have a few columns who possibility is more than 1, and all others below 1.0. These are the **DATE_COLUMNS**.2. **featureDateDifferences() - Explanation**This method adds new features which are the difference between the dates, while taking two at a time. Now, we have a list of Date Columns from the previous method. We need Difference Columns. We need to know how many differences we can make. For instance, if we have 2 date columns, we can feature 1 difference column, and if we have 6 date columns, we can feature 15 difference columns.Example: If Date Columns = [D1,D2,D3], then difference columns = [D1-D2,D1-D3,D2-D3]. Now, for every column in difference columns, get the difference from the required couple of Date Columns.Till now, only columns were validated, now every entry from the Date Columns will be validated, using **REGULAR EXPRESSIONS**. Finally the date is returned by calculating date(column1) - date(column2) using **DATE Module**  for each New Difference Feature.***Important***: The **REGEX** itself gives us a None, if the DATA isn't a date type, but it doesn't check for something like 29/feb/2019 where 2019 is not a leap year. Otherwise, all kinds of invalidity is checked efficiently.***Note***: Similar to **NaN**, **NaT** is also sensed by **df.isnull()** method - Checked. So, dropping missing values is easier  Initial Steps - Problem 1 Package Import, Creation of dummy datasets, shuffling<jupyter_code># Packages Import
import pandas as pd
import numpy as np
from datetime import date
import random
import re
# Creating Date Column 1
dateData1 = ['01/01/2020','01.Feb.2020','01 mar 2015','2016 Jun 15','2016.06.15',np.nan,'1975/dec/02','1975 12 02','abc','1950.02.02']
# Creating Dummy Column 1
dummyData1 = [np.random.rand() for _ in range(len(dateData1))]
# Creating Dummy Column 2
dummyData2 = ['abc','gbvc','7yg','np.nan','someRandomText','hgufg','3y5g','bh4','4fa','/759']
# Creating Date Column 2
dateData2 = dateData1[:]
random.shuffle(dateData2)
x = dateData1[:]
y = dateData1[:]
random.shuffle(x)
random.shuffle(y)
# DataFrame from all above lists
df = pd.DataFrame(list(zip(dateData1,dateData2,dummyData1,dummyData2,x,y)), 
               columns =['Date1', 'date2','Price as of date','dummy2','X','Y'])<jupyter_output><empty_output><jupyter_text>My Dummy DataFrame that contains different columns and different date formats, along with invalid data including np.nan - Problem 1<jupyter_code># The DataFrame with which I'd like to work
df<jupyter_output><empty_output><jupyter_text>**The Functions** - Problem 1<jupyter_code>######################## ----------------- FIRST FUNCTION --------------------- ######################

# Global List of all months
months = ['jan','feb','mar','apr','may','jun','jul','aug','sep','oct','nov','dec']


def getDateColumns(df,withPossibilies=0):

  '''
  This method Identifies all columns with 'DATE' data by maximizing out the possibilities
  '''

  # First get all non-numerical Columns
  numerical_cols = df._get_numeric_data().columns
  non_numeric_cols = list(set(df.columns) - set(numerical_cols))


  # This dictionary stores possibilities of a column containing 'DATES' based on the name of the column
  Possibility = {}
  for column in non_numeric_cols:
    if 'date' in column.lower():                  
      Possibility[column] = 1
    else:
      Possibility[column] = 0

    for entry in df[column]:                                                    # ITERATE THROUGH EVERY ENTRY AND TRY SPLITTING THE VALUE AND INCREMENT/DECREMENT POSSIBILITY 
      try:                                                                      # USING EXCEPTION HANDLING

        if len(entry.split('/')) == 3 or len(entry.split('-')) == 3 or len(entry.split(' ')) == 3 or len(entry.split('.')) == 3:
          Possibility[column] += 0.357

          for month in months:
            if month in entry.lower():
              Possibility[column] += 0.357            
     
      except (ValueError,AttributeError) as e:
        Possibility[column] -= 0.357        

  # This contains the final DATE Columns
  DATE_COLUMNS = []
  for key,value in Possibility.items():             
    if value > 1:                                                               # IF THE POSSIBILITY OF THE COLUMN IN GREATER THAN 1, THEN IT IS DEFINITELY A 'DATE COLUMN'
      DATE_COLUMNS.append(key)    

  if not withPossibilies:
    return DATE_COLUMNS
  else:
    return DATE_COLUMNS,Possibility
######################## ----------------- SECOND FUNCTION -------------------- ########################

def featureDateDifferences(df,DATE_COLUMNS):

  '''
  Creates new features containing difference between date columns while taking two columns at a time. If no columns are created, it means there's one or less number of date columns
  '''

  number_of_diff_columns = 0

  if len(DATE_COLUMNS) > 1:                                                     # At least two columns are required for making a difference
    for i in range(1,len(DATE_COLUMNS)):
      number_of_diff_columns += i                                               # If number of date columns is 6, we can make 15 difference columns taking 2 date columns at a time
                            
  featureDictionary = {}

  # Name New Features
  try:
    for i in range(len(DATE_COLUMNS)):
      for j in range(i+1,len(DATE_COLUMNS)):
        newFeature = DATE_COLUMNS[i] + '_minus_' + DATE_COLUMNS[j]              #Name New difference columns based on names of exiting date columns
        featureDictionary[newFeature] = [DATE_COLUMNS[i],DATE_COLUMNS[j]]
  except IndexError:
    pass



  ######### -------------- REGULAR EXPRESSIONS------------ ############



  # REGULAR EXPRESSIONS
  def returnFormat(string,delimiter):

    '''
    Checks if there's any match for date format
    '''
    string = string.lower()

    # REGULAR EXPRESSIONS
    dmy_num = re.search("^([1-9]|0[1-9]|1[0-9]|2[0-9]|3[0-1])(\.|-|/| )([1-9]|0[1-9]|1[0-2])(\.|-|/| )([0-9][0-9]|19[0-9][0-9]|20[0-9][0-9])$",string)
    ymd_num = re.search("^([0-9][0-9]|19[0-9][0-9]|20[0-9][0-9])(\.|-|/| )([1-9]|0[1-9]|1[0-2])(\.|-|/| )([1-9]|0[1-9]|1[0-9]|2[0-9]|3[0-1])$",string)
    
    dmy_mon = re.search("^([1-9]|0[1-9]|1[0-9]|2[0-9]|3[0-1])(\.|-|/| )(jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec)(\.|-|/| )([0-9][0-9]|19[0-9][0-9]|20[0-9][0-9])$",string)
    ymd_mon = re.search("^([0-9][0-9]|19[0-9][0-9]|20[0-9][0-9])(\.|-|/| )(jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec)(\.|-|/| )([1-9]|0[1-9]|1[0-9]|2[0-9]|3[0-1])$",string)

    if ymd_num:
      return string.split(delimiter)
    elif dmy_num:
      string = string.split(delimiter)
      string.reverse()
      return string
    elif ymd_mon:
      string = string.split(delimiter)
      string[1] = months.index(string[1]) + 1
      return string
    elif dmy_mon:
      string = string.split(delimiter)
      string[1] = months.index(string[1]) + 1
      string.reverse()
      return string
    else:
      return None                                                               # If no regex matches, it's an invalid input in a date column, hence returns 'None'
      


########### Apply function to new difference features by taking two date columns at a time ############


  # Iterate Through all DATE COLUMNS
  def iterateThrough(cols):

    '''
    Iterate through DATE_COLUMNS for every new difference column
    '''

    f_date = str(cols[0])
    l_date = str(cols[1])

    # Select the delimiter and pass it along with the string to enable .split() method
    if f_date.count('/') == 2:
      f_date = returnFormat(f_date,'/')                                         # The delimiter in the date can be a '/'
    elif f_date.count('-') == 2:
      f_date = returnFormat(f_date,'-')                                         # or a '-'
    elif f_date.count(' ') == 2:
      f_date = returnFormat(f_date,' ')                                         # or a space
    elif f_date.count('.') == 2:
      f_date = returnFormat(f_date,'.')                                         # even a '.' as found in various date formats
    else:
      f_date = None                                                             # If nothing is found, this becomes an invalid entry

    if l_date.count('/') == 2:
      l_date = returnFormat(l_date,'/')
    elif l_date.count('-') == 2:
      l_date = returnFormat(l_date,'-')
    elif l_date.count(' ') == 2:
      l_date = returnFormat(l_date,' ')
    elif l_date.count('.') == 2:
      l_date = returnFormat(l_date,'.')
    else:
      l_date = None


    if (f_date == None or l_date == None):
      return np.nan
    else:
      first = date(int(f_date[0]),int(f_date[1]),int(f_date[2]))
      last = date(int(l_date[0]),int(l_date[1]),int(l_date[2]))
      return first-last


  #APPLY FUNCTION FOR EVERY NEW FEATURE FROM EXISTING COUPLES OF DATE COLUMNS FROM DICTIONARY
  for new_column,existing_columns in featureDictionary.items():
    df[new_column] = df[existing_columns].apply(iterateThrough,axis=1)
  

  # RETURN THE NEW DATAFRAME with NEW_FEATURES containing DATE DIFFERENCES
  return df<jupyter_output><empty_output><jupyter_text>OUTPUT - Problem 1<jupyter_code># This is the result
# CONTAINS ORIGINAL DATA ALONG WITH NEW FEATURES CONTAINING THE WORD ** MINUS ** IN IT

# ***********IMPORTANT***********
# There is NaT in the output because there is invalid entry in date columns

date_cols,poss = getDateColumns(df,1)                                           # Second Parameter to get possibilies from the function, can be 0, if we don't need
print('The Date Columns are {}'.format(date_cols))
print('The possibilities Calculated are {}'.format(poss))

df = featureDateDifferences(df,date_cols)                                       # First parameter is DataFrame itself, and second DATE_COLUMNS
                                                                                # As expected the 'dummy2' column has zero possibility of being a date column, as shown below
                                                                                # Whereas all others have a positive possiblity
# Output DataFrame
df<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1424697161453625, 'fraction_numerical': 0.028204664617609836, 'mean_word_length': 2.6693200663349916, 'pattern_counts': {'":': 0, '<': 18, '<?xml version=': 0, '>': 20, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 33, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '291390', 'license': 'MIT', 'n_tokens_mistral': 3515, 'n_tokens_neox': 3277, 'n_words': 1050, 'path': 'DATE COLUMN DETECTION - GANESH RAM GURURAJAN.ipynb', 'repo_name': 'ganeshramg/Automatic-Detection-of-Date-Columns', 'size': '11076'}