Session 11/Python 8: Pandas automation and web APIs

TODO: prepare (split) 5_REST.py

Notes:

Alpha Vantage keys: PR shares them later.
Klausur: Pandas-Teil ist “Pure pandas” (keine Kombination mit Python)
JSON: relevance (apps, …)
API examples: depending on remaining time.

Keep API examples non-executable in rendered notes and select them according to the remaining class time.

flowchart

  subgraph AFTER_BREAK["<b>After break: JSON and APIs</b>"]
    direction TB
    B1["<b>💻 Exercise: JSON stock data</b> (30 min)"]
    B2["<b>💻 Exercise: Cryptocurrency data</b> (30 min)"]
    B3["<b>Optional weather API demo</b> (22 min)"]
    B4["<b>API review and exam scope</b> (8 min)"]
    B1 --> B2 --> B3 --> B4
  end

  subgraph BEFORE_BREAK["<b>Before break: Loops, functions, and REST</b>"]
    direction TB
    A1["<b>Moving averages with loops</b> (20 min) · Stockprice.csv"]
    A2["<b>Semivariance functions</b> (15 min)"]
    A3["<b>Demo: REST and JSON</b> (15 min) · 5_REST.py"]
    A4["<b>💻 Exercise: Mercedes-Benz stock data</b> (18 min)"]
    A5["<b>💻 Exercise: Stock-price collection</b> (20 min) · Stocks.csv"]
    A6["<b>API troubleshooting</b> (2 min)"]
    A1 --> A2 --> A3 --> A4 --> A5 --> A6
  end

Slide 137 — Calculate a moving average with a loop

The loop calculates the mean of the preceding ten observations and writes it into the MA10 column. The first ten positions remain empty because no complete ten-observation window is available.

In-class demo ../materials/data/Stockprice.csv

import pandas as pd

data = pd.read_csv("data/Stockprice.csv")
data.head()

data['MA10'] = None
n = 10

for i in range(n, len(data)):
    data.iloc[i, 2] = data.iloc[(i - n):i, 1].mean()

data.head()
data.head(15)

data.plot(
    y=['price', 'MA10'],
    use_index=True,
)

Slide 138 — Define a semivariance function

Semivariance includes only observations below the mean. While migrating this fragment, the condition was corrected to compare the observed value (rather than its row position) with the average, and the accumulator was renamed so it does not shadow Python’s built-in sum().

def semivariance(values):
    average = values.mean()
    squared_deviation_sum = 0
    count = 0

    for i in range(len(values)):
        if values.iloc[i] < average:
            squared_deviation_sum += (
                values.iloc[i] - average
            ) ** 2
            count += 1

    semivar = squared_deviation_sum / count
    return semivar


semivariance(data['price'])

General teaching notes

In-class demo 5_REST.py

import pandas as pd
stockdata = pd.read_csv('https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol=IBM&apikey=demo&datatype=csv')
stockdata.head()


import requests
r = requests.get('https://www.alphavantage.co/query?function=GLOBAL_QUOTE&symbol=IBM&apikey=demo')
data = r.json()
data

price = data['Global Quote']['05. price']
price

price = float(data['Global Quote']['05. price'])
price

data = data['Global Quote']
price = float(data['05. price'])
price


import requests
r = requests.get('https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol=IBM&apikey=demo')
data = r.json()
data
dfdata = pd.DataFrame(data['Time Series (Daily)'].values(), index=data['Time Series (Daily)'].keys())

for col in list(dfdata):
    dfdata[col] = pd.to_numeric(dfdata[col])

dfdata = dfdata[::-1]

dfdata.plot(y='4. close', use_index=True)


import requests
import pandas as pd
r = requests.get('https://api.coingecko.com/api/v3/coins/bitcoin/market_chart?vs_currency=eur&days=30')
data = r.json()
df = pd.DataFrame(data['prices'], columns=['time','price'])
df.head(2)

from datetime import datetime
df['time'] = pd.to_datetime(df['time'], unit='ms').dt.date
df
df.plot(x='time', y='price', rot=75)


import requests
r = requests.get('https://api.open-meteo.com/v1/forecast?latitude=50.12&'
	'longitude=8.68&daily=temperature_2m_max,temperature_2m_min&'
	'timezone=Europe%2FBerlin')
data = r.json()
data

Explain why JSON is relevant to applications.
Choose API examples according to the remaining time.
Alpha Vantage keys will be shared later by PR.

