Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save rutgerhofste/fd0c5e30b9e66693c4ea50564b342c4f to your computer and use it in GitHub Desktop.
Save rutgerhofste/fd0c5e30b9e66693c4ea50564b342c4f to your computer and use it in GitHub Desktop.
{
"cells": [
{
"cell_type": "code",
"execution_count": 1,
"metadata": {},
"outputs": [],
"source": [
"import pandas as pd\n",
"import numpy as np\n",
"from sqlalchemy import *\n",
"\n",
"DATABASE_ENDPOINT = \"aqueduct30v04.cgpnumwmfcqc.eu-central-1.rds.amazonaws.com\"\n",
"DATABASE_NAME = \"database01\"\n",
"TABLE_NAME = \"test01\"\n",
"\n",
"F = open(\"/.password\",\"r\")\n",
"password = F.read().splitlines()[0]\n",
"F.close()\n",
"\n",
"engine = create_engine(\"postgresql://rutgerhofste:{}@{}:5432/{}\".format(password,DATABASE_ENDPOINT,DATABASE_NAME))\n",
"connection = engine.connect()\n",
"\n",
"d1 = {'id' : [1, 2, 3],\n",
" 'foo' : [20, 40, 10]}\n",
"\n",
"d2 = {'id' : [1, 2, 4],\n",
" 'bar' : [21, 42, 13]}\n",
"\n",
"\n",
"df1 = pd.DataFrame(d1)\n",
"df2 = pd.DataFrame(d2)"
]
},
{
"cell_type": "code",
"execution_count": 2,
"metadata": {},
"outputs": [],
"source": [
"df_merged = df1.merge(df2,on=\"id\",how=\"outer\")"
]
},
{
"cell_type": "code",
"execution_count": 3,
"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>foo</th>\n",
" <th>id</th>\n",
" <th>bar</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>20.0</td>\n",
" <td>1</td>\n",
" <td>21.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>40.0</td>\n",
" <td>2</td>\n",
" <td>42.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>10.0</td>\n",
" <td>3</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>NaN</td>\n",
" <td>4</td>\n",
" <td>13.0</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" foo id bar\n",
"0 20.0 1 21.0\n",
"1 40.0 2 42.0\n",
"2 10.0 3 NaN\n",
"3 NaN 4 13.0"
]
},
"execution_count": 3,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df_merged"
]
},
{
"cell_type": "code",
"execution_count": 4,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"foo int64\n",
"id int64\n",
"dtype: object"
]
},
"execution_count": 4,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df1.dtypes"
]
},
{
"cell_type": "code",
"execution_count": 5,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"foo float64\n",
"id int64\n",
"bar float64\n",
"dtype: object"
]
},
"execution_count": 5,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df_merged.dtypes"
]
},
{
"cell_type": "code",
"execution_count": 6,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"#column types converted to float due to Nans"
]
},
{
"cell_type": "code",
"execution_count": 7,
"metadata": {},
"outputs": [],
"source": [
"# cannot store as integer\n",
"# df_merged[\"bar\"] = df_merged[\"bar\"].astype(np.int64)"
]
},
{
"cell_type": "code",
"execution_count": 10,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"df_merged.to_sql(\"test01\",engine,if_exists='replace', index=False,chunksize=100)"
]
},
{
"cell_type": "code",
"execution_count": 11,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"# Suggested by coldspeed\n",
"df_merged.astype(object).to_sql(\"test02\",engine,if_exists='replace', index=False,chunksize=100)"
]
},
{
"cell_type": "code",
"execution_count": 24,
"metadata": {},
"outputs": [],
"source": [
"# Suggested by Ami Tavory, \n",
"df_merged2 = df_merged.copy()\n",
"df_merged2[\"foo\"].fillna(-9999, inplace=True)\n",
"df_merged2[\"bar\"].fillna(-9999, inplace=True)\n"
]
},
{
"cell_type": "code",
"execution_count": 26,
"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>foo</th>\n",
" <th>id</th>\n",
" <th>bar</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>20.0</td>\n",
" <td>1</td>\n",
" <td>21.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>40.0</td>\n",
" <td>2</td>\n",
" <td>42.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>10.0</td>\n",
" <td>3</td>\n",
" <td>-9999.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>-9999.0</td>\n",
" <td>4</td>\n",
" <td>13.0</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" foo id bar\n",
"0 20.0 1 21.0\n",
"1 40.0 2 42.0\n",
"2 10.0 3 -9999.0\n",
"3 -9999.0 4 13.0"
]
},
"execution_count": 26,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df_merged2"
]
},
{
"cell_type": "code",
"execution_count": 31,
"metadata": {},
"outputs": [],
"source": [
"df_merged2[\"foo\"] = df_merged2[\"foo\"].astype(np.int64)\n",
"df_merged2[\"bar\"] = df_merged2[\"foo\"].astype(np.int64)"
]
},
{
"cell_type": "code",
"execution_count": 32,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"foo int64\n",
"id int64\n",
"bar int64\n",
"dtype: object"
]
},
"execution_count": 32,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df_merged2.dtypes"
]
},
{
"cell_type": "code",
"execution_count": 33,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"df_merged2.to_sql(\"test03\",engine,if_exists='replace', index=False,chunksize=100)"
]
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 35",
"language": "python",
"name": "python35"
},
"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.5.4"
}
},
"nbformat": 4,
"nbformat_minor": 2
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment