Notes S-07/Python 4: Pivoting, prediction, and data types

Python-Teil: Vorsichtig vorgehen (möglichst viele Studis mitnehmen)

Missing material

TODO: Traveltime.py appears in the exercise table, but it is not present in the supplied material inventory. Do not create or substitute a file until the source is located.

Slide 58 — Load the sales data and create a pivot table

The additional shape and column-name checks inspect the dataset before constructing the pivot table.

import pandas as pd

sales = pd.read_excel('Sales.xlsx')
print(sales.shape)
sales.head()
list(sales)

pd.pivot_table(
    sales,
    index='Region',
    values='Turnover',
    aggfunc='sum',
)

Slide 59 — Filter the data before pivoting

Both filtering forms are equivalent and prevent actual and target values from being aggregated together.

pd.pivot_table(
    sales.loc[sales.Category == 'Actual'],
    index='Region',
    values='Turnover',
    aggfunc='sum',
)

pd.pivot_table(
    sales[sales.Category == 'Actual'],
    index='Region',
    values='Turnover',
    aggfunc='sum',
)

Slide 60 — Apply multiple aggregation functions

aggfunc can contain multiple aggregation functions.

pd.pivot_table(
    sales[sales.Category == 'Actual'],
    index='Region',
    values='Turnover',
    aggfunc=['sum', 'mean', 'std'],
)

Slide 61 — Add products as a column dimension

First, compare an unfiltered pivot with the filtered lecture example. columns='Product' introduces a second pivot-table dimension.

pd.pivot_table(
    sales,
    index='Region',
    columns='Product',
    values='Turnover',
    aggfunc='sum',
)

pd.pivot_table(
    sales[sales.Category == 'Actual'],
    index='Region',
    columns='Product',
    values='Turnover',
    aggfunc='sum',
)

Slide 62 — Combine multiple filters

Parentheses are required around the individual Pandas conditions.

pd.pivot_table(
    sales[
        (sales.Category == 'Actual')
        & (sales.Year == 2020)
    ],
    index='Region',
    columns='Product',
    values='Turnover',
    aggfunc='sum',
)

Slide 63 — Store and extend a pivot table

The pivot table is a DataFrame and can therefore be stored, summed, and extended with a total row.

pivot_df = pd.pivot_table(
    sales[
        (sales.Category == 'Actual')
        & (sales.Year == 2020)
    ],
    index='Region',
    columns='Product',
    values='Turnover',
    aggfunc='sum',
)

pivot_df.sum()
pivot_df.loc['Total'] = pivot_df.sum()
print(pivot_df)

Slide 64 — Plot a pivot table

pivot_df.plot(kind='bar')

Slide 65 — Use multiple row dimensions

Passing a list to index creates a hierarchical row index.

pd.pivot_table(
    sales[sales.Category == 'Actual'],
    index=['Year', 'Region'],
    columns='Product',
    values='Turnover',
    aggfunc='sum',
)

Slide 67 — Pivot tables

In-class exercise ../materials/data/insurance.xlsx

In-class exercise ../materials/session_07/insurance2.py

import pandas as pd

insurance = pd.read_excel('insurance.xlsx')

pd.options.display.float_format = '{:.2f}'.format
pd.pivot_table(insurance, index = 'State', values = 'InsuredValue', aggfunc = 'mean')

pd.pivot_table(insurance, index = 'State', columns = 'Location', values = 'InsuredValue',
               aggfunc = 'mean', fill_value=0)

# Remark: The parameter fill_value=0 replaces NaN with 0 !!!

pd.pivot_table(insurance[(insurance.Flood=='Y') & (insurance.Earthquake=='Y')],
               index = 'State', columns = 'Region', values = 'InsuredValue',
               aggfunc = 'mean', fill_value=0)

pd.pivot_table(insurance[(insurance.Flood=='Y') & (insurance.Earthquake=='Y')],
               index = 'State', columns = 'Construction', values = 'InsuredValue',
               aggfunc = 'mean', fill_value=0)

pd.pivot_table(insurance, index = ['Location', 'BusinessType'], columns = 'Region',
               values = 'InsuredValue', aggfunc = 'sum', fill_value=0)

