<jupyter_start><jupyter_text>Data I/O* csv* json* hdf* html* many more Now, let's assume we have already gotten our dataset loaded into pandas.Next job is to do meaningful analysis on the data.  inspecting data<jupyter_code>from urllib import request
url = 'https://raw.githubusercontent.com/MPIDS/Python-Course/master/other/pandas/amazonianBirds_climate.csv'
data = request.urlopen(url)
data = data.read()
st = data.decode('utf-8')

# saving data
import os
datadir = os.path.join(os.getcwd(), '..', 'data') 
birds_filepath = os.path.join(datadir, 'amazonianBirds_climate.csv')
with open(birds_filepath, 'w') as f:
    f.write(st)
df = pd.read_csv(birds_filepath, parse_dates={'datetime':[1,2]}, error_bad_lines=False)
df.head()
df.shape
df.columns
df.sample(n = 4)
df.mean()<jupyter_output><empty_output><jupyter_text>Selection<jupyter_code>df = pd.read_csv(birds_filepath, parse_dates={'datetime':[1,2]}, na_values = ['?'], error_bad_lines=False)
df.head()<jupyter_output><empty_output><jupyter_text>Getting rows by row number<jupyter_code>df.iloc[0:4]<jupyter_output><empty_output><jupyter_text>**Note**: This syntax also works, but see caveats below. ```pythondf[0:4]```  Sorting data by date<jupyter_code>df = df.sort_values('datetime')
df.head()<jupyter_output><empty_output><jupyter_text>Filtering out bad data (Boolean indexing)*we saw it already in the morning*<jupyter_code>df = df[df['datetime']>='1970-01-01']
df.head()
%%latex
\[
\texttt{df = df[}\underbrace{\texttt{df['datetime']>='1970-01-01'}}_{\texttt{Boolean array}}]
\]<jupyter_output><empty_output><jupyter_text>Quite complex filetering is also possible<jupyter_code>df[(df['datetime']>'2012-07-01') & (df['datetime']<'2012-08-01')].head()<jupyter_output><empty_output><jupyter_text>Transforming data<jupyter_code>df['elevation'].astype(float)
def coerce_float(x):
    try:
        return float(x)
    except ValueError:
        return None
df['elevation'] = df['elevation'].apply(coerce_float)<jupyter_output><empty_output><jupyter_text>Reindex by datetime<jupyter_code>df['datetime'] = pd.to_datetime(df['datetime'], errors='coerce')
df = df[pd.notnull(df.datetime)]
df.head()
df = df.set_index('datetime')
df.head()<jupyter_output><empty_output><jupyter_text>Selecting by **index label**<jupyter_code>df.loc['1990-07-01':'1992-12-01']<jupyter_output><empty_output><jupyter_text>This is equivalent to<jupyter_code>df['1990-07-01':'1992-12-01']<jupyter_output><empty_output><jupyter_text>**WARNING:** The `.loc` syntax is infinitely preferable for selecting by label, as then you avoid pitfalls like:<jupyter_code>td = pd.DataFrame(np.random.randint(10, size = (8,4)), index = range(3,11), columns=['A', 'B', 'C', 'D'])
td
td[0:4] # == td.iloc[0:4]<jupyter_output><empty_output><jupyter_text>This does not return rows with index label between 0 and 4, however `.loc` does<jupyter_code>td.loc[0:4]<jupyter_output><empty_output><jupyter_text>Why: the slicing operator `[:]` tries `iloc` first, then falls back to `loc`  Slicing with increments<jupyter_code>td
td.iloc[:5:2, :]
td.iloc[::-1, :]<jupyter_output><empty_output><jupyter_text>Weird behaviour:<jupyter_code>td.iloc[:1:-1, :]
td.loc[3:7:2, :]<jupyter_output><empty_output><jupyter_text>**WARNING:** Unlike Python's array indexing, `df.loc` and `df.iloc` *includes the endpoints* of the slices  Benefits of indexing<jupyter_code>df = pd.read_csv(birds_filepath, parse_dates={'datetime':[1,2]}, error_bad_lines=False)
df.head()
%time df[(df['datetime'] > '2011-02-24') & (df['datetime'] < '2013-04-28') ].count()
idf = df.copy()
idf['datetime'] = pd.to_datetime(idf['datetime'], errors='coerce')
idf = idf[pd.notnull(df.datetime)]
idf = idf.set_index('datetime')
idf.head()
%time idf['2011-02-24':'2013-04-28']['latitude'].count()<jupyter_output>CPU times: user 5.54 ms, sys: 1.27 ms, total: 6.81 ms
Wall time: 5 ms<jupyter_text>Selecting by columns<jupyter_code>df.loc[:, ['recordist', 'latitude', 'longitude']].head()<jupyter_output><empty_output><jupyter_text>Transforming data Adding a column<jupyter_code>df = pd.read_csv(birds_filepath, parse_dates={'datetime':[1,2]}, error_bad_lines=False)
df['datetime'] = pd.to_datetime(df['datetime'], errors='coerce')
df = df[pd.notnull(df.datetime)]
df = df.set_index('datetime')
df.head()
df.loc[:, 'year'] = df.index.year
df.head()<jupyter_output><empty_output><jupyter_text>Applying transformations on a column, revisited<jupyter_code>df.loc[:, 'year'] = df.loc[:, 'year'] - df.loc[:, 'year'].min()
df.head()<jupyter_output><empty_output><jupyter_text>Append<jupyter_code>df1 = pd.DataFrame(np.random.randint(5, size=(4,6)), columns=list('ABCDEF'))
 df2 = pd.DataFrame(np.random.randint(5, size=(4,6)), columns=list('ABCDEF'))
