{ "cells": [ { "cell_type": "markdown", "metadata": { "_cell_guid": "43de0fd2-4d3f-c80e-4f35-e3a89ce9d71f" }, "source": [ "# Detailed Data Cleaning/Visualization\n", "\n", "*A blog post about the final end-to-end solution (21st place) is available [here](http://alanpryorjr.com), and the source code is [on my github](https://github.com/apryor6/Kaggle-Competition-Santander)*\n", "\n", "*This is a Python version of a kernel I wrote in R for this dataset found [here](https://www.kaggle.com/apryor6/santander-product-recommendation/detailed-cleaning-visualization). There are some slight differences between how missing values are treated in Python and R, so the two kernels are not exactly the same, but I have tried to make them as similar as possible. This was done as a convenience to anybody who wanted to use my cleaned data as a starting point but prefers Python to R. It also is educational to compare how the same task can be accomplished in either language.*\n", "\n", "The goal of this competition is to predict which new Santander products, if any, a customer will purchase in the following month. Here, I will do some data cleaning, adjust some features, and do some visualization to get a sense of what features might be important predictors. I won't be building a predictive model in this kernel, but I hope this gives you some insight/ideas and gets you excited to build your own model.\n", "\n", "Let's get to it\n", "\n", "## First Glance\n", "Limit the number of rows read in to avoid memory crashes with the kernel" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "_cell_guid": "85ebf22f-2673-cf92-99f2-f0024d3d7983" }, "outputs": [], "source": [ "import numpy as np\n", "import pandas as pd\n", "import seaborn as sns\n", "import matplotlib.pyplot as plt\n", "%pylab inline\n", "pylab.rcParams['figure.figsize'] = (10, 6)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "_cell_guid": "a833c896-c0a7-86b1-dff7-cdf3325cebfb" }, "outputs": [], "source": [ "limit_rows = 7000000\n", "df = pd.read_csv(\"../input/train_ver2.csv\",dtype={\"sexo\":str,\n", " \"ind_nuevo\":str,\n", " \"ult_fec_cli_1t\":str,\n", " \"indext\":str}, nrows=limit_rows)\n", "unique_ids = pd.Series(df[\"ncodpers\"].unique())\n", "limit_people = 1.2e4\n", "unique_id = unique_ids.sample(n=limit_people)\n", "df = df[df.ncodpers.isin(unique_id)]\n", "df.describe()" ] }, { "cell_type": "markdown", "metadata": { "_cell_guid": "8d1a3836-0310-6a6f-94fc-2a9c2a4984cc" }, "source": [ "We have a number of demographics for each individual as well as the products they currently own. To make a test set, I will separate the last month from this training data, and create a feature that indicates whether or not a product was newly purchased. First convert the dates. There's `fecha_dato`, the row-identifier date, and `fecha_alta`, the date that the customer joined." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "_cell_guid": "9dfc376a-b836-94b4-03e3-bc3740b97aa8" }, "outputs": [], "source": [ "df[\"fecha_dato\"] = pd.to_datetime(df[\"fecha_dato\"],format=\"%Y-%m-%d\")\n", "df[\"fecha_alta\"] = pd.to_datetime(df[\"fecha_alta\"],format=\"%Y-%m-%d\")\n", "df[\"fecha_dato\"].unique()" ] }, { "cell_type": "markdown", "metadata": { "_cell_guid": "5e439040-c7d7-1b7f-2954-ffb535d2bf64" }, "source": [ "I printed the values just to double check the dates were in standard Year-Month-Day format. I expect that customers will be more likely to buy products at certain months of the year (Christmas bonuses?), so let's add a month column. I don't think the month that they joined matters, so just do it for one." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "_cell_guid": "b5e8429d-a0f3-b5a5-81d6-7e524b0a660c" }, "outputs": [], "source": [ "df[\"month\"] = pd.DatetimeIndex(df[\"fecha_dato\"]).month\n", "df[\"age\"] = pd.to_numeric(df[\"age\"], errors=\"coerce\")" ] }, { "cell_type": "markdown", "metadata": { "_cell_guid": "bdb7e954-d855-fd00-613e-1452067a8c99" }, "source": [ "Are there any columns missing values?" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "_cell_guid": "7c2e1189-ac6b-d1bb-48a4-472c1328485d" }, "outputs": [], "source": [ "df.isnull().any()" ] }, { "cell_type": "markdown", "metadata": { "_cell_guid": "87f2316f-9b93-13c8-7f22-a11eda6c7622" }, "source": [ "Definitely. Onto data cleaning.\n", "\n", "## Data Cleaning\n", "\n", "Going down the list, start with `age`" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "_cell_guid": "d0484da4-b8f3-a514-c441-eef972179098" }, "outputs": [], "source": [ "with sns.plotting_context(\"notebook\",font_scale=1.5):\n", " sns.set_style(\"whitegrid\")\n", " sns.distplot(df[\"age\"].dropna(),\n", " bins=80,\n", " kde=False,\n", " color=\"tomato\")\n", " sns.plt.title(\"Age Distribution\")\n", " plt.ylabel(\"Count\")" ] }, { "cell_type": "markdown", "metadata": { "_cell_guid": "c7e2eaae-4bef-d74f-ff3f-50322caf8bd3" }, "source": [ "In addition to NA, there are people with very small and very high ages.\n", "It's also interesting that the distribution is bimodal. There are a large number of university aged students, and then another peak around middle-age. Let's separate the distribution and move the outliers to the mean of the closest one." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "_cell_guid": "73e3143f-d51b-bd0a-a8cc-64178de70e4e" }, "outputs": [], "source": [ "df.loc[df.age < 18,\"age\"] = df.loc[(df.age >= 18) & (df.age <= 30),\"age\"].mean(skipna=True)\n", "df.loc[df.age > 100,\"age\"] = df.loc[(df.age >= 30) & (df.age <= 100),\"age\"].mean(skipna=True)\n", "df[\"age\"].fillna(df[\"age\"].mean(),inplace=True)\n", "df[\"age\"] = df[\"age\"].astype(int)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "_cell_guid": "1d867605-f48a-7109-afa0-de92b2d8fbdd" }, "outputs": [], "source": [ "with sns.plotting_context(\"notebook\",font_scale=1.5):\n", " sns.set_style(\"whitegrid\")\n", " sns.distplot(df[\"age\"].dropna(),\n", " bins=80,\n", " kde=False,\n", " color=\"tomato\")\n", " sns.plt.title(\"Age Distribution\")\n", " plt.ylabel(\"Count\")\n", " plt.xlim((15,100))" ] }, { "cell_type": "markdown", "metadata": { "_cell_guid": "6028f2ab-b7d5-920d-a313-e00776856f79" }, "source": [ "Looks better. \n", "\n", "Next `ind_nuevo`, which indicates whether a customer is new or not. How many missing values are there?" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "_cell_guid": "72710055-5dec-fafc-c712-27475eeb965b" }, "outputs": [], "source": [ "df[\"ind_nuevo\"].isnull().sum()" ] }, { "cell_type": "markdown", "metadata": { "_cell_guid": "a15606ec-bf21-afd6-d5cc-20b7ac0ebc42" }, "source": [ "Let's see if we can fill in missing values by looking how many months of history these customers have." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "_cell_guid": "e72879e7-8853-57da-5211-0bc07dc9baed" }, "outputs": [], "source": [ "months_active = df.loc[df[\"ind_nuevo\"].isnull(),:].groupby(\"ncodpers\", sort=False).size()\n", "months_active.max()" ] }, { "cell_type": "markdown", "metadata": { "_cell_guid": "2e52e713-8002-1ddc-feff-2785f7a97578" }, "source": [ "Looks like these are all new customers, so replace accordingly." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "_cell_guid": "672d6595-2ddc-3a34-48d8-c6f98fe3e0ef" }, "outputs": [], "source": [ "df.loc[df[\"ind_nuevo\"].isnull(),\"ind_nuevo\"] = 1" ] }, { "cell_type": "markdown", "metadata": { "_cell_guid": "07890c58-9e34-4d1a-e6b7-b63b3ab12943" }, "source": [ "Now, `antiguedad`" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "_cell_guid": "f5597d19-ba99-e4d6-36f7-8463b8199ff6" }, "outputs": [], "source": [ "df.antiguedad = pd.to_numeric(df.antiguedad,errors=\"coerce\")\n", "np.sum(df[\"antiguedad\"].isnull())" ] }, { "cell_type": "markdown", "metadata": { "_cell_guid": "78e8d734-464b-9ecf-3314-9cd0a8f1f28e" }, "source": [ "That number again. Probably the same people that we just determined were new customers. Double check." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "_cell_guid": "88314fe6-4eff-7d0b-8d43-e2c1ae315965" }, "outputs": [], "source": [ "df.loc[df[\"antiguedad\"].isnull(),\"ind_nuevo\"].describe()" ] }, { "cell_type": "markdown", "metadata": { "_cell_guid": "5f923230-78ca-a90b-09fa-650cbee30890" }, "source": [ "Yup, same people. Let's give them minimum seniority." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "_cell_guid": "561ac875-a3c8-e843-caf7-aeafbc977185" }, "outputs": [], "source": [ "df.loc[df.antiguedad.isnull(),\"antiguedad\"] = df.antiguedad.min()\n", "df.loc[df.antiguedad <0, \"antiguedad\"] = 0 # Thanks @StephenSmith for bug-find" ] }, { "cell_type": "markdown", "metadata": { "_cell_guid": "00eec8e6-c8c7-5d32-aa7d-43e536368bb1" }, "source": [ "Some entries don't have the date they joined the company. Just give them something in the middle of the pack" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "_cell_guid": "55f39399-dbb7-3b13-5393-f6ad89d4fe24" }, "outputs": [], "source": [ "dates=df.loc[:,\"fecha_alta\"].sort_values().reset_index()\n", "median_date = int(np.median(dates.index.values))\n", "df.loc[df.fecha_alta.isnull(),\"fecha_alta\"] = dates.loc[median_date,\"fecha_alta\"]\n", "df[\"fecha_alta\"].describe()" ] }, { "cell_type": "markdown", "metadata": { "_cell_guid": "bae7c9c5-a972-c397-4c1d-be0d06f7066e" }, "source": [ "Next is `indrel`, which indicates:\n", "\n", "> 1 (First/Primary), 99 (Primary customer during the month but not at the end of the month)\n", "\n", "This sounds like a promising feature. I'm not sure if primary status is something the customer chooses or the company assigns, but either way it seems intuitive that customers who are dropping down are likely to have different purchasing behaviors than others." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "_cell_guid": "9286511c-1f0d-5c7a-e762-6bad69180144" }, "outputs": [], "source": [ "pd.Series([i for i in df.indrel]).value_counts()" ] }, { "cell_type": "markdown", "metadata": { "_cell_guid": "1f57156d-a8fa-6470-83b5-38b1684ca409" }, "source": [ "Fill in missing with the more common status." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "_cell_guid": "4bd3465b-5eae-2743-5929-f5779909df2d" }, "outputs": [], "source": [ "df.loc[df.indrel.isnull(),\"indrel\"] = 1" ] }, { "cell_type": "markdown", "metadata": { "_cell_guid": "bf47c303-22a0-36b2-cd69-4598f6210e84" }, "source": [ "> tipodom\t- Addres type. 1, primary address\n", " cod_prov\t- Province code (customer's address)\n", "\n", "`tipodom` doesn't seem to be useful, and the province code is not needed because the name of the province exists in `nomprov`." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "_cell_guid": "bfbcd9e1-6e22-d742-9daf-d05a471f056a" }, "outputs": [], "source": [ "df.drop([\"tipodom\",\"cod_prov\"],axis=1,inplace=True)" ] }, { "cell_type": "markdown", "metadata": { "_cell_guid": "b8f66021-233e-eb7b-10db-58a059df9306" }, "source": [ "Quick check back to see how we are doing on missing values" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "_cell_guid": "96fc171a-6e3f-f6f9-93d0-c9f9e707d60b" }, "outputs": [], "source": [ "df.isnull().any()" ] }, { "cell_type": "markdown", "metadata": { "_cell_guid": "d85d2c58-b6cd-1928-a321-23467561c7d5" }, "source": [ "Getting closer." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "_cell_guid": "cc0707d4-cea8-174f-16b7-f8fd3f8b67b5" }, "outputs": [], "source": [ "np.sum(df[\"ind_actividad_cliente\"].isnull())" ] }, { "cell_type": "markdown", "metadata": { "_cell_guid": "bd3325ce-c4c9-3c01-c8c1-900093f05bbf" }, "source": [ "By now you've probably noticed that this number keeps popping up. A handful of the entries are just bad, and should probably just be excluded from the model. But for now I will just clean/keep them." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "_cell_guid": "afa661ef-5c01-ef33-3ac0-a7b35ecf2e27" }, "outputs": [], "source": [ "df.loc[df.ind_actividad_cliente.isnull(),\"ind_actividad_cliente\"] = \\\n", "df[\"ind_actividad_cliente\"].median()" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "_cell_guid": "b0400709-8b1d-1440-b940-d27ab17c528b" }, "outputs": [], "source": [ "df.nomprov.unique()" ] }, { "cell_type": "markdown", "metadata": { "_cell_guid": "568e9011-a39a-ff51-67d5-3a260588f636" }, "source": [ "There was an issue with the unicode character \u00f1 in [A Coru\u00f1a](https://en.wikipedia.org/wiki/A_Coru\u00f1a). I'll manually fix it, but if anybody knows a better way to catch cases like this I would be very glad to hear it in the comments." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "_cell_guid": "0dbadbd2-76be-0789-f745-803f93585318" }, "outputs": [], "source": [ "df.loc[df.nomprov==\"CORU\\xc3\\x91A, A\",\"nomprov\"] = \"CORUNA, A\"" ] }, { "cell_type": "markdown", "metadata": { "_cell_guid": "ca29776f-143b-d2e0-ef05-3e573e5af04b" }, "source": [ "There's some rows missing a city that I'll relabel" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "_cell_guid": "f2b0b646-b74a-8f51-d754-40e4c58c9578" }, "outputs": [], "source": [ "df.loc[df.nomprov.isnull(),\"nomprov\"] = \"UNKNOWN\"" ] }, { "cell_type": "markdown", "metadata": { "_cell_guid": "7acb976b-5e9a-51a6-dec2-422ef0b8d97d" }, "source": [ "Now for gross income, aka `renta`" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "_cell_guid": "9830d62e-2fd0-ae57-a616-2a58ef7c93e7" }, "outputs": [], "source": [ "df.renta.isnull().sum()" ] }, { "cell_type": "markdown", "metadata": { "_cell_guid": "7f3938b5-695f-2bc6-0145-37c81ef75be4" }, "source": [ "Here is a feature that is missing a lot of values. Rather than just filling them in with a median, it's probably more accurate to break it down region by region. To that end, let's take a look at the median income by region, and in the spirit of the competition let's color it like the Spanish flag." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "_cell_guid": "587bca44-b0ba-37a9-9b99-b715c690688d" }, "outputs": [], "source": [ "#df.loc[df.renta.notnull(),:].groupby(\"nomprov\").agg([{\"Sum\":sum},{\"Mean\":mean}])\n", "incomes = df.loc[df.renta.notnull(),:].groupby(\"nomprov\").agg({\"renta\":{\"MedianIncome\":median}})\n", "incomes.sort_values(by=(\"renta\",\"MedianIncome\"),inplace=True)\n", "incomes.reset_index(inplace=True)\n", "incomes.nomprov = incomes.nomprov.astype(\"category\", categories=[i for i in df.nomprov.unique()],ordered=False)\n", "incomes.head()" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "_cell_guid": "fd49def1-8da1-2d6f-15b8-73eb2c44a237" }, "outputs": [], "source": [ "with sns.axes_style({\n", " \"axes.facecolor\": \"#ffc400\",\n", " \"axes.grid\" : False,\n", " \"figure.facecolor\": \"#c60b1e\"}):\n", " h = sns.factorplot(data=incomes,\n", " x=\"nomprov\",\n", " y=(\"renta\",\"MedianIncome\"),\n", " order=(i for i in incomes.nomprov),\n", " size=6,\n", " aspect=1.5,\n", " scale=1.0,\n", " color=\"#c60b1e\",\n", " linestyles=\"None\")\n", "plt.xticks(rotation=90)\n", "plt.tick_params(labelsize=16,labelcolor=\"#ffc400\")#\n", "plt.ylabel(\"Median Income\",size=32,color=\"#ffc400\")\n", "plt.xlabel(\"City\",size=32,color=\"#ffc400\")\n", "plt.title(\"Income Distribution by City\",size=40,color=\"#ffc400\")\n", "plt.ylim(0,180000)\n", "plt.yticks(range(0,180000,40000))" ] }, { "cell_type": "markdown", "metadata": { "_cell_guid": "6edf053e-2c31-3f39-e870-62d0b4cd30ac" }, "source": [ "There's a lot of variation, so I think assigning missing incomes by providence is a good idea. First group the data by city, and reduce to get the median. This intermediate data frame is joined by the original city names to expand the aggregated median incomes, ordered so that there is a 1-to-1 mapping between the rows, and finally the missing values are replaced." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "_cell_guid": "6fbf3979-141d-b716-3ba7-830d4b845172" }, "outputs": [], "source": [ "grouped = df.groupby(\"nomprov\").agg({\"renta\":lambda x: x.median(skipna=True)}).reset_index()\n", "new_incomes = pd.merge(df,grouped,how=\"inner\",on=\"nomprov\").loc[:, [\"nomprov\",\"renta_y\"]]\n", "new_incomes = new_incomes.rename(columns={\"renta_y\":\"renta\"}).sort_values(\"renta\").sort_values(\"nomprov\")\n", "df.sort_values(\"nomprov\",inplace=True)\n", "df = df.reset_index()\n", "new_incomes = new_incomes.reset_index()" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "_cell_guid": "62958334-bca1-73e0-871e-20f70bed4867" }, "outputs": [], "source": [ "df.loc[df.renta.isnull(),\"renta\"] = new_incomes.loc[df.renta.isnull(),\"renta\"].reset_index()\n", "df.loc[df.renta.isnull(),\"renta\"] = df.loc[df.renta.notnull(),\"renta\"].median()\n", "df.sort_values(by=\"fecha_dato\",inplace=True)" ] }, { "cell_type": "markdown", "metadata": { "_cell_guid": "c5a83406-42f3-f518-224c-33cb63052cf6" }, "source": [ "The next columns with missing data I'll look at are features, which are just a boolean indicator as to whether or not that product was owned that month. Starting with `ind_nomina_ult1`.." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "_cell_guid": "4f280171-c0f3-c98e-bb35-ef903a9d9564" }, "outputs": [], "source": [ "df.ind_nomina_ult1.isnull().sum()" ] }, { "cell_type": "markdown", "metadata": { "_cell_guid": "cb5e1ac0-7de1-412d-f35c-72f20e2e8e7f" }, "source": [ "I could try to fill in missing values for products by looking at previous months, but since it's such a small number of values for now I'll take the cheap way out." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "_cell_guid": "2ab8cd9b-5ec4-4c99-4f84-c394b39790d8" }, "outputs": [], "source": [ "df.loc[df.ind_nomina_ult1.isnull(), \"ind_nomina_ult1\"] = 0\n", "df.loc[df.ind_nom_pens_ult1.isnull(), \"ind_nom_pens_ult1\"] = 0" ] }, { "cell_type": "markdown", "metadata": { "_cell_guid": "3ad7235d-0368-a84e-6b29-ce4323590eb8" }, "source": [ "There's also a bunch of character columns that contain empty strings. In R, these are kept as empty strings instead of NA like in pandas. I originally worked through the data with missing values first in R, so if you are wondering why I skipped some NA columns here that's why. I'll take care of them now. For the most part, entries with NA will be converted to an unknown category. \n", "First I'll get only the columns with missing values. Then print the unique values to determine what I should fill in with." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "_cell_guid": "299fe28e-8844-ed3d-d558-30986db1ee60" }, "outputs": [], "source": [ "string_data = df.select_dtypes(include=[\"object\"])\n", "missing_columns = [col for col in string_data if string_data[col].isnull().any()]\n", "for col in missing_columns:\n", " print(\"Unique values for {0}:\\n{1}\\n\".format(col,string_data[col].unique()))\n", "del string_data" ] }, { "cell_type": "markdown", "metadata": { "_cell_guid": "17dd06a2-0c4e-49cd-21ef-2cc8235d1706" }, "source": [ "Okay, based on that and the definitions of each variable, I will fill the empty strings either with the most common value or create an unknown category based on what I think makes more sense." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "_cell_guid": "c52777c3-06be-bf9f-0e33-3ae7e1b47d37" }, "outputs": [], "source": [ "df.loc[df.indfall.isnull(),\"indfall\"] = \"N\"\n", "df.loc[df.tiprel_1mes.isnull(),\"tiprel_1mes\"] = \"A\"\n", "df.tiprel_1mes = df.tiprel_1mes.astype(\"category\")\n", "\n", "# As suggested by @StephenSmith\n", "map_dict = { 1.0 : \"1\",\n", " \"1.0\" : \"1\",\n", " \"1\" : \"1\",\n", " \"3.0\" : \"3\",\n", " \"P\" : \"P\",\n", " 3.0 : \"3\",\n", " 2.0 : \"2\",\n", " \"3\" : \"3\",\n", " \"2.0\" : \"2\",\n", " \"4.0\" : \"4\",\n", " \"4\" : \"4\",\n", " \"2\" : \"2\"}\n", "\n", "df.indrel_1mes.fillna(\"P\",inplace=True)\n", "df.indrel_1mes = df.indrel_1mes.apply(lambda x: map_dict.get(x,x))\n", "df.indrel_1mes = df.indrel_1mes.astype(\"category\")\n", "\n", "\n", "unknown_cols = [col for col in missing_columns if col not in [\"indfall\",\"tiprel_1mes\",\"indrel_1mes\"]]\n", "for col in unknown_cols:\n", " df.loc[df[col].isnull(),col] = \"UNKNOWN\"" ] }, { "cell_type": "markdown", "metadata": { "_cell_guid": "1434f0ea-f1f6-8f61-bbf8-6cf299339fc3" }, "source": [ "Let's check back to see if we missed anything" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "_cell_guid": "28ee02b3-7e9c-0a66-3541-211ccd9dc34f" }, "outputs": [], "source": [ "df.isnull().any()" ] }, { "cell_type": "markdown", "metadata": { "_cell_guid": "51cfa3e9-cdb6-a949-ef3c-7f282ccda69b" }, "source": [ "Convert the feature columns into integer values (you'll see why in a second), and we're done cleaning" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "_cell_guid": "2785dd85-e477-c2ca-b726-03fa317e32c2" }, "outputs": [], "source": [ "feature_cols = df.iloc[:1,].filter(regex=\"ind_+.*ult.*\").columns.values\n", "for col in feature_cols:\n", " df[col] = df[col].astype(int)" ] }, { "cell_type": "markdown", "metadata": { "_cell_guid": "170d7959-9731-5fe4-b2ca-9fb4b81b3005" }, "source": [ "Now for the main event. To study trends in customers adding or removing services, I will create a label for each product and month that indicates whether a customer added, dropped or maintained that service in that billing cycle. I will do this by assigning a numeric id to each unique time stamp, and then matching each entry with the one from the previous month. The difference in the indicator value for each product then gives the desired value. " ] }, { "cell_type": "code", "execution_count": null, "metadata": { "_cell_guid": "684f824a-e5a5-abbc-4082-350b453e9a14" }, "outputs": [], "source": [ "unique_months = pd.DataFrame(pd.Series(df.fecha_dato.unique()).sort_values()).reset_index(drop=True)\n", "unique_months[\"month_id\"] = pd.Series(range(1,1+unique_months.size)) # start with month 1, not 0 to match what we already have\n", "unique_months[\"month_next_id\"] = 1 + unique_months[\"month_id\"]\n", "unique_months.rename(columns={0:\"fecha_dato\"},inplace=True)\n", "df = pd.merge(df,unique_months,on=\"fecha_dato\")" ] }, { "cell_type": "markdown", "metadata": { "_cell_guid": "1ee1e571-0483-a2f3-3804-5f2576bf4536" }, "source": [ "Now I'll build a function that will convert differences month to month into a meaningful label. Each month, a customer can either maintain their current status with a particular product, add it, or drop it." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "_cell_guid": "659fc59d-dc8f-a4e6-eefd-a172b9577298" }, "outputs": [], "source": [ "def status_change(x):\n", " diffs = x.diff().fillna(0)# first occurrence will be considered Maintained, \n", " #which is a little lazy. A better way would be to check if \n", " #the earliest date was the same as the earliest we have in the dataset\n", " #and consider those separately. Entries with earliest dates later than that have \n", " #joined and should be labeled as \"Added\"\n", " label = [\"Added\" if i==1 \\\n", " else \"Dropped\" if i==-1 \\\n", " else \"Maintained\" for i in diffs]\n", " return label" ] }, { "cell_type": "markdown", "metadata": { "_cell_guid": "88d69db7-acce-6278-923a-4da1f35f3a73" }, "source": [ "Now we can actually apply this function to each features using `groupby` followed by `transform` to broadcast the result back" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "_cell_guid": "10f17b58-f5b6-bb7b-b822-c1c6db4d09e7" }, "outputs": [], "source": [ "# df.loc[:, feature_cols] = df..groupby(\"ncodpers\").apply(status_change)\n", "df.loc[:, feature_cols] = df.loc[:, [i for i in feature_cols]+[\"ncodpers\"]].groupby(\"ncodpers\").transform(status_change)" ] }, { "cell_type": "markdown", "metadata": { "_cell_guid": "21bba2fd-d0c4-8e1f-63de-0fe876f4e52e" }, "source": [ "I'm only interested in seeing what influences people adding or removing services, so I'll trim away any instances of \"Maintained\"." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "_cell_guid": "6ec85399-7147-45fb-97ac-cd4b3f197e01" }, "outputs": [], "source": [ "df = pd.melt(df, id_vars = [col for col in df.columns if col not in feature_cols],\n", " value_vars= [col for col in feature_cols])\n", "df = df.loc[df.value!=\"Maintained\",:]\n", "df.shape" ] }, { "cell_type": "markdown", "metadata": { "_cell_guid": "f81baa4c-7ae8-9977-d82f-31ec937bac2b" }, "source": [ "And we're done! I hope you found this useful, and if you want to checkout the rest of visualizations I made you can find them [here](https://www.kaggle.com/apryor6/santander-product-recommendation/detailed-cleaning-visualization)." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "_cell_guid": "722ba4e2-01e8-385e-bf23-662fd908d496" }, "outputs": [], "source": [ "# For thumbnail\n", "pylab.rcParams['figure.figsize'] = (6, 4)\n", "with sns.axes_style({\n", " \"axes.facecolor\": \"#ffc400\",\n", " \"axes.grid\" : False,\n", " \"figure.facecolor\": \"#c60b1e\"}):\n", " h = sns.factorplot(data=incomes,\n", " x=\"nomprov\",\n", " y=(\"renta\",\"MedianIncome\"),\n", " order=(i for i in incomes.nomprov),\n", " size=6,\n", " aspect=1.5,\n", " scale=0.75,\n", " color=\"#c60b1e\",\n", " linestyles=\"None\")\n", "plt.xticks(rotation=90)\n", "plt.tick_params(labelsize=12,labelcolor=\"#ffc400\")#\n", "plt.ylabel(\"Median Income\",size=32,color=\"#ffc400\")\n", "plt.xlabel(\"City\",size=32,color=\"#ffc400\")\n", "plt.title(\"Income Distribution by City\",size=40,color=\"#ffc400\")\n", "plt.ylim(0,180000)\n", "plt.yticks(range(0,180000,40000))" ] } ], "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.6.0" } }, "nbformat": 4, "nbformat_minor": 0 }