Banistmo Challenge Exploratory Analysis

Dataset Loading

Load the dataset, with the correct date format and the name of the columns, which is present in the CSV:

library(readr)
dataset <- read_csv("C:/Users/Leonel/Projects/dataset.csv", col_types = cols(fecha = col_date(format = "%Y%m%d")))

Exploratory Analysis

As usual, the first thing is to see what the data looks like and it is correctly handled:

head(dataset)

Now, I need to know the * dimensions * of the dataset:

dim(dataset)

[1] 1000001       4

We see that the dataset has a million rows and 4 columns.

I want to see a fast summary of the data:

summary(dataset)

       X1              fecha                  id                txn            
 Min.   :      0   Min.   :2016-01-04   Min.   :       6   Min.   :-1328767.1  
 1st Qu.: 250000   1st Qu.:2016-01-04   1st Qu.:  432193   1st Qu.:    -100.0  
 Median : 500000   Median :2016-01-08   Median :65048457   Median :     -24.0  
 Mean   : 500000   Mean   :2016-03-23   Mean   :54800228   Mean   :     -97.6  
 3rd Qu.: 750000   3rd Qu.:2016-05-03   3rd Qu.:98663337   3rd Qu.:      -2.9  
 Max.   :1000000   Max.   :2016-12-06   Max.   :98953092   Max.   :  450000.0  
                   NA's   :396151                          NA's   :396151      

It can be seen:

1. The first thing that can be observed is that the identifier column is indexed to 0. This can be an issue if we want to insert that value as a PK in some DB handlers.
2. I see that the range of values for the ID column is quite high, to be a column of users. To study values, it may be convenient to leave out this value.
3. date and txn have 396151 NA’s or missing data. Inspecting quickly, I can see that when one is missing, the other too. Very convenient.

Now, I want a list of rows with missing values:

dataset[!complete.cases(dataset),]

I see that there are more than 390,000 rows without data. Approximately 40% of the data.

Cleaning and Imputation

At this point, it is normal to decide what to do with the missing data. On normal conditions, one would check with the source of the data about the missing rows, if it is possible and the implications. However, for this challenge that’s not a possibility.

With that in mind,the first impulse would be to discard the missing data, especially because the rows that lack information have all 2 missing observations. However, this matter would naturally awaken the curiosity of any scientist. Imputing quantitative observations (txn) would not represent a greater challenge. However, for the missing dates, it may not be trivial. My first impulse is to find out if the dates are ordered logically.

I’m going to extract the month:

library(dplyr)
library(lubridate)
dataset2 <- mutate(dataset, month = month(dataset$fecha, label = TRUE))
library(ggplot2)
ggplot(dataset2,aes(x=month,fill=month)) + geom_bar(width = 0.6) + theme(legend.position = "none") + labs(title="Data distribution by month")

ggplot(dataset2,aes(x=fecha,fill=fecha)) + geom_bar(width = 0.6) + theme(legend.position = "none") + labs(title="Data distribution by day")

I worry that the data does not seem to have an equal distribution. There seems to be much more data in January than in May and December together. In addition, the only 3 months that have data, seem to be randomly distributed. (From January to May there are 4 months and from May to December, there are 7) The distribution per day makes inequality in the distribution of data much more evident.

In conclusion, I do not feel that it has great value for the analysis of data, to make imputations to the date and to the amount of transactions for the users. What I consider valuable, is to take into account the records with missing information, since only the user who realizes the transaction and suna valuable information, in case you want to summarize the amount of missing transactions for a user, or the number of transactions totals in a given month. The way to insert the data in a database will depend on the objective of the analysis, of course. In addition, the management information system that will process the data. I recommend placing an invalid date and zero (0) as the value of the transaction. (This is because being transactions with income and expenses, zero is a value that will not affect the balance calculations).

Although the exercise does not specify the level of confidence that you have in this dataset, due to the missing date and txn data, it is logical to be a bit skeptical. I consider it imperative to see the distribution in the amount of transactions:

boxplot(dataset$txn)

The result makes sense for banking transactions. Although there is someone who made a retreat a little atypical !. -1328767.1 Maybe it’s worth reviewing … But it’s not the goal of this exercise.

Conclusions

The data is somewhat noisy and scattered. It is to be expected in a test dataset. However, performing this exploratory analysis has allowed us to obtain valuable information for the creation of a compatible data model in a Backend system, as required by the exercise. Things to take into account:

• The identifier of the rows is indexed to zero.
• The date may require parsing before being inserted into a handler.
• The UUID must be an integer.
• The TXN must be a signed decimal.
• Records with a missing date should be inserted, but considered in the results.
• The records with missing date must be shown to the end user in some way, without affecting aggregates or accounts.