df1
df2
df3 = df1.append(df2)
df3<jupyter_output><empty_output><jupyter_text>Now df3 has nonsensical index:<jupyter_code>df3.loc[2:3]<jupyter_output><empty_output><jupyter_text>We have to reindex:<jupyter_code>df3
df3.index = range(len(df3))<jupyter_output><empty_output><jupyter_text>Concatenation<jupyter_code>df1 = pd.DataFrame(np.random.randint(5, size=(4,6)), columns=list('ABCDEF'))
df2 = pd.DataFrame(np.random.randint(5, size=(4,6)), columns=list('ABCDEF'))
df3 = pd.DataFrame(np.random.randint(5, size=(4,6)), columns=list('ABCDEF'))
pd.concat([df1, df2, df3])<jupyter_output><empty_output><jupyter_text>Join Combining two tables with some common column(s)<jupyter_code>from urllib import request
from bs4 import BeautifulSoup
import io<jupyter_output><empty_output><jupyter_text>Before running the next cell, you will need to install a python packahe called `lxml`.<jupyter_code>!pip install --user lxml
fossil_fuel_url = 'http://www.worldatlas.com/articles/countries-the-most-dependent-on-fossil-fuels.html'
response = request.urlopen(fossil_fuel_url)
data = response.read()
ff_st = data.decode('utf-8')
soup = BeautifulSoup(ff_st, "lxml")
fossil = str(soup.find_all('table', attrs={'data-role':"table"})[0])
fdf = pd.read_html(io.StringIO(fossil))[0]
fdf.head()
renewable_url = 'https://en.wikipedia.org/wiki/List_of_countries_by_electricity_production_from_renewable_sources'
response = request.urlopen(renewable_url)
data = response.read()
ff_st = data.decode('utf-8')
soup = BeautifulSoup(ff_st, "lxml")
renew = str(soup.find_all('table', class_='wikitable sortable')[0])
rdf = pd.read_html(io.StringIO(renew), header=0)[0]
rdf.head()
df1 = fdf.copy()
df2 = rdf.copy()
df3 = pd.merge(df1, df2, left_on = 'Country Name', right_on='Country')
df3.head()<jupyter_output><empty_output><jupyter_text>By default, join uses the intersection of the join keys<jupyter_code>len(set(df1['Country Name'])), len(set(df2['Country'])), len(set(df1['Country Name'])&set(df2['Country']))
df3.shape<jupyter_output><empty_output><jupyter_text>To have the union of the keys, use `how=outer` option:<jupyter_code>df4 = pd.merge(df1, df2, left_on = 'Country Name', right_on='Country', how = 'outer')
df4.head()<jupyter_output><empty_output><jupyter_text>All countries in the `df1` and `df2` are present, with rows that are not on both tables filled with `NaN` values.<jupyter_code>len(set(df1['Country Name'])), len(set(df2['Country'])), len(set(df1['Country Name'])|set(df2['Country']))
df4.shape<jupyter_output><empty_output><jupyter_text>It is also possible to have *only* the keys in the left table (or right)<jupyter_code>df5 = pd.merge(df1, df2, left_on = 'Country Name', right_on='Country', how = 'left')
df5.head()<jupyter_output><empty_output><jupyter_text>All countries in the `df1` columns are present, even if they are not in `df2`; but not the other way round.  Join (AKA, merging on index)<jupyter_code>idf1 = df1.set_index('Country Name')
idf2 = df2.set_index('Country')
idf1.join(idf2)<jupyter_output><empty_output><jupyter_text>**Warning:** One major difference between `marge` and `join`: by default (i.e. without `how=` specified, `merge` does **inner join**, but `join` does **left outer join**. **Note:** Indexing is better for performing searches.<jupyter_code>%timeit -n 1 idf1.join(idf2)
%timeit -n 1 pd.merge(df1, df2, left_on = 'Country Name', right_on='Country', how = 'left')<jupyter_output>5.86 ms ± 695 µs per loop (mean ± std. dev. of 7 runs, 1 loop each)<jupyter_text>This is of course, also true outside on merge<jupyter_code>df2<jupyter_output><empty_output><jupyter_text>Aggregation<jupyter_code>population_url = 'https://en.wikipedia.org/wiki/List_of_cities_proper_by_population'
response = request.urlopen(population_url)
data = response.read()
pop_st = data.decode('utf-8')
soup = BeautifulSoup(pop_st, "lxml")
pops = str(soup.find_all('table', class_='sortable wikitable')[0])
popdf= pd.read_html(io.StringIO(pops), header=0)[0]
popdf.head()
popdf = popdf.loc[:, ['City', 'Population density (/km²)', 'Country']]
popdf.rename(columns={'Population density (/km²)': 'population density'}, inplace=True)<jupyter_output><empty_output><jupyter_text>How to obtain number of cities per country with population density higher than 3000<jupyter_code>x = popdf[popdf['population density']>3000]
gr = x.groupby('Country')
gr.aggregate('count')<jupyter_output><empty_output><jupyter_text>Using different aggregators<jupyter_code>gr.aggregate({'City':'count', 'population density':'mean'})<jupyter_output><empty_output><jupyter_text>`groupby.describe` gives very succinct statistical summary of your dataset<jupyter_code>gr.describe()<jupyter_output><empty_output><jupyter_text>The result of `groupby` is a DataFrame with a `MultiIndex`<jupyter_code>stats = gr.describe()
stats.index<jupyter_output><empty_output><jupyter_text>This is a very powerful tool to store hierarchical data<jupyter_code>stats<jupyter_output><empty_output><jupyter_text>Selecting in a MultiIndexed dataFrame<jupyter_code>stats.loc['Vietnam']
stats.loc['Vietnam', 'mean']<jupyter_output><empty_output><jupyter_text>Slicing works, too<jupyter_code>stats.loc['Afghanistan':'Brazil']<jupyter_output><empty_output><jupyter_text>Getting a "flat" DataFrame back: `reset_index()` Obtaining the top 10 countries by average population density in urban areas<jupyter_code>x = stats.sort_values('population density', ascending=False)
x
y = x.reset_index()
y.head()
y[y['level_1']=='mean'][0:10]<jupyter_output><empty_output><jupyter_text>That's it! Time for some exercises.  Exercises  Solar energy **Relevant concepts:*** BeautifulSoup* read_html* df.join* df.groupby, df.aggregateUse the [UN data portal](http://data.un.org) to download datasets on [Worldwide sunshine](http://data.un.org/Data.aspx?q=Korea&d=CLINO&f=ElementCode%3A15%3BCountryCode%3AKO), [solar electricity production](http://data.un.org/Data.aspx?d=EDATA&f=cmID%3AES) and [total Electricity production](http://data.un.org/Data.aspx?d=EDATA&f=cmID%3AEC).Compute1. Year-by-year change in Solar electricity output by country. 2. How do sunshine and fractional share of solar in total electricity output correlate?   Beijing PM2.5 **Relevant concepts:*** integer based/label based slicing* df.groupbyRead the [Beijing PM2.5 Data Data Set](http://archive.ics.uci.edu/ml/datasets/Beijing+PM2.5+Data). Plot:1. Average pm2.5 count at each month. Plot separate curves for each year. 2. Plot the correlation between pm2.5 and temperature.<jupyter_code>import matplotlib
matplotlib.rcParams['figure.figsize'] = (8,6)
%matplotlib inline
from importlib import reload
reload(plt)
import pandas as pd
from io import StringIO
from urllib import request
url = 'http://archive.ics.uci.edu/ml/machine-learning-databases/00381/PRSA_data_2010.1.1-2014.12.31.csv'<jupyter_output><empty_output><jupyter_text>GPS tracksDownload the GPS tracks dataset from [UCI Machine Learning Repository](https://archive.ics.uci.edu/ml/datasets/GPS+Trajectories).Read the two csvs `go_track_tracks.csv` and `go_track_trackspoints.csv`. The first one contains a list of GPS tracks, while the second file contains individual points in each GPS track. Determine:1. The origin and destination of each vehicle.2. The top speed of each vehicle (choose a suitable time interval, otherwise you will get a non-smooth velocity profile). 3. The fraction of cars and buses (separately) that exceeded > 80km/hr velocity.   Container in SeaDownload the datset at [http://data.deutschebahn.com/dataset/data-sensordaten-schenker-seefrachtcontainer](http://data.deutschebahn.com/dataset/data-sensordaten-schenker-seefrachtcontainer).The dataset contains GPS traces from contains in Cargo ships. 1. Plot the trajectories of each cargoship.2. Compute the distance travelled in each day by each ship.3. **(Extra challenge)** Visualize the trajectories on top of a world map by using `bokeh` or `plotly`.<jupyter_code>url = 'http://download-data.deutschebahn.com/static/datasets/sensordaten_schenker/161209_Schenker_Sensordaten.csv'<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.15302602696770148, 'fraction_numerical': 0.024302289118846035, 'mean_word_length': 7.8040027605245, 'pattern_counts': {'":': 0, '<': 169, '<?xml version=': 0, '>': 173, 'https://': 4, 'lorem ipsum': 0, 'www.': 1, 'xml': 5}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 4, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '42', 'dir': 'jupyter-structured-clean-dedup', 'id': '149075', 'license': 'CC-BY-4.0', 'n_tokens_mistral': 4845, 'n_tokens_neox': 4630, 'n_words': 940, 'path': 'day2/02-pandas.ipynb', 'repo_name': 'JanaLasser/data-science-course', 'size': '12756'}