Notes S-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.

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.

import pandas as pd

data = pd.read_csv('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 ../materials/session_11/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 ../materials/session_11/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 ../materials/session_11/Stocks.py

import pandas as pd
import requests

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("Stocks.csv")
stocks.loc[len(stocks)] = [date_, ibm, dt_bank, xiaomi, nel_asa]
stocks.to_csv("Stocks.csv", index=False)

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

☕ Break — 10 minutes

Slide 152 — JSON data retrieval

In-class exercise ../materials/session_11/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 ../materials/session_11/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

TODO

Notes for improvement

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