Method and system for identifying duplicate columns using statistical, semantics and machine learning techniques
With the availability of huge amount of data, it has becoming difficult to identify and manage duplicate data, especially when the data is in a plurality of columns. A method and system for identifying duplicate columns using statistical, semantics and machine learning techniques have been provided. The system provides a design framework to compare huge datasets at column level and identify potential duplicate columns, not based on the column title, but based on all of its values. The disclosure has ability to compare values in multiple columns and identify potential duplicate columns wherein comparison of values is not only for the exact match, but for semantic match, smart match, fuzzy match, and match after UOM conversion etc. using Statistical, semantics and machine learning techniques.
1. A processor implemented method for identifying duplicate columns among a plurality of columns, the method comprising:
receiving, by an input/output interface, an input data from an input file, wherein the input data is in a form of tabular data having a plurality of rows and the plurality of columns;
preprocessing, by one or more hardware processors, the input data;
deriving, by the one or more hardware processors, a statistical score for each pair of columns from possible pairs of columns in the plurality of columns in the preprocessed input data, wherein the statistical score is derived using a Kramer's correlation method;
selecting, by the one or more hardware processors, a first set of pair of columns from the possible pairs of columns, wherein the first set of pair of columns satisfies a predefined condition, and wherein the first predefined condition is the statistical score for the pair of columns is more than a first threshold value;
performing, by the one or more hardware processors, a row level analysis on the selected first set of pair of columns using one or more of:
a fuzzy logic technique,
a semantic level analysis using a word embedding technique, wherein the word present in the plurality of columns
checking a concurrence of a plurality of words on the selected first set of pair of columns, and
utilizing a look up table after converting unit of measures of the input data, wherein the row level analysis results in generation of a row level score;
selecting, by the one or more hardware processors, a set of characteristic pair of columns out of the first set of pair of columns if the generated row level score is more than a second threshold value; and
identifying, by the one or more hardware processors, the selected set of characteristic pair of columns as duplicate columns in the form of an output file.
2. The method of claim 1 , further comprising providing intervention by a subject matter expert by manually screening the identified duplicate columns.
3. The method of claim 1 , wherein the preprocessing further comprises:
excluding columns out of the plurality of columns whose attributes do not have any value,
excluding rows out of the plurality of rows having non-positive values, and
maintaining a custom configuration which are specific to a set of conditions.
4. The method of claim 1 , further comprising removing the identified duplicate columns.
5. The method of claim 1 , wherein the input file and the output file are in a form of at least one or more of comma separated value (.csv) format, XLS format, XLSX format.
6. The method of claim 1 , wherein the preprocessing is preceded by merging the input data received from more than one type of input files.
7. A system for identifying duplicate columns among a plurality of columns, the system comprises:
an input/output interface for receiving an input data from an input file, wherein the input data is in a form of tabular data having a plurality of rows and the plurality of columns;
one or more hardware processors;
a memory in communication with the one or more hardware processors, the one or more hardware processors further configured to perform the steps of:
preprocessing the input data;
deriving a statistical score for each pair of columns from possible pairs of columns in the plurality of columns in the preprocessed input data, wherein the statistical score is derived using a Kramer's correlation method;
selecting a first set of pair of columns from the possible pairs of columns, wherein the first set of pair of columns satisfies a predefined condition, and wherein the first predefined condition is the statistical score for the pair of columns is more than a first threshold value;
performing a row level analysis on the selected first set of pair of columns using one or more of:
a fuzzy logic technique,
a semantic level analysis using a word embedding technique, wherein the word present in the plurality of columns
checking a concurrence of a plurality of words on the selected first set of pair of columns, and
utilizing a look up table after converting unit of measures of the input data, wherein the row level analysis results in generation of a row level score;
selecting a set of characteristic pair of columns out of the first set of pair of columns if the generated row level score is more than a second threshold value; and
identifying the selected set of characteristic pair of columns as duplicate columns in the form of an output file.
8. The system of claim 7 , wherein the input file and the output file are in a form of at least one or more of comma separated value (.csv) format, XLS format, XLSX format.
9. One or more non-transitory machine readable information storage mediums comprising one or more instructions which when executed by one or more hardware processors cause managing a plurality of events, the instructions cause:
receiving, by an input/output interface, an input data from an input file, wherein the input data is in a form of tabular data having a plurality of rows and the plurality of columns;
preprocessing the input data;
deriving a statistical score for each pair of columns from possible pairs of columns in the plurality of columns in the preprocessed input data, wherein the statistical score is derived using a Kramer's correlation method;
selecting a first set of pair of columns from the possible pairs of columns, wherein the first set of pair of columns satisfies a predefined condition, and wherein the first predefined condition is the statistical score for the pair of columns is more than a first threshold value;
performing a row level analysis on the selected first set of pair of columns using one or more of:
a fuzzy logic technique,
a semantic level analysis using a word embedding technique, wherein the word present in the plurality of columns
checking a concurrence of a plurality of words on the selected first set of pair of columns, and
utilizing a look up table after converting unit of measures of the input data, wherein the row level analysis results in generation of a row level score;
selecting a set of characteristic pair of columns out of the first set of pair of columns if the generated row level score is more than a second threshold value; and
identifying the selected set of characteristic pair of columns as duplicate columns in the form of an output file.