{
"cells": [
{
"cell_type": "code",
"execution_count": 1,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Päivitetty 2024-03-30 / Aki Taanila\n"
]
}
],
"source": [
"from datetime import datetime\n",
"print(f'Päivitetty {datetime.now().date()} / Aki Taanila')"
]
},
{
"cell_type": "code",
"execution_count": 2,
"metadata": {},
"outputs": [],
"source": [
"import pandas as pd"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Puuttuvat tai päällekkäiset aikaleimat ja havainnot\n",
"\n",
"Aikasarjojen analysoinnissa ja aikasarjaennustamisessa puuttuvat ja päällekkäiset havainnot aiheuttavat ongelmia.\n",
"\n",
"## Puuttuvat havainnot\n",
"\n",
"Tarkastelen esimerkkinä kuukauden ensimmäisten päivien muodostamaa aikasarjaa, josta puuttuu välistä kahden kuukauden (huhtikuu ja toukokuu) tiedot."
]
},
{
"cell_type": "code",
"execution_count": 3,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"
\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" date | \n",
" value | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" 2019-01-01 | \n",
" 2 | \n",
"
\n",
" \n",
" 1 | \n",
" 2019-02-01 | \n",
" 3 | \n",
"
\n",
" \n",
" 2 | \n",
" 2019-03-01 | \n",
" 4 | \n",
"
\n",
" \n",
" 3 | \n",
" 2019-06-01 | \n",
" 7 | \n",
"
\n",
" \n",
" 4 | \n",
" 2019-07-01 | \n",
" 8 | \n",
"
\n",
" \n",
" 5 | \n",
" 2019-08-01 | \n",
" 9 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" date value\n",
"0 2019-01-01 2\n",
"1 2019-02-01 3\n",
"2 2019-03-01 4\n",
"3 2019-06-01 7\n",
"4 2019-07-01 8\n",
"5 2019-08-01 9"
]
},
"execution_count": 3,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# Avaan datan:\n",
"df1 = pd.read_excel('https://taanila.fi/aika1.xlsx')\n",
"df1"
]
},
{
"cell_type": "code",
"execution_count": 4,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" value | \n",
"
\n",
" \n",
" date | \n",
" | \n",
"
\n",
" \n",
" \n",
" \n",
" 2019-01-01 | \n",
" 2 | \n",
"
\n",
" \n",
" 2019-02-01 | \n",
" 3 | \n",
"
\n",
" \n",
" 2019-03-01 | \n",
" 4 | \n",
"
\n",
" \n",
" 2019-06-01 | \n",
" 7 | \n",
"
\n",
" \n",
" 2019-07-01 | \n",
" 8 | \n",
"
\n",
" \n",
" 2019-08-01 | \n",
" 9 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" value\n",
"date \n",
"2019-01-01 2\n",
"2019-02-01 3\n",
"2019-03-01 4\n",
"2019-06-01 7\n",
"2019-07-01 8\n",
"2019-08-01 9"
]
},
"execution_count": 4,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# Siirrän aikaleimat indeksiin:\n",
"df1.index = pd.to_datetime(df1['date'])\n",
"df1 = df1.drop('date', axis=1)\n",
"df1"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"**resample()** lisää puuttuvat kuukaudet indeksiin. Aikafrekvenssi **'MS'** tarkoittaa kuukauden ensimmäisiä päivä (**M**onth**S**tart). Eri tilanteisiin sopivia aikafrekvenssejä löydät osoitteesta https://pandas.pydata.org/pandas-docs/stable/user_guide/timeseries.html#offset-aliases.\n",
"\n",
"**ffill()** täydentää puuttuvat arvot niitä edeltävällä arvolla."
]
},