{ "cells": [ { "cell_type": "code", "execution_count": 15, "metadata": { "collapsed": true }, "outputs": [], "source": [ "import os\n", "import pandas as pd\n", "\n", "from plotly.offline import download_plotlyjs, init_notebook_mode, plot, iplot\n", "import plotly.offline as py\n", "import plotly.graph_objs as go\n", "\n", "from pandas_gbq import read_gbq" ] }, { "cell_type": "code", "execution_count": 2, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/html": [ "" ], "text/vnd.plotly.v1+html": [ "" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "# enable ploty jupyter inline\n", "init_notebook_mode(connected=True)" ] }, { "cell_type": "code", "execution_count": 3, "metadata": { "collapsed": false }, "outputs": [], "source": [ "# standard SQL table id syntax\n", "table_id = \"`{}.{}.{}`\".format(\n", " os.environ['BQ_PROJECT_ID'],\n", " os.environ['BQ_DATASET_ID'],\n", " os.environ['BQ_TABLE_ID']\n", ")\n", "\n", "def read_data(query):\n", " df = read_gbq(query,\n", " project_id=os.environ['BQ_PROJECT_ID'],\n", " private_key=os.environ['BQ_PRIVATE_KEY'],\n", " dialect=\"standard\")\n", " return df" ] }, { "cell_type": "code", "execution_count": 4, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Requesting query... ok.\n", "Job ID: job_a4UyJGwvGPthf7PWJG1Ee_4LA3sM\n", "Query running...\n", "Query done.\n", "Processed: 41.6 KB\n", "Standard price: $0.00 USD\n", "\n", "Retrieving results...\n", "Got 10 rows.\n", "\n", "Total time taken 1.94 s.\n", "Finished at 2017-08-21 11:53:28.\n" ] }, { "data": { "text/html": [ "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
task_idstory_idstory_createdatinterval_begininterval_endstory_point_beginstory_point_deltacomment_count_begincomment_count_deltastory_rank_beginstory_rank_end
0S1ATRx_d-150602372017-08-20 21:00:532017-08-21 06:33:42.4542017-08-21 06:33:57.4566103202323
1S1ATRx_d-150547522017-08-19 18:13:242017-08-21 06:33:42.4542017-08-21 06:33:57.456151040055
2S1ATRx_d-150580902017-08-20 12:12:552017-08-21 06:33:42.4542017-08-21 06:33:57.4567601901515
3S1ATRx_d-150604882017-08-20 22:09:402017-08-21 06:33:42.4542017-08-21 06:33:57.4566704402626
4S1ATRx_d-150587232017-08-20 15:16:172017-08-21 06:33:42.4542017-08-21 06:33:57.45622402401717
5rJYnOWuu-150609982017-08-21 00:08:192017-08-21 07:16:00.6622017-08-21 07:16:15.6633101088
6rJYnOWuu-150583512017-08-20 13:35:092017-08-21 07:16:00.6622017-08-21 07:16:15.663520028001516
7rJYnOWuu-150573712017-08-20 07:33:412017-08-21 07:16:00.6622017-08-21 07:16:15.663242012302222
8rJYnOWuu-150547522017-08-19 18:13:242017-08-21 07:16:00.6622017-08-21 07:16:15.663152040055
9rJYnOWuu-150613562017-08-21 01:46:202017-08-21 07:16:00.6622017-08-21 07:16:15.66371001614
\n", "
" ], "text/plain": [ " task_id story_id story_createdat interval_begin \\\n", "0 S1ATRx_d- 15060237 2017-08-20 21:00:53 2017-08-21 06:33:42.454 \n", "1 S1ATRx_d- 15054752 2017-08-19 18:13:24 2017-08-21 06:33:42.454 \n", "2 S1ATRx_d- 15058090 2017-08-20 12:12:55 2017-08-21 06:33:42.454 \n", "3 S1ATRx_d- 15060488 2017-08-20 22:09:40 2017-08-21 06:33:42.454 \n", "4 S1ATRx_d- 15058723 2017-08-20 15:16:17 2017-08-21 06:33:42.454 \n", "5 rJYnOWuu- 15060998 2017-08-21 00:08:19 2017-08-21 07:16:00.662 \n", "6 rJYnOWuu- 15058351 2017-08-20 13:35:09 2017-08-21 07:16:00.662 \n", "7 rJYnOWuu- 15057371 2017-08-20 07:33:41 2017-08-21 07:16:00.662 \n", "8 rJYnOWuu- 15054752 2017-08-19 18:13:24 2017-08-21 07:16:00.662 \n", "9 rJYnOWuu- 15061356 2017-08-21 01:46:20 2017-08-21 07:16:00.662 \n", "\n", " interval_end story_point_begin story_point_delta \\\n", "0 2017-08-21 06:33:57.456 61 0 \n", "1 2017-08-21 06:33:57.456 151 0 \n", "2 2017-08-21 06:33:57.456 76 0 \n", "3 2017-08-21 06:33:57.456 67 0 \n", "4 2017-08-21 06:33:57.456 224 0 \n", "5 2017-08-21 07:16:15.663 31 0 \n", "6 2017-08-21 07:16:15.663 520 0 \n", "7 2017-08-21 07:16:15.663 242 0 \n", "8 2017-08-21 07:16:15.663 152 0 \n", "9 2017-08-21 07:16:15.663 7 1 \n", "\n", " comment_count_begin comment_count_delta story_rank_begin story_rank_end \n", "0 32 0 23 23 \n", "1 40 0 5 5 \n", "2 19 0 15 15 \n", "3 44 0 26 26 \n", "4 24 0 17 17 \n", "5 1 0 8 8 \n", "6 280 0 15 16 \n", "7 123 0 22 22 \n", "8 40 0 5 5 \n", "9 0 0 16 14 " ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# test bq connection, read 10 rows\n", "read_data(\"SELECT * from {} LIMIT 10\".format(table_id))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Load Data From Simple SQL Query\n", "\n", "Find all entries in the last day." ] }, { "cell_type": "code", "execution_count": 55, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Requesting query... ok.\n", "Job ID: job_RBoiCT1EMJJJ_iyJV5AYiwWieMKY\n", "Query running...\n", "Query done.\n", "Processed: 46.7 KB\n", "Standard price: $0.00 USD\n", "\n", "Retrieving results...\n", "Got 526 rows.\n", "\n", "Total time taken 2.89 s.\n", "Finished at 2017-08-21 12:50:48.\n" ] } ], "source": [ "query_last_24_hour = \"\"\"\n", "SELECT\n", " *\n", "FROM\n", " {}\n", "WHERE\n", " `interval_begin` >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)\n", "ORDER BY\n", " `interval_begin`;\n", "\"\"\".format(table_id)\n", "\n", "df = read_data(query_last_24_hour)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Plot total story points on HN frontpage and total comment counts over time." ] }, { "cell_type": "code", "execution_count": 57, "metadata": { "collapsed": false }, "outputs": [ { "data": { "application/vnd.plotly.v1+json": { "data": [ { "name": "Story Points", "type": "scatter", "x": [ "2017-08-21 01:53:47.771000", "2017-08-21 03:47:09.760000", "2017-08-21 04:23:05.747000", "2017-08-21 05:13:23.647000", "2017-08-21 06:33:42.454000", "2017-08-21 07:16:00.662000", "2017-08-21 08:16:00.805000", "2017-08-21 09:16:02.058000", "2017-08-21 10:16:01.554000", "2017-08-21 11:16:01.406000", "2017-08-21 12:16:00.363000", "2017-08-21 13:16:01.882000", "2017-08-21 14:16:02.426000", "2017-08-21 15:16:02.150000", "2017-08-21 16:16:02.700000", "2017-08-21 17:16:02.301000", "2017-08-21 18:16:02.907000", "2017-08-21 19:16:02.589000" ], "y": [ 2300, 2900, 3206, 3225, 3575, 3956, 3918, 3067, 2964, 2900, 3342, 2306, 2208, 2284, 2271, 2661, 2361, 2624 ] }, { "name": "Comment Count", "type": "scatter", "x": [ "2017-08-21 01:53:47.771000", "2017-08-21 03:47:09.760000", "2017-08-21 04:23:05.747000", "2017-08-21 05:13:23.647000", "2017-08-21 06:33:42.454000", "2017-08-21 07:16:00.662000", "2017-08-21 08:16:00.805000", "2017-08-21 09:16:02.058000", "2017-08-21 10:16:01.554000", "2017-08-21 11:16:01.406000", "2017-08-21 12:16:00.363000", "2017-08-21 13:16:01.882000", "2017-08-21 14:16:02.426000", "2017-08-21 15:16:02.150000", "2017-08-21 16:16:02.700000", "2017-08-21 17:16:02.301000", "2017-08-21 18:16:02.907000", "2017-08-21 19:16:02.589000" ], "y": [ 982, 1206, 1410, 1484, 1650, 1830, 1785, 1424, 1544, 1448, 1541, 1075, 774, 924, 997, 1148, 914, 1013 ], "yaxis": "y2" } ], "layout": { "title": "Frontpage Story Points and Comment Count over time", "yaxis": { "title": "Story Points" }, "yaxis2": { "overlaying": "y", "side": "right", "tickfont": { "color": "rgb(148, 103, 189)" }, "title": "Comment Count", "titlefont": { "color": "rgb(148, 103, 189)" } } } }, "text/html": [ "
" ], "text/vnd.plotly.v1+html": [ "
" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "x=total['story_point_begin'].sum().index\n", "\n", "traces = [\n", " go.Scatter(\n", " x=x,\n", " y=total['story_point_begin'].sum(),\n", " name=\"Story Points\"\n", " ),\n", " go.Scatter(\n", " x=x,\n", " y=total['comment_count_begin'].sum(),\n", " yaxis=\"y2\",\n", " name=\"Comment Count\"\n", " )\n", "]\n", "layout = go.Layout(\n", " title='Frontpage Story Points and Comment Count over time',\n", " yaxis=dict(\n", " title='Story Points'\n", " ),\n", " yaxis2=dict(\n", " title='Comment Count',\n", " titlefont=dict(\n", " color='rgb(148, 103, 189)'\n", " ),\n", " tickfont=dict(\n", " color='rgb(148, 103, 189)'\n", " ),\n", " overlaying='y',\n", " side='right'\n", " )\n", ")\n", "fig = go.Figure(data=traces, layout=layout)\n", "py.iplot(fig)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Plot story points deltas per sample interval (15 seconds) over time. Unfortunately 15 seconds was too short most entries observes 0 deltas." ] }, { "cell_type": "code", "execution_count": 56, "metadata": { "collapsed": false }, "outputs": [ { "data": { "application/vnd.plotly.v1+json": { "data": [ { "name": "Story Points Delta", "type": "scatter", "x": [ "2017-08-21 01:53:47.771000", "2017-08-21 03:47:09.760000", "2017-08-21 04:23:05.747000", "2017-08-21 05:13:23.647000", "2017-08-21 06:33:42.454000", "2017-08-21 07:16:00.662000", "2017-08-21 08:16:00.805000", "2017-08-21 09:16:02.058000", "2017-08-21 10:16:01.554000", "2017-08-21 11:16:01.406000", "2017-08-21 12:16:00.363000", "2017-08-21 13:16:01.882000", "2017-08-21 14:16:02.426000", "2017-08-21 15:16:02.150000", "2017-08-21 16:16:02.700000", "2017-08-21 17:16:02.301000", "2017-08-21 18:16:02.907000", "2017-08-21 19:16:02.589000" ], "y": [ 1, 0, 0, 2, 0, 2, 4, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 ] }, { "name": "Comment Count Delta", "type": "scatter", "x": [ "2017-08-21 01:53:47.771000", "2017-08-21 03:47:09.760000", "2017-08-21 04:23:05.747000", "2017-08-21 05:13:23.647000", "2017-08-21 06:33:42.454000", "2017-08-21 07:16:00.662000", "2017-08-21 08:16:00.805000", "2017-08-21 09:16:02.058000", "2017-08-21 10:16:01.554000", "2017-08-21 11:16:01.406000", "2017-08-21 12:16:00.363000", "2017-08-21 13:16:01.882000", "2017-08-21 14:16:02.426000", "2017-08-21 15:16:02.150000", "2017-08-21 16:16:02.700000", "2017-08-21 17:16:02.301000", "2017-08-21 18:16:02.907000", "2017-08-21 19:16:02.589000" ], "y": [ 2, 0, 0, 1, 0, 1, 3, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 ], "yaxis": "y2" } ], "layout": { "title": "HN Activity per 15 Seconds", "yaxis": { "title": "Story Points" }, "yaxis2": { "overlaying": "y", "side": "right", "tickfont": { "color": "rgb(148, 103, 189)" }, "title": "Comment Count", "titlefont": { "color": "rgb(148, 103, 189)" } } } }, "text/html": [ "
" ], "text/vnd.plotly.v1+html": [ "
" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "total = df.groupby(\"interval_begin\")\n", "x=total['story_point_delta'].sum().index\n", "\n", "traces = [\n", " go.Scatter(\n", " x=x,\n", " y=total['story_point_delta'].sum(),\n", " name=\"Story Points Delta\"\n", " ),\n", " go.Scatter(\n", " x=x,\n", " y=total['comment_count_delta'].sum(),\n", " yaxis=\"y2\",\n", " name=\"Comment Count Delta\"\n", " )\n", "]\n", "layout = go.Layout(\n", " title='HN Activity per 15 Seconds',\n", " yaxis=dict(\n", " title='Story Points'\n", " ),\n", " yaxis2=dict(\n", " title='Comment Count',\n", " titlefont=dict(\n", " color='rgb(148, 103, 189)'\n", " ),\n", " tickfont=dict(\n", " color='rgb(148, 103, 189)'\n", " ),\n", " overlaying='y',\n", " side='right'\n", " )\n", ")\n", "fig = go.Figure(data=traces, layout=layout)\n", "py.iplot(fig)" ] }, { "cell_type": "code", "execution_count": 65, "metadata": { "collapsed": false }, "outputs": [ { "data": { "application/vnd.plotly.v1+json": { "data": [ { "name": "Story Point Rank=1", "type": "scatter", "x": [ "2017-08-21 01:53:47.771000", "2017-08-21 03:47:09.760000", "2017-08-21 04:23:05.747000", "2017-08-21 05:13:23.647000", "2017-08-21 06:33:42.454000", "2017-08-21 07:16:00.662000", "2017-08-21 08:16:00.805000", "2017-08-21 09:16:02.058000", "2017-08-21 10:16:01.554000", "2017-08-21 11:16:01.406000", "2017-08-21 12:16:00.363000", "2017-08-21 13:16:01.882000", "2017-08-21 14:16:02.426000", "2017-08-21 15:16:02.150000", "2017-08-21 16:16:02.700000", "2017-08-21 17:16:02.301000", "2017-08-21 18:16:02.907000", "2017-08-21 19:16:02.589000" ], "y": [ 152, 192, 22, 73, 82, 112, 155, 181, 47, 25, 59, 108, 163, 51, 59, 125, 175, 219 ] }, { "name": "Story Point Rank=5", "type": "scatter", "x": [ "2017-08-21 01:53:47.771000", "2017-08-21 03:47:09.760000", "2017-08-21 04:23:05.747000", "2017-08-21 05:13:23.647000", "2017-08-21 06:33:42.454000", "2017-08-21 07:16:00.662000", "2017-08-21 08:16:00.805000", "2017-08-21 09:16:02.058000", "2017-08-21 10:16:01.554000", "2017-08-21 11:16:01.406000", "2017-08-21 12:16:00.363000", "2017-08-21 13:16:01.882000", "2017-08-21 14:16:02.426000", "2017-08-21 15:16:02.150000", "2017-08-21 16:16:02.700000", "2017-08-21 17:16:02.301000", "2017-08-21 18:16:02.907000", "2017-08-21 19:16:02.589000" ], "y": [ 78, 19, 209, 177, 151, 152, 110, 18, 212, 38, 29, 235, 20, 27, 14, 86, 95, 41 ] }, { "name": "Story Point Rank=10", "type": "scatter", "x": [ "2017-08-21 01:53:47.771000", "2017-08-21 03:47:09.760000", "2017-08-21 04:23:05.747000", "2017-08-21 05:13:23.647000", "2017-08-21 06:33:42.454000", "2017-08-21 07:16:00.662000", "2017-08-21 08:16:00.805000", "2017-08-21 09:16:02.058000", "2017-08-21 10:16:01.554000", "2017-08-21 11:16:01.406000", "2017-08-21 12:16:00.363000", "2017-08-21 14:16:02.426000", "2017-08-21 15:16:02.150000", "2017-08-21 16:16:02.700000", "2017-08-21 17:16:02.301000", "2017-08-21 18:16:02.907000", "2017-08-21 19:16:02.589000" ], "y": [ 3, 173, 140, 117, 174, 40, 203, 113, 9, 18, 44, 10, 13, 84, 61, 27, 25 ] }, { "name": "Story Point Rank=25", "type": "scatter", "x": [ "2017-08-21 01:53:47.771000", "2017-08-21 03:47:09.760000", "2017-08-21 04:23:05.747000", "2017-08-21 05:13:23.647000", "2017-08-21 06:33:42.454000", "2017-08-21 07:16:00.662000", "2017-08-21 08:16:00.805000", "2017-08-21 09:16:02.058000", "2017-08-21 10:16:01.554000", "2017-08-21 11:16:01.406000", "2017-08-21 12:16:00.363000", "2017-08-21 13:16:01.882000", "2017-08-21 14:16:02.426000", "2017-08-21 15:16:02.150000", "2017-08-21 16:16:02.700000", "2017-08-21 17:16:02.301000", "2017-08-21 18:16:02.907000", "2017-08-21 19:16:02.589000" ], "y": [ 4, 60, 57, 32, 236, 315, 248, 535, 168, 16, 5, 9, 25, 38, 19, 22, 9, 39 ] } ], "layout": { "title": "Story Points by Rank over time" } }, "text/html": [ "
" ], "text/vnd.plotly.v1+html": [ "
" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "def make_trace(rank):\n", " df_rank = df.query(\"story_rank_begin == {}\".format(rank))\n", " return go.Scatter(\n", " x=df_rank['interval_begin'],\n", " y=df_rank['story_point_begin'],\n", " name=\"Story Point Rank={}\".format(rank)\n", " )\n", "traces = [make_trace(r) for r in [1, 5, 10, 25]]\n", "layout = go.Layout(title=\"Story Points by Rank over time\")\n", "fig = go.Figure(data=traces, layout=layout)\n", "py.iplot(fig)" ] }, { "cell_type": "code", "execution_count": 108, "metadata": { "collapsed": false }, "outputs": [ { "data": { "application/vnd.plotly.v1+json": { "data": [ { "marker": { "color": [ 30, 8, 23, 27, 24, 28, 13, 19, 7, 29, 18, 9, 5, 14, 1, 21, 17, 25, 6, 16, 3, 2, 15, 4, 26, 20, 10, 22, 11, 20, 14, 13, 29, 6, 11, 18, 10, 24, 27, 30, 15, 21, 2, 3, 7, 17, 26, 4, 1, 8, 16, 12, 5, 28, 19, 25, 9, 22, 12, 6, 25, 2, 24, 19, 1, 5, 28, 29, 30, 8, 20, 16, 7, 3, 27, 23, 22, 17, 21, 11, 15, 14, 18, 10, 13, 9, 4, 26, 22, 23, 27, 8, 14, 18, 28, 13, 19, 20, 15, 1, 11, 17, 4, 9, 24, 2, 25, 10, 30, 16, 7, 12, 21, 3, 6, 5, 13, 29, 4, 24, 30, 18, 19, 2, 11, 27, 6, 8, 21, 3, 10, 22, 17, 26, 15, 5, 23, 9, 25, 14, 16, 7, 20, 28, 1, 21, 22, 10, 27, 6, 8, 15, 16, 5, 23, 29, 26, 11, 28, 9, 25, 1, 18, 20, 13, 12, 24, 2, 19, 7, 3, 30, 17, 4, 15, 9, 20, 11, 17, 1, 8, 10, 3, 2, 12, 25, 6, 23, 21, 24, 26, 28, 13, 4, 27, 7, 5, 22, 29, 16, 14, 30, 18, 16, 30, 26, 4, 5, 25, 7, 1, 3, 2, 15, 22, 24, 6, 10, 18, 21, 14, 11, 9, 28, 20, 29, 13, 8, 17, 19, 12, 23, 13, 27, 4, 16, 1, 19, 11, 14, 15, 12, 30, 29, 24, 3, 5, 23, 20, 25, 9, 22, 6, 7, 21, 2, 28, 10, 17, 8, 18, 26, 13, 27, 5, 14, 15, 12, 16, 30, 3, 9, 17, 23, 2, 19, 26, 10, 29, 8, 20, 28, 25, 4, 21, 24, 1, 6, 22, 11, 7, 18, 27, 28, 18, 20, 7, 6, 15, 9, 13, 25, 23, 11, 26, 21, 24, 3, 12, 4, 22, 10, 30, 1, 16, 8, 14, 19, 29, 2, 17, 5, 3, 25, 28, 23, 24, 9, 6, 30, 18, 2, 8, 21, 1, 13, 15, 27, 14, 22, 4, 19, 11, 5, 12, 29, 20, 7, 26, 16, 17, 28, 22, 6, 26, 7, 29, 10, 23, 15, 25, 27, 13, 1, 17, 21, 19, 9, 11, 18, 24, 14, 3, 8, 5, 20, 4, 12, 30, 2, 25, 26, 15, 29, 19, 13, 20, 30, 27, 3, 17, 28, 5, 22, 1, 2, 11, 21, 23, 8, 12, 4, 14, 16, 7, 6, 18, 10, 9, 15, 10, 4, 3, 28, 12, 13, 25, 27, 8, 14, 2, 1, 7, 26, 24, 19, 17, 20, 30, 6, 18, 11, 9, 22, 5, 16, 23, 21, 24, 17, 10, 2, 16, 12, 8, 3, 26, 15, 23, 9, 18, 4, 5, 11, 21, 27, 14, 30, 29, 20, 7, 28, 1, 19, 13, 6, 25, 22, 29, 18, 8, 15, 11, 6, 21, 27, 23, 10, 25, 22, 14, 17, 20, 19, 7, 16, 2, 13, 4, 28, 5, 24, 30, 1, 9, 26, 3, 29, 30, 13, 22, 25, 10, 2, 12, 19, 15, 11, 5, 23, 9, 27, 17, 7, 18, 8, 14, 3, 26, 16, 24, 4, 6, 28, 1, 21 ] }, "mode": "markers", "type": "scatter", "x": [ 784.61285, 140.04618333333335, 402.71285, 292.91285, 622.91285, 224.1295166666667, 404.77951666666667, 820.8795166666666, 343.09618333333333, 191.56285, 750.3961833333334, 1900.3961833333333, 186.1295166666667, 1563.6461833333333, 369.5795166666667, 1577.6961833333332, 736.5461833333334, 17.096183333333332, 642.2461833333333, 72.81285, 738.6461833333334, 105.47951666666667, 581.9461833333334, 637.51285, 755.76285, 771.2961833333334, 56.12951666666667, 45.62951666666667, 1657.0628499999998, 884.6626666666667, 934.246, 1677.0126666666665, 736.2793333333334, 755.6126666666667, 456.4626666666666, 863.7626666666667, 1770.4293333333333, 337.496, 516.0793333333334, 1213.4793333333332, 849.9126666666667, 1691.0626666666665, 253.41266666666667, 852.0126666666667, 2013.7626666666665, 432.5793333333333, 406.2793333333333, 750.8793333333334, 482.94599999999997, 518.146, 186.17933333333335, 695.3126666666667, 218.846, 897.9793333333333, 84.81266666666667, 869.1293333333334, 299.496, 41.912666666666674, 1806.36245, 254.77911666666665, 442.21245, 518.8791166666666, 552.0124500000001, 899.6957833333333, 55.612449999999995, 786.81245, 772.2124500000001, 571.3791166666667, 1542.91245, 2049.6957833333336, 920.5957833333334, 468.51245, 791.5457833333334, 289.3457833333333, 1249.41245, 373.42911666666663, 905.06245, 970.1791166666667, 885.8457833333334, 120.74578333333334, 1712.9457833333333, 731.2457833333334, 255.07911666666666, 335.42911666666663, 492.3957833333333, 554.0791166666667, 887.9457833333333, 203.16078333333334, 1856.6607833333333, 1763.2441166666665, 621.6774499999999, 604.37745, 43.06078333333333, 938.2441166666666, 1593.2107833333332, 136.46078333333332, 492.51078333333334, 423.72745000000003, 305.37745, 105.91078333333333, 518.8107833333333, 171.04411666666667, 837.1107833333333, 385.72745000000003, 1299.7107833333332, 569.1774499999999, 822.5107833333333, 542.6941166666667, 346.0441166666667, 1020.47745, 2099.9941166666667, 781.5441166666666, 602.3107833333332, 339.64411666666666, 305.07745, 841.8441166666667, 861.8575666666667, 1673.5242333333333, 419.9575666666667, 251.35756666666666, 701.9909, 922.1575666666666, 426.3575666666667, 649.4909, 123.37423333333332, 902.8242333333333, 684.6908999999999, 599.1242333333333, 287.37423333333334, 186.22423333333333, 466.0409, 1936.9742333333334, 917.4242333333333, 504.0409, 1100.7909, 2180.307566666667, 572.8242333333333, 623.0075666666667, 1380.0242333333333, 385.6909, 1018.5575666666666, 385.39090000000004, 682.6242333333333, 1843.5575666666666, 216.77423333333334, 1979.2776999999999, 1422.3277, 165.67770000000002, 546.3443666666666, 726.9943666666666, 427.69436666666667, 1060.8610333333334, 329.6777, 2222.6110333333336, 615.1276999999999, 293.66103333333336, 744.2943666666666, 508.34436666666664, 1353.0110333333332, 665.3110333333333, 1715.8277, 259.0777, 964.4610333333333, 724.9277, 427.9943666666667, 904.1610333333333, 1885.8610333333334, 691.7943666666666, 468.66103333333336, 641.4277, 228.5277, 1410.9443666666666, 959.7276999999999, 462.26103333333333, 487.99675, 225.68008333333333, 1024.4634166666667, 701.4300833333333, 528.6634166666666, 319.08008333333333, 487.69675, 568.34675, 135.58008333333333, 751.79675, 725.3134166666666, 1482.3300833333333, 786.99675, 2039.2800833333333, 2282.6134166666666, 964.1634166666666, 675.1300833333333, 804.29675, 226.29675, 522.2634166666667, 1775.8300833333333, 288.5300833333333, 150.91341666666668, 784.9300833333333, 606.34675, 1120.8634166666666, 389.68008333333336, 1945.8634166666666, 1019.7300833333334, 785.3343, 2342.6342999999997, 195.6009666666667, 811.8176333333333, 121.65096666666666, 1180.8843000000002, 83.2343, 379.1009666666667, 170.26763333333335, 65.4843, 889.6676333333332, 588.6843, 761.4509666666667, 210.9343, 847.0176333333333, 197.23430000000002, 1097.0176333333334, 628.3676333333333, 547.7176333333333, 582.2842999999999, 1084.4842999999998, 286.31763333333333, 1079.7509666666667, 348.5509666666667, 163.23430000000002, 548.0176333333333, 777.1676333333332, 844.9509666666667, 854.5009666666666, 257.2259, 1240.8759, 223.2259, 346.30923333333334, 125.4759, 688.3592333333332, 642.2759, 607.7092333333333, 5002.9759, 907.0092333333333, 372.7259, 1144.4759000000001, 914.4925666666666, 230.25923333333333, 270.9259, 845.3258999999999, 837.1592333333333, 648.6759, 172.84256666666667, 821.4425666666666, 871.8092333333333, 181.64256666666668, 608.0092333333333, 439.09256666666664, 1157.0092333333334, 884.4925666666666, 949.6592333333333, 143.2259, 904.9425666666666, 408.5425666666667, 667.7067666666667, 974.4901000000001, 944.4901000000001, 406.30676666666665, 931.8067666666667, 702.2734333333334, 798.8234333333334, 967.0067666666667, 499.0901, 330.9234333333333, 668.0067666666667, 897.1567666666667, 185.47343333333336, 748.3567666666667, 1217.0067666666666, 5062.973433333334, 1300.8734333333334, 203.22343333333336, 500.4567666666666, 708.6734333333334, 432.72343333333333, 290.25676666666664, 881.4401, 905.3234333333334, 39.52343333333333, 232.8401, 283.22343333333333, 317.22343333333333, 241.64010000000002, 1009.6567666666667, 1026.9893833333333, 528.5227166666667, 991.7893833333333, 388.08938333333333, 390.90605, 301.6227166666667, 1069.6393833333334, 5122.95605, 466.2893833333334, 560.4393833333332, 492.70605, 762.25605, 1360.8560499999999, 808.3393833333333, 768.6560499999999, 559.0727166666667, 377.20605, 1004.4727166666667, 1276.9893833333333, 292.8227166666667, 1259.7227166666667, 99.50605, 1034.4727166666667, 1031.90605, 727.6893833333332, 858.8060499999999, 1264.45605, 245.45604999999998, 727.9893833333333, 263.20605, 305.4813666666667, 620.4647, 868.3647, 788.0147, 1051.8147, 361.64803333333333, 45.731366666666666, 828.6813666666666, 822.2813666666666, 28.281366666666667, 323.2313666666667, 1129.6647, 159.53136666666666, 5182.981366666666, 292.39803333333333, 374.58136666666667, 39.9147, 787.7147, 1064.4980333333333, 448.1147, 1091.9313666666667, 619.0980333333333, 526.3147, 918.8313666666667, 1094.4980333333333, 450.9313666666667, 1337.0147, 16.4147, 437.2313666666667, 848.0237666666667, 586.3237666666666, 1124.5071, 882.2904333333333, 145.17376666666667, 847.7237666666666, 169.69043333333332, 497.2404333333333, 21.8071, 978.8404333333333, 1189.6737666666668, 383.2404333333333, 219.5404333333333, 35.073766666666664, 1037.0237666666667, 508.12376666666665, 352.4071, 421.6571, 5242.990433333333, 680.4737666666666, 1151.9404333333334, 105.74043333333334, 679.1071, 88.29043333333334, 1152.2404333333334, 365.4904333333333, 510.9404333333333, 1111.8237666666669, 93.14043333333333, 557.2358333333334, 81.8025, 443.23583333333335, 1038.8358333333333, 5302.985833333334, 481.65250000000003, 570.9358333333333, 907.7191666666666, 908.0191666666667, 165.73583333333332, 1212.2358333333332, 1249.6691666666666, 148.28583333333333, 646.3191666666667, 153.13583333333332, 205.16916666666665, 35.535833333333336, 739.1025000000001, 740.4691666666666, 412.40250000000003, 472.8525, 279.53583333333336, 1097.0191666666665, 1211.9358333333332, 1184.5024999999998, 425.48583333333335, 568.1191666666667, 754.5025, 229.68583333333333, 814.5116666666667, 472.4116666666667, 213.145, 265.17833333333334, 630.9449999999999, 21.978333333333335, 208.29500000000002, 1157.0283333333332, 5362.995, 1244.5116666666665, 289.695, 225.745, 74.41166666666666, 95.545, 1272.245, 66.51166666666667, 583.245, 2243.911666666667, 1122.4783333333332, 706.3283333333333, 70.945, 541.6616666666666, 485.495, 339.545, 1271.945, 77.42833333333333, 532.8616666666667, 628.1283333333333, 503.245, 41.93835, 592.8550166666666, 349.68835, 81.97168333333335, 874.5050166666666, 399.53835, 155.53834999999998, 137.42168333333333, 688.1216833333333, 268.28835, 563.23835, 532.4050166666667, 1182.4716833333334, 325.1716833333333, 285.73834999999997, 1304.5050166666667, 601.6550166666667, 5422.98835, 2303.9050166666666, 690.93835, 126.50501666666668, 643.23835, 130.93835, 1331.9383500000001, 134.40501666666668, 1201.4050166666668, 545.48835, 273.13835, 1217.0216833333334, 496.12168333333335, 623.2484499999999, 652.8651166666666, 1364.5151166666667, 517.54845, 459.54845, 409.69845, 703.2484499999999, 11.615116666666667, 1242.4817833333334, 2363.9151166666666, 556.1317833333334, 190.94844999999998, 605.4984499999999, 328.29845, 140.59844999999999, 83.68178333333334, 215.54844999999997, 710.4817833333333, 141.9817833333333, 934.5151166666667, 385.1817833333333, 1277.0317833333334, 333.14844999999997, 661.6651166666667, 197.43178333333333, 194.41511666666665, 592.4151166666667, 748.1317833333334, 345.74845, 1302.4764833333334, 683.24315, 469.69315, 665.49315, 388.29315, 200.59315, 254.40981666666667, 393.14315, 994.5098166666667, 3385.1764833333336, 1139.64315, 25.49315, 652.4098166666666, 71.60981666666666, 721.6598166666666, 2423.9098166666668, 770.4764833333334, 577.54315, 405.74315, 1424.509816666667, 49.42648333333333, 763.24315, 275.54315, 712.8598166666667, 74.94315, 445.17648333333335, 1337.0264833333335, 201.97648333333333, 519.54315 ], "y": [ 19, 0, 54, 24, 2, 22, 9, 14, 75, 82, 24, 33, 107, 67, 50, 9, 20, 2, 18, 3, 246, 1, 48, 15, 6, 7, 0, 1, 24, 11, 19, 79, 3, 21, 97, 24, 25, 29, 58, 114, 21, 9, 8, 260, 37, 7, 31, 23, 61, 14, 18, 49, 1, 20, 4, 6, 148, 9, 26, 1, 31, 72, 58, 24, 3, 24, 3, 7, 165, 38, 11, 9, 22, 8, 115, 32, 6, 19, 21, 40, 82, 51, 0, 160, 100, 15, 267, 6, 28, 88, 10, 15, 0, 267, 165, 0, 32, 38, 2, 40, 10, 71, 24, 178, 118, 75, 3, 107, 1, 19, 39, 54, 59, 9, 1, 25, 53, 165, 10, 93, 13, 26, 13, 80, 2, 3, 16, 17, 0, 89, 191, 30, 24, 44, 19, 40, 32, 113, 123, 3, 277, 1, 61, 92, 20, 31, 123, 2, 48, 19, 1, 280, 0, 40, 33, 105, 14, 199, 46, 118, 166, 34, 26, 65, 4, 56, 94, 82, 21, 24, 108, 55, 26, 10, 6, 5, 26, 28, 33, 55, 1, 218, 2, 84, 127, 125, 20, 32, 40, 57, 33, 14, 2, 11, 166, 129, 37, 70, 48, 290, 2, 98, 26, 133, 40, 9, 87, 2, 292, 0, 83, 1, 8, 0, 51, 31, 103, 22, 1, 3, 232, 1, 12, 26, 2, 27, 146, 3, 6, 26, 74, 3, 1, 294, 27, 4, 18, 243, 12, 1, 0, 25, 2, 26, 5, 23, 163, 136, 31, 65, 3, 35, 88, 3, 8, 87, 3, 0, 0, 0, 76, 165, 1, 7, 7, 6, 90, 12, 1, 26, 88, 221, 8, 34, 26, 253, 3, 1, 297, 0, 0, 78, 3, 31, 40, 139, 2, 20, 48, 1, 4, 1, 28, 184, 91, 0, 249, 4, 2, 5, 9, 0, 5, 12, 298, 269, 88, 96, 1, 20, 3, 48, 27, 9, 16, 0, 1, 7, 27, 31, 10, 1, 38, 1, 286, 11, 92, 4, 0, 111, 12, 1, 1, 2, 16, 11, 0, 1, 7, 1, 37, 0, 1, 101, 11, 9, 29, 288, 3, 0, 1, 16, 13, 48, 12, 1, 2, 0, 2, 21, 12, 3, 1, 24, 0, 0, 1, 7, 4, 13, 2, 2, 16, 103, 3, 12, 47, 317, 92, 0, 2, 35, 1, 16, 14, 6, 367, 2, 17, 19, 29, 3, 5, 15, 8, 9, 2, 107, 11, 15, 68, 39, 3, 3, 63, 56, 1, 0, 8, 1, 21, 16, 14, 404, 0, 7, 5, 15, 77, 13, 27, 4, 15, 42, 0, 107, 0, 2, 22, 10, 7, 59, 46, 5, 4, 72, 1, 1, 3, 76, 20, 17, 2, 48, 18, 26, 1, 7, 1, 24, 3, 20, 31, 84, 8, 16, 0, 434, 6, 128, 58, 6, 23, 0, 60, 20, 7, 1, 1, 79, 92, 5, 48, 31, 148, 0, 8, 0, 2, 97, 61, 8, 0, 12, 29, 5, 52, 3, 25, 9, 22, 10, 41, 44, 48, 2, 32, 8, 1, 42, 62, 10, 2, 55, 23, 4, 0, 4, 13, 63, 26, 11, 3, 25, 15, 35, 103, 34, 164, 40, 89, 22, 30, 10, 71, 48 ] } ], "layout": { "title": "Comment Count vs. Story Age", "xaxis": { "title": "Story Age (in minutes)" }, "yaxis": { "title": "Comment Count" } } }, "text/html": [ "
" ], "text/vnd.plotly.v1+html": [ "
" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "def timedf(x, y):\n", " # in seconds\n", " diff = (x.astype(\"int64\") - y.astype(\"int64\")) / 1000000000\n", " # in minutes\n", " return diff / 60\n", "df['story_age_begin'] = timedf(df['interval_begin'], df['story_createdat'])\n", "\n", "traces = [\n", " go.Scatter(\n", " y=df['comment_count_begin'],\n", " x=df['story_age_begin'],\n", " mode=\"markers\",\n", " marker=dict(\n", " color=df['story_rank_begin']\n", " )\n", " )\n", "]\n", "layout = go.Layout(\n", " title=\"Comment Count vs. Story Age\",\n", " xaxis=dict(\n", " title='Story Age (in minutes)'\n", " ),\n", " yaxis=dict(\n", " title='Comment Count'\n", " ),\n", ")\n", "fig = go.Figure(data=traces, layout=layout)\n", "py.iplot(fig)" ] }, { "cell_type": "code", "execution_count": 115, "metadata": { "collapsed": false }, "outputs": [ { "data": { "application/vnd.plotly.v1+json": { "data": [ { "mode": "markers", "name": "Rank=1", "type": "scatter", "x": [ 152, 192, 22, 73, 82, 112, 155, 181, 47, 25, 59, 108, 163, 51, 59, 125, 175, 219 ], "y": [ 50, 61, 3, 40, 20, 34, 55, 83, 18, 2, 9, 16, 24, 8, 4, 23, 44, 71 ] }, { "mode": "markers", "name": "Rank=2", "type": "scatter", "x": [ 13, 37, 202, 212, 229, 234, 247, 24, 196, 84, 103, 9, 28, 70, 79, 99, 165, 208 ], "y": [ 1, 8, 72, 75, 80, 82, 84, 8, 87, 26, 31, 1, 0, 9, 27, 17, 52, 55 ] }, { "mode": "markers", "name": "Rank=3", "type": "scatter", "x": [ 419, 462, 41, 54, 109, 118, 33, 18, 41, 213, 222, 129, 24, 51, 97, 44, 94, 57 ], "y": [ 246, 260, 8, 9, 89, 108, 2, 1, 23, 88, 96, 38, 16, 19, 14, 26, 32, 34 ] }, { "mode": "markers", "name": "Rank=4", "type": "scatter", "x": [ 192, 206, 477, 215, 62, 66, 70, 258, 28, 59, 62, 88, 149, 208, 70, 118, 134, 29 ], "y": [ 15, 23, 267, 24, 10, 10, 11, 87, 27, 31, 20, 37, 47, 39, 16, 20, 25, 22 ] }, { "mode": "markers", "name": "Rank=5", "type": "scatter", "x": [ 78, 19, 209, 177, 151, 152, 110, 18, 212, 38, 29, 235, 20, 27, 14, 86, 95, 41 ], "y": [ 107, 1, 24, 25, 40, 40, 37, 2, 163, 7, 1, 101, 3, 5, 4, 31, 22, 13 ] }, { "mode": "markers", "name": "Rank=6", "type": "scatter", "x": [ 152, 168, 20, 27, 82, 89, 102, 164, 267, 28, 39, 4, 128, 166, 11, 84, 79, 156 ], "y": [ 18, 21, 1, 1, 16, 19, 20, 103, 88, 20, 4, 0, 48, 56, 10, 20, 31, 30 ] }, { "mode": "markers", "name": "Rank=7", "type": "scatter", "x": [ 92, 131, 169, 142, 30, 86, 129, 5, 26, 33, 293, 344, 22, 158, 49, 52, 83, 32 ], "y": [ 75, 37, 22, 39, 1, 24, 129, 0, 3, 4, 249, 288, 1, 63, 15, 58, 29, 25 ] }, { "mode": "markers", "name": "Rank=8", "type": "scatter", "x": [ 12, 62, 135, 70, 72, 31, 33, 10, 10, 21, 13, 36, 254, 59, 187, 67, 220, 102 ], "y": [ 0, 14, 38, 15, 17, 1, 1, 3, 0, 0, 0, 1, 103, 15, 77, 18, 92, 35 ] }, { "mode": "markers", "name": "Rank=9", "type": "scatter", "x": [ 111, 130, 63, 156, 126, 131, 53, 77, 11, 256, 23, 44, 38, 25, 236, 96, 110, 44 ], "y": [ 33, 148, 15, 178, 113, 118, 5, 12, 3, 221, 5, 4, 7, 8, 46, 24, 48, 26 ] }, { "mode": "markers", "name": "Rank=10", "type": "scatter", "x": [ 3, 173, 140, 117, 174, 40, 203, 113, 9, 18, 44, 10, 13, 84, 61, 27, 25 ], "y": [ 0, 25, 160, 107, 191, 2, 218, 22, 0, 1, 48, 0, 0, 21, 20, 0, 2 ] } ], "layout": { "title": "Comment Count vs. Story Points (by rank)", "xaxis": { "title": "Story Points" }, "yaxis": { "title": "Comment Count" } } }, "text/html": [ "
" ], "text/vnd.plotly.v1+html": [ "
" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "def make_trace(rank):\n", " df_rank = df.query(\"story_rank_begin == {}\".format(rank))\n", " return go.Scatter(\n", " x=df_rank['story_point_begin'],\n", " y=df_rank['comment_count_begin'],\n", " name=\"Rank={}\".format(rank),\n", " mode=\"markers\"\n", " )\n", "traces = [make_trace(r) for r in range(1, 11)]\n", "layout = go.Layout(\n", " title=\"Comment Count vs. Story Points (by rank)\",\n", " xaxis=dict(\n", " title='Story Points'\n", " ),\n", " yaxis=dict(\n", " title='Comment Count'\n", " ),\n", ")\n", "fig = go.Figure(data=traces, layout=layout)\n", "py.iplot(fig)" ] } ], "metadata": { "anaconda-cloud": {}, "kernelspec": { "display_name": "Python [default]", "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.12" } }, "nbformat": 4, "nbformat_minor": 1 }