<jupyter_start><jupyter_text>Importing data into pandasThere are tons of ways you can get data into a pandas dataframe. Here are a few of the more common ones.First, let's import pandas `as` pd.<jupyter_code>import pandas as pd<jupyter_output><empty_output><jupyter_text>From a CSV fileIf your data file is delimited with something other than a comma, you'll need to specify that in the `sep` argument. For example, if you had a pipe-delimited file: `pd.read_csv('../data/my-delimited-file.txt', sep='|')`Let's read in the MLB salary data.<jupyter_code>df_csv = pd.read_csv('../data/mlb.csv')
df_csv.head()<jupyter_output><empty_output><jupyter_text>From a CSV file on the InternetJust pass in the URL. This example uses [licensed child care facility data from Colorado's open data portal](https://data.colorado.gov/Early-childhood/Colorado-Licensed-Child-Care-Facilities-Report/a9rr-k8mu).The values that get returned aren't live -- like, if the results changed, your data frame would not update with new values. It reads in the data once.<jupyter_code>df_csv_internet = pd.read_csv('https://data.colorado.gov/api/views/a9rr-k8mu/rows.csv?accessType=DOWNLOAD')
df_csv_internet.head()<jupyter_output><empty_output><jupyter_text>From an Excel fileTo read an Excel file in pandas, use the [`read_excel()`](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_excel.html) method. Depending on the filetype (`xls` or `xlsx`), you'd also need to separately install into your virtual environment the `xlrd` or `openpyxl` modules. (We've already installed both here.)You might also want to specify the `sheet_name` to select your worksheet of interest -- the default is "the first one."Here, we're reading in a spreadsheet with data on accidental drug overdoses in Connecticut.<jupyter_code>df_xl = pd.read_excel('../data/CT_Overdoses_2012-2016.xlsx', sheet_name='Accidental_Drug_Related_Deaths_')
df_xl.head()<jupyter_output><empty_output><jupyter_text>From a Python data collectionMaybe the work you're doing in pandas happens downstream of some other Python processing, so the data exists as a native Python data collection -- say, a list of dictionaries. You can turn this (and other Python data collections, like a list of lists) into a pandas dataframe, too.<jupyter_code>test_data = [
    {'name': '<NAME>', 'job': 'Training director', 'location': 'Colorado Springs, CO'},
    {'name': '<NAME>', 'job': 'Gourmand', 'location': 'Flavortown'},
    {'name': '<NAME>', 'job': 'Senator', 'location': 'Washington, D.C.'}
]
df_py_lod = pd.DataFrame(data=test_data)
df_py_lod.head()<jupyter_output><empty_output><jupyter_text>If you have a list of lists, you would need to also specify the `columns` keyword argument, as well:<jupyter_code>test_data_ls = [
    ['<NAME>', 'Training director', 'Colorado Springs, CO'],
    ['<NAME>', 'Gourmand', 'Flavortown'],
    ['<NAME>', 'Senator', 'Washington, D.C']
]
df_py_lol = pd.DataFrame(data=test_data_ls, columns=['name', 'job', 'location'])
df_py_lol.head()<jupyter_output><empty_output><jupyter_text>From an HTML tableOK SO.This one requires you to install and specify the Python package that has the HTML parsing engine of your choice -- [BeautifulSoup](https://www.crummy.com/software/BeautifulSoup/bs4/doc/) or [lxml](http://lxml.de/). The default is `lxml`, but here we're going to use BeautifulSoup.Huge caveat! Pulling data directly from an HTML table can be hit and miss, depending on how hairy the underlying HTML is. And if you want to scrape data from a website, it's usually better practice to save the results to a local file, _then_ load it up for analysis. But it's good to know that it's an option.In this example, we've installed `BeautifulSoup` (alias `bs4`) and we're going to import [a table of media witnesses](http://www.tdcj.state.tx.us/death_row/dr_media_witness_list.html) to Texas death row executions.We're going to pass four things to [the pandas `read_html()` method](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_html.html):1. The URL we want to scrape (in quotes, as a string)2. The `flavor` of parser that we'd like to use to process the HTML (`bs4`)3. The HTML attributes of the table we're targeting (in this case, the table has a `class` called `tdcj_table`)4. The number of the list, in the list of lists that gets returned in a dataframe, that has the `header`? (Usually it's 0 -- the first one)Reading through the documentation for this method, we also notice that this method returns a _list_ of matching tables as dataframes, so we need to grab the _first_ item in this list of tables returned. Our arguments were specific enough that there's only one item in the returned list, though, so we can just grab the first item with `[0]`.<jupyter_code>html_df = pd.read_html('http://www.tdcj.state.tx.us/death_row/dr_media_witness_list.html',
                       flavor='bs4',
                       attrs={'class': 'tdcj_table'},
                       header=0)[0]
html_df.head()<jupyter_output><empty_output><jupyter_text>From JSONJSON stands for JavaScript Object Notation. It's a common data interchange format on the web. The [`read_json()`](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_json.html) method can pull JSON into a data frame.Pandas can slurp in data from a local `.json` file, or from a URL -- say, a JSON API with data on dogs and cats registered in the Sunshine Coast Region of Australia. That one sounds fun! Let's do that.<jupyter_code>json_df = pd.read_json('https://data.sunshinecoast.qld.gov.au/resource/44qj-t4fr.json')
json_df.head()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10980810234541578, 'fraction_numerical': 0.004797441364605544, 'mean_word_length': 5.02676659528908, 'pattern_counts': {'":': 0, '<': 39, '<?xml version=': 0, '>': 39, 'https://': 7, 'lorem ipsum': 0, 'www.': 3, 'xml': 3}, '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': '8', 'dir': 'jupyter-structured-clean-dedup', 'id': '410280', 'license': 'MIT', 'n_tokens_mistral': 1812, 'n_tokens_neox': 1728, 'n_words': 639, 'path': 'notebooks/Importing data into pandas.ipynb', 'repo_name': 'ireapps/python-mini-bootcamp-2021', 'size': '5668'}