
{
"cell_type": "code",
"execution_count": 5,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" value | \n",
"
\n",
" \n",
" date | \n",
" | \n",
"
\n",
" \n",
" \n",
" \n",
" 2019-01-01 | \n",
" 2 | \n",
"
\n",
" \n",
" 2019-02-01 | \n",
" 3 | \n",
"
\n",
" \n",
" 2019-03-01 | \n",
" 4 | \n",
"
\n",
" \n",
" 2019-04-01 | \n",
" 4 | \n",
"
\n",
" \n",
" 2019-05-01 | \n",
" 4 | \n",
"
\n",
" \n",
" 2019-06-01 | \n",
" 7 | \n",
"
\n",
" \n",
" 2019-07-01 | \n",
" 8 | \n",
"
\n",
" \n",
" 2019-08-01 | \n",
" 9 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" value\n",
"date \n",
"2019-01-01 2\n",
"2019-02-01 3\n",
"2019-03-01 4\n",
"2019-04-01 4\n",
"2019-05-01 4\n",
"2019-06-01 7\n",
"2019-07-01 8\n",
"2019-08-01 9"
]
},
"execution_count": 5,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df2 = df1.resample('MS').ffill()\n",
"df2"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"**bfill()** täydentää puuttuvat arvot niitä seuraavalla arvolla."
]
},
{
"cell_type": "code",
"execution_count": 6,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" value | \n",
"
\n",
" \n",
" date | \n",
" | \n",
"
\n",
" \n",
" \n",
" \n",
" 2019-01-01 | \n",
" 2 | \n",
"
\n",
" \n",
" 2019-02-01 | \n",
" 3 | \n",
"
\n",
" \n",
" 2019-03-01 | \n",
" 4 | \n",
"
\n",
" \n",
" 2019-04-01 | \n",
" 7 | \n",
"
\n",
" \n",
" 2019-05-01 | \n",
" 7 | \n",
"
\n",
" \n",
" 2019-06-01 | \n",
" 7 | \n",
"
\n",
" \n",
" 2019-07-01 | \n",
" 8 | \n",
"
\n",
" \n",
" 2019-08-01 | \n",
" 9 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" value\n",
"date \n",
"2019-01-01 2\n",
"2019-02-01 3\n",
"2019-03-01 4\n",
"2019-04-01 7\n",
"2019-05-01 7\n",
"2019-06-01 7\n",
"2019-07-01 8\n",
"2019-08-01 9"
]
},
"execution_count": 6,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df3 = df1.resample('MS').bfill()\n",
"df3"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"**interpolate()** interpoloi puuttuvat arvot niitä edeltävän ja seuraavan arvon perusteella."
]
},
{
"cell_type": "code",
"execution_count": 7,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" value | \n",
"
\n",
" \n",
" date | \n",
" | \n",
"
\n",
" \n",
" \n",
" \n",
" 2019-01-01 | \n",
" 2.0 | \n",
"
\n",
" \n",
" 2019-02-01 | \n",
" 3.0 | \n",
"
\n",
" \n",
" 2019-03-01 | \n",
" 4.0 | \n",
"
\n",
" \n",
" 2019-04-01 | \n",
" 5.0 | \n",
"
\n",
" \n",
" 2019-05-01 | \n",
" 6.0 | \n",
"
\n",
" \n",
" 2019-06-01 | \n",
" 7.0 | \n",
"
\n",
" \n",
" 2019-07-01 | \n",
" 8.0 | \n",
"
\n",
" \n",
" 2019-08-01 | \n",
" 9.0 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" value\n",
"date \n",
"2019-01-01 2.0\n",
"2019-02-01 3.0\n",
"2019-03-01 4.0\n",
"2019-04-01 5.0\n",
"2019-05-01 6.0\n",
"2019-06-01 7.0\n",
"2019-07-01 8.0\n",
"2019-08-01 9.0"
]
},
"execution_count": 7,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df4 = df1.resample('MS').interpolate()\n",
"df4"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Päällekkäiset havainnot\n",
"\n",
"Tarkastelen seuraavaksi esimerkkiä päällekkäisistä havainnoista. Seuraavassa esimerkissä on puuttuvien kuukausien (huhtikuu ja toukokuu) lisäksi päällekkäinen havainto helmikuun kohdalla."
]
},