0a - Preliminar steps.Rmd

---
title: "Preliminary"
author: '100385774'
date: "2023-02-18"
output: html_document
---

```{r setup, include=FALSE}
knitr::opts_chunk$set(echo = TRUE)
```

```{r}
library(data.table)
library(tidyverse)
library(bit64)
```
# Tweets

```{r}

data1 <- as.data.frame(data.table::fread("data/football_mentions_players.csv.gz"))
data2 <- as.data.frame(data.table::fread("data/football_mentions_teams.csv.gz"))
data3 <- as.data.frame(data.table::fread("data/football_players_cr.csv.gz"))
data4 <- as.data.frame(data.table::fread("data/football_players_de.csv.gz"))
data5 <- as.data.frame(data.table::fread("data/football_players_es.csv.gz"))
data6 <- as.data.frame(data.table::fread("data/football_players_jp.csv.gz"))
data7 <- as.data.frame(data.table::fread("data/football_players_ma.csv.gz"))
data8 <- as.data.frame(data.table::fread("data/football_teams.csv.gz"))
data9 <- as.data.frame(data.table::fread("data/football_terms.csv.gz"))

```

```{r}
tweets <- bind_rows(data1, data2, data3, data4, data5, data6, data7, data8, data9)
```

```{r}
#write.xlsx(data, file = "preliminar.xlsx", row.names = FALSE, sheetName = "Tweets")
```

#Accounts

```{r}
profiles <- data.table::fread("data/SEFutbol.csv.gz")
```


```{r}
 
profiles <- profiles %>% 
  select(c(id, created_at, public_metrics.followers_count, public_metrics.following_count, public_metrics.tweet_count))

```

## Filters histograms: 

```{r}
#Data preparation: 

profiles <- profiles %>% 
  mutate_if(is.integer, as.numeric)
```


```{r}
#Followers count: 
profiles %>% 
  filter(public_metrics.followers_count < 10000) %>% 
  ggplot(aes(x = public_metrics.followers_count)) +
  geom_histogram(bins = 50) +
  scale_x_continuous(limits = c(0,2500))
```


```{r}
profiles %>% 
  filter(public_metrics.followers_count > 30, public_metrics.followers_count < 1000) %>% 
  ggplot(aes(y = public_metrics.followers_count)) +
  geom_boxplot()

profiles %>% 
  filter(public_metrics.followers_count , public_metrics.followers_count < 1000) %>% 
  summary(profiles$public_metrics.followers_count)
```

```{r}
# Created at: 
profiles %>% 
  filter(created_at > 01/01/2006) %>% 
  ggplot(aes(created_at)) +
  geom_histogram(bins = 50)

summary(profiles$created_at)
```

```{r}

#Following count: 
profiles %>% 
  filter(public_metrics.following_count < 10000) %>% 
  ggplot(aes(x = public_metrics.following_count)) +
  geom_histogram(bins = 50) +
  scale_x_continuous(limits = c(0,7500)) + 
  scale_y_continuous(limits = c(0,100000))

summary(profiles$public_metrics.following_count)

```

```{r}
profiles %>% 
  filter(public_metrics.following_count > 10, public_metrics.following_count < 5000) %>% 
  ggplot(aes(y = public_metrics.following_count)) +
  geom_boxplot()
```


```{r}
#Tweets count: 
profiles %>% 
  filter(public_metrics.tweet_count > 0) %>% 
  ggplot(aes(x = public_metrics.tweet_count)) +
  geom_histogram(bins = 50) +
  scale_x_continuous(limits = c(1, 10000)) 

summary(profiles$public_metrics.tweet_count)

```


```{r}
profiles %>% 
  filter(public_metrics.tweet_count > 10, public_metrics.tweet_count < 50000) %>% 
  ggplot(aes(y = public_metrics.tweet_count)) +
  geom_boxplot() 
```

Variable creation: 
```{r}

profiles <- profiles %>% 
  mutate(ratio = public_metrics.tweet_count/round(as.numeric(as.POSIXct("2023-02-21") - created_at)),
         ratio = round(ratio, 2))
```

# All filtering
```{r}
filter1 <- profiles %>% 
  filter(public_metrics.followers_count > 50,
         public_metrics.following_count > 50, 
         public_metrics.following_count < 5000,
         ratio > 2, 
         ratio < 150,
         created_at > "2015-01-01" , 
         created_at < "2023-01-01")
```

# Random sample: 
```{r}

sample <- sample(filter1$id, 30000, replace = FALSE)

sampletext <- as.character(sample)
#write.table(sampletext, file = "sample.txt", row.names = FALSE)
```

# Importing the whole dataset

```{r}
SEF <- as.data.frame(data.table::fread("data/SEFutbol_sample.csv.gz"))

SEF <- SEF %>% 
  select(created_at, text)

```

```{r}
data1 <- as.data.frame(data.table::fread("data/lang/football_mentions_players.csv.gz"))
data2 <- as.data.frame(data.table::fread("data/lang/football_mentions_teams.csv.gz"))
data3 <- as.data.frame(data.table::fread("data/lang/football_players_cr.csv.gz"))
data4 <- as.data.frame(data.table::fread("data/lang/football_players_de.csv.gz"))
data5 <- as.data.frame(data.table::fread("data/lang/football_players_es.csv.gz"))
data6 <- as.data.frame(data.table::fread("data/lang/football_players_jp.csv.gz"))
data7 <- as.data.frame(data.table::fread("data/lang/football_players_ma.csv.gz"))
data8 <- as.data.frame(data.table::fread("data/lang/football_teams.csv.gz"))

tweets2 <- bind_rows(data1, data2, data3, data4, data5, data6, data7, data8)

tweetstotal <- bind_rows(tweets, tweets2, SEF)
```

Selecting only desired columns: 

```{r}
tweetstotal <- tweetstotal %>% 
  select(c(id, author_id, created_at, text, lang, entities.hashtags, entities.mentions, entities.urls))
```


Export all tweets: 
```{r}

#write.csv(tweetstotal, file = "data/tweets.csv", row.names = FALSE)

```