pivot_df = pd.pivot_table(insurance, index = ['Location', 'BusinessType'],
                          columns = 'Region', values = 'InsuredValue', aggfunc = 'sum', fill_value=0)
pivot_df.loc['Total'] = pivot_df.sum()
print(pivot_df)

pivot_df = pd.pivot_table(insurance, index = 'Location', columns = 'State',
                          values = 'InsuredValue', aggfunc = 'count', fill_value=0)
pivot_df.plot(kind='bar')

Slides 68–69 — Prediction concepts

Input variables describe the observations, while the target variable is the value to predict. A predictive model learns their relationship; prediction error measures how closely its predictions match known targets. Scikit-learn uses a standardized workflow: construct an estimator, fit it, predict, and evaluate.

Slide 70 — Prepare input and output data

x contains the predictor variables and y contains the target variable. The following demonstrations are sequential and reuse these arrays.

import pandas as pd

students = pd.read_csv("Student_performance.csv")
students.head()

x = students.loc[
    :,
    [
        'Hours_Studied',
        'Sleep_Hours',
        'Exercises_Practiced',
    ],
].values

y = students.loc[:, 'Performance'].values

Slide 71 — Linear regression

This constructs the estimator, fits it, predicts the known observations, calculates \(R^2\), and finally predicts one new observation.

from sklearn.linear_model import LinearRegression
from sklearn.metrics import r2_score

predictivemodel_reg = LinearRegression()
predictivemodel_reg.fit(x, y)

y_pred = predictivemodel_reg.predict(x)

r2_score(y, y_pred)
predictivemodel_reg.predict([[5, 5, 1]])

Slide 72 — Random-forest regression

random_state=0 makes the model’s random choices reproducible.

from sklearn.ensemble import RandomForestRegressor
from sklearn.metrics import r2_score

predictivemodel_rf = RandomForestRegressor(random_state=0)
predictivemodel_rf.fit(x, y)

y_pred = predictivemodel_rf.predict(x)

r2_score(y, y_pred)
predictivemodel_rf.predict([[5, 5, 1]])

Slide 73 — Neural-network regression

from sklearn.neural_network import MLPRegressor
from sklearn.metrics import r2_score

predictivemodel_net = MLPRegressor(
    solver='lbfgs',
    max_iter=3000,
    random_state=0,
)

predictivemodel_net.fit(x, y)
y_pred = predictivemodel_net.predict(x)

r2_score(y, y_pred)
predictivemodel_net.predict([[5, 5, 1]])

Slides 74–75 — Chipotle analysis

In-class exercise ../materials/data/Chipotle.csv

In-class exercise ../materials/session_07/Chipotle.py

import pandas as pd

chipo = pd.read_csv('Chipotle.csv')

#1 Display the first 10 entries
chipo.head(10)

#2 What is the number of observations in the dataset?
chipo.shape[0]

#3 What is the number of columns in the dataset?
chipo.shape[1]

#4 Print the names of all the columns as a list.
list(chipo)

#5 How many items were ordered in total?
chipo['quantity'].sum()

#6 How many different items are sold?
chipo['item_name'].value_counts().count()

#7 How many orders were made in the period?
chipo['order_id'].value_counts().count()

#8 How much was the revenue for the period in the dataset? Solve without creating a new column 'revenue'.
(chipo['quantity']* chipo['item_price']).sum()

#9 What is the average revenue amount per order? Solve with creating a new column 'revenue'.
chipo['revenue'] = chipo['quantity'] * chipo['item_price']
chipo.groupby('order_id')['revenue'].sum().mean()

#10 Which was the most-ordered item?
chipo.groupby('item_name').sum().sort_values(['quantity'], ascending=False).head(1)

#11 How many products cost more than $10.00?
chipo.loc[chipo.item_price>10, 'item_price'].count()

#12 What was the quantity of the most expensive item ordered?
chipo.loc[chipo.item_price==chipo.item_price.max(), 'quantity']

#13 How many times was a Veggie Salad Bowl ordered?
chipo[chipo.item_name == "Veggie Salad Bowl"].shape[0]
len(chipo[chipo.item_name == "Veggie Salad Bowl"])

