1 - Cleaning.Rmd

**loading libraries**

```{r message=FALSE}
library(tidyverse)
```

**Importing the CSV files**

```{r}
in13 <- read.csv('Data_files/in13.csv', row.names = 1)
an13 <- read.csv('Data_files/an13.csv', row.names = 1)
dati1 <- read.csv('Data_files/dati1.csv', row.names = 1)
```

# Data cleaning

**Looking for NA values**

```{r}
sum(is.na(in13))
sum(is.na(an13))
sum(is.na(dati1))

# As we can observe, the datasets 'an13' and 'dati1' 
# contains many missing values, while '1n13' has 0 NA.
```

**Now let's analyze each csv singularly:**

##   in13:

### General cleaning

```{r}
in13$prov_museo[in13$museo == "CENTRO FAUNISTICO UOMINI E LUPI"] <- "CN"
in13$com_museo[in13$museo == "CENTRO FAUNISTICO UOMINI E LUPI"] <- "ENTRACQUE"
in13$prov_museo[in13$museo == "MOSTRA BORN SOMEWHERE"] <- "TO"
in13$com_museo[in13$museo == "MOSTRA BORN SOMEWHERE"] <- "TORINO"
```

### Changing variable type

```{r}
in13$museo <- as.factor(in13$museo)
in13$prov_museo <- as.factor(in13$prov_museo)
in13$com_museo <- as.factor(in13$com_museo)
in13$datai <- as.Date(in13$datai, "%d/%m/%Y")
in13$orai <- str_sub(in13$orai, end = -4)
in13$orai <- as.integer(in13$orai)
```

## an13 & dati1

### Cleaning time variables

```{r}
dati1$ultimo_ing.x <- sub( '(?<=.{6})', '20', dati1$ultimo_ing.x, perl=TRUE)
dati1$abb13 <- sub( '(?<=.{6})', '20', dati1$abb13, perl=TRUE)
dati1$abb14 <- sub( '(?<=.{6})', '20', dati1$abb14, perl=TRUE)
dati1$ultimo_ing.x <- as.Date(dati1$ultimo_ing.x, "%d/%m/%Y")
dati1$abb13 <- as.Date(dati1$abb13, "%d/%m/%Y")
dati1$abb14 <- as.Date(dati1$abb14, "%d/%m/%Y")

an13$data_inizio <- str_sub(an13$data_inizio, end = -7) # remove hours and minutes
an13$data_inizio <- as.Date(an13$data_inizio, "%d/%m/%Y")
```

### Cleaning Cap

```{r}
turin_caps <- c(10121:10156)
turin_caps <- as.character(turin_caps)

#First: create cap for Turin --> 2328
generate_cap_for_turin <- function(comune, cap) {
  if (comune == 'TORINO' && cap == 'XXXXX') {
    cap <- sample(turin_caps, 1, replace = TRUE)
  }
  return(cap)
}


set.seed(1)
an13$cap <- mapply(generate_cap_for_turin, an13$comune, an13$cap)


#second: delete rows with dato mancante and XXXXX --> 181
an13 <- subset(an13, !(comune == 'DATO MANCANTE' & cap == 'XXXXX'))

#third: deleting remaining rows with XXXXX --> 181
an13 <- subset(an13, !( cap == 'XXXXX'))

#see results
# an13 %>% filter(cap=='XXXXX')
```

### Cleaning comune

```{r}
# an13 %>% filter(comune=='DATO MANCANTE') #-->1238
cap <- read_csv('Data_files/Cap.csv', show_col_types = FALSE)
# cap
matching_indices <- match(an13$cap, cap$CAP)

# Identify the rows in 'an13' with missing 'Comune' values ('DATO MANCANTE')
missing_comune_rows <- an13$comune == "DATO MANCANTE"

# Update the missing 'Comune' values in 'an13' using the 'cap' dataset
an13$comune[missing_comune_rows] <- cap$Comune[matching_indices[missing_comune_rows]]
# an13 %>% filter(comune=='DATO MANCANTE')
```

### General cleaning

```{r}
an13 <- an13[an13$data_nascita != "1-01", ]
an13 <- an13[an13$data_nascita != "9-02", ]
an13$data_nascita <- as.integer(an13$data_nascita)
an13 <- an13[an13$data_nascita <2014, ]

# an13 <- an13[an13$comune != "DATO MANCANTE", ]
# an13 <- an13[an13$cap != "XXXXX", ]

an13$agenzia_tipo[an13$agenzia == "ASSOCIAZIONE TORINO CITTA' CAPITALE EURO"] <- "ASSOCIAZIONE"
an13$agenzia_tipo[an13$agenzia == "LICEO SCIENTIFICO GALILEO FERRARIS"] <- "OFFERTA SCUOLE "
colnames(an13)[colnames(an13) == "OFFERTA SCUOLE "] <- "OFFERTA SCUOLE"

an13 <- an13[an13$cap != "10.12", ]
an13 <- an13[an13$cap != "BIANC", ]
an13 <- an13[an13$cap != "\xa0 100", ]
an13 <- an13[an13$cap != "100", ]
an13 <- an13[an13$cap != "10000", ]
an13 <- an13[an13$cap != "10007", ]
an13 <- an13[an13$cap != "CORSO", ]
an13 <- an13[an13$cap != "ESTER", ]
an13 <- an13[an13$cap != "L2210", ]
an13 <- an13[ nchar(an13$cap) >= 5, ]

an13$cap[an13$codcliente == 14061] <- "10129"
an13$cap[an13$codcliente == 127888] <- "10124"
an13$cap[an13$codcliente == 199123] <- "10145"
an13$cap[an13$codcliente == 203569] <- "10048"
an13$cap[an13$codcliente == 211576] <- "10125"
```


### Merging

```{r}
total <- merge(an13, dati1, by="codcliente", all.x = TRUE)
```

```{r}
sapply(total, function(x) sum(is.na(x)))
```

```{r}
total <- total[, !(names(total) %in% c('sesso.y', 'prezzo13', 'prov', 'professione'))]
colnames(total)[colnames(total) == "sesso.x"] <- "sesso"

total["nvisite0512"][is.na(total["nvisite0512"])] <- 0
total["nmusei0512"][is.na(total["nmusei0512"])] <- 0
```

### Changing variable type

```{r}
total$churn <- ifelse(total$si2014 == 0, 1, 0)

total$si2014 <- NULL
total$si2013 <- NULL
total$data_nascita <- NULL

total$sconto <- as.factor(total$sconto)
total$riduzione <- as.factor(total$riduzione)
total$tipo_pag <- as.factor(total$tipo_pag)
total$agenzia <- as.factor(total$agenzia)
total$agenzia_tipo <- as.factor(total$agenzia_tipo)
total$sesso <- as.factor(total$sesso)
total$comune <- as.factor(total$comune)
total$cap <- as.integer(total$cap)
total$nuovo_abb <- as.factor(total$nuovo_abb)

total$data_inizio <- as.integer(month(total$data_inizio))
total$ultimo_ing.x <- as.integer(month(total$ultimo_ing.x))
total$abb14 <- as.integer(month(total$abb14))
total$abb13 <- as.integer(month(total$abb13))

total$ultimo_ing.x <- ifelse(is.na(total$ultimo_ing.x),0, total$ultimo_ing.x)
total$abb14 <- ifelse(is.na(total$abb14),0, total$abb14)
total$abb13 <- ifelse(is.na(total$abb13),0, total$abb13)
```


```{r}
sapply(total, function(x) sum(is.na(x)))
```

```{r}
total <- na.omit(total)
```


```{r}
sapply(total, function(x) sum(is.na(x)))
```