{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"#### New to Plotly?\n",
"Plotly's Python library is free and open source! [Get started](https://plotly.com/python/getting-started/) by downloading the client and [reading the primer](https://plotly.com/python/getting-started/).\n",
"
You can set up Plotly to work in [online](https://plotly.com/python/getting-started/#initialization-for-online-plotting) or [offline](https://plotly.com/python/getting-started/#initialization-for-offline-plotting) mode, or in [jupyter notebooks](https://plotly.com/python/getting-started/#start-plotting-online).\n",
"
We also have a quick-reference [cheatsheet](https://images.plot.ly/plotly-documentation/images/python_cheat_sheet.pdf) (new!) to help you get started!\n",
"#### Version Check \n",
"Plotly's python package is updated frequently. Run `pip install plotly --upgrade` to use the latest version."
]
},
{
"cell_type": "code",
"execution_count": 1,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"'2.4.1'"
]
},
"execution_count": 1,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"import plotly\n",
"plotly.__version__"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"#### Imports\n",
"This notebook uses the MySQL world database:http://dev.mysql.com/doc/index-other.html. Instructions for setting up the world database in MySQL are [here](https://dev.mysql.com/doc/world-setup/en/). This notebook was created for [this article in Modern Data](http://mod.plot.ly/graph-data-from-mysql-database-in-python/)"
]
},
{
"cell_type": "code",
"execution_count": 3,
"metadata": {},
"outputs": [],
"source": [
"import plotly.plotly as py\n",
"import plotly.graph_objs as go\n",
"\n",
"import MySQLdb\n",
"import pandas as pd"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"#### Connect to MySQL Database"
]
},
{
"cell_type": "code",
"execution_count": 5,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"\"(('Aruba', 'North America', 103000L, 78.4, 828.0), ('Afghanistan', 'Asia', 22720000L, 45.9, 5976.0), ('Angola', 'Africa', 12878000L, 38.3, 6648.0), ('Anguilla', 'North America', 8000L, 76.1, 63.2), ('Albania', 'Europe', 3401200L, 71.6, 3205.0), ('Andorra', 'Europe', 78000L, 83.5, 1630.0), ('Netherla\""
]
},
"execution_count": 5,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"conn = MySQLdb.connect(host=\"localhost\", user=\"root\", passwd=\"xxxx\", db=\"world\")\n",
"cursor = conn.cursor()\n",
"cursor.execute('select Name, Continent, Population, LifeExpectancy, GNP from Country');\n",
"\n",
"rows = cursor.fetchall()\n",
"str(rows)[0:300]"
]
},
{
"cell_type": "code",
"execution_count": 7,
"metadata": {},
"outputs": [],
"source": [
"df = pd.DataFrame( [[ij for ij in i] for i in rows] )\n",
"df.rename(columns={0: 'Name', 1: 'Continent', 2: 'Population', 3: 'LifeExpectancy', 4:'GNP'}, inplace=True);\n",
"df = df.sort_values(['LifeExpectancy'], ascending=[1]);"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Some country names cause serialization errors in early versions of Plotly's Python client. The code block below takes care of this."
]
},
{
"cell_type": "code",
"execution_count": 8,
"metadata": {},
"outputs": [
{
"name": "stderr",
"output_type": "stream",
"text": [
"C:\\Python27\\lib\\site-packages\\ipykernel_launcher.py:4: SettingWithCopyWarning:\n",
"\n",
"\n",
"A value is trying to be set on a copy of a slice from a DataFrame\n",
"\n",
"See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
"\n"
]
}
],
"source": [
"country_names = df['Name']\n",
"for i in range(len(country_names)):\n",
" try:\n",
" country_names[i] = str(country_names[i]).decode('utf-8')\n",
" except:\n",
" country_names[i] = 'Country name decode error'"
]
},
{
"cell_type": "code",
"execution_count": 10,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
""
],
"text/plain": [
""
]
},
"execution_count": 10,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"trace1 = go.Scatter(\n",
" x=df['GNP'],\n",
" y=df['LifeExpectancy'],\n",
" text=country_names,\n",
" mode='markers'\n",
")\n",
"layout = go.Layout(\n",
" title='Life expectancy vs GNP from MySQL world database',\n",
" xaxis=dict( type='log', title='GNP' ),\n",
" yaxis=dict( title='Life expectancy' )\n",
")\n",
"data = [trace1]\n",
"fig = go.Figure(data=data, layout=layout)\n",
"py.iplot(fig, filename='world GNP vs life expectancy')"
]
},
{
"cell_type": "code",
"execution_count": 11,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
""
],
"text/plain": [
""
]
},
"execution_count": 11,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# (!) Set 'size' values to be proportional to rendered area,\n",
"# instead of diameter. This makes the range of bubble sizes smaller\n",
"sizemode='area' \n",
"\n",
"# (!) Set a reference for 'size' values (i.e. a population-to-pixel scaling).\n",
"# Here the max bubble area will be on the order of 100 pixels\n",
"sizeref=df['Population'].max()/1e2**2\n",
"\n",
"colors = {\n",
" 'Asia':\"rgb(255,65,54)\", \n",
" 'Europe':\"rgb(133,20,75)\",\n",
" 'Africa':\"rgb(0,116,217)\",\n",
" 'North America':\"rgb(255,133,27)\",\n",
" 'South America':\"rgb(23,190,207)\",\n",
" 'Antarctica':\"rgb(61,153,112)\",\n",
" 'Oceania':\"rgb(255,220,0)\",\n",
"}\n",
"\n",
"# Define a hover-text generating function (returns a list of strings)\n",
"def make_text(X):\n",
" return 'Country: %s\\\n",
"
Life Expectancy: %s years\\\n",
"
Population: %s million'\\\n",
" % (X['Name'], X['LifeExpectancy'], X['Population']/1e6) \n",
"\n",
"# Define a trace-generating function (returns a Scatter object)\n",
"def make_trace(X, continent, sizes, color): \n",
" return go.Scatter(\n",
" x=X['GNP'], # GDP on the x-xaxis\n",
" y=X['LifeExpectancy'], # life Exp on th y-axis\n",
" name=continent, # label continent names on hover\n",
" mode='markers', # (!) point markers only on this plot\n",
" text=X.apply(make_text, axis=1).tolist(),\n",
" marker= dict(\n",
" color=color, # marker color\n",
" size=sizes, # (!) marker sizes (sizes is a list)\n",
" sizeref=sizeref, # link sizeref\n",
" sizemode=sizemode, # link sizemode\n",
" opacity=0.6, # (!) partly transparent markers\n",
" line= dict(width=3,color=\"white\") # marker borders\n",
" )\n",
" )\n",
"\n",
"# Initialize data object \n",
"data = []\n",
"\n",
"# Group data frame by continent sub-dataframe (named X), \n",
"# make one trace object per continent and append to data object\n",
"for continent, X in df.groupby('Continent'):\n",
" \n",
" sizes = X['Population'] # get population array \n",
" color = colors[continent] # get bubble color\n",
" \n",
" data.append(\n",
" make_trace(X, continent, sizes, color) # append trace to data object\n",
" ) \n",
"\n",
" # Set plot and axis titles\n",
"title = \"Life expectancy vs GNP from MySQL world database (bubble chart)\"\n",
"x_title = \"Gross National Product\"\n",
"y_title = \"Life Expectancy [in years]\"\n",
"\n",
"# Define a dictionary of axis style options\n",
"axis_style = dict( \n",
" type='log',\n",
" zeroline=False, # remove thick zero line\n",
" gridcolor='#FFFFFF', # white grid lines\n",
" ticks='outside', # draw ticks outside axes \n",
" ticklen=8, # tick length\n",
" tickwidth=1.5 # and width\n",
")\n",
"\n",
"# Make layout object\n",
"layout = go.Layout(\n",
" title=title, # set plot title\n",
" plot_bgcolor='#EFECEA', # set plot color to grey\n",
" hovermode=\"closest\",\n",
" xaxis=dict(\n",
" axis_style, # add axis style dictionary\n",
" title=x_title, # x-axis title\n",
" range=[2.0,7.2], # log of min and max x limits\n",
" ),\n",
" yaxis=dict(\n",
" axis_style, # add axis style dictionary\n",
" title=y_title, # y-axis title\n",
" )\n",
")\n",
"\n",
"# Make Figure object\n",
"fig = go.Figure(data=data, layout=layout)\n",
"\n",
"# (@) Send to Plotly and show in notebook\n",
"py.iplot(fig, filename='s3_life-gdp')"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"#### References\n",
"See https://plotly.com/python/getting-started/ for more information about Plotly's Python API! "
]
},
{
"cell_type": "code",
"execution_count": 12,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
""
],
"text/plain": [
""
]
},
"metadata": {},
"output_type": "display_data"
},
{
"data": {
"text/html": [
""
],
"text/plain": [
""
]
},
"metadata": {},
"output_type": "display_data"
},
{
"name": "stdout",
"output_type": "stream",
"text": [
"Collecting git+https://github.com/plotly/publisher.git\n",
" Cloning https://github.com/plotly/publisher.git to c:\\users\\brand\\appdata\\local\\temp\\pip-req-build-xvur5k83\n",
"Installing collected packages: publisher\n",
" Found existing installation: publisher 0.11\n",
" Uninstalling publisher-0.11:\n",
" Successfully uninstalled publisher-0.11\n",
" Running setup.py install for publisher: started\n",
" Running setup.py install for publisher: finished with status 'done'\n",
"Successfully installed publisher-0.11\n"
]
},
{
"name": "stderr",
"output_type": "stream",
"text": [
"C:\\Python27\\lib\\site-packages\\IPython\\nbconvert.py:13: ShimWarning:\n",
"\n",
"The `IPython.nbconvert` package has been deprecated since IPython 4.0. You should import from nbconvert instead.\n",
"\n",
"C:\\Python27\\lib\\site-packages\\publisher\\publisher.py:53: UserWarning:\n",
"\n",
"Did you \"Save\" this notebook before running this command? Remember to save, always save.\n",
"\n"
]
}
],
"source": [
"from IPython.display import display, HTML\n",
"\n",
"display(HTML(''))\n",
"display(HTML(''))\n",
"\n",
"! pip install git+https://github.com/plotly/publisher.git --upgrade\n",
"import publisher\n",
"publisher.publish('mysql-ipython-notebook.ipynb', 'python/graph-data-from-mysql-database-in-python/', \n",
" 'Plot Data from MySQL', 'How to graph data from a MySQL database with Python.',\n",
" title='Plot Data from a MySQL Database | Plotly', has_thumbnail='false', \n",
" page_type='example_index', display_as='databases', order=1, language='python',\n",
" uses_plotly_offline=True)"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": []
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 2",
"language": "python",
"name": "python2"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 2
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython2",
"version": "2.7.14"
}
},
"nbformat": 4,
"nbformat_minor": 1
}