I am into fintech business and I have customer level data like below:
| Customer ID | day1 | day2 | day3 | day4 |
|---|---|---|---|---|
| 1 | 50 | 0 | NaN | 5 |
| 2 | NaN | 10 | NaN | NaN |
| 3 | -100 | -100 | 5 | 0 |
| 4 | 10 | -60 | 0 | 100 |
| 5 | 20 | NaN | -20 | NaN |
In the above data, the rows represent customer unique ID, columns represent the specific day of the year and the values represent the net of credit and debit on that specific day.
For example, for customer ID = 1 on day1, the credit = 100 and debit = 50 therefore net is 50. Zero means credit = debit on that day.
NaN in my data simply shows that on that specific day, the customer neither did any credit or debit transactions from the app, and this knowledge is important to me. Let NaN be the instance of NO INTERACTION.
Now my question is how do I tell K-Means in scikit-learn python to treat NaN values like No Interaction? I don't want to eliminate NaN values. I also don't want to replace NaN values with mean or median values. NaN values are additional information for me and how do I retain this information?
import numpy as np
df_no = df.replace(np.nan, 'no', regex=True)
The k-means and other clustering methods using distance (Euclidean distance) to calculate.
To calculate distance you need numbers and all thing must be quantitative.
Exactly, you must delete those or replace those NaN values with the best representative (median or mean or 0).
How do I decide?
Which customer do you think is closer to customer number 1?
customer 2, 3 or 4?
| Customer ID | day1 | day2 | day3 | day4 |
|---|---|---|---|---|
| 1 | 50 | 0 | NaN | 5 |
| 2 | 50 | 0 | 0 | 5 |
| 3 | 50 | 0 | median | 5 |
| 4 | 50 | 0 | mean | 5 |