<jupyter_start><jupyter_text>Practice Loading DatasetsThis assignment is purposely semi-open-ended you will be asked to load datasets both from github and also from CSV files from the [UC Irvine Machine Learning Repository](https://archive.ics.uci.edu/ml/index.php). Remember that the UCI datasets may not have a file type of `.csv` so it's important that you learn as much as you can about the dataset before you try and load it. See if you can look at the raw text of the file either locally, on github, using the `!curl` shell command, or in some other way before you try and read it in as a dataframe, this will help you catch what would otherwise be unforseen problems.  1) Load a dataset from Github (via its *RAW* URL)Pick a dataset from the following repository and load it into Google Colab. Make sure that the headers are what you would expect and check to see if missing values have been encoded as NaN values:<jupyter_code>!git clone https://raw.githubusercontent.com/ryanleeallred/datasets/master/concrete_data.csv<jupyter_output>Cloning into 'concrete_data.csv'...
fatal: repository 'https://raw.githubusercontent.com/ryanleeallred/datasets/master/concrete_data.csv/' not found<jupyter_text>* above method is unreliable. In this case it seems to be a credential/privacy issue.<jupyter_code>import pandas as pd
url = 'https://raw.githubusercontent.com/ryanleeallred/datasets/master/concrete_data.csv'
df1 = pd.read_csv(url)
df1.head()<jupyter_output><empty_output><jupyter_text>* graciously, column headings are included  2) Load a dataset from your local machineDownload a dataset from the [UC Irvine Machine Learning Repository](https://archive.ics.uci.edu/ml/index.php) and then upload the file to Google Colab either using the files tab in the left-hand sidebar or by importing `files` from `google.colab` The following link will be a useful resource if you can't remember the syntax: While you are free to try and load any dataset from the UCI repository, I strongly suggest starting with one of the most popular datasets like those that are featured on the right-hand side of the home page. Some datasets on UCI will have challenges associated with importing them far beyond what we have exposed you to in class today, so if you run into a dataset that you don't know how to deal with, struggle with it for a little bit, but ultimately feel free to simply choose a different one. - Make sure that your file has correct headers, and the same number of rows and columns as is specified on the UCI page. If your dataset doesn't have headers use the parameters of the `read_csv` function to add them. Likewise make sure that missing values are encoded as `NaN`.  [UCI ML - Forest Fires](https://archive.ics.uci.edu/ml/datasets/Forest+Fires)NOTE: It may appear a lack of creativity to have chosen the same dataset as was used in the lecture, but actually I had chosen and started using it before the lecture, not knowing in advance.<jupyter_code># https://archive.ics.uci.edu/ml/datasets/Forest+Fires

from google.colab import files
uploaded = files.upload()
import io
df2 = pd.read_csv(io.BytesIO(uploaded['forestfires.csv']))
df2.head()<jupyter_output><empty_output><jupyter_text>* graciously, column headings are included  3) Load a dataset from UCI using `!wget`"Shell Out" and try loading a file directly into your google colab's memory using the `!wget` command and then read it in with `read_csv`.With this file we'll do a bit more to it.[x] - Read it in, [x] fix any problems with the header [_] as well as make sure missing values are encoded as `NaN`.- Use the `.fillna()` method to fill any missing values.  - [.fillna() documentation](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.fillna.html)Create one of each of the following plots using the Pandas plotting functionality: - Scatterplot - Histogram - Density Plot  UCI ML [Adult](https://archive.ics.uci.edu/ml/datasets/Adult) dataset<jupyter_code># parse the column names from the .names file

!wget https://archive.ics.uci.edu/ml/machine-learning-databases/adult/adult.names
!tail adult.names
#do the same with old.adult.names
!wget https://archive.ics.uci.edu/ml/machine-learning-databases/adult/old.adult.names
!tail old.adult.names<jupyter_output><empty_output><jupyter_text>NOTE : this could all be done with just awk or sed, if one has time to master them  1. use 'awk' to just get the last "paragraph" https://unix.stackexchange.com/questions/82944/how-to-grep-for-text-in-a-file-and-display-the-paragraph-that-has-the-text 2. use 'grep' to eliminate non-matches (awk prepends those with '|') https://stackoverflow.com/questions/3548453/negative-matching-using-grep-match-lines-that-do-not-contain-foo 3. use 'cut' with ':' as delimiter to get column names only without their definitions https://www.computerhope.com/unix/ucut.htm 4. use 'tr' to replace newlines with comma to form csv header. theoretically can be done with sed, but it's arcane https://www.computerhope.com/unix/utr.htm 5. save output to adult.header<jupyter_code>#this needs improvement but does most of the work

!awk -v RS='' '/:/' adult.names | grep -v '|' | cut -d ':' -f 1 | tr \\n , > adult.header

!cat adult.header
# same as above but for old.adult.names for the sake of comparison
# not fully processing or saving this

!awk '/Attribute Information/{y=1;next}/^8/{y=0}y' old.adult.names 
#| cut -d ':' -f 1 | tr \\n , > adult.header

#!cat adult.header
#manually fixed and string quoted
adultheaders = ['age','workclass','fnlwgt','education','education-num','marital-status','occupation','relationship','race','sex','capital-gain','capital-loss','hours-per-week','native-country','income']<jupyter_output><empty_output><jupyter_text>the last column, **income**, was omitted from the names description. the 'old.adult.names' file calls this 'class' - needed to add this manually in the adultheaders variable.<jupyter_code>adultdataurl =  'https://archive.ics.uci.edu/ml/machine-learning-databases/adult/adult.data'
df2 = pd.read_csv(adultdataurl  , 
    names=adultheaders)
df2.head()
df2.describe(exclude="number")
df2["marital-status"].value_counts()
df2.isna().sum()<jupyter_output><empty_output><jupyter_text>so there are no missing values (nulls) but...<jupyter_code># randomly inspect parts of the data without having to show all of it at once
df2.sample(10)<jupyter_output><empty_output><jupyter_text>this seems to be not so effective with large datasets with sparse anomalies or irregularities.<jupyter_code>df2["native-country"].value_counts()<jupyter_output><empty_output><jupyter_text>there are 500+ unknown countries represented by '?'. If we had known this before loading we could have loaded it as:<jupyter_code>df4 = pd.read_csv(adultdataurl, names=adultheaders, na_values="?")
df4.head()<jupyter_output><empty_output><jupyter_text>But, as we already read it in, in case it were huge or a lot of work has already been done on it, then it's better to do a replace:<jupyter_code>import numpy as np
df2 = df2.replace(regex=".+\?", value=np.NaN) # use regex pattern instead of to_replace= because these have imported hidden spaces   
df2.head(16)<jupyter_output><empty_output><jupyter_text>TODO: find out how to replace part of matched string (e.g.: " India" > "India")should be something like POSIX regex:```` df.replace(regex="[ ]+(India)", value="\1") just India  df.replace(regex="\s+(.+)", value="\1")        any str ````<jupyter_code>df2 = df2.replace(regex="[ ]+(India)", value="India")
df2 = df2.replace(regex="[ ]+(United-States)", value="USA")
df2["native-country"].value_counts()
#SHOWS that USA, India were stripped of leading space, but same problem exists for all other values ; probably best to do a list comprehension
import pandas as pd
import re #where regex lives ; not the same as df.replace(regex=) so methods and syntax could be different
help(re)
help(df2.replace)
df5["native-country"] = df2["native-country"].replace(regex="[ ]+(.+)", value="\1") #spaces followed by alphanumeric string
df5["native-country"].value_counts()
df5.shape() #above code destroyed all other values in native-country<jupyter_output><empty_output><jupyter_text>given the problems in this case, it would be best to just eliminate all spaces via setting the delimiter as ", " upon import<jupyter_code>df2 = pd.read_csv(adultdataurl  , sep=', ' , 
    names=adultheaders)
