<jupyter_start><jupyter_text>Now we need to decompress the zip files.<jupyter_code>! mkdir data
from zipfile import ZipFile

for i in range(1,4):
    # Create a ZipFile Object and load data_train_*.zip in it
    with ZipFile(f'../data/data_train_{i}.zip', 'r') as zipObj:
        # Extract all the contents of zip file in different directory
        zipObj.extractall(f'data/data_train_{i}')<jupyter_output><empty_output><jupyter_text>Data train 1<jupyter_code>ls data/data_train_1/
import pandas as pd
df1 = pd.read_csv('data/data_train_1/datasource1.csv')<jupyter_output><empty_output><jupyter_text>First error! It seems to be a unicodedecode error, this means the file is not unicode (utf8) valid. There are a huge number of encodings available, we can use [chardet](https://chardet.readthedocs.io/en/latest/usage.html) to detect the valid encoding.To use it we just have to read the file in byte format (the `rb` flag). We only read the first 1000 bytes (so it's faster) and use `chardet.detect`.<jupyter_code>import chardet
with open('data/data_train_1/datasource1.csv', 'rb') as fname:
    raw_data = fname.read()[:1000]
    
chardet.detect(raw_data)
df1 = pd.read_csv('data/data_train_1/datasource1.csv', encoding='windows-1251')
df1.head()<jupyter_output><empty_output><jupyter_text>Let's set the id as the index for all the files.<jupyter_code>df1 = df1.set_index('id')<jupyter_output><empty_output><jupyter_text>Done! Let's go to the second file of this folder.<jupyter_code>ls data/data_train_1/<jupyter_output>datasource1.csv   datasource2.json  datasource3.csv<jupyter_text>It seems to be a json file...<jupyter_code>with open('data/data_train_1/datasource2.json') as fname:
    df2 = pd.read_json(fname, lines=True)
df2.head()<jupyter_output><empty_output><jupyter_text>Ok that didn't work, maybe a different orientation? Let's check the [documentation](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_json.html) and preview the json.<jupyter_code># this shows the first 500 bytes of the file
! head -c 500 data/data_train_1/datasource2.json
with open('data/data_train_1/datasource2.json') as fname:
    df2 = pd.read_json(fname, orient='split')
df2.head()<jupyter_output><empty_output><jupyter_text>Ok, let's go to the third file!<jupyter_code>!head data/data_train_1/datasource3.csv<jupyter_output>id|tierbeforeorder|ddprate|platform|style|region
cfcd208495d565ef66e7dff9f98764da||0.0|web|1|1
c4ca4238a0b923820dcc509a6f75849b|T3|5.0083|app|2|1
c81e728d9d4c2f636f067f89cc14862c||42.2351||3|1
eccbc87e4b5ce2fe28308fd9f2a7baf3||42.2351|web|4|1
a87ff679a2f3e71d9181a67b7542122c||5.0083|web|5|1
e4da3b7fbbce2345d7772b0674a318d5|T2|0.0|web|6|2
1679091c5a880faf6fb5e6087eb1b2dc|T2|0.0|web|7|2
8f14e45fceea167a5a36dedd4bea2543|T2|0.0|web|8|2
c9f0f895fb98ab9159f51fd0297e236d|T4|0.0|web|9|2<jupyter_text>Simple, it's a csv but with a pipe `|` as a separator.This is why doing a simple `!head` before running into pandas is super helpful!<jupyter_code>df3 = pd.read_csv('data/data_train_1/datasource3.csv', sep='|')
df3.head()
df3 = df3.set_index('id')<jupyter_output><empty_output><jupyter_text>Done! Let's move to the second folder.  data train 2<jupyter_code>!ls -alh data/data_train_2/<jupyter_output>total 375472
drwxr-xr-x  5 chi  staff   160B Sep 14 19:54 [1m[36m.[m[m
drwxr-xr-x  5 chi  staff   160B Sep 14 19:54 [1m[36m..[m[m
-rw-r--r--  1 chi  staff    65M Sep 14 19:54 datasource4.html
-rw-r--r--  1 chi  staff    84M Sep 14 19:54 datasource5.csv
-rw-r--r--  1 chi  staff    34M Sep 14 19:54 datasource7.csv<jupyter_text>Let's proceed with datasource4, it seems like an html file?<jupyter_code>!head -n 20 data/data_train_2/datasource4.html<jupyter_output><table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>isusingmultipledevices</th>
      <th>freereturn</th>
      <th>userid</th>
    </tr>
    <tr>
      <th>id</th>
      <th></th>
      <th></th>
      <th></th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>cfcd208495d565ef66e7dff9f98764da</th>
      <td>No</td>
      <td>1</td><jupyter_text>Let's try `pandas.read_html`!<jupyter_code># This takes a while to run...
df4 = pd.read_html('./data/data_train_2/datasource4.html', attrs = {'class': 'dataframe'})<jupyter_output><empty_output><jupyter_text>`read_html` returns a list.<jupyter_code>len(df4)<jupyter_output><empty_output><jupyter_text>There is only one table on the html, so we get the first element of the list.<jupyter_code>df4 = df4[0]
df4.head()
df4.columns<jupyter_output><empty_output><jupyter_text>It seems it is a  multiindex!, we need to get the id as an index.<jupyter_code>df4.columns = df4.columns.droplevel(level=0)
df4.head()
df4.columns = ['id', 'isusingmultipledevices', 'freereturn', 'userid']
df4.head()
df4 = df4.set_index('id')<jupyter_output><empty_output><jupyter_text>Done! Let's move to file 5.<jupyter_code># this might crash your browser
# !head data/data_train_2/datasource5.csv<jupyter_output><empty_output><jupyter_text>Ok, this file disguises itself as a csv but its actually a json! A good reminder that file extensions don't mean anything!<jupyter_code>df5 = pd.read_json('data/data_train_2/datasource5.csv')
df5.head()<jupyter_output><empty_output><jupyter_text>Done! Let's go to file 6.<jupyter_code>!ls -alh data/data_train_2/<jupyter_output>total 375472
drwxr-xr-x  5 chi  staff   160B Sep 14 19:54 [1m[36m.[m[m
drwxr-xr-x  5 chi  staff   160B Sep 14 19:54 [1m[36m..[m[m
-rw-r--r--  1 chi  staff    65M Sep 14 19:54 datasource4.html
-rw-r--r--  1 chi  staff    84M Sep 14 19:54 datasource5.csv
-rw-r--r--  1 chi  staff    34M Sep 14 19:54 datasource7.csv<jupyter_text>Well, it seems that there is no file 6 here, let's go to file 7 then.<jupyter_code>!head data/data_train_2/datasource7.csv<jupyter_output>id,countrycode,countryoforigin,userfraudstatus,useless values (delete please),test strings (delete please)
91b48f444340e38b245224d3cf495dd6,48,20,3,0.5972732194313508,tMdME
68c26935d45bf7340b70c481e2578906,WEBSITE,WEBSITE,WEBSITE,0.31678691699869344,gJtKD
5ef781f891290d7ff10c44fc54f28829,14,12,3,0.6405539685733376,K6iBC
fe548a297e49243c8838b61221a09def,5,12,3,0.34994931238397897,0mcLU
bdb4fc61cdf6cfadfd20db40fa1a64d2,17,4,1,0.6095160940464983,iBSjr
24fc676fd9f7bba69640c6b1fd5c52f5,3,1,3,0.6492229983006353,2P5o3
c6321d2275686eee40744053a81dd7fc,2,1,3,0.48500347145892464,8tRfn
d6557be41069a1036c6cedf514f17346,46,26,3,0.6455367880637098,4o4rc
5d9920797a7f15da0ac6b68a0e3db25e,3,1,3,0.60239127186448,UcbXQ<jupyter_text>Ok, we see this seems to be a csv that someone has messed with. Since csvs are basically text files this happens sometimes No problem, we will skip the header and we will remove the columns that are useless and have to be deleted (the file is asking very nicely after all...).<jupyter_code>df7 = pd.read_csv('data/data_train_2/datasource7.csv', 
                  header=None, 
                  skiprows=1,
                  usecols=[0,1,2,3],
                  names=['id','countrycode','countryoforigin','userfraudstatus']
                 )
df7.head()
df7 = df7.set_index('id')<jupyter_output><empty_output><jupyter_text>Done!  Folder 3<jupyter_code>!ls -alh data/data_train_3/<jupyter_output>total 0
drwxr-xr-x     3 chi  staff    96B Sep 14 19:54 [1m[36m.[m[m
drwxr-xr-x     5 chi  staff   160B Sep 14 19:54 [1m[36m..[m[m
drwxr-xr-x  5002 chi  staff   156K Sep 14 19:54 [1m[36mdatasource6[m[m<jupyter_text>Ok, here is the datasource6, which seems to be another folder?<jupyter_code># try running it without the "| head -10" to see all the files
!ls -alh data/data_train_3/datasource6/ | head -10<jupyter_output>total 118248
drwxr-xr-x  5002 chi  staff   156K Sep 14 19:54 .
drwxr-xr-x     3 chi  staff    96B Sep 14 19:54 ..
-rw-r--r--     1 chi  staff   8.1K Sep 14 19:54 0.csv
-rw-r--r--     1 chi  staff   8.8K Sep 14 19:54 1.csv
-rw-r--r--     1 chi  staff   8.3K Sep 14 19:54 10.csv
-rw-r--r--     1 chi  staff   8.7K Sep 14 19:54 100.csv
-rw-r--r--     1 chi  staff   8.6K Sep 14 19:54 1000.csv
-rw-r--r--     1 chi  staff   8.4K Sep 14 19:54 1001.csv
-rw-r--r--     1 chi  staff   8.7K Sep 14 19:54 1002.csv<jupyter_text>Ok, seems to be a lot of csv files. Since they have similar names, they are probably created by some automatic process. This is good because it means that we can use the same function to read all of the files.<jupyter_code>!head data/data_train_3/datasource6/1000.csv<jupyter_output>id,ddpsubcategory,shiptypeid,hasitemsonbag,country
6a6331cd682c28bd1d95cb865c69cbdd,"Trousers, overalls, shorts",2,Yes,1
d2f358973ec4382ff877a9f928876e90,WEBSITE,WEBSITE,WEBSITE,WEBSITE
e350ebea9bef9cd420f59c34dfa4f589,WEBSITE,WEBSITE,WEBSITE,WEBSITE
a8d972f22527b09a03564f854a3eb9a3,"Jerseys, pullovers, cardigans, waistcoats and similar articles, knitted or crocheted",2,Yes,1
2d273973a88b3ab45f0d0763300b0695,"Handbags, whether or not with shoulder strap, including those without handle",2,Yes,1
ac0ce4c020f526032faee133bea0673b,API,API,API,API
1d5d5a604ba4a3c33a00faf9494c6dbf,Other footwear with outer soles of leather,2,Yes,1
c556b66075e6ceb64e3b73914c1e1cd6,Footwear with outer soles of rubber or plastics,2,No,5
f26d104eb67d84cc4c8f2cb272501b56,N/D,3,No,9<jupyter_text>We will create a list of dataframes and will concatenate them.<jupyter_code>from pathlib import Path

datasource6 = Path('data/data_train_3/datasource6/')
# this takes a while to run
ind_dfs = []
for file in datasource6.glob('*'):
    ind_dfs.append(pd.read_csv(file))
df6 = pd.concat(ind_dfs)
df6.shape
df6.head()
df6 = df6.set_index('id')<jupyter_output><empty_output><jupyter_text>Done! Now we merged them all!<jupyter_code>files_df = df1.join(df2).join(df3).join(df4).join(df5).join(df6).join(df7)
files_df.head()<jupyter_output><empty_output><jupyter_text>And output the resulting dataframe to a pickle in the `data/clean` directory!<jupyter_code>! mkdir data/clean
files_df.to_pickle('data/clean/files_df.pkl')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1187804151355646, 'fraction_numerical': 0.12175985698679113, 'mean_word_length': 5.066265060240964, 'pattern_counts': {'":': 0, '<': 127, '<?xml version=': 0, '>': 127, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '26', 'dir': 'jupyter-structured-clean-dedup', 'id': '304388', 'license': 'MIT', 'n_tokens_mistral': 4890, 'n_tokens_neox': 4206, 'n_words': 843, 'path': 'S02 - Data Wrangling/HCKT02 - Data Wrangling/instructor_solution/1.local_files.ipynb', 'repo_name': 'jtiagosg/batch3-students', 'size': '10069'}