#14 How many times did someone order more than one Canned Soda?
chipo[(chipo.item_name == "Canned Soda") & (chipo.quantity > 1)].shape[0]
len(chipo[(chipo.item_name == "Canned Soda") & (chipo.quantity > 1)])

#15 Create a bar chart of the top 5 items bought
chipo['item_name'].value_counts().head(5).plot(kind='bar', xlabel='Items', ylabel='Number of Times Ordered', title='Most ordered Chipotles Items', rot=20)

#16 Create a scatterplot with the number of items orderered per order price
chipo.groupby('order_id').sum().plot(kind='scatter', x='quantity', y='item_price', xlabel='Items ordered', ylabel='Order Price', title='Number of items ordered per order price')

Slides 76–77 — Florida housing analysis

In-class exercise ../materials/data/Homes_Florida.xlsx

In-class exercise ../materials/session_07/Homes_Florida.py

import pandas as pd

houses = pd.read_excel('Homes_Florida.xlsx')

#1 Erzeugen Sie eine neue Spalte namens Profit, die den bei den Haustransaktionen erzielten Gewinn enthält.
houses['Profit'] = houses.Sales_Price - houses.Purchase_Price

#2 Welchen Gewinn hat die Agency "Orlando" erzielt?
houses.loc[houses.Agency=='Orlando', 'Profit'].sum()

#3 Ermitteln Sie die Gewinne, die pro Area gemacht wurden. Visualisieren Sie diese anschließend in einem Bar-Chart.
houses.groupby('Area')['Profit'].sum()
houses.groupby('Area')['Profit'].sum().plot(kind='bar', x='Area', y='Profit')

#4 Ermitteln Sie die durchschnittlichen Gewinne in Prozent von Häusern, deren Size kleiner als 2000 ist.
(houses.loc[houses.Size<2000, 'Profit']/houses.loc[houses.Size<2000, 'Sales_Price']).mean()*100

#5 Wie viele Häuser mit einem Alter von 6 Jahren sind größer als 2000 qm?
houses.loc[(houses.Age==6) & (houses.Size>2000)].count()
houses.loc[(houses.Age==6) & (houses.Size>2000), 'ID'].count()

#6 Wie viele Häuser mit 3 oder 4 Bedrooms sind älter als 6 Jahre?
houses.loc[(houses.Bedrooms>=3) & (houses.Bedrooms<=4) & (houses.Age>=6), 'ID'].count()
houses.loc[((houses.Bedrooms==3) | (houses.Bedrooms==4)) & (houses.Age>=6), 'ID'].count()

#7 Erstellen Sie eine Häufigkeitsverteilung der Profite.
houses['Profit'].plot(kind='hist')

#8 Wie viele Agencies hat das Unternehmen?
houses['Agency'].unique()

#9 Zeigen Sie den mittleren Verkaufswert und dessen Standardabweichung pro Agency an.
pd.pivot_table(houses, index='Agency', values='Purchase_Price', aggfunc=['mean', 'std'])

#10 Stellen Sie die statistischen Zusammenhänge (Korrelationen) zwischen dem Purchase_Preis und den Variablen Size, Bedrooms und Age dar.
houses.loc[:, ['Age', 'Bedrooms', 'Size', 'Purchase_Price']].corr().loc[['Age', 'Bedrooms', 'Size'], 'Purchase_Price']

#11 In welcher Area steht das teuerste Haus und wie viele Zimmer hat es?
houses.loc[houses.Purchase_Price==houses.Purchase_Price.max(), ['Area', 'Bedrooms']]

#12 Create a forecast model for the house price based on a Linear Regression
x = houses.loc[:, ['Age', 'Size', 'Bedrooms']]
y = houses.Purchase_Price

from sklearn.linear_model import LinearRegression
forecastmodel = LinearRegression()
forecastmodel.fit(x, y)

forecasts = forecastmodel.predict(x)
from sklearn.metrics import r2_score
r2_score(y, forecasts)

forecastmodel.predict([[5, 2000, 3]])

#13 Create a forecast model based on a decision tree
from sklearn.tree import DecisionTreeRegressor
#forecastmodel = DecisionTreeRegressor(random_state=0)
forecastmodel = DecisionTreeRegressor(max_depth=3, random_state=0)
forecastmodel.fit(x, y)