df2.head()
df2["native-country"].value_counts()
df2 = df2.replace(regex="[ ]+(United-States)", value="USA")
df2 = df2.replace(regex="\?", value="unknown") #use unknown because NaN are not counted
help(pd.read_csv)<jupyter_output><empty_output><jupyter_text>Stretch Goals - Other types and sources of dataNot all data comes in a nice single file - for example, image classification involves handling lots of image files. You still will probably want labels for them, so you may have tabular data in addition to the image blobs - and the images may be reduced in resolution and even fit in a regular csv as a bunch of numbers.If you're interested in natural language processing and analyzing text, that is another example where, while it can be put in a csv, you may end up loading much larger raw data and generating features that can then be thought of in a more standard tabular fashion.Overall you will in the course of learning data science deal with loading data in a variety of ways. Another common way to get data is from a database - most modern applications are backed by one or more databases, which you can query to get data to analyze. We'll cover this more in our data engineering unit.How does data get in the database? Most applications generate logs - text files with lots and lots of records of each use of the application. Databases are often populated based on these files, but in some situations you may directly analyze log files. The usual way to do this is with command line (Unix) tools - command lines are intimidating, so don't expect to learn them all at once, but depending on your interests it can be useful to practice.One last major source of data is APIs: https://github.com/toddmotto/public-apisAPI stands for Application Programming Interface, and while originally meant e.g. the way an application interfaced with the GUI or other aspects of an operating system, now it largely refers to online services that let you query and retrieve data. You can essentially think of most of them as "somebody else's database" - you have (usually limited) access.*Stretch goal* - research one of the above extended forms of data/data loading. See if you can get a basic example working in a notebook. Image, text, or (public) APIs are probably more tractable - databases are interesting, but there aren't many publicly accessible and they require a great deal of setup.  https://www.bls.gov/data/employmentquery API https://beta.bls.gov/dataQuery/find?fq=survey:[ln]&s=popularity:Dcurrent population survey https://www.bls.gov/cps/employment https://www.bls.gov/cps/lfcharacteristics.htmempdatabases https://data.bls.gov/cgi-bin/surveymost?lncharacteristics of the employed https://www.bls.gov/cps/tables.htmcharemp* HTML format https://www.bls.gov/cps/cpsaat23.htm Reading HTML (scraping)https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_html.htmlthis will be difficult because of stacked summary colummns. USGov loves to publish cross tabulations for statistics.* [read_html() doesn't handle tables with multiple header rows 13434 ](https://github.com/pandas-dev/pandas/issues/13434)I've gone down a dead end. Need to backtrack to some data that is more accessible.Arg. BLS gives .xlsx files, which I can't open.Trying to work with BLS was a bad idea. Resourses for dealing with summarized data in pandas[MultiIndex / advanced indexing](https://pandas.pydata.org/pandas-docs/stable/user_guide/advanced.html)[How to create a multilevel dataframe in pandas](https://stackoverflow.com/questions/40820017/how-to-create-a-multilevel-dataframe-in-pandas) NOTE: multi index dataframe is well beyond my capablity currently, so I'll skip to the JSON problem.  CryptoCompare APIhttps://min-api.cryptocompare.com/documentationdealing with JSON as a dict https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.from_dict.html<jupyter_code>ccurl='https://min-api.cryptocompare.com/data/price?fsym=HOLO&tsyms=USD,JPY,EUR' #endpoint for HOLO priced in USD, JPY, EUR

