Skip to content

Instantly share code, notes, and snippets.

@Skyentific

Skyentific/Resample timeseries to fill dates.ipynb

Created October 3, 2018 09:09
Show Gist options
  • Save Skyentific/b6cd18a3296ee20eabd9858fca9fe385 to your computer and use it in GitHub Desktop.
Save Skyentific/b6cd18a3296ee20eabd9858fca9fe385 to your computer and use it in GitHub Desktop.
Download ZIP
Display the source blob
Display the rendered blob
Raw
Resample timeseries to fill dates.ipynb
{
"cells": [
{
"cell_type": "code",
"execution_count": 1,
"metadata": {},
"outputs": [],
"source": [
"import pandas as pd"
]
},
{
"cell_type": "code",
"execution_count": 9,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>Date</th>\n",
" <th>Store</th>\n",
" <th>Description</th>\n",
" <th>Category</th>\n",
" <th>Amount</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>2018-08-01</td>\n",
" <td>Portmans</td>\n",
" <td>Clotes</td>\n",
" <td>Clothing</td>\n",
" <td>130</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>2018-08-04</td>\n",
" <td>Sussan</td>\n",
" <td>Pants</td>\n",
" <td>Clothing</td>\n",
" <td>99</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>2018-08-05</td>\n",
" <td>Dan Murphys</td>\n",
" <td>Wine</td>\n",
" <td>Food and wine</td>\n",
" <td>37</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>2018-08-06</td>\n",
" <td>Coles</td>\n",
" <td>Groceries</td>\n",
" <td>Food and wine</td>\n",
" <td>82</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>2018-08-07</td>\n",
" <td>Mr Yummy noodles</td>\n",
" <td>Takeaway</td>\n",
" <td>Food and wine</td>\n",
" <td>14</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5</th>\n",
" <td>2018-08-10</td>\n",
" <td>Coles</td>\n",
" <td>Groceries</td>\n",
" <td>Food and wine</td>\n",
" <td>82</td>\n",
" </tr>\n",
" <tr>\n",
" <th>6</th>\n",
" <td>2018-08-11</td>\n",
" <td>Castros bar and bistro</td>\n",
" <td>Food and wine</td>\n",
" <td>Food and wine</td>\n",
" <td>75</td>\n",
" </tr>\n",
" <tr>\n",
" <th>7</th>\n",
" <td>2018-08-12</td>\n",
" <td>Google Play</td>\n",
" <td>Pretty Woman</td>\n",
" <td>Entertainment</td>\n",
" <td>7</td>\n",
" </tr>\n",
" <tr>\n",
" <th>8</th>\n",
" <td>2018-08-13</td>\n",
" <td>Resolution health club</td>\n",
" <td>Monthly membership</td>\n",
" <td>Health</td>\n",
" <td>32</td>\n",
" </tr>\n",
" <tr>\n",
" <th>9</th>\n",
" <td>2018-08-15</td>\n",
" <td>Coles</td>\n",
" <td>Groceries</td>\n",
" <td>Food and wine</td>\n",
" <td>82</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" Date Store Description Category \\\n",
"0 2018-08-01 Portmans Clotes Clothing \n",
"1 2018-08-04 Sussan Pants Clothing \n",
"2 2018-08-05 Dan Murphys Wine Food and wine \n",
"3 2018-08-06 Coles Groceries Food and wine \n",
"4 2018-08-07 Mr Yummy noodles Takeaway Food and wine \n",
"5 2018-08-10 Coles Groceries Food and wine \n",
"6 2018-08-11 Castros bar and bistro Food and wine Food and wine \n",
"7 2018-08-12 Google Play Pretty Woman Entertainment \n",
"8 2018-08-13 Resolution health club Monthly membership Health \n",
"9 2018-08-15 Coles Groceries Food and wine \n",
"\n",
" Amount \n",
"0 130 \n",
"1 99 \n",
"2 37 \n",
"3 82 \n",
"4 14 \n",
"5 82 \n",
"6 75 \n",
"7 7 \n",
"8 32 \n",
"9 82 "
]
},
"execution_count": 9,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df = pd.read_excel('/Users/sky/Dropbox/workspace/Jupyter/examples/Resample_data.xlsx')\n",
"df.head(10)"
]
},
{
"cell_type": "code",
"execution_count": 8,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th>Category</th>\n",
" <th>Clothing</th>\n",
" <th>Entertainment</th>\n",
" <th>Food and wine</th>\n",
" <th>Fuel and maintenance</th>\n",
" <th>Health</th>\n",
" </tr>\n",
" <tr>\n",
" <th>Date</th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>2018-08-01</th>\n",
" <td>130.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2018-08-04</th>\n",
" <td>99.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2018-08-05</th>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>37.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2018-08-06</th>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>82.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2018-08-07</th>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>14.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2018-08-10</th>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>82.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2018-08-11</th>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>75.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2018-08-12</th>\n",
" <td>0.0</td>\n",
" <td>7.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2018-08-13</th>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>32.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2018-08-15</th>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>82.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
"Category Clothing Entertainment Food and wine Fuel and maintenance \\\n",
"Date \n",
"2018-08-01 130.0 0.0 0.0 0.0 \n",
"2018-08-04 99.0 0.0 0.0 0.0 \n",
"2018-08-05 0.0 0.0 37.0 0.0 \n",
"2018-08-06 0.0 0.0 82.0 0.0 \n",
"2018-08-07 0.0 0.0 14.0 0.0 \n",
"2018-08-10 0.0 0.0 82.0 0.0 \n",
"2018-08-11 0.0 0.0 75.0 0.0 \n",
"2018-08-12 0.0 7.0 0.0 0.0 \n",
"2018-08-13 0.0 0.0 0.0 0.0 \n",
"2018-08-15 0.0 0.0 82.0 0.0 \n",
"\n",
"Category Health \n",
"Date \n",
"2018-08-01 0.0 \n",
"2018-08-04 0.0 \n",
"2018-08-05 0.0 \n",
"2018-08-06 0.0 \n",
"2018-08-07 0.0 \n",
"2018-08-10 0.0 \n",
"2018-08-11 0.0 \n",
"2018-08-12 0.0 \n",
"2018-08-13 32.0 \n",
"2018-08-15 0.0 "
]
},
"execution_count": 8,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"dfPiv = pd.pivot_table(df, values='Amount', columns='Category', index='Date').fillna(0)\n",
"dfPiv.head(10)"
]
},
{
"cell_type": "code",
"execution_count": 12,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th>Category</th>\n",
" <th>Clothing</th>\n",
" <th>Entertainment</th>\n",
" <th>Food and wine</th>\n",
" <th>Fuel and maintenance</th>\n",
" <th>Health</th>\n",
" </tr>\n",
" <tr>\n",
" <th>Date</th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>2018-08-01</th>\n",
" <td>130.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2018-08-02</th>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2018-08-03</th>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2018-08-04</th>\n",
" <td>99.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2018-08-05</th>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>37.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2018-08-06</th>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>82.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2018-08-07</th>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>14.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2018-08-08</th>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2018-08-09</th>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2018-08-10</th>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>82.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2018-08-11</th>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>75.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2018-08-12</th>\n",
" <td>0.0</td>\n",
" <td>7.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2018-08-13</th>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>32.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2018-08-14</th>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2018-08-15</th>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>82.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
"Category Clothing Entertainment Food and wine Fuel and maintenance \\\n",
"Date \n",
"2018-08-01 130.0 0.0 0.0 0.0 \n",
"2018-08-02 0.0 0.0 0.0 0.0 \n",
"2018-08-03 0.0 0.0 0.0 0.0 \n",
"2018-08-04 99.0 0.0 0.0 0.0 \n",
"2018-08-05 0.0 0.0 37.0 0.0 \n",
"2018-08-06 0.0 0.0 82.0 0.0 \n",
"2018-08-07 0.0 0.0 14.0 0.0 \n",
"2018-08-08 0.0 0.0 0.0 0.0 \n",
"2018-08-09 0.0 0.0 0.0 0.0 \n",
"2018-08-10 0.0 0.0 82.0 0.0 \n",
"2018-08-11 0.0 0.0 75.0 0.0 \n",
"2018-08-12 0.0 7.0 0.0 0.0 \n",
"2018-08-13 0.0 0.0 0.0 0.0 \n",
"2018-08-14 0.0 0.0 0.0 0.0 \n",
"2018-08-15 0.0 0.0 82.0 0.0 \n",
"\n",
"Category Health \n",
"Date \n",
"2018-08-01 0.0 \n",
"2018-08-02 0.0 \n",
"2018-08-03 0.0 \n",
"2018-08-04 0.0 \n",
"2018-08-05 0.0 \n",
"2018-08-06 0.0 \n",
"2018-08-07 0.0 \n",
"2018-08-08 0.0 \n",
"2018-08-09 0.0 \n",
"2018-08-10 0.0 \n",
"2018-08-11 0.0 \n",
"2018-08-12 0.0 \n",
"2018-08-13 32.0 \n",
"2018-08-14 0.0 \n",
"2018-08-15 0.0 "
]
},
"execution_count": 12,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"dfPiv = dfPiv.resample('d').sum()\n",
"dfPiv.head(15)"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 3",
"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.6.6"
}
},
"nbformat": 4,
"nbformat_minor": 2
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment