Appearance
Homework 4
Instructions
Make sure of the following:
- Your solutions should be in form of a report in .md or .ipynb format.
- You have documented your procedure properly.
- Your answers are clear and concise.
When you are finished push you results to Github and raise an issue, just as you have done in previous homeworks. To pass the homework you will have to complete the assigments below and also finish the peer-review.
Feel free to contact me if anything is unclear.
Joining Data
Use the cleaned data from the previous homework!
Now that we have cleaned our cell phones data, we want to analyse the number of cell phones per capita. To make this possible, we need to expand our original dataset. The file pop_data.csv contains data about the population size of countries over years. Once again there will be missing values. Deal with them before proceeding.
To combine the cell phone data with the population data, we need to reshape both datasets. This can be done by pivotting the tables into the right format. Remember the 3 rules from week 3. Present a similar table to below the one below.
| iso-3 | year | n_cellphones | population |
|---|---|---|---|
| ABW | 1960 | 0 | 54608 |
| AFG | 1960 | 0 | 8.62247e+06 |
| AGO | 1960 | 0 | 5.3572e+06 |
| ALB | 1960 | 0 | 1.6088e+06 |
| AND | 1960 | 0 | 9443 |
The iso-3 format is nice to work with but for presentation it is better to work with real names of the countries. A mapping between iso-3 and country name can be found in the country_data.csv file.
Combine the cell phone, population and country data into a single table. Calcualte the number of phones per capita. I.e n_cellphones/population_size. Present a similar table to below. Here, the coutries are the ones with highest number of cell phones per capita in 2019.
| year | Macao | Hong Kong | United Arab Emirates | Antigua and Barbuda | Seychelles |
|---|---|---|---|---|---|
| 2015 | 3.08823 | 2.2904 | 2.00742 | 1.95684 | 1.58426 |
| 2016 | 3.14351 | 2.39893 | 2.21252 | 1.98754 | 1.60546 |
| 2017 | 3.2101 | 2.48877 | 2.18343 | 2.01934 | 1.74243 |
| 2018 | 3.34874 | 2.67021 | 2.19908 | 2.00816 | 1.8499 |
| 2019 | 3.28485 | 2.86365 | 2.12774 | 1.99746 | 1.9872 |
Note that you might get a slightly different table based on how you deal with missing data. This is okay! make sure to document your procedure.
Analyse the country with highest growth rate of cell phones (per capita) in the last 10 years. That is, create a plot that illustrates the growth rate of the countries in the table above over the last 10 years.
Hint: You can pivot the tables to get the desired output.
SQL
The file user_actions.db in the data repo contains an SQLite database. In the database there is a table named user_actions. Analyse the table and solve the following tasks.
- Retrieve the usernames of all users who have performed the "signup" action.
- Find the total number of log entries for each user. Display the user_id, username, and the count of log entries.
- Identify users who have both logged in (action = 'login') and signed up (action = 'signup') on the same day. Display the user_id and username.
Each task should be solved only using SQL.
Regex
The file comments.txt in the data repo contains lines of text, each representing a user comment. Users sometimes include tags in their comments using the format "#tag". Analyse the file and solve the following tasks.
- Write a regular expression to extract all hashtags from a given comment. For example, applying the regex to comment 1 should return ["#programming", "#tips"].
- Create a regular expression to find comments that mention both "#programming" and "#python". Apply the regex to comment 2 and check if it matches.
- Using your regular expression, extract all unique hashtags from the entire text file. (*)
The last task is optional (*)!
Each task should be solved only using regex.
Good Luck!