This project was made using the following Youtube video as a reference: [Click here to see the video](https://www.youtube.com/watch?v=5T9Bbe1RZGU&list=PLMfXakCUhXsH2WQa255vU75R8-u46-eF0&index=3).

The work consisted in using the .bak database, import it in Microsoft SQL Server Management Studio and do some tasks in order to get the related tables. The original database had two tables, the first one with data about the athlete's country and the other one with data about the athlete, the olympic games, the sports and the winners.

First of all, the following actions were carried out in the table that had more information:

*   Split into two different columns the values of year and season, that were initially in one column.

*   Turn into NULL the NA values when the athlete didn´t win any medal.

*   Create different tables for: athletes, games, events, sports and medals.

*   Remove duplicates from the new tables and replace in the original table the columns of the new tables for the ID number (foreign keys).

Then, it was identified that in the table of countries some names were duplicated with differences/typing errors, therefore, the following was done:

*   A csv file with names and ISO3 codes of the countries was imported and names in the table of countries were replaced for the correct ones.

Finally in SQL, the top 10 countries with the most medals in the summer olympic games were consulted.

<div style="text-align: center">➡️[Access the complete SQL code here](https://github.com/julietacoll/olympic_games)⬅️</div>

After finishing in SQL, the database was connected to Power BI and the following aditional steps were taken:

*   Add a new auxiliary table to calculate the age of each athlete at the moment of the olympic games

*   Create measures for the age, the weight and the height.

The following recomendations were followed to create the map graph: [Steve Winward Github](https://github.com/SteveWinward/PowerBI/blob/master/WriteUps/world-map.md)

This is how the dashboard looks after appliyng some filters:

![](/images/1.png)

![](/images/2.png)

![](/images/3.png)


Olympic Games

This project analyses the increase of prices in food in Argentina over the period of a month. Firstly, the following groups of food were selected: meat and products of animal origin, fruits and vegetables and legumes and grains. Then, specific products were selected for each group according to the the basic food basket of the National Statistics and Census Institute (INDEC).

##### Data mining and data cleaning

During 31 days data mining was carried out through the web site of a supermarket and data cleaning was made using the following R script:

<div style="text-align: center">➡️[Access the full R script here](https://rpubs.com/julietacoll/scraping_alimentos)⬅️</div>

<div style="text-align: center"></div>

<div style="text-align: center"></div>

Every food category was analysed in Excel in order to find outliers, which were then removed in R.

![](/images/outl-1985f6de.jpg)

As the graphic shows, some food categories didn´t have the information of the price, such like "carnaza" and "espinazo", so they were removed.

Daily, the new data was appended to the existing one and records were deleted if the product title was not repeted the same number of times as days with available information, that is, if the individual product did not appear every day (checked using an Excel formula) it was deleted in order to avoid that the variation in the price is given by variations in the product selection.

Thus, we arrive at a base with 11.935 observations for the 385 products extracted daily between January 27 and February 26.

Finally, using dynamic tables, graphs and formulas, the Excel dashboard was developed.

##### Some conclusions

*   Inflation between January 27 and Febreuary 26 was 6.77%

*   The item which price increased the most was legumes, close to 58%

*   The item which price increased the least was vegetables by 2%, being this, together with fruits, the two items with the greatest fluctuation in prices, wich may be due to seasonality and weather conditions.

*   Although the price of meat increased by 15%, beef price is the one that increased the most, while fish and chicken incrising below 10%. The product which price increased the most is the flank, with an increase of 100%.

*   The category with the highest prices is cold cuts, followed by dairy products and meat.

*   Although legumes were the ones which price increased the most, the maximum price per kilo of legumes is $2.000 and the average price per kilo is $1.000.



Food prices in Argentina

Big Brother is an argentinian TV show that lunched its tenth edition on October 2022. Between January 10, 2023 and February 26, 2023 web scrapping of Twitter was made in order to analyse the behaviour of the audience according to their tweets.

At the moment this work started, January 10, 2023, only 10 participants remained of the 20 who entered the house and when the work was finished 6 participants remained.

The steps taken to arrive at the Excel dashboard were the following:

##### **In R**

*   Usage of the Twitter API to get the information on the tweets containing the following hashtags:

<div style="text-align: center">*#GranHermano*</div>

<div style="text-align: center">*#GH2022*</div>

<div style="text-align: center">*#GH22*</div>

<div style="text-align: center">*#GH2023*</div>

<div style="text-align: center">*#GH23*</div>

*   Extraction of the names of the participants mentioned in each tweet.

<div style="text-align: center">➡️[Access the R script here](https://rpubs.com/julietacoll/scraping_twitter_gh)⬅️</div>

##### **In Excel**

*   Extraction of the time and day of the week of the creation of the tweet



*   Creation of dynamic tables to create graphs



*   Excel formulas



*   Dashboard

Because of the limitations to take data from Twitter API the R script was run daily and then duplicates were deleted using the ID as primary key.

##### **Some observations**

*   The day with the most tweets was Monday, February 13, the day after the elimination of the participant Alfa.

*   Dawn is the time of the day with more tweets, being 1 am the hour with the most tweets.

*   Monday, Tuesday and Friday are the days with the most tweets. Taking into account that the most popular time is at dawn, these events correspond respectively to the elimination of participants on Sunday night, the live debate with the one who was eliminated on Monday night and the definition of the nominees after the leader of the week saves a participant from the plaque on Thursday night.

*   The tweet with the most likes and retweets is the same: it is the picture of the two puppies which entered the house on Februrary 13.

*   During the period covered by the data, those eliminated from the house were:

    *   Agustín on January 29

    *   Ariel on February 5

    *   Alfa on February 12

    *   Daniela on February 19

Agustín, Alfa and Daniela had more mentions the day after they left the house, probably that same dawn, while Ariel had more mentions two days before leaving the house.

*   La Tora is the participant who is still in the house with the fewer mentions, even less than Alfa and Daniela who have already left the house. It should be noted that the partcipant entered by repechage after being eliminated and she did so with a lower profile.
*   Marcos is the participant with the most mentions despite the fact that he is not a controversial character nor does he have much activity in the house.

Big Brother Argentina

##### About the data

The dashboard was made using the data made available by the Maven Analytics company through the following website: [Click here.](https://www.mavenanalytics.io/blog/maven-marketing-challenge)

The dataset contains data of the marketing campaigns on 2.240 clients and have information about the customer profile, their product preferences, performance of sales channels and success of the different marketing campaigns.

##### Cleaning

With the data available, each feature of the dataset was analysed looking for outliers, data inconsistencies and mistakes. In order to achive this the following graphs were made:

##### ![](/images/analisisvariables_page-0001.jpg)

As a result of the analysis, the following actions were carried out:

Year_Birth: three customers whose year of birth is less than 1940 were eliminated.

Income: The furthest outlier value was modified by the next maximum value. The gaps were filled with the average value of the column.

Marital_status: Absurd, YOLO and Alone were changed by Single.

Finally, dynamic tables and formulas were made to achieve the dashboard.


Maven Marketing Challenge

El presente trabajo fue realizado en Power BI como trabajo final del curso de Data Analytics de Coder House y fue calificado con un 10, permitiendo a los integrantes del grupo formar parte del Top 10 de Coder House.

La base de datos utilizada fue descargada del sitio de Datos Abiertos del Ministerio de Desarrollo Productivo y detalla los grandes proyectos de inversión aprobados durante los años 2020 y 2021 en la República Argentina para recibir beneficios impositivos en la importación de bienes nuevos que formen parte de nuevas líneas de producción completas y autónomas.

El objetivo de este programa es alentar las inversiones para incorporar tecnologías de última generación en los productos industrializados nacionales. Estos  beneficios impositivos son la reducción al 0% de la tasa en concepto de derechos de importación y su excepción al pago de tasa de comprobación de destino.

El dataset aporta descripción de los proyectos, montos de inversión, localización geográfica del proyecto e importaciones afectadas a los mismos, entre otros datos.

El tablero permite observar cómo se están distribuyendo los recursos del programa, tanto geográficamente como entre rubros. Asimismo, permite observar cómo el programa incrementó el empleo y así obtener información acerca de los rubros que más empleo generan dentro del programa. Adicionalmente, brinda información sobre los montos de los proyectos y de las inversiones realizadas, lo que puede cruzarse con los datos de generación de empleo y por rubros.

Puede consultar el tablero completo en el siguiente link:

<div style="text-align: center">➡️[TABLERO DE POWER BI](https://drive.google.com/file/d/1mdF7lX4l9wRp3AsTerQXWjV84Kkltav3/view) ⬅️</div>

Puede consultar la documentación y descripción del proyecto y los pasos  desarrollados en el siguiente link:

<div style="text-align: center">➡️[DOCUMENTACIÓN](https://drive.google.com/file/d/1XB_8qa7jpeX-FxpYUlaOoGbutXtXse_q/view) ⬅️</div>



Beneficios impositivos para proyectos de inversión (spanish)

El trabajo fue realizado de manera grupal para la Diplomatura de Análisis de Datos aplicados a Políticas Públicas de la Universidad Nacional Guillermo Brown (UNAB) y abarcó las siguientes tareas:

1.  Búsqueda de bases de datos del sector público con información relativa al sector productivo apícola

2.  Limpieza de datos en R

3.  Caracterízación del sector mediante visualizaciones realizadas en R y excel

4.  Cruce de distintas bases de datos

5.  Análisis de mejora de políticas públicas orientadas al sector.

<div style="text-align: center">➡️[ACCEDÉ AL TRABAJO (CON EL SCRIPT DE R) ACÁ](https://rpubs.com/julietacoll/apicolas-final-codigo)⬅️</div>



Projects

Olympic Games

Food prices in Argentina

Big Brother Argentina

Maven Marketing Challenge

Beneficios impositivos para proyectos de inversión (spanish)

Datos del sector público sobre la actividad apícola (spanish)