{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Which files are the most popular by downloads * size?\n",
    "====\n",
    "\n",
    "Query most popular files.\n",
    "\n",
    "```\n",
    "SELECT * FROM (\n",
    "  SELECT url, count(*) as downloads FROM `the-psf.pypi.file_downloads`  \n",
    "  WHERE DATE(timestamp) = \"2020-04-16\" GROUP by url\n",
    ") ORDER BY downloads DESC\n",
    "LIMIT 1000\n",
    "```\n",
    "\n",
    "Download query as json. Attach Content-Length to each file.\n",
    "\n",
    "```\n",
    "prefix = 'https://files.pythonhosted.org'\n",
    "for item in data: \n",
    "    url = item['url'] \n",
    "    content = session.head(prefix + url) \n",
    "    length = content.headers['Content-Length'] \n",
    "    item['length'] = int(length)\n",
    "    item['downloads'] = int(item['downloads'])\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "data = json.load(open('pypipopular.json'))\n",
    "for item in data:\n",
    "    item['aggregate'] = item['length'] * item['downloads']\n",
    "data.sort(key=lambda x: -x['downloads'])\n",
    "for i, item in enumerate(data): item['rank'] = i+1 # ranked by number of downloads\n",
    "data.sort(key=lambda x: -x['aggregate']) # sorted by downloads * file size"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "rows = []\n",
    "for item in data[:128]:\n",
    "    rows.append((item['url'].split('/')[-1], \"{:.0f}\".format(item['aggregate']/2**30), item['rank']))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table><tr><th>file</th><th>GiB/day</th><th>popularity</th></tr><tr><td>pyspark-2.4.5.tar.gz</td><td>15345</td><td>369</td></tr>\n",
       "<tr><td>tensorflow-2.1.0-cp36-cp36m-manylinux2010_x86_64.whl</td><td>15083</td><td>643</td></tr>\n",
       "<tr><td>tensorflow-2.1.0-cp37-cp37m-manylinux2010_x86_64.whl</td><td>9552</td><td>905</td></tr>\n",
       "<tr><td>tensorflow-2.0.0-cp27-cp27mu-manylinux2010_x86_64.whl</td><td>8004</td><td>298</td></tr>\n",
       "<tr><td>botocore-1.15.39-py2.py3-none-any.whl</td><td>7769</td><td>12</td></tr>\n",
       "<tr><td>numpy-1.18.2-cp36-cp36m-manylinux1_x86_64.whl</td><td>7001</td><td>60</td></tr>\n",
       "<tr><td>scipy-1.4.1-cp36-cp36m-manylinux1_x86_64.whl</td><td>4243</td><td>177</td></tr>\n",
       "<tr><td>xgboost-1.0.2-py3-none-manylinux1_x86_64.whl</td><td>3488</td><td>705</td></tr>\n",
       "<tr><td>scipy-1.1.0-cp27-cp27mu-manylinux1_x86_64.whl</td><td>3200</td><td>269</td></tr>\n",
       "<tr><td>numpy-1.18.2-cp37-cp37m-manylinux1_x86_64.whl</td><td>3096</td><td>185</td></tr>\n",
       "<tr><td>xgboost-0.90-py2.py3-none-manylinux1_x86_64.whl</td><td>3054</td><td>939</td></tr>\n",
       "<tr><td>scipy-1.4.1-cp37-cp37m-manylinux1_x86_64.whl</td><td>2426</td><td>297</td></tr>\n",
       "<tr><td>numpy-1.16.6-cp27-cp27mu-manylinux1_x86_64.whl</td><td>2376</td><td>202</td></tr>\n",
       "<tr><td>pyarrow-0.16.0-cp36-cp36m-manylinux1_x86_64.whl</td><td>2185</td><td>659</td></tr>\n",
       "<tr><td>gensim-3.6.0-cp27-cp27mu-manylinux1_x86_64.whl</td><td>2137</td><td>310</td></tr>\n",
       "<tr><td>botocore-1.15.40-py2.py3-none-any.whl</td><td>2080</td><td>63</td></tr>\n",
       "<tr><td>scipy-1.2.3-cp27-cp27mu-manylinux1_x86_64.whl</td><td>2064</td><td>329</td></tr>\n",
       "<tr><td>awscli-1.18.39-py2.py3-none-any.whl</td><td>1971</td><td>31</td></tr>\n",
       "<tr><td>pip-20.0.2-py2.py3-none-any.whl</td><td>1822</td><td>13</td></tr>\n",
       "<tr><td>ansible-2.9.6.tar.gz</td><td>1551</td><td>254</td></tr>\n",
       "<tr><td>numpy-1.18.2-cp35-cp35m-manylinux1_x86_64.whl</td><td>1432</td><td>364</td></tr>\n",
       "<tr><td>Babel-2.8.0-py2.py3-none-any.whl</td><td>1417</td><td>171</td></tr>\n",
       "<tr><td>pyarrow-0.16.0-cp36-cp36m-manylinux2014_x86_64.whl</td><td>1393</td><td>921</td></tr>\n",
       "<tr><td>pandas-1.0.3-cp36-cp36m-manylinux1_x86_64.whl</td><td>1383</td><td>206</td></tr>\n",
       "<tr><td>botocore-1.13.50-py2.py3-none-any.whl</td><td>1300</td><td>118</td></tr>\n",
       "<tr><td>matplotlib-3.2.1-cp36-cp36m-manylinux1_x86_64.whl</td><td>1246</td><td>278</td></tr>\n",
       "<tr><td>botocore-1.12.253-py2.py3-none-any.whl</td><td>1235</td><td>124</td></tr>\n",
       "<tr><td>notebook-5.7.8-py2.py3-none-any.whl</td><td>1121</td><td>232</td></tr>\n",
       "<tr><td>virtualenv-20.0.17-py2.py3-none-any.whl</td><td>1059</td><td>113</td></tr>\n",
       "<tr><td>scipy-1.4.1-cp35-cp35m-manylinux1_x86_64.whl</td><td>1012</td><td>603</td></tr>\n",
       "<tr><td>pandas-1.0.3-cp37-cp37m-manylinux1_x86_64.whl</td><td>975</td><td>291</td></tr>\n",
       "<tr><td>notebook-6.0.3-py3-none-any.whl</td><td>903</td><td>296</td></tr>\n",
       "<tr><td>pycryptodomex-3.9.7-cp37-cp37m-manylinux1_x86_64.whl</td><td>867</td><td>413</td></tr>\n",
       "<tr><td>matplotlib-3.2.1-cp37-cp37m-manylinux1_x86_64.whl</td><td>864</td><td>371</td></tr>\n",
       "<tr><td>pandas-0.24.2-cp27-cp27mu-manylinux1_x86_64.whl</td><td>801</td><td>340</td></tr>\n",
       "<tr><td>mypy-0.770-cp37-cp37m-manylinux1_x86_64.whl</td><td>772</td><td>644</td></tr>\n",
       "<tr><td>grpcio-1.28.1.tar.gz</td><td>767</td><td>597</td></tr>\n",
       "<tr><td>numpy-1.16.4-cp35-cp35m-manylinux1_x86_64.whl</td><td>752</td><td>548</td></tr>\n",
       "<tr><td>cryptography-2.9-cp35-abi3-manylinux2010_x86_64.whl</td><td>717</td><td>88</td></tr>\n",
       "<tr><td>pandas-0.24.2-cp35-cp35m-manylinux1_x86_64.whl</td><td>680</td><td>380</td></tr>\n",
       "<tr><td>mlflow-1.7.2-py3-none-any.whl</td><td>636</td><td>590</td></tr>\n",
       "<tr><td>virtualenv-20.0.18-py2.py3-none-any.whl</td><td>635</td><td>207</td></tr>\n",
       "<tr><td>awscli-1.18.40-py2.py3-none-any.whl</td><td>558</td><td>143</td></tr>\n",
       "<tr><td>pytz-2019.3-py2.py3-none-any.whl</td><td>546</td><td>15</td></tr>\n",
       "<tr><td>setuptools-46.1.3-py3-none-any.whl</td><td>544</td><td>18</td></tr>\n",
       "<tr><td>scikit_learn-0.22.2.post1-cp36-cp36m-manylinux1_x86_64.whl</td><td>542</td><td>347</td></tr>\n",
       "<tr><td>matplotlib-2.2.5-cp27-cp27mu-manylinux1_x86_64.whl</td><td>540</td><td>562</td></tr>\n",
       "<tr><td>numpy-1.18.2-cp38-cp38-manylinux1_x86_64.whl</td><td>535</td><td>819</td></tr>\n",
       "<tr><td>pycryptodome-3.9.7-cp37-cp37m-manylinux1_x86_64.whl</td><td>492</td><td>642</td></tr>\n",
       "<tr><td>snowflake_connector_python-2.2.4-cp37-cp37m-manylinux2010_x86_64.whl</td><td>491</td><td>588</td></tr>\n",
       "<tr><td>docutils-0.15.2-py2-none-any.whl</td><td>474</td><td>22</td></tr>\n",
       "<tr><td>plotly-4.6.0-py2.py3-none-any.whl</td><td>472</td><td>391</td></tr>\n",
       "<tr><td>docutils-0.15.2-py3-none-any.whl</td><td>468</td><td>23</td></tr>\n",
       "<tr><td>python_dateutil-2.8.1-py2.py3-none-any.whl</td><td>447</td><td>4</td></tr>\n",
       "<tr><td>widgetsnbextension-3.5.1-py2.py3-none-any.whl</td><td>445</td><td>130</td></tr>\n",
       "<tr><td>scikit_learn-0.20.3-cp36-cp36m-manylinux1_x86_64.whl</td><td>445</td><td>331</td></tr>\n",
       "<tr><td>future-0.18.2.tar.gz</td><td>428</td><td>39</td></tr>\n",
       "<tr><td>numpy-1.17.4-cp35-cp35m-manylinux1_x86_64.whl</td><td>424</td><td>938</td></tr>\n",
       "<tr><td>numpy-1.17.4-cp37-cp37m-manylinux1_x86_64.whl</td><td>412</td><td>959</td></tr>\n",
       "<tr><td>numpy-1.18.1-cp37-cp37m-manylinux1_x86_64.whl</td><td>405</td><td>984</td></tr>\n",
       "<tr><td>SQLAlchemy-1.3.16.tar.gz</td><td>391</td><td>404</td></tr>\n",
       "<tr><td>SQLAlchemy-1.3.13.tar.gz</td><td>389</td><td>403</td></tr>\n",
       "<tr><td>awscli-1.16.314-py2.py3-none-any.whl</td><td>384</td><td>204</td></tr>\n",
       "<tr><td>boto-2.49.0-py2.py3-none-any.whl</td><td>377</td><td>85</td></tr>\n",
       "<tr><td>PyYAML-5.3.1.tar.gz</td><td>363</td><td>11</td></tr>\n",
       "<tr><td>tensorboard-2.0.2-py2-none-any.whl</td><td>349</td><td>304</td></tr>\n",
       "<tr><td>cryptography-2.9-cp35-abi3-manylinux1_x86_64.whl</td><td>345</td><td>228</td></tr>\n",
       "<tr><td>pipenv-2018.11.26-py3-none-any.whl</td><td>344</td><td>389</td></tr>\n",
       "<tr><td>lxml-4.5.0-cp36-cp36m-manylinux1_x86_64.whl</td><td>337</td><td>435</td></tr>\n",
       "<tr><td>pycryptodomex-3.9.7-cp36-cp36m-manylinux1_x86_64.whl</td><td>335</td><td>857</td></tr>\n",
       "<tr><td>Django-3.0.5-py3-none-any.whl</td><td>334</td><td>538</td></tr>\n",
       "<tr><td>statsmodels-0.11.1-cp36-cp36m-manylinux1_x86_64.whl</td><td>331</td><td>611</td></tr>\n",
       "<tr><td>lxml-4.5.0-cp37-cp37m-manylinux1_x86_64.whl</td><td>323</td><td>445</td></tr>\n",
       "<tr><td>certifi-2020.4.5.1-py2.py3-none-any.whl</td><td>312</td><td>3</td></tr>\n",
       "<tr><td>pandas-0.25.3-cp36-cp36m-manylinux1_x86_64.whl</td><td>299</td><td>760</td></tr>\n",
       "<tr><td>grpcio-1.28.1-cp36-cp36m-manylinux2010_x86_64.whl</td><td>294</td><td>272</td></tr>\n",
       "<tr><td>scikit_learn-0.22.2.post1-cp37-cp37m-manylinux1_x86_64.whl</td><td>290</td><td>572</td></tr>\n",
       "<tr><td>Pygments-2.6.1-py3-none-any.whl</td><td>289</td><td>71</td></tr>\n",
       "<tr><td>pandas-0.24.2-cp36-cp36m-manylinux1_x86_64.whl</td><td>273</td><td>793</td></tr>\n",
       "<tr><td>chardet-3.0.4-py2.py3-none-any.whl</td><td>272</td><td>2</td></tr>\n",
       "<tr><td>pandas-0.25.3-cp37-cp37m-manylinux1_x86_64.whl</td><td>271</td><td>815</td></tr>\n",
       "<tr><td>pandas-0.23.4-cp37-cp37m-manylinux1_x86_64.whl</td><td>271</td><td>722</td></tr>\n",
       "<tr><td>matplotlib-3.0.3-cp35-cp35m-manylinux1_x86_64.whl</td><td>262</td><td>980</td></tr>\n",
       "<tr><td>sphinx_rtd_theme-0.4.3-py2.py3-none-any.whl</td><td>252</td><td>596</td></tr>\n",
       "<tr><td>Pillow-6.2.2-cp27-cp27mu-manylinux1_x86_64.whl</td><td>248</td><td>241</td></tr>\n",
       "<tr><td>tensorboard-2.1.1-py3-none-any.whl</td><td>244</td><td>412</td></tr>\n",
       "<tr><td>botocore-1.14.17-py2.py3-none-any.whl</td><td>244</td><td>569</td></tr>\n",
       "<tr><td>setuptools-44.1.0-py2.py3-none-any.whl</td><td>243</td><td>54</td></tr>\n",
       "<tr><td>h5py-2.10.0-cp36-cp36m-manylinux1_x86_64.whl</td><td>237</td><td>333</td></tr>\n",
       "<tr><td>mysql-connector-2.2.9.tar.gz</td><td>236</td><td>1000</td></tr>\n",
       "<tr><td>protobuf-3.11.3-cp36-cp36m-manylinux1_x86_64.whl</td><td>226</td><td>151</td></tr>\n",
       "<tr><td>cryptography-2.9-cp27-cp27mu-manylinux2010_x86_64.whl</td><td>225</td><td>328</td></tr>\n",
       "<tr><td>pycountry-19.8.18.tar.gz</td><td>221</td><td>920</td></tr>\n",
       "<tr><td>protobuf-3.11.3-cp37-cp37m-manylinux1_x86_64.whl</td><td>212</td><td>164</td></tr>\n",
       "<tr><td>urllib3-1.25.8-py2.py3-none-any.whl</td><td>210</td><td>7</td></tr>\n",
       "<tr><td>scikit_learn-0.20.4-cp27-cp27mu-manylinux1_x86_64.whl</td><td>209</td><td>620</td></tr>\n",
       "<tr><td>networkx-2.4-py3-none-any.whl</td><td>208</td><td>222</td></tr>\n",
       "<tr><td>tensorflow_data_validation-0.15.0-cp27-cp27mu-manylinux2010_x86_64.whl</td><td>208</td><td>313</td></tr>\n",
       "<tr><td>lxml-4.5.0-cp27-cp27mu-manylinux1_x86_64.whl</td><td>204</td><td>641</td></tr>\n",
       "<tr><td>distlib-0.3.0.zip</td><td>195</td><td>64</td></tr>\n",
       "<tr><td>cryptography-2.9-cp27-cp27mu-manylinux1_x86_64.whl</td><td>182</td><td>387</td></tr>\n",
       "<tr><td>cryptography-2.8-cp34-abi3-manylinux2010_x86_64.whl</td><td>177</td><td>344</td></tr>\n",
       "<tr><td>Django-2.2.12-py3-none-any.whl</td><td>176</td><td>883</td></tr>\n",
       "<tr><td>scikit_learn-0.22.2.post1-cp35-cp35m-manylinux1_x86_64.whl</td><td>175</td><td>838</td></tr>\n",
       "<tr><td>numpy-1.18.2.zip</td><td>174</td><td>699</td></tr>\n",
       "<tr><td>botocore-1.13.20-py2.py3-none-any.whl</td><td>171</td><td>714</td></tr>\n",
       "<tr><td>tfx_bsl-0.15.3-cp27-cp27mu-manylinux2010_x86_64.whl</td><td>169</td><td>314</td></tr>\n",
       "<tr><td>tensorboard-1.15.0-py3-none-any.whl</td><td>169</td><td>533</td></tr>\n",
       "<tr><td>Pygments-2.5.2-py2.py3-none-any.whl</td><td>168</td><td>141</td></tr>\n",
       "<tr><td>grpcio-1.28.1-cp37-cp37m-manylinux2010_x86_64.whl</td><td>168</td><td>432</td></tr>\n",
       "<tr><td>jedi-0.17.0-py2.py3-none-any.whl</td><td>164</td><td>194</td></tr>\n",
       "<tr><td>protobuf-3.11.3-cp35-cp35m-manylinux1_x86_64.whl</td><td>163</td><td>223</td></tr>\n",
       "<tr><td>protobuf-3.11.3-cp27-cp27mu-manylinux1_x86_64.whl</td><td>162</td><td>225</td></tr>\n",
       "<tr><td>scikit_learn-0.21.3-cp36-cp36m-manylinux1_x86_64.whl</td><td>161</td><td>864</td></tr>\n",
       "<tr><td>protobuf-3.6.0-cp36-cp36m-manylinux1_x86_64.whl</td><td>149</td><td>950</td></tr>\n",
       "<tr><td>docutils-0.16-py2.py3-none-any.whl</td><td>146</td><td>89</td></tr>\n",
       "<tr><td>netaddr-0.7.19-py2.py3-none-any.whl</td><td>144</td><td>316</td></tr>\n",
       "<tr><td>lightgbm-2.3.1-py2.py3-none-manylinux1_x86_64.whl</td><td>142</td><td>248</td></tr>\n",
       "<tr><td>Werkzeug-1.0.1-py2.py3-none-any.whl</td><td>142</td><td>47</td></tr>\n",
       "<tr><td>tensorboard-1.14.0-py3-none-any.whl</td><td>140</td><td>541</td></tr>\n",
       "<tr><td>pyzmq-19.0.0-cp27-cp27mu-manylinux1_x86_64.whl</td><td>136</td><td>221</td></tr>\n",
       "<tr><td>SQLAlchemy-1.3.15.tar.gz</td><td>135</td><td>915</td></tr>\n",
       "<tr><td>Sphinx-3.0.1-py3-none-any.whl</td><td>133</td><td>518</td></tr>\n",
       "<tr><td>joblib-0.14.1-py2.py3-none-any.whl</td><td>133</td><td>51</td></tr>\n",
       "<tr><td>h5py-2.10.0-cp27-cp27mu-manylinux1_x86_64.whl</td><td>132</td><td>512</td></tr>\n",
       "<tr><td>imageio-2.8.0-py3-none-any.whl</td><td>129</td><td>604</td></tr>\n",
       "<tr><td>Pillow-7.1.1-cp37-cp37m-manylinux1_x86_64.whl</td><td>127</td><td>429</td></tr>\n",
       "<tr><td>tensorflow_model_analysis-0.15.4-py2-none-any.whl</td><td>126</td><td>312</td></tr></table>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "table = (\"<table>\" + \"<tr><th>file</th><th>GiB/day</th><th>popularity</th></tr>\" + \"\\n\".join(\"<tr><td>{}</td><td>{}</td><td>{}</td></tr>\".format(*row) for row in rows) + \"</table>\")\n",
    "import IPython.display\n",
    "IPython.display.HTML(table)"
   ]
  },
  {
   "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.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}