
{
"cell_type": "code",
"execution_count": 8,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" date | \n",
" value | \n",
"
\n",
" \n",
" date | \n",
" | \n",
" | \n",
"
\n",
" \n",
" \n",
" \n",
" 2019-01-01 | \n",
" 2019-01-01 | \n",
" 2 | \n",
"
\n",
" \n",
" 2019-02-01 | \n",
" 2019-02-01 | \n",
" 3 | \n",
"
\n",
" \n",
" 2019-02-01 | \n",
" 2019-02-01 | \n",
" 5 | \n",
"
\n",
" \n",
" 2019-03-01 | \n",
" 2019-03-01 | \n",
" 4 | \n",
"
\n",
" \n",
" 2019-06-01 | \n",
" 2019-06-01 | \n",
" 7 | \n",
"
\n",
" \n",
" 2019-07-01 | \n",
" 2019-07-01 | \n",
" 8 | \n",
"
\n",
" \n",
" 2019-08-01 | \n",
" 2019-08-01 | \n",
" 9 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" date value\n",
"date \n",
"2019-01-01 2019-01-01 2\n",
"2019-02-01 2019-02-01 3\n",
"2019-02-01 2019-02-01 5\n",
"2019-03-01 2019-03-01 4\n",
"2019-06-01 2019-06-01 7\n",
"2019-07-01 2019-07-01 8\n",
"2019-08-01 2019-08-01 9"
]
},
"execution_count": 8,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df5 = pd.read_excel('https://taanila.fi/aika2.xlsx')\n",
"df5.index = pd.to_datetime(df5['date'])\n",
"df5.drop('date', axis=1)\n",
"df5"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"**resample()** hoitaa myös päällekkäiset aikaleimat. Seuraavassa korvaan päällekkäiset havainnot keskiarvollaan ja puuttuvat havainnot interpoloin."
]
},
{
"cell_type": "code",
"execution_count": 9,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" date | \n",
" value | \n",
"
\n",
" \n",
" date | \n",
" | \n",
" | \n",
"
\n",
" \n",
" \n",
" \n",
" 2019-01-01 | \n",
" 2019-01-01 00:00:00 | \n",
" 2.0 | \n",
"
\n",
" \n",
" 2019-02-01 | \n",
" 2019-02-01 00:00:00 | \n",
" 4.0 | \n",
"
\n",
" \n",
" 2019-03-01 | \n",
" 2019-03-01 00:00:00 | \n",
" 4.0 | \n",
"
\n",
" \n",
" 2019-04-01 | \n",
" 2019-03-31 16:00:00 | \n",
" 5.0 | \n",
"
\n",
" \n",
" 2019-05-01 | \n",
" 2019-05-01 08:00:00 | \n",
" 6.0 | \n",
"
\n",
" \n",
" 2019-06-01 | \n",
" 2019-06-01 00:00:00 | \n",
" 7.0 | \n",
"
\n",
" \n",
" 2019-07-01 | \n",
" 2019-07-01 00:00:00 | \n",
" 8.0 | \n",
"
\n",
" \n",
" 2019-08-01 | \n",
" 2019-08-01 00:00:00 | \n",
" 9.0 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" date value\n",
"date \n",
"2019-01-01 2019-01-01 00:00:00 2.0\n",
"2019-02-01 2019-02-01 00:00:00 4.0\n",
"2019-03-01 2019-03-01 00:00:00 4.0\n",
"2019-04-01 2019-03-31 16:00:00 5.0\n",
"2019-05-01 2019-05-01 08:00:00 6.0\n",
"2019-06-01 2019-06-01 00:00:00 7.0\n",
"2019-07-01 2019-07-01 00:00:00 8.0\n",
"2019-08-01 2019-08-01 00:00:00 9.0"
]
},
"execution_count": 9,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df6 = df5.resample('MS').mean().interpolate()\n",
"df6"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Lisätietoa\n",
"\n",
"**resample()** on monipuolinen toiminto. Lue lisää https://pandas.pydata.org/pandas-docs/dev/reference/api/pandas.DataFrame.resample.html"
]
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 3 (ipykernel)",
"language": "python",
"name": "python3"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 3
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython3",
"version": "3.12.2"
}
},
"nbformat": 4,
"nbformat_minor": 4
}