<jupyter_start><jupyter_text>DSCI: 525 Milestone 1 - Group 8 <NAME>, <NAME>, <NAME>, <NAME>  Imports<jupyter_code>import re
import os
import glob
import zipfile
import requests
from urllib.request import urlretrieve
import json
import pandas as pd
from memory_profiler import memory_usage

# Dask
import dask.dataframe as dd

# pyarrow and feather
import pyarrow.feather as feather
import pyarrow.dataset as ds
%load_ext rpy2.ipython
%load_ext memory_profiler<jupyter_output><empty_output><jupyter_text>Downloading the data<jupyter_code># Necessary metadata
article_id = 14096681  # unique identifier of the article on figshare
url = f"https://api.figshare.com/v2/articles/{article_id}"
headers = {"Content-Type": "application/json"}
output_directory = "figshare/"
response = requests.request("GET", url, headers=headers)
data = json.loads(response.text)  # this contains all the articles data, feel free to check it out
files = data["files"]             # this is just the data about the files, which is what we want
files<jupyter_output><empty_output><jupyter_text>Unzipping the data<jupyter_code>%%time
files_to_dl = ["data.zip"]  # feel free to add other files here
for file in files:
    if file["name"] in files_to_dl:
        os.makedirs(output_directory, exist_ok=True)
        urlretrieve(file["download_url"], output_directory + file["name"])
%%time
with zipfile.ZipFile(os.path.join(output_directory, "data.zip"), 'r') as f:
    f.extractall(output_directory)<jupyter_output>CPU times: user 19.4 s, sys: 3.86 s, total: 23.2 s
Wall time: 24.6 s<jupyter_text>Combining data CSVs using Pandas<jupyter_code>df = pd.read_csv("./figshare/ACCESS-CM2_daily_rainfall_NSW.csv")
df
%%time
%memit
# Shows time that regular python takes to merge file
# Join all data together
## here we are using a normal python way of merging the data 

files = glob.glob('figshare/*.csv') # load all the CSVs
df = pd.concat((pd.read_csv(file, index_col=0) # combine them all
                .assign(model=re.findall(r'/([^_]*)', file)[0])
                for file in files)
              )
