Pandas Dataframes

%matplotlib inline
%config InlineBackend.figure_format = 'retina'
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt

pd.set_option("display.max_rows", 8)
plt.rcParams['figure.figsize'] = (9, 6)

Create a DataFrame

dates = pd.date_range('20130101', periods=6)
pd.DataFrame(np.random.randn(6,4), index=dates, columns=list('ABCD'))

pd.DataFrame({'A' : 1.,
              'B' : pd.Timestamp('20130102'),
              'C' : pd.Series(1,index=list(range(4)),dtype='float32'),
              'D' : np.arange(4,dtype='int32'),
              'E' : pd.Categorical(["test","train","test","train"]),
              'F' : 'foo' })

Load Data from CSV File

url = "https://www.fun-mooc.fr/c4x/agrocampusouest/40001S03/asset/AnaDo_JeuDonnees_TemperatFrance.csv"
french_cities = pd.read_csv(url, delimiter=";", encoding="latin1", index_col=0)
french_cities

Viewing Data

french_cities.head()

french_cities.tail()

Index

french_cities.index

We can rename an index by setting its name.

french_cities.index.name = "City"
french_cities.head()

import locale
import calendar
 
locale.setlocale(locale.LC_ALL,'C')
 
months = calendar.month_abbr
print(*months)
 
french_cities.rename(
  columns={ old : new 
           for old, new in zip(french_cities.columns[:12], months[1:])
          if old != new },
  inplace=True)
 
french_cities.rename(columns={'Moye':'Mean'}, inplace=True)
french_cities

Exercise: Rename DataFrame Months in English

From a local or remote HTML file

We can download and extract data about mean sea level stations around the world from the PSMSL website.

# Needs `lxml`, `beautifulSoup4` and `html5lib` python packages
table_list = pd.read_html("http://www.psmsl.org/data/obtaining/")

# there is 1 table on that page which contains metadata about the stations where 
# sea levels are recorded
local_sea_level_stations = table_list[0]
local_sea_level_stations

Indexing on DataFrames

french_cities['Lati']  # DF [] accesses columns (Series)

.loc and .iloc allow to access individual values, slices or masked selections:

french_cities.loc['Rennes', "Sep"]

french_cities.loc['Rennes', ["Sep", "Dec"]]

french_cities.loc['Rennes', "Sep":"Dec"]

Masking

mask = [True, False] * 6 + 5 * [False]
print(french_cities.iloc[:, mask])

print(french_cities.loc["Rennes", mask])

New column

french_cities["std"] = french_cities.iloc[:,:12].std(axis=1)
french_cities

french_cities = french_cities.drop("std", axis=1) # remove this new column

french_cities

Modifying a dataframe with multiple indexing

# french_cities['Rennes']['Sep'] = 25 # It does not works and breaks the DataFrame
french_cities.loc['Rennes']['Sep'] # = 25 is the right way to do it

french_cities

Transforming datasets

french_cities['Mean'].min(), french_cities['Ampl'].max()

Apply

Let’s convert the temperature mean from Celsius to Fahrenheit degree.

fahrenheit = lambda T: T*9/5+32
french_cities['Mean'].apply(fahrenheit)

Sort

french_cities.sort_values(by='Lati')

french_cities = french_cities.sort_values(by='Lati',ascending=False)
french_cities

Stack and unstack

Instead of seeing the months along the axis 1, and the cities along the axis 0, let’s try to convert these into an outer and an inner axis along only 1 time dimension.

pd.set_option("display.max_rows", 20)
unstacked = french_cities.iloc[:,:12].unstack()
unstacked

type(unstacked)

Transpose

The result is grouped in the wrong order since it sorts first the axis that was unstacked. We need to transpose the dataframe.

city_temp = french_cities.iloc[:,:12].transpose()
city_temp.plot()

city_temp.boxplot(rot=90);

Describing

french_cities['Région'].describe()

french_cities['Région'].unique()

french_cities['Région'].value_counts()

# To save memory, we can convert it to a categorical column:
french_cities["Région"] = french_cities["Région"].astype("category")

french_cities.memory_usage()

Data Aggregation/summarization

groupby

fc_grouped_region = french_cities.groupby("Région")
type(fc_grouped_region)

for group_name, subdf in fc_grouped_region:
    print(group_name)
    print(subdf)
    print("")

Exercise

Consider the following dataset UCI Machine Learning Repository Combined Cycle Power Plant Data Set. This dataset consists of records of measurements relating to peaker power plants of 10000 points over 6 years (2006-2011).

Variables - AT = Atmospheric Temperature in C - V = Exhaust Vaccum Speed - AP = Atmospheric Pressure - RH = Relative Humidity - PE = Power Output

We want to model the power output as a function of the other parameters.

Observations are in 5 excel sheets of about 10000 records in “Folds5x2_pp.xlsx”. These 5 sheets are same data shuffled. - Read this file with the pandas function read_excel. What is the type returned by this function? - Implement a select function to regroup all observations in a pandas serie. - Use select function and corr to compute the maximum correlation. - Parallelize this loop with concurrent.futures.