# US 2021/0342320 A1 · Method and system for identifying duplicate columns using statistical, semantics and machine learning techniques

> Agents: this data is available as JSON from the patentgrep API and MCP server — see https://patentgrep.com/llms.txt

- Number: US 2021/0342320 A1
- Document ID: US20210342320A1
- Status: Published application
- Kind: A1
- Application number: 17/136,124
- Filed: 2020-12-29
- Published: 2021-11-04
- Earliest claimed: 2020-03-27
- Assignees: Tata Consultancy Services Limited
- Applicants: Tata Consultancy Services Limited
- Inventors: Ganesh Prasath RAMANI; Aasish CHANDRA; Jayanth SHENAI; Raja ANGAMUTHU; Pankaj Kumar MISHRA
- Web page: https://patentgrep.com/patent/US20210342320A1

## CPC

- G06F 16/215: Improving data quality; Data cleansing, e.g. de-duplication, removing invalid entries or correcting typographical errors
- G06F 40/18: of spreadsheets (form-filling G06F40/174)
- G06F 16/221: {Column-oriented storage; Management thereof}
- G06F 40/211: Syntactic parsing, e.g. based on context-free grammar [CFG] or unification grammars
- G06F 40/30: Semantic analysis
- G06N 20/00: Machine learning
- G06F 40/284: Lexical analysis, e.g. tokenisation or collocates
- G06N 7/02: using fuzzy logic (computing arrangements based on biological models G06N3/00; computing arrangements using knowledge-based models G06N5/00)
- G06F: ELECTRIC DIGITAL DATA PROCESSING (computer systems based on specific computational models G06N)
- G06N: COMPUTING ARRANGEMENTS BASED ON SPECIFIC COMPUTATIONAL MODELS

## Abstract

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.

## Claims

### Claim 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 the form of tabular data having a plurality of rows and the plurality of columns (202);
preprocessing, by one or more hardware processors, the input data (204);
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 (206);
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 (208);
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 the 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 (210);
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 (212); 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 (214).

### Claim 2 (depends on 1)

The method of claim 1 further comprising the step of providing intervention by a subject matter expert by manually screening the identified duplicate columns.

### Claim 3 (depends on 1)

The method of claim 1, wherein the statistical score is derived using a Kramer's correlation method.

### Claim 4 (depends on 1)

The method of claim 1, wherein the preprocessing 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.

### Claim 5 (depends on 1)

The method of claim 1, further comprising the step of removing the identified duplicate columns.

### Claim 6 (depends on 1)

The method of claim 1, wherein the input file and the output file is are in the form of at least one or more of comma separated value (.csv) format, XLS format, XLSX format.

### Claim 7 (depends on 1)

The method of claim 1 wherein the step of preprocessing is preceded by the step of merging the input data received from more than one type input files.

### Claim 8

A system (100) for identifying duplicate columns among a plurality of columns, the system comprises:
an input/output interface (102) for receiving an input data from an input file, wherein the input data is in the form of tabular data having a plurality of rows and the plurality of columns;
one or more hardware processors (104);
a memory (106) 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;
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 the 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.

### Claim 9 (depends on 8)

The system of claim 8, wherein the input file and the output file is are in the form of at least one or more of comma separated value (.csv) format, XLS format, XLSX format.

### Claim 10

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 the 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;
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 the 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.
