# Data cleaning: Relationships between columns

I have a training data set distributed in two files.

File 1: This contains actual classification for each X1. X1 is unique in this file. X1 has one-to-one relationship with X2, i.e. X2 is also unique. Y is binary.

| X1 | X2 | Y  |
| 1  | 4  | 0  |
| 3  | 5  | 1  |
...
| 8  | 9  | 1  |


File 2: This contains the real 'observations' of the experiment. X1 can appear multiple times.

| X1 | X3 | X4 |
| 3  | 4  | 5  |
| 3  | 1  | 2  |
...
| 1  | 4  | 8  |


Here I can combine the two tables to have a structure like below and use them as observations:

| X1 | X2 | X3 | X4 | Y |
| 3  | 5  | 4  | 5  | 1 |
| 3  | 5  | 1  | 2  | 1 |
...
| 1  | 4  | 4  | 8  | 0 |


For test data I have similar structure, just the Y column is missing in File 1.

I have multiple concerns here:

1. X1 and X2 has one-to-one dependency in the data, i.e. X1 = f(X2) and X2 = f(X1)
2. Y = f'(X1) or f'(X2)
3. Frequency distribution of X1,X2 and Y changes dramatically in the new joined data set.

Questions:

1. Does this kind of transformation of data leads to any insights?
2. Does regression and ensemble learning techniques are capable of capturing these internal relationships?

I see several issues in your data.

First of all, if there is a one-to-one relationship between X1 and X2, you should remove one of the two columns, because they are redundant. Redundant data may have a negative impact on your classification performance.

Secondly, the fields X3 and X4 also seem to be totally redundant, since the value of the class label Y only depends on X1/X2. So unless the columns X3 and X4 may be interesting on their own, I don't see the point of including them into the data.

Having dealt with these issues, and in order to obtain Y from X1/X2, there are two possibilities. If file1 contains the value of Y for any possible value of X1 in your domain, you don't need any machine learning technique. You have a perfect mapping. Otherwise, you will need to apply machine learning to find a function that "fills the gaps". Depending on the nature of the Y variable, you will need to use a regression (if Y is a real number) or classification (if Y is a discrete variable).

• Quoting : "For test data I have similar structure, just the Y column is missing in File 1." This means that in the test data, I get File 1 without Y column (we have to predict). The objective is to classify the observations in test data File 2, and using that figure out the classification of X1. For example, a candidate in X1=6 might be appearing in File 2 100 times. If in those 100 instances, we can classify 80 to be 1 and 20 to be 0, we can classify X1=1 with 0.80 probability. – Mohitt May 15 '15 at 8:53
• Are you assigning a Y value in your training set in file1 for each value of X1? – Pablo Suau May 15 '15 at 8:55
• No. In training data set File 1 has X1 classified already. The observations where X1 (both that are classified as 0 or 1) are participating in File 2. For test data I have X1 only in my File 1, i.e. no classification. The range(X1, training_data) is mutually exclusive to range(X1, test_data). – Mohitt May 15 '15 at 8:59
• Ok, so I have one additional question. Are there observations in file2 with the same value of X1 but different values of Y? – Pablo Suau May 15 '15 at 9:57
• file2 does not contain any classification. Let me spill the beans here. This question is an obfuscated version of facebook problem on kaggle. The X1 is the bidder_id. File1 contains classification info of bidders. File2 contains the bids from the bidders. The bids are not classified for us. Now, I am trying to reformulate this problem as bid-classification leading to bidder-classification. If I can classify bids then i guess classifying bidders is easier. – Mohitt May 15 '15 at 10:05