# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     name: python3
# ---

# + [markdown] id="view-in-github" colab_type="text"
# <a href="https://colab.research.google.com/github/mfernandes61/python-intro-gapminder/blob/binder/colab/07_reading_tabular.ipynb" target="_parent"><img src="https://colab.research.google.com/assets/colab-badge.svg" alt="Open In Colab"/></a>

# + id="n7pdtN1ZwFFj"


# + [markdown] id="K7NHbklvwjDQ"
# ---
# title: "Reading Tabular Data into DataFrames"
# teaching: 10
# exercises: 10
# questions:
# - "How can I read tabular data?"
# objectives:
# - "Import the Pandas library."
# - "Use Pandas to load a simple CSV data set."
# - "Get some basic information about a Pandas DataFrame."
# keypoints:
# - "Use the Pandas library to get basic statistics out of tabular data."
# - "Use `index_col` to specify that a column's values should be used as row headings."
# - "Use `DataFrame.info` to find out more about a dataframe."
# - "The `DataFrame.columns` variable stores information about the dataframe's columns."
# - "Use `DataFrame.T` to transpose a dataframe."
# - "Use `DataFrame.describe` to get summary statistics about data."
# ---
# ## Use the Pandas library to do statistics on tabular data.
#
# *   Pandas is a widely-used Python library for statistics, particularly on tabular data.
# *   Borrows many features from R's dataframes.
#     *   A 2-dimensional table whose columns have names
#         and potentially have different data types.
# *   Load it with `import pandas as pd`. The alias pd is commonly used for Pandas.
# *   Read a Comma Separated Values (CSV) data file with `pd.read_csv`.
#     *   Argument is the name of the file to be read.
#     *   Assign result to a variable to store the data that was read.
#
# ~~~
# import pandas as pd
#
# data = pd.read_csv('data/gapminder_gdp_oceania.csv')
# print(data)
# ~~~
# {: .language-python}
# ~~~
#        country  gdpPercap_1952  gdpPercap_1957  gdpPercap_1962  \
# 0    Australia     10039.59564     10949.64959     12217.22686
# 1  New Zealand     10556.57566     12247.39532     13175.67800
#
#    gdpPercap_1967  gdpPercap_1972  gdpPercap_1977  gdpPercap_1982  \
# 0     14526.12465     16788.62948     18334.19751     19477.00928
# 1     14463.91893     16046.03728     16233.71770     17632.41040
#
#    gdpPercap_1987  gdpPercap_1992  gdpPercap_1997  gdpPercap_2002  \
# 0     21888.88903     23424.76683     26997.93657     30687.75473
# 1     19007.19129     18363.32494     21050.41377     23189.80135
#
#    gdpPercap_2007
# 0     34435.36744
# 1     25185.00911
# ~~~
# {: .output}
#
# *   The columns in a dataframe are the observed variables, and the rows are the observations.
# *   Pandas uses backslash `\` to show wrapped lines when output is too wide to fit the screen.
#
# > ## File Not Found
# >
# > Our lessons store their data files in a `data` sub-directory,
# > which is why the path to the file is `data/gapminder_gdp_oceania.csv`.
# > If you forget to include `data/`,
# > or if you include it but your copy of the file is somewhere else,
# > you will get a [runtime error]({{ page.root }}/04-built-in/#runtime-error)
# > that ends with a line like this:
# >
# > ~~~
# > FileNotFoundError: [Errno 2] No such file or directory: 'data/gapminder_gdp_oceania.csv'
# > ~~~
# > {: .error}
# {: .callout}
#
# ## Use `index_col` to specify that a column's values should be used as row headings.
#
# *   Row headings are numbers (0 and 1 in this case).
# *   Really want to index by country.
# *   Pass the name of the column to `read_csv` as its `index_col` parameter to do this.
#
# ~~~
# data = pd.read_csv('data/gapminder_gdp_oceania.csv', index_col='country')
# print(data)
# ~~~
# {: .language-python}
# ~~~
#              gdpPercap_1952  gdpPercap_1957  gdpPercap_1962  gdpPercap_1967  \
# country
# Australia       10039.59564     10949.64959     12217.22686     14526.12465
# New Zealand     10556.57566     12247.39532     13175.67800     14463.91893
#
#              gdpPercap_1972  gdpPercap_1977  gdpPercap_1982  gdpPercap_1987  \
# country
# Australia       16788.62948     18334.19751     19477.00928     21888.88903
# New Zealand     16046.03728     16233.71770     17632.41040     19007.19129
#
#              gdpPercap_1992  gdpPercap_1997  gdpPercap_2002  gdpPercap_2007
# country
# Australia       23424.76683     26997.93657     30687.75473     34435.36744
# New Zealand     18363.32494     21050.41377     23189.80135     25185.00911
# ~~~
# {: .output}
#
# ## Use the `DataFrame.info()` method to find out more about a dataframe.
#
# ~~~
# data.info()
# ~~~
# {: .language-python}
# ~~~
# <class 'pandas.core.frame.DataFrame'>
# Index: 2 entries, Australia to New Zealand
# Data columns (total 12 columns):
# gdpPercap_1952    2 non-null float64
# gdpPercap_1957    2 non-null float64
# gdpPercap_1962    2 non-null float64
# gdpPercap_1967    2 non-null float64
# gdpPercap_1972    2 non-null float64
# gdpPercap_1977    2 non-null float64
# gdpPercap_1982    2 non-null float64
# gdpPercap_1987    2 non-null float64
# gdpPercap_1992    2 non-null float64
# gdpPercap_1997    2 non-null float64
# gdpPercap_2002    2 non-null float64
# gdpPercap_2007    2 non-null float64
# dtypes: float64(12)
# memory usage: 208.0+ bytes
# ~~~
# {: .output}
#
# *   This is a `DataFrame`
# *   Two rows named `'Australia'` and `'New Zealand'`
# *   Twelve columns, each of which has two actual 64-bit floating point values.
#     *   We will talk later about null values, which are used to represent missing observations.
# *   Uses 208 bytes of memory.
#
# ## The `DataFrame.columns` variable stores information about the dataframe's columns.
#
# *   Note that this is data, *not* a method.  (It doesn't have parentheses.)
#     *   Like `math.pi`.
#     *   So do not use `()` to try to call it.
# *   Called a *member variable*, or just *member*.
#
# ~~~
# print(data.columns)
# ~~~
# {: .language-python}
# ~~~
# Index(['gdpPercap_1952', 'gdpPercap_1957', 'gdpPercap_1962', 'gdpPercap_1967',
#        'gdpPercap_1972', 'gdpPercap_1977', 'gdpPercap_1982', 'gdpPercap_1987',
#        'gdpPercap_1992', 'gdpPercap_1997', 'gdpPercap_2002', 'gdpPercap_2007'],
#       dtype='object')
# ~~~
# {: .output}
#
# ## Use `DataFrame.T` to transpose a dataframe.
#
# *   Sometimes want to treat columns as rows and vice versa.
# *   Transpose (written `.T`) doesn't copy the data, just changes the program's view of it.
# *   Like `columns`, it is a member variable.
#
# ~~~
# print(data.T)
# ~~~
# {: .language-python}
# ~~~
# country           Australia  New Zealand
# gdpPercap_1952  10039.59564  10556.57566
# gdpPercap_1957  10949.64959  12247.39532
# gdpPercap_1962  12217.22686  13175.67800
# gdpPercap_1967  14526.12465  14463.91893
# gdpPercap_1972  16788.62948  16046.03728
# gdpPercap_1977  18334.19751  16233.71770
# gdpPercap_1982  19477.00928  17632.41040
# gdpPercap_1987  21888.88903  19007.19129
# gdpPercap_1992  23424.76683  18363.32494
# gdpPercap_1997  26997.93657  21050.41377
# gdpPercap_2002  30687.75473  23189.80135
# gdpPercap_2007  34435.36744  25185.00911
# ~~~
# {: .output}
#
# ## Use `DataFrame.describe()` to get summary statistics about data.
#
# `DataFrame.describe()` gets the summary statistics of only the columns that have numerical data. 
# All other columns are ignored, unless you use the argument `include='all'`.
# ~~~
# print(data.describe())
# ~~~
# {: .language-python}
# ~~~
#        gdpPercap_1952  gdpPercap_1957  gdpPercap_1962  gdpPercap_1967  \
# count        2.000000        2.000000        2.000000        2.000000
# mean     10298.085650    11598.522455    12696.452430    14495.021790
# std        365.560078      917.644806      677.727301       43.986086
# min      10039.595640    10949.649590    12217.226860    14463.918930
# 25%      10168.840645    11274.086022    12456.839645    14479.470360
# 50%      10298.085650    11598.522455    12696.452430    14495.021790
# 75%      10427.330655    11922.958888    12936.065215    14510.573220
# max      10556.575660    12247.395320    13175.678000    14526.124650
#
#        gdpPercap_1972  gdpPercap_1977  gdpPercap_1982  gdpPercap_1987  \
# count         2.00000        2.000000        2.000000        2.000000
# mean      16417.33338    17283.957605    18554.709840    20448.040160
# std         525.09198     1485.263517     1304.328377     2037.668013
# min       16046.03728    16233.717700    17632.410400    19007.191290
# 25%       16231.68533    16758.837652    18093.560120    19727.615725
# 50%       16417.33338    17283.957605    18554.709840    20448.040160
# 75%       16602.98143    17809.077557    19015.859560    21168.464595
# max       16788.62948    18334.197510    19477.009280    21888.889030
#
#        gdpPercap_1992  gdpPercap_1997  gdpPercap_2002  gdpPercap_2007
# count        2.000000        2.000000        2.000000        2.000000
# mean     20894.045885    24024.175170    26938.778040    29810.188275
# std       3578.979883     4205.533703     5301.853680     6540.991104
# min      18363.324940    21050.413770    23189.801350    25185.009110
# 25%      19628.685413    22537.294470    25064.289695    27497.598692
# 50%      20894.045885    24024.175170    26938.778040    29810.188275
# 75%      22159.406358    25511.055870    28813.266385    32122.777857
# max      23424.766830    26997.936570    30687.754730    34435.367440
# ~~~
# {: .output}
#
# *   Not particularly useful with just two records,
#     but very helpful when there are thousands.
#
# > ## Reading Other Data
# >
# > Read the data in `gapminder_gdp_americas.csv`
# > (which should be in the same directory as `gapminder_gdp_oceania.csv`)
# > into a variable called `americas`
# > and display its summary statistics.
# >
# > > ## Solution
# > > To read in a CSV, we use `pd.read_csv` and pass the filename `'data/gapminder_gdp_americas.csv'` to it.
# > > We also once again pass the column name `'country'` to the parameter `index_col` in order to index by country.
# > > The summary statistics can be displayed with the `DataFrame.describe()` method.
# > > ~~~
# > > americas = pd.read_csv('data/gapminder_gdp_americas.csv', index_col='country')
# > > americas.describe()
# > > ~~~
# > >{: .language-python}
# > {: .solution}
# {: .challenge}
#
# > ## Inspecting Data
# >
# > After reading the data for the Americas,
# > use `help(americas.head)` and `help(americas.tail)`
# > to find out what `DataFrame.head` and `DataFrame.tail` do.
# >
# > 1.  What method call will display the first three rows of this data?
# > 2.  What method call will display the last three columns of this data?
# >     (Hint: you may need to change your view of the data.)
# >
# > > ## Solution
# > > 1. We can check out the first five rows of `americas` by executing `americas.head()`
# > >    (allowing us to view the head of the DataFrame). We can specify the number of rows we wish
# > >    to see by specifying the parameter `n` in our call
# > >    to `americas.head()`. To view the first three rows, execute:
# > >
# > >    ~~~
# > >    americas.head(n=3)
# > >    ~~~
# > >    {: .language-python}
# > >    ~~~
# > >              continent  gdpPercap_1952  gdpPercap_1957  gdpPercap_1962  \
# > >    country
# > >    Argentina  Americas     5911.315053     6856.856212     7133.166023
# > >    Bolivia    Americas     2677.326347     2127.686326     2180.972546
# > >    Brazil     Americas     2108.944355     2487.365989     3336.585802
# > >
# > >               gdpPercap_1967  gdpPercap_1972  gdpPercap_1977  gdpPercap_1982  \
# > >    country
# > >    Argentina     8052.953021     9443.038526    10079.026740     8997.897412
# > >    Bolivia       2586.886053     2980.331339     3548.097832     3156.510452
# > >    Brazil        3429.864357     4985.711467     6660.118654     7030.835878
# > >
# > >               gdpPercap_1987  gdpPercap_1992  gdpPercap_1997  gdpPercap_2002  \
# > >    country
# > >    Argentina     9139.671389     9308.418710    10967.281950     8797.640716
# > >    Bolivia       2753.691490     2961.699694     3326.143191     3413.262690
# > >    Brazil        7807.095818     6950.283021     7957.980824     8131.212843
# > >
# > >               gdpPercap_2007
# > >    country
# > >    Argentina    12779.379640
# > >    Bolivia       3822.137084
# > >    Brazil        9065.800825
# > >    ~~~
# > >    {: .output}
# > > 2. To check out the last three rows of `americas`, we would use the command,
# > >    `americas.tail(n=3)`, analogous to `head()` used above. However, here we want to look at
# > >    the last three columns so we need to change our view and then use `tail()`. To do so, we
# > >     create a new DataFrame in which rows and columns are switched:
# > >
# > >    ~~~
# > >    americas_flipped = americas.T
# > >    ~~~
# > >    {: .language-python}
# > >
# > >    We can then view the last three columns of `americas` by viewing the last three rows
# > >    of `americas_flipped`:
# > >    ~~~
# > >    americas_flipped.tail(n=3)
# > >    ~~~
# > >    {: .language-python}
# > >    ~~~
# > >    country        Argentina  Bolivia   Brazil   Canada    Chile Colombia  \
# > >    gdpPercap_1997   10967.3  3326.14  7957.98  28954.9  10118.1  6117.36
# > >    gdpPercap_2002   8797.64  3413.26  8131.21    33329  10778.8  5755.26
# > >    gdpPercap_2007   12779.4  3822.14   9065.8  36319.2  13171.6  7006.58
# > >
# > >    country        Costa Rica     Cuba Dominican Republic  Ecuador    ...     \
# > >    gdpPercap_1997    6677.05  5431.99             3614.1  7429.46    ...
# > >    gdpPercap_2002    7723.45  6340.65            4563.81  5773.04    ...
# > >    gdpPercap_2007    9645.06   8948.1            6025.37  6873.26    ...
# > >
# > >    country          Mexico Nicaragua   Panama Paraguay     Peru Puerto Rico  \
# > >    gdpPercap_1997   9767.3   2253.02  7113.69   4247.4  5838.35     16999.4
# > >    gdpPercap_2002  10742.4   2474.55  7356.03  3783.67  5909.02     18855.6
# > >    gdpPercap_2007  11977.6   2749.32  9809.19  4172.84  7408.91     19328.7
# > >
# > >    country        Trinidad and Tobago United States  Uruguay Venezuela
# > >    gdpPercap_1997             8792.57       35767.4  9230.24   10165.5
# > >    gdpPercap_2002             11460.6       39097.1     7727   8605.05
# > >    gdpPercap_2007             18008.5       42951.7  10611.5   11415.8
# > >    ~~~
# > >    {: .output}
# > >    
# > >    This shows the data that we want, but we may prefer to display three columns instead of three rows,
# > >    so we can flip it back:
# > >    ~~~
# > >    americas_flipped.tail(n=3).T    
# > >    ~~~
# > >    {: .language-python}    
# > >    __Note:__ we could have done the above in a single line of code by 'chaining' the commands:
# > >    ~~~
# > >    americas.T.tail(n=3).T
# > >    ~~~
# > >    {: .language-python}
# > {: .solution}
# {: .challenge}
#
#
# > ## Reading Files in Other Directories
# >
# > The data for your current project is stored in a file called `microbes.csv`,
# > which is located in a folder called `field_data`.
# > You are doing analysis in a notebook called `analysis.ipynb`
# > in a sibling folder called `thesis`:
# >
# > ~~~
# > your_home_directory
# > +-- field_data/
# > |   +-- microbes.csv
# > +-- thesis/
# >     +-- analysis.ipynb
# > ~~~
# > {: .output}
# >
# > What value(s) should you pass to `read_csv` to read `microbes.csv` in `analysis.ipynb`?
# > 
# > > ## Solution
# > > We need to specify the path to the file of interest in the call to `pd.read_csv`. We first need to 'jump' out of
# > > the folder `thesis` using '../' and then into the folder `field_data` using 'field_data/'. Then we can specify the filename `microbes.csv.
# > > The result is as follows:
# > > ~~~
# > > data_microbes = pd.read_csv('../field_data/microbes.csv')
# > > ~~~
# > >{: .language-python}
# > {: .solution}
# {: .challenge}
#
# > ## Writing Data
# > 
# > As well as the `read_csv` function for reading data from a file,
# > Pandas provides a `to_csv` function to write dataframes to files.
# > Applying what you've learned about reading from files,
# > write one of your dataframes to a file called `processed.csv`.
# > You can use `help` to get information on how to use `to_csv`.
# > > ## Solution
# > > In order to write the DataFrame `americas` to a file called `processed.csv`, execute the following command:
# > > ~~~
# > > americas.to_csv('processed.csv')
# > > ~~~
# > >{: .language-python}
# > > For help on `to_csv`, you could execute, for example:
# > > ~~~
# > > help(americas.to_csv)
# > > ~~~
# > >{: .language-python}
# > > Note that `help(to_csv)` throws an error! This is a subtlety and is due to the fact that `to_csv` is NOT a function in 
# > > and of itself and the actual call is `americas.to_csv`. 
# > {: .solution}
# {: .challenge}

# + [markdown] id="8AONlfPywjiE"
#

StarcoderdataJupyterScriptsDedupFiltered