from sklearn.tree import plot_tree
plot_tree(forecastmodel, feature_names=list(x), filled=True)

forecasts = forecastmodel.predict(x)
from sklearn.metrics import r2_score
r2_score(y, forecasts)

forecastmodel.predict([[5, 2000, 3]])

#14  Create a forecast model based on a Neural Network
from sklearn.neural_network import MLPRegressor
forecastmodel = MLPRegressor(solver='lbfgs', random_state=0)
forecastmodel.fit(x, y)

forecasts = forecastmodel.predict(x)
from sklearn.metrics import r2_score
r2_score(y, forecasts)

forecastmodel.predict([[5, 2000, 3]])

☕ Break — 10 minutes

Slide 80 — Integers and floating-point numbers

Python distinguishes integers from floating-point numbers and may display very large or very small floating-point values in scientific notation.

127

x = -127
x
type(x)

-236.4532
12398741634341798.132

x = 0.0000000000234234
x
type(x)

Slide 82 — Type conversion

Use this solution when reviewing conversions:

Value	Type	To Type	Using function	New value
`"3"`	`str`	`int`	`int("3")`	`3`
`"3.14"`	`str`	`float`	`float("3.14")`	`3.14`
`3`	`int`	`str`	`str(3)`	`"3"`
`3.14`	`float`	`str`	`str(3.14)`	`"3.14"`
`False`	`bool`	`str`	`str(False)`	`"False"`
`"True"`	`str`	`bool`	`bool("True")`	`True`
`17.75`	`float`	`int`	`int(17.75)`	`17`
`"3"`	`str`	`float`	`float("3")`	`3.0`

Slide 83 — Keyboard input and type conversion

input() returns a string. The final call uses int() to convert the second input explicitly.

name = input("What's your name? ")
print("Nice to meet you " + name + "!")

age = input("Your age? ")
print(
    "So, you are already "
    + age
    + " years old, "
    + name
    + "!"
)

type(age)

age = int(input("Your age? "))
type(age)

Slides 84–85 — Interactive formula programs

In-class exercise ../materials/session_07/CircleArea2.py

radius = float(input ("Input the radius of the circle : "))
area = 3.14159 * radius**2
print ('The area of the circle with radius', radius, 'is:', area)

In-class exercise ../materials/session_07/Currency.py

amount = float(input("Enter the amount in the original currency: "))
exrate = float(input("Enter the exchange rate: "))
value = amount * exrate
print("The exchange amount is:", value)

In-class exercise ../materials/session_07/Distance.py

lat1 = float(input("Enter the latitude of the first location: "))
long1 = float(input("Enter the longitude of the first location: "))
lat2 = float(input("Enter the latitude of the second location: "))
long2 = float(input("Enter the longitude of the second location: "))

# Example: https://www.gpskoordinaten.de/
# Frankfurt: lat=50.1109, long=8.6821
# Paris: lat=48.8534951, long=2.3483915
# Distance=445.55

distance = 69 * ((lat1-lat2)**2 + (long1-long2)**2)**0.5
print("The distance is:", distance)

In-class exercise ../materials/session_07/Fuel.py

kilometers = float(input("Enter your driven kilometers: "))
volume = float(input("Enter the quantity of petrol tanked : "))

consumption = volume*100/kilometers

print("Your car consumes", consumption, " liters per 100 km.")

In-class exercise ../materials/session_07/AnnuityLoan1.py

amount = float(input("Enter the amount: "))
interestrate = float(input("Enter the interest rate (in %): "))
interestrate = interestrate/100.0
duration = int(input("Enter the duration (in years): "))

annualpayment  = amount * (((1 + interestrate) ** duration * interestrate) /
                           ((1 + interestrate) ** duration - 1))

print("The annual payment is ", round(annualpayment,2))

Assign exercises 4, 5, and 6 as homework.

Slide 86 — Boolean values

Boolean literals, comparisons, and conversion with bool() all produce truth values.

12 > 11

x = True
x

x = bool(1)
x

answer = (2 * 3 == 6)
answer

Summary and announcements

TODO

Notes for improvement

Take notes on improvements and common questions during the session and add them to feedback.qmd afterwards.