{ "cells": [ { "cell_type": "code", "execution_count": 88, "metadata": {}, "outputs": [], "source": [ "import pandas as pd\n", "import re\n", "from hashlib import sha256\n", "from itertools import combinations\n", "%run ./utils.ipynb\n", "get_rds_engine()\n", "engine = get_rds_engine()\n", "\n", "# setting pandas visual settings \n", "pd.set_option('display.max_columns', 20)\n", "pd.set_option('max_colwidth', 1000)\n", "pd.set_option(\"max_rows\", 100)" ] }, { "cell_type": "code", "execution_count": 94, "metadata": {}, "outputs": [], "source": [ "# schema = 'ca7a460d855e229dd0384001e1c9f5d6fb59381f967ba1b7ec36a7591' # schema \"kaspar\"\n", "# schema = 'bd345f915775993a4d3de1dae65b93b067ad69dde4286a1e1639e5cd2' # IHW \"Aleks\"\n", "# schema = 'a441ffb172866cb4928c84a73de403ca15da4a54cc535e704413ab621' # OCEAN \"Aleks\"\n", "schema = 'ace3c2bb4abd9fcaf9807975e6ab940131386dc9ecddb2b7e31288ff8' # PIXIES \"Aleks\"\n", "\n", "def obfuscate_sha256(string: str):\n", " \"\"\"Returns SHA 256 obfuscated string\"\"\"\n", " return sha256(str(string).encode()).hexdigest()\n", "\n", "def get_master_deduped(schema, join_part = ''):\n", "# schema = \"a441ffb172866cb4928c84a73de403ca15da4a54cc535e704413ab621\"\n", "\n", " query = f\"\"\"\n", " SELECT DISTINCT ON (ft.fan_id)\n", " lower(ft.root_email) as email\n", " , initcap(first_name) as first_name\n", " , initcap(last_name) as last_name\n", " , case when fag.country = 'Australia' then 'AU' \n", " when fag.country = 'New Zealand' then 'NZ' \n", " when fag.country = 'United Kingdom' then 'UK'\n", " when fag.country = 'United States' then 'US' \n", " end country -- TODO! get ISO2 from somewhere else\n", " , fag.locality as city\n", " , fag.administrative_area_level_1\n", " , fag.administrative_area_level_2\n", " FROM {schema}.fan_table ft\n", " LEFT JOIN {schema}.fan_address_geocoded fag ON ft.fan_id = fag.fan_id\n", " -- TODO! geocoded table needs to be in correct schema\n", " {join_part}\n", " ;\n", " \"\"\"\n", "\n", " # Replace \"None\" with NaN\n", " df = pd.read_sql(query, engine)\n", " df.replace('None', np.nan, inplace=True)\n", " df.replace('unknown', np.nan, inplace=True)\n", " df.fillna(value=pd.np.nan, inplace=True)\n", " print(df.shape)\n", " return df\n", "\n", "def get_fan_clusters(schema, table):\n", " query = f\"\"\"\n", " SELECT fan_id, cluster\n", " FROM {schema}.{table}\n", " \"\"\"\n", " try:\n", " df = pd.read_sql(query, engine)\n", " return df\n", " except:\n", " print(f'Something Went wrong check if {schema}.{table} table exists')\n", "\n", "def get_facebook_files(schema, join_part = ''):\n", "# schema = \"a441ffb172866cb4928c84a73de403ca15da4a54cc535e704413ab621\"\n", "\n", " query = f\"\"\"\n", " SELECT DISTINCT ON (ft.fan_id)\n", " lower(ft.root_email) as email\n", " , initcap(first_name) as fn\n", " , initcap(last_name) as ln\n", " , to_char(dob::timestamp, 'YYYY-MM-DD') as dob\n", " , to_char(dob::timestamp, 'YYYY') as doby\n", " , cast(extract(year from age(now(), dob::timestamp)) as integer) as age\n", " , case when lower(gender) = 'male' then 'M' when lower(gender) = 'female' then 'F' end gen\n", " , case when fag.country = 'Australia' then 'AU' \n", " when fag.country = 'New Zealand' then 'NZ' \n", " when fag.country = 'United Kingdom' then 'UK'\n", " when fag.country = 'United States' then 'US' \n", " end country -- TODO! get ISO2 from somewhere else\n", " , fag.locality as city\n", " FROM {schema}.fan_table ft\n", " LEFT JOIN {schema}.fan_address_geocoded fag ON ft.fan_id = fag.fan_id\n", " -- TODO! geocoded table needs to be in correct schema\n", " {join_part}\n", " ;\n", " \"\"\"\n", "\n", " # Replace \"None\" with NaN\n", " df = pd.read_sql(query, engine)\n", " df.replace('None', np.nan, inplace=True)\n", " df.replace('unknown', np.nan, inplace=True)\n", " df.fillna(value=pd.np.nan, inplace=True)\n", " return df\n", "\n", "\n", "def get_google_files(schema, join_part = '', hash_data=True):\n", " \"\"\"\n", " https://support.google.com/google-ads/answer/7659867\n", " \"\"\"\n", "# schema = \"a441ffb172866cb4928c84a73de403ca15da4a54cc535e704413ab621\"\n", " \n", " query = f\"\"\"\n", " SELECT DISTINCT ON (ft.fan_id)\n", " lower(ft.root_email) as \"Email\"\n", " \n", " -- TODO! below only works for NZ and AU; put phone normalization somewhere else, not this SQL\n", " , regexp_replace(CASE WHEN char_length(fp.fan_phone)<11 THEN\n", " CASE when fag.country = 'Australia' then concat('+61', fp.fan_phone)\n", " when fag.country = 'New Zealand' then concat('+64', fp.fan_phone) END\n", " WHEN char_length(fp.fan_phone)=11 THEN concat('+', fp.fan_phone)\n", " ELSE fp.fan_phone END, '[^+0-9]', '', 'g')\n", " AS \"Phone\"\n", " \n", " , trim(lower(first_name)) as \"First Name\"\n", " , trim(lower(last_name)) as \"Last Name\"\n", " , case when fag.country = 'Australia' then 'AU' \n", " when fag.country = 'New Zealand' then 'NZ' \n", " when fag.country = 'United Kingdom' then 'UK'\n", " when fag.country = 'United States' then 'US' \n", " end \"Country\" -- TODO! get ISO2 from somewhere else\n", " , fa.fan_zip AS \"Zip\"\n", " FROM {schema}.fan_table ft\n", " LEFT JOIN {schema}.fan_address fa ON ft.fan_id = fa.fan_id\n", " LEFT JOIN (\n", " SELECT fan_id\n", " , replace(replace(fan_phone, ' ', ''), '.0', '') as fan_phone \n", " FROM {schema}.fan_phone\n", " ) fp ON ft.fan_id = fp.fan_id\n", " LEFT JOIN {schema}.fan_address_geocoded fag ON ft.fan_id = fag.fan_id\n", " -- TODO! geocoded table needs to be in correct schema\n", " {join_part}\n", " ;\n", " \"\"\"\n", " # Replace \"None\" with NaN\n", " df = pd.read_sql(query, engine)\n", " df.replace('None', np.nan, inplace=True)\n", " df.replace('unknown', np.nan, inplace=True)\n", " df.fillna(value=pd.np.nan, inplace=True)\n", " \n", " if hash_data:\n", " # Obfuscate some fields (Country and Zip must not be obfuscated)\n", " df['Email'] = df['Email'].apply(obfuscate_sha256)\n", " df['Phone'] = df['Phone'].apply(obfuscate_sha256)\n", " df['First Name'] = df['First Name'].apply(obfuscate_sha256)\n", " df['Last Name'] = df['Last Name'].apply(obfuscate_sha256)\n", " \n", " return df\n", "\n", "def get_superfans(schema, scope: list=None) -> pd.DataFrame:\n", " if scope:\n", " scope = [str(x) for x in scope]\n", " where_query = f' WHERE total_score IN ({\", \".join(scope)})'\n", " else:\n", " where_query = ''\n", " \n", " query = f'''\n", " SELECT DISTINCT fan_id\n", " FROM {schema}.superfans\n", " {where_query}\n", " '''\n", " superfans_df = pd.read_sql(query, engine)\n", " return superfans_df\n", "\n", "def get_rfm_segmetns(schema, scope: list= None) -> pd.DataFrame:\n", "\n", " if scope:\n", " scope = [\"%%\".join(re.split(r\"[^a-zA-Z0-9\\s]\", x)) for x in scope]\n", " where_query = f\"WHERE rfm_segment SIMILAR TO '%%{'|'.join(scope)}%%'\"\n", " else:\n", " where_query = ''\n", "\n", " query = f\"\"\"\n", " SELECT fan_id\n", " FROM {schema}.rfm_segments\n", " {where_query}\n", " \"\"\"\n", " rfm_df = pd.read_sql(query, engine)\n", " return rfm_df\n", "\n", "def compare_segments(source_list:list) -> pd.DataFrame:\n", "\n", " file_list = [x for x in range(0, len(source_list))]\n", " combinations_list = [list(comb) for comb in combinations(file_list, 2)]\n", "\n", " comparison_result = []\n", " for comb in combinations_list:\n", " df1 = source_list[comb[0]]['source_df'][['fan_id']]\n", " df2 = source_list[comb[1]]['source_df'][['fan_id']]\n", "\n", " df3 = pd.merge(df1, df2, how='outer', on='fan_id', indicator='check')\n", "\n", " result_dict = {'c1 name': source_list[comb[0]]['name'],\n", " 'c2 name': source_list[comb[1]]['name'],\n", " 'c1 total fans': len(df1),\n", " 'c2 total fans': len(df2),\n", " 'c1 unique fans': len(df3[df3['check'] == 'left_only']),\n", " 'c1 unique fans %': len(df3[df3['check'] == 'left_only']) / len(df1),\n", " 'c2 unique fans': len(df3[df3['check'] == 'right_only']),\n", " 'c2 unique fans %': len(df3[df3['check'] == 'right_only']) / len(df2),\n", " 'both fans': len(df3[df3['check'] == 'both'])}\n", "\n", " comparison_result.append(result_dict)\n", "\n", " comp_result_df = pd.DataFrame(comparison_result)\n", " comp_result_df = comp_result_df[[x for x, y in result_dict.items()]]\n", " return comp_result_df" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### PIXIES segments\n", "Segment 1 - Superfans (altogether 1878 fans, combines score 4 (1667 fans), 5 (194 fans) and 6 (17 fans)\n", "\n", "Segment 2 - Can´t Lose Them (RFM cluster) (584 fans)\n", "\n", "Segment 3 - Loyal Customers (RFM cluster) (1153 fans)\n", "\n", "Segment 4 – Champions (RFM cluster) (675 fans)\n", "\n", "Segment 5 – Merch Upsell - ML cluster 4 (639 fans)\n", "\n", "Segment 6 – Merch Most Valuable (Big / Active Spenders) - ML Clusters 2, 4, 6 combined (1139 fans altogether)" ] }, { "cell_type": "code", "execution_count": 90, "metadata": {}, "outputs": [], "source": [ "merch_clusters_df = get_fan_clusters(schema, 'merch_clusters')\n", "segments = [\n", " {'name': 'superfans', 'source_df': get_superfans(schema, scope=[4, 5, 6])},\n", " {'name': 'rfm_cant_lose_them', 'source_df': get_rfm_segmetns(schema, scope=[\"Can't Lose Them\"])},\n", " {'name': 'rfm_loyal_customers', 'source_df': get_rfm_segmetns(schema, scope=[\"Loyal Customers\"])},\n", " {'name': 'rfm_champions', 'source_df': get_rfm_segmetns(schema, scope=[\"Champions\"])},\n", " {'name': 'merch_cluster_4', 'source_df': merch_clusters_df.loc[merch_clusters_df['cluster'].isin([4])][['fan_id']]},\n", " {'name': 'merch_most_valuable', 'source_df': merch_clusters_df.loc[merch_clusters_df['cluster'].isin([2, 4, 6])][['fan_id']]}]" ] }, { "cell_type": "code", "execution_count": 91, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "superfans (1877, 1)\n", "rfm_cant_lose_them (584, 1)\n", "rfm_loyal_customers (1153, 1)\n", "rfm_champions (675, 1)\n", "merch_cluster_4 (639, 1)\n", "merch_most_valuable (1139, 1)\n" ] } ], "source": [ "for segment in segments:\n", " try:\n", " print(segment['name'], segment['source_df'].shape)\n", " except:\n", " print(segment['name'], segment['source_df'])" ] }, { "cell_type": "code", "execution_count": 97, "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "/home/ec2-user/anaconda3/envs/python3/lib/python3.6/site-packages/ipykernel/__main__.py:37: FutureWarning: The pandas.np module is deprecated and will be removed from pandas in a future version. Import numpy directly instead\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "(1877, 7)\n", "Done emails_deduped for PIXIES/email_superfans.csv with (1877, 7)\n", "(584, 7)\n", "Done emails_deduped for PIXIES/email_rfm_cant_lose_them.csv with (584, 7)\n", "(1153, 7)\n", "Done emails_deduped for PIXIES/email_rfm_loyal_customers.csv with (1153, 7)\n", "(675, 7)\n", "Done emails_deduped for PIXIES/email_rfm_champions.csv with (675, 7)\n", "(639, 7)\n", "Done emails_deduped for PIXIES/email_merch_cluster_4.csv with (639, 7)\n", "(1139, 7)\n", "Done emails_deduped for PIXIES/email_merch_most_valuable.csv with (1139, 7)\n" ] } ], "source": [ "folder = 'PIXIES'\n", "\n", "for segment in segments:\n", " df = segment['source_df']\n", " \n", " df[['fan_id']].to_sql(f'fblist_temporary',\n", " engine,\n", " schema=schema,\n", " if_exists='replace',\n", " # index_label='fan_id', # TODO! duplicates need to be handled properly\n", " # index=False\n", " )\n", " join_part = f\"INNER JOIN {schema}.fblist_temporary j on ft.fan_id = j.fan_id\"\n", " \n", "# file = f'{folder}/fb_{segment[\"name\"]}.csv'\n", "# fb_df = get_facebook_files(schema, join_part)\n", "# fb_df.to_csv(file, sep=',', index=False)\n", "# print(f'Done {file} with {fb_df.shape}')\n", " \n", "# file = f'{folder}/google_{segment[\"name\"]}_unhashed.csv'\n", "# google_df = get_google_files(schema, join_part, hash_data=False)\n", "# google_df.to_csv(file, sep=',', index=False)\n", "# print(f'Done {file} with {google_df.shape}')\n", " \n", "# file = f'{folder}/google_{segment[\"name\"]}.csv'\n", "# google_df = get_google_files(schema, join_part)\n", "# google_df.to_csv(file, sep=',', index=False)\n", "# print(f'Done {file} with {google_df.shape}')\n", "\n", " file= f'{folder}/email_{segment[\"name\"]}.csv'\n", " email_df = get_master_deduped(schema, join_part)\n", " email_df.to_csv(file, sep=',', index=False)\n", " print(f'Done emails_deduped for {file} with {email_df.shape}')\n" ] }, { "cell_type": "code", "execution_count": 86, "metadata": {}, "outputs": [], "source": [ "comparison_df = compare_segments(segments)\n", "comparison_df.to_csv('PIXIES/segments_comparison.csv', sep=',')" ] }, { "cell_type": "code", "execution_count": 85, "metadata": {}, "outputs": [ { "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", " \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", "
c1 namec2 namec1 total fansc2 total fansc1 unique fansc1 unique fans %c2 unique fansc2 unique fans %both fans
0superfansrfm_cant_lose_them187758417300.9216844370.748288147
1superfansrfm_loyal_customers1877115312920.6883325680.492628585
2superfansrfm_champions187767513220.7043151200.177778555
3superfansmerch_cluster_4187763916920.9014384540.710485185
4superfansmerch_most_valuable1877113912840.6840705460.479368593
5rfm_cant_lose_themrfm_loyal_customers58411535841.00000011531.0000000
6rfm_cant_lose_themrfm_champions5846755841.0000006751.0000000
7rfm_cant_lose_themmerch_cluster_45846395100.8732885650.88419474
8rfm_cant_lose_themmerch_most_valuable58411393920.6712339470.831431192
9rfm_loyal_customersrfm_champions115367511531.0000006751.0000000
10rfm_loyal_customersmerch_cluster_411536399280.8048574140.647887225
11rfm_loyal_customersmerch_most_valuable115311396580.5706856440.565408495
12rfm_championsmerch_cluster_46756395940.8800005580.87323981
13rfm_championsmerch_most_valuable67511394820.7140749460.830553193
14merch_cluster_4merch_most_valuable639113900.0000005000.438982639
\n", "
" ], "text/plain": [ " c1 name c2 name c1 total fans c2 total fans \\\n", "0 superfans rfm_cant_lose_them 1877 584 \n", "1 superfans rfm_loyal_customers 1877 1153 \n", "2 superfans rfm_champions 1877 675 \n", "3 superfans merch_cluster_4 1877 639 \n", "4 superfans merch_most_valuable 1877 1139 \n", "5 rfm_cant_lose_them rfm_loyal_customers 584 1153 \n", "6 rfm_cant_lose_them rfm_champions 584 675 \n", "7 rfm_cant_lose_them merch_cluster_4 584 639 \n", "8 rfm_cant_lose_them merch_most_valuable 584 1139 \n", "9 rfm_loyal_customers rfm_champions 1153 675 \n", "10 rfm_loyal_customers merch_cluster_4 1153 639 \n", "11 rfm_loyal_customers merch_most_valuable 1153 1139 \n", "12 rfm_champions merch_cluster_4 675 639 \n", "13 rfm_champions merch_most_valuable 675 1139 \n", "14 merch_cluster_4 merch_most_valuable 639 1139 \n", "\n", " c1 unique fans c1 unique fans % c2 unique fans c2 unique fans % \\\n", "0 1730 0.921684 437 0.748288 \n", "1 1292 0.688332 568 0.492628 \n", "2 1322 0.704315 120 0.177778 \n", "3 1692 0.901438 454 0.710485 \n", "4 1284 0.684070 546 0.479368 \n", "5 584 1.000000 1153 1.000000 \n", "6 584 1.000000 675 1.000000 \n", "7 510 0.873288 565 0.884194 \n", "8 392 0.671233 947 0.831431 \n", "9 1153 1.000000 675 1.000000 \n", "10 928 0.804857 414 0.647887 \n", "11 658 0.570685 644 0.565408 \n", "12 594 0.880000 558 0.873239 \n", "13 482 0.714074 946 0.830553 \n", "14 0 0.000000 500 0.438982 \n", "\n", " both fans \n", "0 147 \n", "1 585 \n", "2 555 \n", "3 185 \n", "4 593 \n", "5 0 \n", "6 0 \n", "7 74 \n", "8 192 \n", "9 0 \n", "10 225 \n", "11 495 \n", "12 81 \n", "13 193 \n", "14 639 " ] }, "execution_count": 85, "metadata": {}, "output_type": "execute_result" } ], "source": [ "comparison_df" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# NOT USED PARTS BELOW" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Grouped Merch Cluster" ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "(91, 2)\n", "(200, 1)\n", "(291, 1)\n", "291\n" ] } ], "source": [ "query = f'''\n", " select fan_id from\n", " (\n", " select m.fan_id,\n", " sum(merch_purchase_monetary) as monetary_value \n", " from {schema}.fan_merch_data m\n", " inner join {schema}.merch_clusters c\n", " on c.fan_id=m.fan_id\n", " where c.cluster=5\n", " group by m.fan_id\n", " order by 2 desc\n", " limit 200\n", " ) sub\n", "'''\n", "\n", "base_df = merch_clusters_df.loc[merch_clusters_df['cluster'].isin([3, 4])]\n", "print(base_df.shape)\n", "extra_df = pd.read_sql(query, engine)\n", "print(extra_df.shape)\n", "grouped_df = pd.concat([base_df[['fan_id']], extra_df],\n", " ignore_index=True)\n", "print(grouped_df.shape)\n", "print(grouped_df.fan_id.nunique())" ] }, { "cell_type": "code", "execution_count": 20, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "(291, 1)\n" ] } ], "source": [ "# Enter our fan_ids to temporary table\n", "# df = pd.read_csv('RFM_segments_1706.csv', sep=\";\")\n", "# df = get_fan_segments(schema)\n", "\n", "\n", "# df = df.loc[df['cluster'].isin([2])]\n", "# print(df.columns)\n", "# print(df.shape)\n", "# clusters_pca4_kmedoids - cluster 1, 5678 profiles\n", "\n", "# schema = 'a441ffb172866cb4928c84a73de403ca15da4a54cc535e704413ab621' # unique customer schema id\n", "\n", "# df = superfans_df\n", "# df = optin_clusters_df.loc[optin_clusters_df['cluster'].isin([5])]\n", "# df = optin_clusters_df.loc[optin_clusters_df['cluster'].isin([1])]\n", "# df = merch_clusters_df.loc[merch_clusters_df['cluster'].isin([5])]\n", "df = grouped_df\n", "\n", "print(df.shape)\n", "\n", "df[['fan_id']].to_sql(f'fblist_temporary',\n", " engine,\n", " schema=schema,\n", " if_exists='replace',\n", " # index_label='fan_id', # TODO! duplicates need to be handled properly\n", " # index=False\n", " )\n", "join_part = f\"JOIN {schema}.fblist_temporary j on ft.fan_id = j.fan_id\"" ] }, { "cell_type": "code", "execution_count": 151, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "(291, 9)\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", "
emailfnlndobdobyagegencountrycity
0garry@utopia.com.auGarryStapletonNaNNaNNaNMAUSydney
1zacduck4@gmail.comZacCiccarelloNaNNaNNaNMAUTorrensville
2nickjdeveril@msn.comNicholasDeverilNaNNaNNaNMAUYarrawonga
3luke_meli@hotmail.comLukeNaNNaNNaNNaNMNaNNaN
4ethi69@hotmail.comStevenNaNNaNNaNNaNMNaNNaN
\n", "
" ], "text/plain": [ " email fn ln dob doby age gen country \\\n", "0 garry@utopia.com.au Garry Stapleton NaN NaN NaN M AU \n", "1 zacduck4@gmail.com Zac Ciccarello NaN NaN NaN M AU \n", "2 nickjdeveril@msn.com Nicholas Deveril NaN NaN NaN M AU \n", "3 luke_meli@hotmail.com Luke NaN NaN NaN NaN M NaN \n", "4 ethi69@hotmail.com Steven NaN NaN NaN NaN M NaN \n", "\n", " city \n", "0 Sydney \n", "1 Torrensville \n", "2 Yarrawonga \n", "3 NaN \n", "4 NaN " ] }, "execution_count": 151, "metadata": {}, "output_type": "execute_result" } ], "source": [ "fb_df = get_facebook_files(schema, join_part)\n", "fb_df.to_csv('IHW/fb_merch_cluster_grouped.csv', sep=',')\n", "fb_df.head(5)" ] }, { "cell_type": "code", "execution_count": 21, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "(291, 6)\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "/home/ec2-user/anaconda3/envs/python3/lib/python3.6/site-packages/ipykernel/__main__.py:126: FutureWarning: The pandas.np module is deprecated and will be removed from pandas in a future version. Import numpy directly instead\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", "
EmailPhoneFirst NameLast NameCountryZip
0garry@utopia.com.au+610295716662garrystapletonAUNaN
1zacduck4@gmail.comNaNzacciccarelloAUNaN
2nickjdeveril@msn.com+610466654417nicholasdeverilAUNaN
3luke_meli@hotmail.comNaNlukeNaNNaNNaN
4ethi69@hotmail.comNaNstevenNaNNaNNaN
\n", "
" ], "text/plain": [ " Email Phone First Name Last Name Country Zip\n", "0 garry@utopia.com.au +610295716662 garry stapleton AU NaN\n", "1 zacduck4@gmail.com NaN zac ciccarello AU NaN\n", "2 nickjdeveril@msn.com +610466654417 nicholas deveril AU NaN\n", "3 luke_meli@hotmail.com NaN luke NaN NaN NaN\n", "4 ethi69@hotmail.com NaN steven NaN NaN NaN" ] }, "execution_count": 21, "metadata": {}, "output_type": "execute_result" } ], "source": [ "google_df = get_google_files(schema, join_part, hash_data=False)\n", "google_df.to_csv('IHW/google_merch_cluster_grouped_unhashed.csv', sep=',')\n", "google_df.head(5)" ] }, { "cell_type": "code", "execution_count": 62, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "(37688, 7)\n", " email first_name last_name country city \\\n", "0 tim.online.89@gmail.com Tim Arthars AU Noosaville \n", "1 troy--mcfarlane@hotmail.com NaN Mcfarlane AU Caringbah \n", "2 hannahmarshall16@gmail.com Hannah Marshall AU Warragul \n", "3 jackmcdonvld@gmail.com NaN NaN NaN NaN \n", "4 chooksbum@hotmail.com NaN Walsh NaN NaN \n", "\n", " administrative_area_level_1 administrative_area_level_2 \n", "0 Queensland Noosa Shire \n", "1 New South Wales Sutherland Shire \n", "2 Victoria Baw Baw Shire \n", "3 NaN NaN \n", "4 NaN NaN \n" ] } ], "source": [ "df = get_master_deduped(schema)\n", "df.to_csv('IHW/IHW_deduped.csv', sep=',')\n", "df.head(5)" ] } ], "metadata": { "kernelspec": { "display_name": "conda_python3", "language": "python", "name": "conda_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.6.10" } }, "nbformat": 4, "nbformat_minor": 4 }