cchurl='https://min-api.cryptocompare.com/data/histoday?fsym=HOLO&tsym=USD&limit=10' #endpoint for historical price of HOLO in USD<jupyter_output><empty_output><jupyter_text>retrieve JSON data into Pandas https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_json.html<jupyter_code>import requests
ccrequest = requests.get(cchurl)
jsondata = ccrequest.json()
type(jsondata)
jsondata.keys()
res = jsondata['Data'] #key 'All' did not work
res[:3]
df6 = pd.read_json(jsondata) #wrong way to do it<jupyter_output><empty_output><jupyter_text>JSON is basically just a dictionary in python (or can be converted to one)https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.from_dict.html<jupyter_code>df6 = pd.DataFrame.from_dict(jsondata ) # orient='columns' is default<jupyter_output><empty_output><jupyter_text>https://stackoverflow.com/questions/49505872/read-json-to-pandas-dataframe-valueerror-mixing-dicts-with-non-series-may-lea<jupyter_code>import json
data = json.loads(jsondata)
# https://stackoverflow.com/questions/33559660/error-while-reading-json-file

df6 = pd.DataFrame(res["Data"])
data
df6 = pd.DataFrame(res[:3]) #only a slice ; not sure how to get all data
df6.head()
df6 = pd.DataFrame(res[:]) 
df6.head()<jupyter_output><empty_output><jupyter_text>Ought to be more than 5 results ; there are 11 Seems to me that the trick must have something to do with [read_json(... 'orient='...)](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_json.html)orient : string,    Indication of expected JSON string format. Compatible JSON strings can be produced by to_json() with a corresponding orient value. The set of possible orients is:        'split' : dict like {index -> [index], columns -> [columns], data -> [values]}        'records' : list like [{column -> value}, ... , {column -> value}]        'index' : dict like {index -> {column -> value}}        'columns' : dict like {column -> {index -> value}}        'values' : just the values array    The allowed and default values depend on the value of the typ parameter.        when typ == 'series',            allowed orients are {'split','records','index'}            default is 'index'            The Series index must be unique for orient 'index'.        when typ == 'frame',            allowed orients are {'split','records','index', 'columns','values', 'table'}            default is 'columns'            The DataFrame index must be unique for orients 'index' and 'columns'.            The DataFrame columns must be unique for orients 'index', 'columns', and 'records'.<jupyter_code>pd.read_json('cchurl',orient='records',typ='series')
# Defaults
 # typ='frame' : orient='columns'
 # typ='series' : orient='index'
cchurl
type(cchurl) # I don't understand why 'url/path' can't be a str, pretty sure a str is an obj<jupyter_output><empty_output><jupyter_text>[pandas.read_json](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_json.html)pandas.read_json(path_or_buf=None, ...)**path_or_buf** : a valid JSON str, path object or file-like objectAny valid string path is acceptable. The string could be a URL. Valid URL schemes include http, ftp, s3, and file. For file URLs, a host is expected. A local file could be: file://localhost/path/to/table.json.If you want to pass in a path object, pandas accepts any os.PathLike.By file-like object, we refer to objects with a read() method, such as a file handler (e.g. via builtin open function) or StringIO.<jupyter_code>jsondata
df = pd.read_json(cchurl,typ='series')
pd.DataFrame(data=df['Data'])<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10362920857017928, 'fraction_numerical': 0.007433318758198513, 'mean_word_length': 4.942835931700074, 'pattern_counts': {'":': 0, '<': 72, '<?xml version=': 0, '>': 84, 'https://': 38, 'lorem ipsum': 0, 'www.': 7, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '18', 'dir': 'jupyter-structured-clean-dedup', 'id': '243887', 'license': 'MIT', 'n_tokens_mistral': 4908, 'n_tokens_neox': 4700, 'n_words': 1786, 'path': 'module2-loadingdata/Joseph_Shirk_LS_DSPT3_112_Loading_Data_Assignment.ipynb', 'repo_name': 'jbshirk/DS-Unit-1-Sprint-1-Dealing-With-Data', 'size': '16009'}