df.to_csv("figshare/combined_data.csv")
df_combined = pd.read_csv("./figshare/combined_data.csv")
df_combined # combined dataframe<jupyter_output><empty_output><jupyter_text>Summary of Performance on Different MachinesEveryone in the team tried to run the combined files section and we have recorded our time comsuption and detailed `RAM`, `processor`, and `IF SSD` to check if they are relevant.Below is the summarized table:| Team Member      |  Operating System |RAM |Processor |Is SSD|Time used to combine csv files |Time used to load combined csv to memory || ----------- | ----------- |----------- |----------- |----------- |----------- |----------- || Rachel      |  MacOS Catalina Version 10.15.6 |16GB of 3733MHz       |2 GHz Quad-Core Intel Core i5       |Yes    |peak memory: 404.39 MiB, increment: 0.05 MiB, CPU times: user 5min 29s, sys: 19.5 s, total: 5min 48s Wall time: 6min       |peak memory: 7112.71 MiB, increment: 3458.57 MiB CPU times: user 58.7 s, sys: 15.7 s, total: 1min 14s Wall time: 1min 23s       || Daniel   |  Windows 10 Education |8 GB        |1.6 GHz Intel Core i5        |Yes        |peak memory: 2596.04 MiB, increment: 0.89 MiB,Wall time: 28min 59s        |peak memory: 3737.39 MiB, increment: 0.82 MiB,Wall time: 6min 13s        || Santiago   |  Windows 10 Education |8 GB 2400 MHz   |2 GHz Intel Core i7-8550U        |Yes        |peak memory: 297.27 MiB, increment: 0.06 MiB, Wall time: 24min 47s       |peak memory: 2803.73 MiB, increment: 1.09 MiB,Wall time: 4min 19s      || Rui   |  MacOS Catalina Version 10.15.7 |16 GB 2133 MHz |2.9 GHz Quad-Core Intel Core i7        |Yes        |peak memory: 356.82 MiB, increment: 0.31 MiB, CPU times: user 6min 43s, sys: 19.4 s, total: 7min 3s Wall time: 7min 13s        |peak memory: 2983.91 MiB, increment: 0.19 MiB,CPU times: user 1min, sys: 15 s, total: 1min 15s, Wall time: 1min 21s        |As we can see from the performance data we collected, the `Operating system`,`RAM`, `Proccessor` and `Hard Drive Type` could influence the data combining efficiency. As for this particular action, we realize the `oprating system` and `RAM` might be highly correlated to the time spent. We noticed that with Mac operating systems the code ran efficiently from top to bottom, whereas Windows operating systems ran slower. This may also be due to the GB of RAM (random-access memory) that the Mac users of our team had vs. the Windows users (Mac users had 16GB of RAM, double what the Windows users had). RAM seems to play an important role here when accessing large data because RAM is the area to store and access working data on a short-term basis, therefore, the larger our data, the more RAM we will need for peak performance and speed. Overall, from the table above we can see that Mac users with 16GB ran the code much faster with a lower peak memory on average than the Windows users with 8GB. This is particularily evident in combining CSV files where Windows users took almost 30min to run while Mac users took around 7min. We also noticed that Windows users ran into an `index error` when running the code to combine the CSVs. We combatted this issue by adding a `./` to the filename, but have removed it currently for Mac systems to process.<jupyter_code>df_combined["model"].unique() # print out the unique models
%%sh
du -sh figshare/combined_data.csv<jupyter_output>5.6G	figshare/combined_data.csv<jupyter_text>We can see from our combined dataframe that we have 28 unique models to continue our analysis with.  Load the combined CSV to memory and perform a simple EDA<jupyter_code>%%time
%%memit
#simple pandas - This is how we do normally ,which means we are loading the entire data to the memory
df = pd.read_csv("figshare/combined_data.csv")
print(df["model"].value_counts())
df.head()
# checking datatypes for columns
df.dtypes<jupyter_output><empty_output><jupyter_text>We can see that we have object and float64 type columns in our dataframe. This makes sense that `time` and `model` are object types and the rest such as latitude, longitude, and rain are float64 types.  Investigate changing the `dtype` of our data<jupyter_code>print(f"The memory usage with the original float64 dtype: {df[['lat_min','lat_max','rain (mm/day)']].memory_usage().sum() / 1e6:.2f} MB")
print(f"The memory usage after changing to float32 dtype: {df[['lat_min','lat_max','rain (mm/day)']].astype('float32', errors='ignore').memory_usage().sum() / 1e6:.2f} MB")<jupyter_output>The memory usage with the original float64 dtype: 1500.33 MB
The memory usage after changing to float32 dtype: 750.17 MB<jupyter_text>Observation1:> As we switch the data type from `float64` to `float32` the memory usage reduced by a half. This is because `float32` is stored as a 32-bit number, while `float64` is stored as twice as much memory as `float32`. If we have a large amount of data and we don't have a specific requirement on the precision or our original data is not accurate to a certain number of decimal places, `float32` is sufficient enough for us to process the data, which is not only faster but also resource-saving.  Loading our data in chunks using Pandas and checking the value counts of models<jupyter_code>%%time
%%memit
counts = pd.Series(dtype=int)
for chunk in pd.read_csv("figshare/combined_data.csv", chunksize=10_000_000):
    counts = counts.add(chunk["model"].value_counts(), fill_value=0)