API execution

Do not execute API scripts while rendering these notes. They may require network access, API keys, or current external responses.

Exam relevance

The pandas portion of the exam is “pure pandas”; it does not combine pandas with the Python topics taught here.

Slide 143 — Stock-market API

In-class exercise MercedesBenz1.py

import pandas as pd

stockdata = pd.read_csv('https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol=MBG.DEX&outputsize=full&apikey=YOURKEY&datatype=csv')
stockdata = stockdata[::-1]
stockdata.plot(x='timestamp', y='close', rot=75)
stockdata.loc[:, 'return'] = stockdata.loc[:, 'close'].pct_change()
stockdata.plot(x='timestamp', y='return', rot=75)

Slide 148 — Stock-price collection

In-class exercise Stocks.py

import pandas as pd
import requests
import os

r = requests.get('https://www.alphavantage.co/query?function=GLOBAL_QUOTE&symbol=IBM&apikey=YOURKEY')
data = r.json()
#data
ibm = data["Global Quote"]["05. price"]
date_ = data["Global Quote"]["07. latest trading day"]

r = requests.get('https://www.alphavantage.co/query?function=GLOBAL_QUOTE&symbol=DBK.DE&apikey=YOURKEY')
data = r.json()
#data
dt_bank = data["Global Quote"]["05. price"]

r = requests.get('https://www.alphavantage.co/query?function=GLOBAL_QUOTE&symbol=XIACF&apikey=YOURKEY')
data = r.json()
#data
xiaomi = data["Global Quote"]["05. price"]

r = requests.get('https://www.alphavantage.co/query?function=GLOBAL_QUOTE&symbol=NLLSF&apikey=YOURKEY')
data = r.json()
#data
nel_asa = data["Global Quote"]["05. price"]

stocks = pd.read_csv("data/Stocks.csv")
stocks.loc[len(stocks)] = [date_, ibm, dt_bank, xiaomi, nel_asa]
os.makedirs("outputs", exist_ok=True)
stocks.to_csv("outputs/Stocks.csv", index=False)

../materials/data/Stocks.csv

Break — 10 minutes

Slide 152 — JSON data retrieval

In-class exercise MercedesBenz2.py

import pandas as pd
import requests

url = 'https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol=MBG.DEX&outputsize=full&YOURKEY'
r = requests.get(url)
data = r.json()
stockdata = pd.DataFrame(data['Time Series (Daily)'].values(), index=data['Time Series (Daily)'].keys())
for col in list(stockdata):
	stockdata[col] = pd.to_numeric(stockdata[col])
stockdata = stockdata[::-1]
stockdata.plot(y='4. close', use_index=True, rot=75)
stockdata.loc[:, '6. return'] = stockdata.loc[:, '4. close'].pct_change()
stockdata.plot(y='6. return', use_index=True, rot=75)

Slide 155 — Cryptocurrency API analysis

In-class exercise Cryptocurrencies.py

import requests
import pandas as pd

r = requests.get('https://api.coingecko.com/api/v3/coins/bitcoin/market_chart?vs_currency=eur&days=30')
data = r.json()
btc = pd.DataFrame(data['prices'], columns=['time','price'])
btc.head(3)

r = requests.get('https://api.coingecko.com/api/v3/coins/ethereum/market_chart?vs_currency=eur&days=30')
data = r.json()
eth = pd.DataFrame(data['prices'], columns=['time','price'])
eth.head(3)

compare = pd.DataFrame(btc.time)
compare['BTC'] = btc.price
compare['ETH'] = eth.price

compare['BTC_returns'] = compare['BTC'].pct_change()
compare['ETH_returns'] = compare['ETH'].pct_change()

compare['time'] = compare['time'].astype('object')
from datetime import datetime
for i in range(len(compare)):
    compare.iloc[i, 0] = datetime.fromtimestamp(int(compare.iloc[i, 0])/1000).date()

compare['BTC_returns'].var()
compare['ETH_returns'].var()

compare.loc[:,['BTC_returns', 'ETH_returns']].corr()
compare['BTC_returns'].corr(compare['ETH_returns'])

compare.plot(x='time', y=['BTC_returns', 'ETH_returns'], rot=75)

Slide 159 — Weather API

Configure and process the weather API example described in the exercise table.
No source filename is provided in the existing notes or material inventory.

Summary and announcements

Exam:

Important to use standard Python functions that were covered in class (no f-strings!)

Notes for improvement

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