{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import boto3\n",
    "import pandas as pd\n",
    "import gzip\n",
    "from io import BytesIO"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "outputs": [],
   "source": [
    "s3 = boto3.client('s3')\n",
    "obj = s3.get_object(Bucket='cucumbers', Key='BI/reports/bidi/sample1.sql/latest.csv.gz')\n",
    "buffer = BytesIO(obj[\"Body\"].read())"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "outputs": [],
   "source": [
    "with gzip.GzipFile(fileobj=buffer) as file:\n",
    "    df = pd.read_csv(file)"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "outputs": [
    {
     "data": {
      "text/plain": "  ACTIVITY_DATE     COUNTRYNAME COUNTRY_CODE                  STORENAME  \\\n0    2021-12-01  United Kingdom           GB  Proper Music Distribution   \n1    2021-12-01          Mexico           MX                     Deezer   \n2    2021-12-01          Mexico           MX                     Deezer   \n3    2021-12-01          Greece           GR               iTunes/Apple   \n4    2021-12-01         Bolivia           BO                    Spotify   \n\n         GENRE TRANSACTION_GROUP     TRANSACTION_DESCRIPTION  GROSS_REVENUE  \\\n0        Metal          Physical              Physical Sales  114203.173829   \n1  World Music         Streaming  Ad-Supported Audio Streams    2753.721779   \n2  Hip-hop/Rap         Streaming  Ad-Supported Audio Streams    4051.424771   \n3         Rock         Streaming  Subscription Audio Streams     548.606007   \n4  World Music         Streaming  Ad-Supported Audio Streams     130.971052   \n\n       UNITS   AVG_RATE  \n0    10326.0  10.985724  \n1  3484558.0   0.000767  \n2  4948140.0   0.000790  \n3    69395.0   0.007422  \n4  1847137.0   0.000071  ",
      "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>ACTIVITY_DATE</th>\n      <th>COUNTRYNAME</th>\n      <th>COUNTRY_CODE</th>\n      <th>STORENAME</th>\n      <th>GENRE</th>\n      <th>TRANSACTION_GROUP</th>\n      <th>TRANSACTION_DESCRIPTION</th>\n      <th>GROSS_REVENUE</th>\n      <th>UNITS</th>\n      <th>AVG_RATE</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>2021-12-01</td>\n      <td>United Kingdom</td>\n      <td>GB</td>\n      <td>Proper Music Distribution</td>\n      <td>Metal</td>\n      <td>Physical</td>\n      <td>Physical Sales</td>\n      <td>114203.173829</td>\n      <td>10326.0</td>\n      <td>10.985724</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>2021-12-01</td>\n      <td>Mexico</td>\n      <td>MX</td>\n      <td>Deezer</td>\n      <td>World Music</td>\n      <td>Streaming</td>\n      <td>Ad-Supported Audio Streams</td>\n      <td>2753.721779</td>\n      <td>3484558.0</td>\n      <td>0.000767</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>2021-12-01</td>\n      <td>Mexico</td>\n      <td>MX</td>\n      <td>Deezer</td>\n      <td>Hip-hop/Rap</td>\n      <td>Streaming</td>\n      <td>Ad-Supported Audio Streams</td>\n      <td>4051.424771</td>\n      <td>4948140.0</td>\n      <td>0.000790</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>2021-12-01</td>\n      <td>Greece</td>\n      <td>GR</td>\n      <td>iTunes/Apple</td>\n      <td>Rock</td>\n      <td>Streaming</td>\n      <td>Subscription Audio Streams</td>\n      <td>548.606007</td>\n      <td>69395.0</td>\n      <td>0.007422</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>2021-12-01</td>\n      <td>Bolivia</td>\n      <td>BO</td>\n      <td>Spotify</td>\n      <td>World Music</td>\n      <td>Streaming</td>\n      <td>Ad-Supported Audio Streams</td>\n      <td>130.971052</td>\n      <td>1847137.0</td>\n      <td>0.000071</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "outputs": [
    {
     "data": {
      "text/plain": "                GROSS_REVENUE       UNITS   AVG_RATE\nCOUNTRYNAME                                         \nAfghanistan          0.783540       459.0   0.043411\nAlbania            648.657577    684875.0  11.971727\nAlgeria           8528.796380  70903361.0   0.292454\nAmerican Samoa      90.269412     73825.0   0.464970\nAndorra           4189.360128   1157577.0   1.566237\n...                       ...         ...        ...\nWestern Sahara       0.266903       276.0   0.002836\nYemen              112.366077   1246466.0   0.372418\nZambia            1039.906323   1134990.0   0.883420\nZimbabwe           781.115510   1660363.0   9.783224\nÅland Islands        1.282646         4.0   0.641323\n\n[227 rows x 3 columns]",
      "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>GROSS_REVENUE</th>\n      <th>UNITS</th>\n      <th>AVG_RATE</th>\n    </tr>\n    <tr>\n      <th>COUNTRYNAME</th>\n      <th></th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>Afghanistan</th>\n      <td>0.783540</td>\n      <td>459.0</td>\n      <td>0.043411</td>\n    </tr>\n    <tr>\n      <th>Albania</th>\n      <td>648.657577</td>\n      <td>684875.0</td>\n      <td>11.971727</td>\n    </tr>\n    <tr>\n      <th>Algeria</th>\n      <td>8528.796380</td>\n      <td>70903361.0</td>\n      <td>0.292454</td>\n    </tr>\n    <tr>\n      <th>American Samoa</th>\n      <td>90.269412</td>\n      <td>73825.0</td>\n      <td>0.464970</td>\n    </tr>\n    <tr>\n      <th>Andorra</th>\n      <td>4189.360128</td>\n      <td>1157577.0</td>\n      <td>1.566237</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>Western Sahara</th>\n      <td>0.266903</td>\n      <td>276.0</td>\n      <td>0.002836</td>\n    </tr>\n    <tr>\n      <th>Yemen</th>\n      <td>112.366077</td>\n      <td>1246466.0</td>\n      <td>0.372418</td>\n    </tr>\n    <tr>\n      <th>Zambia</th>\n      <td>1039.906323</td>\n      <td>1134990.0</td>\n      <td>0.883420</td>\n    </tr>\n    <tr>\n      <th>Zimbabwe</th>\n      <td>781.115510</td>\n      <td>1660363.0</td>\n      <td>9.783224</td>\n    </tr>\n    <tr>\n      <th>Åland Islands</th>\n      <td>1.282646</td>\n      <td>4.0</td>\n      <td>0.641323</td>\n    </tr>\n  </tbody>\n</table>\n<p>227 rows × 3 columns</p>\n</div>"
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby('COUNTRYNAME').sum()"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "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.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}