print(counts.astype(int))<jupyter_output>ACCESS-CM2          1932840
ACCESS-ESM1-5       1610700
AWI-ESM-1-1-LR       966420
BCC-CSM2-MR         3035340
BCC-ESM1             551880
CMCC-CM2-HR4        3541230
CMCC-CM2-SR5        3541230
CMCC-ESM2           3541230
CanESM5              551880
EC-Earth3-Veg-LR    3037320
FGOALS-f3-L         3219300
FGOALS-g3           1287720
GFDL-CM4            3219300
GFDL-ESM4           3219300
INM-CM4-8           1609650
INM-CM5-0           1609650
KIOST-ESM           1287720
MIROC6              2070900
MPI-ESM-1-2-HAM      966420
MPI-ESM1-2-HR       5154240
MPI-ESM1-2-LR        966420
MRI-ESM2-0          3037320
NESM3                966420
NorESM2-LM           919800
NorESM2-MM          3541230
SAM0-UNICON         3541153
TaiESM1             3541230
observed              46020
dtype: int64
peak memory: 2139.21 MiB, increment: 2031.93 MiB
CPU times: user 54.7 s, sys: 10.6 s, total: 1min 5s
Wall time: 1min 7s<jupyter_text>Observation 2:> By loading in chunks, the value counts are exactly the same as other methods we tried. The peak memory for chunks is significantly lower than that without using chunking method. From our observation, we can conclude that for large-scaled data, if we choose to load in chunks, we can gain the competitive edge for lower memory usage and faster processing speed.  Loading our data using Dask and checking the value counts of models<jupyter_code>%%time
%%memit
# Dask
df_dask = dd.read_csv('figshare/combined_data.csv')
print(df_dask["model"].value_counts().compute())<jupyter_output>MPI-ESM1-2-HR       5154240
TaiESM1             3541230
CMCC-CM2-HR4        3541230
CMCC-CM2-SR5        3541230
CMCC-ESM2           3541230
NorESM2-MM          3541230
SAM0-UNICON         3541153
FGOALS-f3-L         3219300
GFDL-CM4            3219300
GFDL-ESM4           3219300
EC-Earth3-Veg-LR    3037320
MRI-ESM2-0          3037320
BCC-CSM2-MR         3035340
MIROC6              2070900
ACCESS-CM2          1932840
ACCESS-ESM1-5       1610700
INM-CM4-8           1609650
INM-CM5-0           1609650
KIOST-ESM           1287720
FGOALS-g3           1287720
MPI-ESM-1-2-HAM      966420
MPI-ESM1-2-LR        966420
NESM3                966420
AWI-ESM-1-1-LR       966420
NorESM2-LM           919800
CanESM5              551880
BCC-ESM1             551880
observed              46020
Name: model, dtype: int64
peak memory: 5816.75 MiB, increment: 1481.29 MiB
CPU times: user 1min 16s, sys: 19.6 s, total: 1min 36s
Wall time: 46.6 s<jupyter_text>Observation3:> So far, dask is the best pick for us to read large csv files to dataframe. Compared with loading the csv to pandas data frame, when we load the csv file to `dask`, the `peak memory`, `increment memory`, and `wall time` all reduced dramatically for the `value_count()` operation. This is likely because `dask` partitioned the dataframe based on row index and did the calculation in parallel to improve the efficiency. Thus, for large-scale data calculation, we could use dask instead of pandas to improve the code efficiency with minimum syntax change.   Transfering the dataframe from Python to R using Feather<jupyter_code>%%time
%%memit
dataset = ds.dataset("figshare/combined_data.csv", format="csv")
## this is of arrow table format
table = dataset.to_table()
%%time
# writing in feather format
feather.write_feather(table, 'figshare/combined_data.feather')<jupyter_output>CPU times: user 5.34 s, sys: 14.1 s, total: 19.5 s
Wall time: 9.87 s<jupyter_text>Reason for choosing `feather`> Our team did a comprehensive comparision and research among the four data formats online and testing in practice, in the end `feather` is our best pick for this project scenario. Our reasoning is listed below:> - `Feather` enables us to store and read the data from raw arrow format without much serialization and deserialization which renders it faster (higher I/O speed) than Parquet, although Parquet can take less storage memory which is more suitable for long term data storage.    > - Feather is a columnar dataframe which can speed up the data analytics queries.     > - It has the unique competitive advantage for not taking too much memory without the need to unpacking the data before loading to RAM.  Simple EDA in R<jupyter_code>%%R
library(tidyr)
%%time
%%R
library(arrow)
start_time <- Sys.time()
r_table <- arrow::read_feather("figshare/combined_data.feather")
print(class(r_table))
library(dplyr)
result <- r_table %>% count(model) # showing the different counts of the models 
end_time <- Sys.time()
print(result)
print(end_time - start_time)
%%R
result <- r_table %>% count(time) # showing the different counts of the time
print(result)<jupyter_output>[90m# A tibble: 92,040 x 2[39m
   time                    n
   [3m[90m<dttm>[39m[23m              [3m[90m<int>[39m[23m
[90m 1[39m 1888-12-31 [90m16:00:00[39m    29
[90m 2[39m 1889-01-01 [90m04:00:00[39m  [4m1[24m330
[90m 3[39m 1889-01-01 [90m16:00:00[39m    29
[90m 4[39m 1889-01-02 [90m04:00:00[39m  [4m1[24m330
[90m 5[39m 1889-01-02 [90m16:00:00[39m    29
[90m 6[39m 1889-01-03 [90m04:00:00[39m  [4m1[24m330
[90m 7[39m 1889-01-03 [90m16:00:00[39m    29
[90m 8[39m 1889-01-04 [90m04:00:00[39m  [4m1[24m330
[90m 9[39m 1889-01-04 [90m16:00:00[39m    29
[90m10[39m 1889-01-05 [90m04:00:00[39m  [4m1[24m330
[90m# … with 92,030 more rows[39m<jupyter_text>Observation From EDA: > The counts for models in R is the same as the counts for models we did previously in python, time to count is faster, which double confirmed the accuracy of EDA analysis.<jupyter_code>%%R
r_table_d <- r_table %>% drop_na() # drop NA values
%%R
r_table_d <- r_table_d %>% rename(rain_mmperday = `rain (mm/day)`) # rename the column for rain
%%R
# function to calculate the mode
mode <- function(x) {
  ux <- unique(x)
  ux[which.max(tabulate(match(x, ux)))]
}
%%R
Columns <- c("lat_min", "lat_max", "lon_min", "lon_max", "rain (mm/perday)")
Mean <- c(mean(r_table_d$lat_min), mean(r_table_d$lat_max), mean(r_table_d$lon_min), mean(r_table_d$lon_max), mean(r_table_d$rain_mmperday))
Mode <- c(mode(r_table_d$lat_min), mode(r_table_d$lat_max), mode(r_table_d$lon_min), mode(r_table_d$lon_max), mode(r_table_d$rain_mmperday))
Median <- c(median(r_table_d$lat_min), median(r_table_d$lat_max), median(r_table_d$lon_min), median(r_table_d$lon_max), median(r_table_d$rain_mmperday))

result <- data.frame(Columns, Mean, Mode, Median)
print(result)<jupyter_output>Columns      Mean      Mode       Median
1          lat_min -33.10482 -34.86911 -33.00000000
2          lat_max -31.92201 -34.86911 -32.04188482
3          lon_min 146.90590 144.37500 146.87500000
4          lon_max 148.28781 144.37500 148.12500000
5 rain (mm/perday)   1.90117   0.00000   0.06154947
{'fraction_non_alphanumeric': 0.09950808151791989, 'fraction_numerical': 0.08559381588193957, 'mean_word_length': 3.655217533529604, 'pattern_counts': {'":': 1, '<': 61, '<?xml version=': 0, '>': 59, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 1, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '12', 'dir': 'jupyter-structured-clean-dedup', 'id': '601973', 'license': 'MIT', 'n_tokens_mistral': 5573, 'n_tokens_neox': 4651, 'n_words': 1664, 'path': 'notebooks/milestone_1.ipynb', 'repo_name': 'UBC-MDS/525-group8', 'size': '14266'}