{"cells":[{"metadata":{"_cell_guid":"3b594320-8094-c257-7d7e-ed603b9bf591","_uuid":"fc3cc5087374a0942c1bb8e090974ad9ccd4c10c"},"cell_type":"markdown","source":"The methods used to generate the filter terms involved looking at the most frequent words/client names for clues about what types of establishments were mentioned in this data set. \n\nDeveloping the filters took a fair amount of \"human\" sleuthing while looking at TF-IDF scores, frequency counts of Client names, as well as just general knowledge of common Spanish words used to refer to certain establishment types. Especially with all the noise provided by the Clients referred to only by a proper name, the filtered data is a proportionally small figure- but, significant."},{"metadata":{"_cell_guid":"ea1ed3af-7c78-af45-48eb-63318a2b7442","_uuid":"43e28800fb7fe92a5ed3e86cee789450c825a3fa","trusted":false},"cell_type":"code","source":"# This Python 3 environment comes with many helpful analytics libraries installed\n# It is defined by the kaggle/python docker image: https://github.com/kaggle/docker-python\n# For example, here's several helpful packages to load in \n\nimport numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\n\n# Input data files are available in the \"../input/\" directory.\n# For example, running this (by clicking run or pressing Shift+Enter) will list the files in the input directory\n\nfrom subprocess import check_output\n#print(check_output([\"ls\", \"../input\"]).decode(\"utf8\"))\n\n# Any results you write to the current directory are saved as output.\n\n# Load in the Client Name data\n# Make sure all names uppercase (there are some mixed instances)\npd.set_option('display.max_rows', 50)\nvf = pd.read_csv('../input/cliente_tabla.csv',header=0)\nvf['NombreCliente'] = vf['NombreCliente'].str.upper()","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"bd0d3505-f09a-a87a-cd21-2eed0ab908a9","_uuid":"5e5fa169c76dd9bf0fc2be291fbcab6c7189114a","trusted":false},"cell_type":"code","source":"# Begin with a scan of the Client Name Data based on Top Frequency Client Names\n# Notice there are a lot of Proper Names\nvf['NombreCliente'].value_counts()[0:200]","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"90d7e98e-d2d2-bdb1-2ea9-cfeb284fff69","_uuid":"ce0a79f1ca2193b9985c39e4941beaf4d6826516","trusted":false},"cell_type":"code","source":"# Let's also generate a list of individual word frequency across all names\ndef tfidf_score_list(vf2, list_len):\n    from sklearn.feature_extraction.text import TfidfVectorizer\n    v = TfidfVectorizer()\n\n    vf2['New'] = 'na'\n    a = \" \".join(vf2['NombreCliente'])\n    vf2['New'][0] = a\n\n    tfidf = v.fit_transform(vf2['New'])\n\n    feature_names = v.get_feature_names()\n\n    freq = []\n    doc = 0\n    feature_index = tfidf[doc,:].nonzero()[1]\n    tfidf_scores = zip(feature_index, [tfidf[doc, x] for x in feature_index])\n    for w, s in [(feature_names[i], s) for (i, s) in tfidf_scores]:\n            freq.append((w.encode('utf-8'),s))\n    \n    del vf2['New']\n    \n    import numpy as np\n    names = ['word','score']\n    formats = ['S50','f8']\n    dtype = dict(names = names, formats=formats)\n    array = np.array(freq, dtype=dtype)\n\n    b = np.sort(array, order='score')\n    \n    if list_len > len(b)+1:\n        list_len = len(b)+1\n    for i in range(1,list_len):\n        print(b[-i])","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"df442457-7ff7-9fe3-c98f-cb3364c8abbd","_uuid":"9112d3974861eedfa99dae3e481d642a7d695599","trusted":false},"cell_type":"code","source":"tfidf_score_list(vf, 200)","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"b0b380ad-daeb-d162-3afa-cda2beb1f238","_uuid":"886e8d0198279252658857a5ad3bb1b7e9b22de1"},"cell_type":"markdown","source":"Again, notice the prevalence of personal names. By looking at a long enough list, however, we can start to see some other useful terms appear such as particles of speech (i.e. 'ag', 'los', 'san') or more useful words like \"super\", \"oxxo\", \"mini\", \"comodin\". If I found a word that I thought represent a type of establishment, I'd double check it by doing a single search. If that search was fruitful and had a good amount of relevant results, I'd add it to my filter. An example of a single term search is below:"},{"metadata":{"_cell_guid":"f99fdec9-2183-336b-4823-a12415cf0faa","_uuid":"1047cb3c93526cee27dc1ec760a058f1d2e277ea","trusted":false},"cell_type":"code","source":"print(vf[vf['NombreCliente'].str.contains('.*CAFE.*')])","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"de47e9ea-9257-3cd1-8fea-ae1e558571d8","_uuid":"421676986f7a00a40ad639d276104d782aae330b"},"cell_type":"markdown","source":"The result is a filter derived from hand-picking the best, most common, most interesting terms I could determine."},{"metadata":{"_cell_guid":"15b70b9a-5eee-60fe-30c6-734f2427e354","_uuid":"63f6e9d96eb2696f700a0068451e7a696d946e57","trusted":false},"cell_type":"code","source":"# --- Begin Filtering for specific terms\n\n# Note that the order of filtering is significant.\n# For example: \n# The regex of .*ERIA.* will assign \"FRUITERIA\" to 'Eatery' rather than 'Fresh Market'.\n# In other words, the first filters to occur have a bigger priority.\n\ndef filter_specific(vf2):\n    \n    # Known Large Company / Special Group Types\n    vf2['NombreCliente'] = vf2['NombreCliente'].str.replace('.*REMISION.*','Consignment')\n    vf2['NombreCliente'] = vf2['NombreCliente'].replace(['.*WAL MART.*','.*SAMS CLUB.*'],'Walmart', regex=True)\n    vf2['NombreCliente'] = vf2['NombreCliente'].str.replace('.*OXXO.*','Oxxo Store')\n    vf2['NombreCliente'] = vf2['NombreCliente'].str.replace('.*CONASUPO.*','Govt Store')\n    vf2['NombreCliente'] = vf2['NombreCliente'].str.replace('.*BIMBO.*','Bimbo Store')\n\n    \n\n    # General term search for a random assortment of words I picked from looking at\n    # their frequency of appearance in the data and common spanish words for these categories\n    vf2['NombreCliente'] = vf2['NombreCliente'].replace(['.*COLEG.*','.*UNIV.*','.*ESCU.*','.*INSTI.*',\\\n                                                        '.*PREPAR.*'],'School', regex=True)\n    vf2['NombreCliente'] = vf2['NombreCliente'].str.replace('.*PUESTO.*','Post')\n    vf2['NombreCliente'] = vf2['NombreCliente'].replace(['.*FARMA.*','.*HOSPITAL.*','.*CLINI.*'],'Hospital/Pharmacy', regex=True)\n    vf2['NombreCliente'] = vf2['NombreCliente'].replace(['.*CAFE.*','.*CREMERIA.*','.*DULCERIA.*',\\\n                                                        '.*REST.*','.*BURGER.*','.*TACO.*', '.*TORTA.*',\\\n                                                        '.*TAQUER.*','.*HOT DOG.*',\\\n                                                        '.*COMEDOR.*', '.*ERIA.*','.*BURGU.*'],'Eatery', regex=True)\n    vf2['NombreCliente'] = vf2['NombreCliente'].str.replace('.*SUPER.*','Supermarket')\n    vf2['NombreCliente'] = vf2['NombreCliente'].replace(['.*COMERCIAL.*','.*BODEGA.*','.*DEPOSITO.*',\\\n                                                            '.*ABARROTES.*','.*MERCADO.*','.*CAMBIO.*',\\\n                                                        '.*MARKET.*','.*MART .*','.*MINI .*',\\\n                                                        '.*PLAZA.*','.*MISC.*','.*ELEVEN.*','.*EXP.*',\\\n                                                         '.*SNACK.*', '.*PAPELERIA.*', '.*CARNICERIA.*',\\\n                                                         '.*LOCAL.*','.*COMODIN.*','.*PROVIDENCIA.*'\n                                                        ],'General Market/Mart'\\\n                                                       , regex=True)\n\n    vf2['NombreCliente'] = vf2['NombreCliente'].replace(['.*VERDU.*','.*FRUT.*'],'Fresh Market', regex=True)\n    vf2['NombreCliente'] = vf2['NombreCliente'].replace(['.*HOTEL.*','.*MOTEL.*'],'Hotel', regex=True)","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"75fe8a16-850a-58f2-05fc-aa1d57230a18","_uuid":"4f7d4457c834b3b33cae4cbbd91ada126a525bef","trusted":false},"cell_type":"code","source":"filter_specific(vf)","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"11333ead-d5e9-d2dd-42c0-c76a37557c3f","_uuid":"dbe7c510fbad6770b466be77bcc392a7b5d0727a","trusted":false},"cell_type":"code","source":"# --- Begin filtering for more general terms\n# The idea here is to look for names with particles of speech that would\n# not appear in a person's name.\n# i.e. \"Individuals\" should not contain any participles or numbers in their names.\ndef filter_participle(vf2):\n    vf2['NombreCliente'] = vf2['NombreCliente'].replace([\n            '.*LA .*','.*EL .*','.*DE .*','.*LOS .*','.*DEL .*','.*Y .*', '.*SAN .*', '.*SANTA .*',\\\n            '.*AG .*','.*LAS .*','.*MI .*','.*MA .*', '.*II.*', '.*[0-9]+.*'\\\n    ],'Small Franchise', regex=True)","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"d8d886b1-f9f3-aa3b-969b-fc87af86a3b7","_uuid":"0ac80734ff745521a7e18084a8e2f72fcee0deee","trusted":false},"cell_type":"code","source":"filter_participle(vf)","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"cbb3224c-176d-44f7-a836-7d679d125670","_uuid":"42efee0170e935984600569edea7da830033b923","trusted":false},"cell_type":"code","source":"# Any remaining entries should be \"Individual\" Named Clients, there are some outliers.\n# More specific filters could be used in order to reduce the percentage of outliers in this final set.\ndef filter_remaining(vf2):\n    def function_word(data):\n        # Avoid the single-words created so far by checking for upper-case\n        if (data.isupper()) and (data != \"NO IDENTIFICADO\"): \n            return 'Individual'\n        else:\n            return data\n    vf2['NombreCliente'] = vf2['NombreCliente'].map(function_word)","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"2183458d-79c4-f1ab-c95c-8b0c2d6b1245","_uuid":"78bc28dda3e1ef59440a35ee2615e69c02211892","trusted":false},"cell_type":"code","source":"filter_remaining(vf)","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"246e11a0-1f67-a9f1-ddba-8952ca5e1ae6","_uuid":"f830cd7571194717eba8a50f5893851bfb0528fc"},"cell_type":"markdown","source":"With the filtering complete, let's look at the breakdown of how the data is classified now:"},{"metadata":{"_cell_guid":"95fff309-9d78-da69-a9a1-fe0a804910f0","_uuid":"af561afbb0c7d779cd6c7f520e871e7eb8d43cc2","trusted":false},"cell_type":"code","source":"vf['NombreCliente'].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"42eab5d7-6f9d-330c-d3a2-0889a48b56a6","_uuid":"2ad895a2eccb8847dd5f667d842b858ddcde404b"},"cell_type":"markdown","source":"Finally, we can apply these new tags on the actual Training and Test data sets that have been provided!"},{"metadata":{"_cell_guid":"a9a0c462-aa96-19dd-da6b-24b62210bd2a","_uuid":"596e6cfb7c9a8a50c6d73ca5e9c52f27401544d3","trusted":false},"cell_type":"code","source":"#trdf = pd.read_csv('../input/train.csv',header=0)\n#trdf_stores = trdf.merge(vf.drop_duplicates(subset=['Cliente_ID']), how=\"left\")","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"1fdc94cd-eca3-4f3b-ad3b-467ea796b08e","_uuid":"55f057161c70b0b635f7adf80f804f107c067a75","trusted":false},"cell_type":"code","source":"#tsdf = pd.read_csv('../input/test.csv',header=0)\n#tsdf_stores = tsdf.merge(vf.drop_duplicates(subset=['Cliente_ID']), how=\"left\")","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"a81b2b30-2d17-2e23-d2c2-da5e31569a5b","_uuid":"bff189ad308c20ef8ec82a69b9eb723c5f8e52d8"},"cell_type":"markdown","source":"Write the data to file to save it for a new session...."},{"metadata":{"_cell_guid":"6c2b467e-a4e4-8baa-ca6c-624ab7bcd097","_uuid":"1a35b22213ea4a620dc6f345543bf7c2afeb330c","trusted":false},"cell_type":"code","source":"#trdf.to_csv('../output/train_with_cnames.csv')\n#tsdf.to_csv('../output/test_with_cnames.csv')","execution_count":null,"outputs":[]}],"metadata":{"_change_revision":0,"_is_fork":false,"kernelspec":{"display_name":"Python 3","language":"python","name":"python3"},"language_info":{"name":"python","version":"3.6.6","mimetype":"text/x-python","codemirror_mode":{"name":"ipython","version":3},"pygments_lexer":"ipython3","nbconvert_exporter":"python","file_extension":".py"}},"nbformat":4,"nbformat_minor":1}