{"cells":[{"cell_type":"markdown","metadata":{"_cell_guid":"aafa209a-05c0-f5ed-12c4-09d4a808bf29"},"source":"This notebook is based on sample version with first 10,000,000 rows.<br><br>\nIt contains two main parts:<br>\n1. Exploration by country<br>\n2. Exploration by State for USA data<br>\n\nAny comments and improvements are welcome!"},{"cell_type":"markdown","metadata":{"_cell_guid":"8e1927e6-f75c-34f9-251e-9674957b4552"},"source":"### Load \"Page view\" sample data"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"7dd256bf-858e-70b3-4e17-d76b6f375b0e"},"outputs":[],"source":"import pandas as pd\nimport warnings\nwarnings.filterwarnings('ignore')\n\npage_views_sample_df = pd.read_csv(\"../input/page_views_sample.csv\", usecols=['uuid', 'geo_location'])\n# Drop NAs and no return\npage_views_sample_df.dropna(inplace=True)\n# Drop EU code\npage_views_sample_df = page_views_sample_df.loc[~page_views_sample_df.geo_location.isin(['EU', '--']), :]\n# Drop duplicates\npage_views_sample_df = page_views_sample_df.drop_duplicates('uuid', keep='first')"},{"cell_type":"markdown","metadata":{"_cell_guid":"cdd5e3e6-bb80-d3c3-41e5-69f32cceacc5"},"source":"# Exploration by Country"},{"cell_type":"markdown","metadata":{"_cell_guid":"e5e5777a-1d28-2152-2456-3c2a956544d5"},"source":"At the beginning let see distribution by country."},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"1b9e2a1b-2add-4a06-4e7a-75fb4b2e2065"},"outputs":[],"source":"country = page_views_sample_df.copy()\ncountry.columns = ['uuid', 'Country']\ncountry.Country = country.Country.str[:2]\ncountry.loc[:, 'UserCount'] = country.groupby('Country')['Country'].transform('count')\ncountry = country.loc[:, ['Country', 'UserCount']].drop_duplicates('Country', keep='first')\ncountry.sort_values('UserCount', ascending=False, inplace=True)\ncountry.head(10)"},{"cell_type":"markdown","metadata":{"_cell_guid":"11d72e89-dc13-e223-0490-eb0e3c9c82e1"},"source":"Adding country names to show data in a beautiful way, to generate dictionary with Country names I've used **pycountry** library."},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"74d1159e-e073-4452-43d4-7739bb01c6ce"},"outputs":[],"source":"countryCode2Name = {u'BD': u'Bangladesh', u'BE': u'Belgium', u'BF': u'Burkina Faso', u'BG': u'Bulgaria', u'BA': u'Bosnia and Herzegovina', u'BB': u'Barbados', u'WF': u'Wallis and Futuna', u'BL': u'Saint Barth\\xe9lemy', u'BM': u'Bermuda', u'BN': u'Brunei Darussalam', u'BO': u'Bolivia, Plurinational State of', u'BH': u'Bahrain', u'BI': u'Burundi', u'BJ': u'Benin', u'BT': u'Bhutan', u'JM': u'Jamaica', u'BV': u'Bouvet Island', u'BW': u'Botswana', u'WS': u'Samoa', u'BQ': u'Bonaire, Sint Eustatius and Saba', u'BR': u'Brazil', u'BS': u'Bahamas', u'JE': u'Jersey', u'BY': u'Belarus', u'BZ': u'Belize', u'RU': u'Russian Federation', u'RW': u'Rwanda', u'RS': u'Serbia', u'TL': u'Timor-Leste', u'RE': u'R\\xe9union', u'TM': u'Turkmenistan', u'TJ': u'Tajikistan', u'RO': u'Romania', u'TK': u'Tokelau', u'GW': u'Guinea-Bissau', u'GU': u'Guam', u'GT': u'Guatemala', u'GS': u'South Georgia and the South Sandwich Islands', u'GR': u'Greece', u'GQ': u'Equatorial Guinea', u'GP': u'Guadeloupe', u'JP': u'Japan', u'GY': u'Guyana', u'GG': u'Guernsey', u'GF': u'French Guiana', u'GE': u'Georgia', u'GD': u'Grenada', u'GB': u'United Kingdom', u'GA': u'Gabon', u'GN': u'Guinea', u'GM': u'Gambia', u'GL': u'Greenland', u'GI': u'Gibraltar', u'GH': u'Ghana', u'OM': u'Oman', u'TN': u'Tunisia', u'JO': u'Jordan', u'HR': u'Croatia', u'HT': u'Haiti', u'HU': u'Hungary', u'HK': u'Hong Kong', u'HN': u'Honduras', u'HM': u'Heard Island and McDonald Islands', u'VE': u'Venezuela, Bolivarian Republic of', u'PR': u'Puerto Rico', u'PS': u'Palestine, State of', u'PW': u'Palau', u'PT': u'Portugal', u'KN': u'Saint Kitts and Nevis', u'PY': u'Paraguay', u'IQ': u'Iraq', u'PA': u'Panama', u'PF': u'French Polynesia', u'PG': u'Papua New Guinea', u'PE': u'Peru', u'PK': u'Pakistan', u'PH': u'Philippines', u'PN': u'Pitcairn', u'PL': u'Poland', u'PM': u'Saint Pierre and Miquelon', u'ZM': u'Zambia', u'EH': u'Western Sahara', u'EE': u'Estonia', u'EG': u'Egypt', u'ZA': u'South Africa', u'EC': u'Ecuador', u'IT': u'Italy', u'VN': u'Viet Nam', u'SB': u'Solomon Islands', u'ET': u'Ethiopia', u'SO': u'Somalia', u'ZW': u'Zimbabwe', u'SA': u'Saudi Arabia', u'ES': u'Spain', u'ER': u'Eritrea', u'ME': u'Montenegro', u'MD': u'Moldova, Republic of', u'MG': u'Madagascar', u'MF': u'Saint Martin (French part)', u'MA': u'Morocco', u'MC': u'Monaco', u'UZ': u'Uzbekistan', u'MM': u'Myanmar', u'ML': u'Mali', u'MO': u'Macao', u'MN': u'Mongolia', u'MH': u'Marshall Islands', u'MK': u'Macedonia, Republic of', u'MU': u'Mauritius', u'MT': u'Malta', u'MW': u'Malawi', u'MV': u'Maldives', u'MQ': u'Martinique', u'MP': u'Northern Mariana Islands', u'MS': u'Montserrat', u'MR': u'Mauritania', u'IM': u'Isle of Man', u'UG': u'Uganda', u'TZ': u'Tanzania, United Republic of', u'MY': u'Malaysia', u'MX': u'Mexico', u'IL': u'Israel', u'FR': u'France', u'AW': u'Aruba', u'SH': u'Saint Helena, Ascension and Tristan da Cunha', u'SJ': u'Svalbard and Jan Mayen', u'FI': u'Finland', u'FJ': u'Fiji', u'FK': u'Falkland Islands (Malvinas)', u'FM': u'Micronesia, Federated States of', u'FO': u'Faroe Islands', u'NI': u'Nicaragua', u'NL': u'Netherlands', u'NO': u'Norway', u'NA': u'Namibia', u'VU': u'Vanuatu', u'NC': u'New Caledonia', u'NE': u'Niger', u'NF': u'Norfolk Island', u'NG': u'Nigeria', u'NZ': u'New Zealand', u'NP': u'Nepal', u'NR': u'Nauru', u'NU': u'Niue', u'CK': u'Cook Islands', u'CI': u\"C\\xf4te d'Ivoire\", u'CH': u'Switzerland', u'CO': u'Colombia', u'CN': u'China', u'CM': u'Cameroon', u'CL': u'Chile', u'CC': u'Cocos (Keeling) Islands', u'CA': u'Canada', u'CG': u'Congo', u'CF': u'Central African Republic', u'CD': u'Congo, The Democratic Republic of the', u'CZ': u'Czech Republic', u'CY': u'Cyprus', u'CX': u'Christmas Island', u'CR': u'Costa Rica', u'CW': u'Cura\\xe7ao', u'CV': u'Cape Verde', u'CU': u'Cuba', u'SZ': u'Swaziland', u'SY': u'Syrian Arab Republic', u'SX': u'Sint Maarten (Dutch part)', u'KG': u'Kyrgyzstan', u'KE': u'Kenya', u'SS': u'South Sudan', u'SR': u'Suriname', u'KI': u'Kiribati', u'KH': u'Cambodia', u'SV': u'El Salvador', u'KM': u'Comoros', u'ST': u'Sao Tome and Principe', u'SK': u'Slovakia', u'KR': u'Korea, Republic of', u'SI': u'Slovenia', u'KP': u\"Korea, Democratic People's Republic of\", u'KW': u'Kuwait', u'SN': u'Senegal', u'SM': u'San Marino', u'SL': u'Sierra Leone', u'SC': u'Seychelles', u'KZ': u'Kazakhstan', u'KY': u'Cayman Islands', u'SG': u'Singapore', u'SE': u'Sweden', u'SD': u'Sudan', u'DO': u'Dominican Republic', u'DM': u'Dominica', u'DJ': u'Djibouti', u'DK': u'Denmark', u'DE': u'Germany', u'YE': u'Yemen', u'DZ': u'Algeria', u'US': u'United States', u'UY': u'Uruguay', u'YT': u'Mayotte', u'UM': u'United States Minor Outlying Islands', u'LB': u'Lebanon', u'LC': u'Saint Lucia', u'LA': u\"Lao People's Democratic Republic\", u'TV': u'Tuvalu', u'TW': u'Taiwan, Province of China', u'TT': u'Trinidad and Tobago', u'TR': u'Turkey', u'LK': u'Sri Lanka', u'LI': u'Liechtenstein', u'LV': u'Latvia', u'TO': u'Tonga', u'LT': u'Lithuania', u'LU': u'Luxembourg', u'LR': u'Liberia', u'LS': u'Lesotho', u'TH': u'Thailand', u'TF': u'French Southern Territories', u'TG': u'Togo', u'TD': u'Chad', u'TC': u'Turks and Caicos Islands', u'LY': u'Libya', u'VA': u'Holy See (Vatican City State)', u'VC': u'Saint Vincent and the Grenadines', u'AE': u'United Arab Emirates', u'AD': u'Andorra', u'AG': u'Antigua and Barbuda', u'AF': u'Afghanistan', u'AI': u'Anguilla', u'IS': u'Iceland', u'IR': u'Iran, Islamic Republic of', u'AM': u'Armenia', u'AL': u'Albania', u'AO': u'Angola', u'AQ': u'Antarctica', u'AS': u'American Samoa', u'AR': u'Argentina', u'AU': u'Australia', u'AT': u'Austria', u'IO': u'British Indian Ocean Territory', u'IN': u'India', u'AX': u'\\xc5land Islands', u'AZ': u'Azerbaijan', u'IE': u'Ireland', u'ID': u'Indonesia', u'UA': u'Ukraine', u'QA': u'Qatar', u'MZ': u'Mozambique', u'FX': u'France, Metropolitan', u'AN': u'Netherlands Antilles', u'A1': u'Anguilla'}\ncountry['CountryName'] = country['Country'].map(countryCode2Name)\n\n# Drop NAs\ncountry.dropna(inplace=True)\n\ncountry['CumulativePercentage'] = 100 * country.UserCount.cumsum()/country.UserCount.sum()\ncountry.reset_index(drop=True, inplace=True)\ncountry[['CountryName', 'UserCount', 'CumulativePercentage']].head(10)"},{"cell_type":"markdown","metadata":{"_cell_guid":"da1e06fb-d68d-55d8-7d0e-8090cd328668"},"source":"Show data on the world map"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"bb2651fb-71e5-9962-c567-a0b54fc12213"},"outputs":[],"source":"import plotly.offline as py\npy.offline.init_notebook_mode()\n\ndata = [ dict(\n        type = 'choropleth',\n        locations = country['CountryName'],\n        z = country['UserCount'],\n        locationmode = 'country names',\n        text = country['CountryName'],\n        colorscale = [[0,\"rgb(153, 241, 243)\"],[0.005,\"rgb(16, 64, 143)\"],[1,\"rgb(0, 0, 0)\"]],\n        autocolorscale = False,\n        marker = dict(\n            line = dict(color = 'rgb(58,100,69)', width = 0.6)),\n            colorbar = dict(autotick = True, tickprefix = '', title = '# of Users')\n            )\n       ]\n\nlayout = dict(\n    title = 'Total number of users by country',\n    geo = dict(\n        showframe = False,\n        showcoastlines = True,\n        projection = dict(\n        type = 'equirectangular'\n        ),\n    margin = dict(b = 0, t = 0, l = 0, r = 0)\n            )\n    )\n\nfig = dict(data=data, layout=layout)\npy.iplot(fig, validate=False, filename='worldmap')"},{"cell_type":"markdown","metadata":{"_cell_guid":"2456c195-fd55-ccc0-cea8-93e3596dd61a"},"source":"As we can see more than 81% of all Users are from USA. Lets see distribution by States for USA."},{"cell_type":"markdown","metadata":{"_cell_guid":"6b270880-dfb3-38db-4322-50f65a2cf1fb"},"source":"# Exploration by State for USA data"},{"cell_type":"markdown","metadata":{"_cell_guid":"e6d92b03-79b4-496c-8372-2ed858d146fc"},"source":"Doing the same things, that I have done for countries"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"dafe4b5b-6131-912e-b468-120db05713f4"},"outputs":[],"source":"usa = page_views_sample_df.loc[page_views_sample_df.geo_location.str[:2] == 'US', :]\nusa.columns = ['uuid', 'State']\n\nusa.State = usa.State.str[3:5]\n\n# Drop Data with missing state info\nusa = usa.loc[usa.State != '', :]\n\nusa.loc[:, 'UserCount'] = usa.groupby('State')['State'].transform('count')\nusa.loc[:, ['State', 'UserCount']] = usa.loc[:, ['State', 'UserCount']].drop_duplicates('State', keep='first')\nusa.sort_values('UserCount', ascending=False, inplace=True)"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"234a2f4f-6a0e-4c71-b448-b71c856ccf18"},"outputs":[],"source":"stateCode2Name = {'AK': 'Alaska', 'AL': 'Alabama', 'AR': 'Arkansas', 'AS': 'American Samoa', 'AZ': 'Arizona', 'CA': 'California', 'CO': 'Colorado', 'CT': 'Connecticut', 'DC': 'District of Columbia', 'DE': 'Delaware', 'FL': 'Florida', 'GA': 'Georgia', 'GU': 'Guam', 'HI': 'Hawaii', 'IA': 'Iowa', 'ID': 'Idaho', 'IL': 'Illinois', 'IN': 'Indiana', 'KS': 'Kansas', 'KY': 'Kentucky', 'LA': 'Louisiana', 'MA': 'Massachusetts', 'MD': 'Maryland', 'ME': 'Maine', 'MI': 'Michigan', 'MN':'Minnesota', 'MO': 'Missouri', 'MP': 'Northern Mariana Islands', 'MS': 'Mississippi', 'MT': 'Montana', 'NA': 'National', 'NC': 'North Carolina', 'ND': 'North Dakota', 'NE':'Nebraska', 'NH': 'New Hampshire', 'NJ': 'New Jersey', 'NM': 'New Mexico', 'NV': 'Nevada', 'NY': 'New York', 'OH': 'Ohio', 'OK': 'Oklahoma', 'OR': 'Oregon', 'PA': 'Pennsylvania', 'PR': 'Puerto Rico', 'RI': 'Rhode Island', 'SC': 'South Carolina', 'SD': 'South Dakota', 'TN': 'Tennessee', 'TX': 'Texas', 'UT': 'Utah', 'VA': 'Virginia', 'VI': 'Virgin Islands', 'VT': 'Vermont', 'WA': 'Washington', 'WI': 'Wisconsin', 'WV': 'West Virginia', 'WY': 'Wyoming'}\nusa['StateName'] = usa['State'].map(stateCode2Name)\n# Drop NAs\nusa.dropna(inplace=True)\n\nusa['CumulativePercentage'] = 100 * usa.UserCount.cumsum()/usa.UserCount.sum()\nusa.reset_index(drop=True, inplace=True)\nusa[['StateName', 'UserCount', 'CumulativePercentage']].head(50)"},{"cell_type":"markdown","metadata":{"_cell_guid":"736b2a9f-ff99-28bd-6256-1be7c15c3794"},"source":"## Show USA map"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"0ef3e3cc-441c-b300-1359-f8cb4b9a2581"},"outputs":[],"source":"import plotly.offline as py\npy.offline.init_notebook_mode()\n\ndata = [ dict(\n        type = 'choropleth',\n        locations = usa['State'],\n        z = usa['UserCount'],\n        locationmode = 'USA-states',\n        text = usa['StateName'],\n        colorscale = [[0,\"rgb(153, 241, 243)\"],[0.33,\"rgb(16, 64, 143)\"],[1,\"rgb(0, 0, 0)\"]],\n        autocolorscale = False,\n        marker = dict(\n            line = dict(color = 'rgb(58,100,69)', width = 0.6)),\n            colorbar = dict(autotick = True, tickprefix = '', title = '# of Users')\n            )\n       ]\n\nlayout = dict(\n    title = 'Total number of users by state',\n    geo = dict(\n        scope='usa',\n        projection=dict( type='albers usa' ),\n        showlakes = True,\n        lakecolor = 'rgb(255, 255, 255)'),\n    )\n\nfig = dict(data=data, layout=layout)\npy.iplot(fig, validate=False, filename='USmap')"}],"metadata":{"_change_revision":0,"_is_fork":false,"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.5.2"}},"nbformat":4,"nbformat_minor":0}