{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"markdown","source":"# 25 Pandas Functions You Didn't Know Existed | P(Guarantee) = 0.8\n## ExcelWriter, factorize, explode, squeeze, T, mask, idxmax, clip, ...\n\n\n \n Photo by \n Erik Mclean\n on \n Pexels. All images are by the author unless specified otherwise.\n \n","metadata":{}},{"cell_type":"markdown","source":"## Setup","metadata":{"_kg_hide-input":true}},{"cell_type":"code","source":"import warnings\n\nimport matplotlib.pyplot as plt\nimport numpy as np\nimport pandas as pd\nimport seaborn as sns\n\npd.options.display.max_rows = None\nwarnings.filterwarnings(\"ignore\")","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-08-03T11:59:16.137637Z","iopub.execute_input":"2021-08-03T11:59:16.138013Z","iopub.status.idle":"2021-08-03T11:59:16.143536Z","shell.execute_reply.started":"2021-08-03T11:59:16.137983Z","shell.execute_reply":"2021-08-03T11:59:16.142304Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Introduction","metadata":{}},{"cell_type":"markdown","source":"\"I wish I could do this operation in Pandas….\"\n\nWell, chances are, you can!\n\nPandas is so vast and deep that it enables you to execute virtually any tabular manipulation you can think of. However, this vastness sometimes comes at a disadvantage.\n\nMany elegant features that solve rare edge-cases, unique scenarios are lost in the documentation, shadowed by the more frequently used functions.\n\nThis kernel aims to rediscover those features and show you that Pandas is more capable than you ever knew.","metadata":{}},{"cell_type":"markdown","source":"## 1. `ExcelWriter`","metadata":{}},{"cell_type":"markdown","source":"`ExcelWriter` is a generic class for creating excel files (with sheets!) and writing DataFrames to them. Let's say we have these 2:","metadata":{}},{"cell_type":"code","source":"# Load two datasets\ndiamonds = sns.load_dataset(\"diamonds\")\ntips = sns.load_dataset(\"tips\")","metadata":{"execution":{"iopub.status.busy":"2021-08-03T11:59:16.147951Z","iopub.execute_input":"2021-08-03T11:59:16.148272Z","iopub.status.idle":"2021-08-03T11:59:16.258364Z","shell.execute_reply.started":"2021-08-03T11:59:16.148244Z","shell.execute_reply":"2021-08-03T11:59:16.257363Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"```python\n# Write to the same excel file\nwith pd.ExcelWriter(\"data.xlsx\") as writer:\n\n diamonds.to_excel(writer, sheet_name=\"diamonds\")\n tips.to_excel(writer, sheet_name=\"tips\")\n```","metadata":{}},{"cell_type":"markdown","source":"It has additional attributes to specify the DateTime format to be used, whether you want to create a new excel file or modify an existing one, what happens when a sheet exists, etc. Check out the details from the [documentation](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.ExcelWriter.html).","metadata":{}},{"cell_type":"markdown","source":"## 2. `pipe`","metadata":{}},{"cell_type":"markdown","source":"\n\n \n Photo by \n Leah Kelley\n on \n Pexels\n \n","metadata":{}},{"cell_type":"markdown","source":"`pipe` is one of the best functions for doing data cleaning in a concise, compact manner in Pandas. It allows you to chain multiple custom functions into a single operation.\n\nFor example, let's say you have functions to `drop_duplicates`, `remove_outliers`, `encode_categoricals` that accept their own arguments. Here is how you apply all three in a single operation:","metadata":{}},{"cell_type":"markdown","source":"```python\ndf_preprocessed = (diamonds.pipe(drop_duplicates).\n pipe(remove_outliers, ['price', 'carat', 'depth']).\n pipe(encode_categoricals, ['cut', 'color', 'clarity'])\n )\n```","metadata":{}},{"cell_type":"markdown","source":"I like how this function resembles [Sklearn pipelines](https://towardsdatascience.com/how-to-use-sklearn-pipelines-for-ridiculously-neat-code-a61ab66ca90d). There is more you can do with it, so check out the [documentation](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.pipe.html) or this [helpful article](https://towardsdatascience.com/a-better-way-for-data-preprocessing-pandas-pipe-a08336a012bc).","metadata":{}},{"cell_type":"markdown","source":"## 3. `factorize`","metadata":{}},{"cell_type":"markdown","source":"This function is a pandas alternative to Sklearn's `LabelEncoder`:","metadata":{}},{"cell_type":"code","source":"# Mind the [0] at the end\ndiamonds[\"cut_enc\"] = pd.factorize(diamonds[\"cut\"])[0]\n\ndiamonds[\"cut_enc\"].sample(5)","metadata":{"execution":{"iopub.status.busy":"2021-08-03T11:59:16.260734Z","iopub.execute_input":"2021-08-03T11:59:16.261175Z","iopub.status.idle":"2021-08-03T11:59:16.272627Z","shell.execute_reply.started":"2021-08-03T11:59:16.261129Z","shell.execute_reply":"2021-08-03T11:59:16.271548Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Unlike `LabelEncoder`, `factorize` returns a tuple of two values: the encoded column and a list of the unique categories:","metadata":{}},{"cell_type":"code","source":"codes, unique = pd.factorize(diamonds[\"cut\"], sort=True)\n\ncodes[:10]","metadata":{"execution":{"iopub.status.busy":"2021-08-03T11:59:16.27401Z","iopub.execute_input":"2021-08-03T11:59:16.27441Z","iopub.status.idle":"2021-08-03T11:59:16.282782Z","shell.execute_reply.started":"2021-08-03T11:59:16.274381Z","shell.execute_reply":"2021-08-03T11:59:16.28166Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"unique","metadata":{"execution":{"iopub.status.busy":"2021-08-03T11:59:16.284158Z","iopub.execute_input":"2021-08-03T11:59:16.284488Z","iopub.status.idle":"2021-08-03T11:59:16.292938Z","shell.execute_reply.started":"2021-08-03T11:59:16.284457Z","shell.execute_reply":"2021-08-03T11:59:16.292167Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## 4. `explode` - 🤯🤯","metadata":{}},{"cell_type":"markdown","source":"\n\n \n Photo by \n Joshua Sukoff\n on \n Unsplash\n \n","metadata":{}},{"cell_type":"markdown","source":"A function with an interesting name is `explode`. Let's see an example first and then, explain:","metadata":{}},{"cell_type":"code","source":"data = pd.Series([1, 6, 7, [46, 56, 49], 45, [15, 10, 12]]).to_frame(\"dirty\")\ndata","metadata":{"execution":{"iopub.status.busy":"2021-08-03T11:59:16.294211Z","iopub.execute_input":"2021-08-03T11:59:16.294796Z","iopub.status.idle":"2021-08-03T11:59:16.313106Z","shell.execute_reply.started":"2021-08-03T11:59:16.294752Z","shell.execute_reply":"2021-08-03T11:59:16.312202Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"The `dirty` column has two rows where values are recorded as actual lists. You may often see this type of data in surveys as some questions accept multiple answers.","metadata":{}},{"cell_type":"code","source":"data.explode(\"dirty\", ignore_index=True)","metadata":{"execution":{"iopub.status.busy":"2021-08-03T11:59:16.314315Z","iopub.execute_input":"2021-08-03T11:59:16.314899Z","iopub.status.idle":"2021-08-03T11:59:16.33494Z","shell.execute_reply.started":"2021-08-03T11:59:16.314858Z","shell.execute_reply":"2021-08-03T11:59:16.334111Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"`explode` takes a cell with an array-like value and explodes it into multiple rows. Set `ignore_index` to True to keep the ordering of a numeric index.","metadata":{}},{"cell_type":"markdown","source":"## 5. `squeeze`","metadata":{}},{"cell_type":"markdown","source":"\n\n \n Photo by \n cottonbro\n on \n Pexels.\n \n","metadata":{}},{"cell_type":"markdown","source":"Another function with a funky name is `squeeze` and is used in very rare but annoying edge cases.\n\nOne of these cases is when a single value is returned from a condition used to subset a DataFrame. Consider this example:","metadata":{}},{"cell_type":"code","source":"subset = diamonds.loc[diamonds.index < 1, [\"price\"]]\nsubset","metadata":{"execution":{"iopub.status.busy":"2021-08-03T11:59:16.336512Z","iopub.execute_input":"2021-08-03T11:59:16.336903Z","iopub.status.idle":"2021-08-03T11:59:16.355926Z","shell.execute_reply.started":"2021-08-03T11:59:16.336875Z","shell.execute_reply":"2021-08-03T11:59:16.354683Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Even though there is just one cell, it is returned as a DataFrame. This can be annoying since you now have to use `.loc` again with both the column name and index to access the price.\n\nBut, if you know `squeeze`, you don't have to. The function enables you to remove an axis from a single-cell DataFrame or Series. For example:","metadata":{}},{"cell_type":"code","source":"subset.squeeze()","metadata":{"execution":{"iopub.status.busy":"2021-08-03T11:59:16.358204Z","iopub.execute_input":"2021-08-03T11:59:16.358681Z","iopub.status.idle":"2021-08-03T11:59:16.365754Z","shell.execute_reply.started":"2021-08-03T11:59:16.358626Z","shell.execute_reply":"2021-08-03T11:59:16.364719Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Now, only the scalar is returned. It is also possible to specify the axis to remove:","metadata":{}},{"cell_type":"code","source":"subset.squeeze(\"columns\") # or \"rows\"","metadata":{"execution":{"iopub.status.busy":"2021-08-03T11:59:16.36705Z","iopub.execute_input":"2021-08-03T11:59:16.367355Z","iopub.status.idle":"2021-08-03T11:59:16.379728Z","shell.execute_reply.started":"2021-08-03T11:59:16.367317Z","shell.execute_reply":"2021-08-03T11:59:16.378627Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Note that `squeeze` only works for DataFrames or Series with single values.","metadata":{}},{"cell_type":"markdown","source":"## 6. between","metadata":{}},{"cell_type":"markdown","source":"\n\n \n Photo by \n Justin Dream\n on \n Pexels\n \n","metadata":{}},{"cell_type":"markdown","source":"A rather nifty function for boolean indexing numeric features within a range:","metadata":{}},{"cell_type":"code","source":"# Get diamonds that are priced between 3500 and 3700 dollars\ndiamonds[diamonds[\"price\"].between(3500, 3700, inclusive=\"neither\")].sample(5)","metadata":{"execution":{"iopub.status.busy":"2021-08-03T11:59:16.381663Z","iopub.execute_input":"2021-08-03T11:59:16.382192Z","iopub.status.idle":"2021-08-03T11:59:16.412036Z","shell.execute_reply.started":"2021-08-03T11:59:16.382146Z","shell.execute_reply":"2021-08-03T11:59:16.41092Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## 7. `T`","metadata":{}},{"cell_type":"markdown","source":"\n\n \n Photo by \n Pixabay\n on \n Pexels\n \n","metadata":{}},{"cell_type":"markdown","source":"All DataFrames have a simple `T` attribute, which stands for transpose. You may not use it often, but I find it quite useful when displaying DataFrames of the `describe` method:","metadata":{}},{"cell_type":"code","source":"## HIDE\nfrom sklearn.datasets import load_boston\n\nbunch = load_boston()\nboston = pd.DataFrame(bunch[\"data\"], columns=bunch[\"feature_names\"])","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-08-03T11:59:16.414555Z","iopub.execute_input":"2021-08-03T11:59:16.41501Z","iopub.status.idle":"2021-08-03T11:59:16.43176Z","shell.execute_reply.started":"2021-08-03T11:59:16.414965Z","shell.execute_reply":"2021-08-03T11:59:16.430825Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"boston.describe().T.head(10)","metadata":{"execution":{"iopub.status.busy":"2021-08-03T11:59:16.433323Z","iopub.execute_input":"2021-08-03T11:59:16.433931Z","iopub.status.idle":"2021-08-03T11:59:16.484095Z","shell.execute_reply.started":"2021-08-03T11:59:16.433886Z","shell.execute_reply":"2021-08-03T11:59:16.483145Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"The Boston housing dataset has 30 numeric columns. If you call `describe` as-is, the DataFrame will stretch horizontally, making it hard to compare the statistics. Taking the transpose will switch the axes so that summary statistics are given in columns.","metadata":{}},{"cell_type":"markdown","source":"## 8. Pandas Styler","metadata":{}},{"cell_type":"markdown","source":"Did you know that Pandas allows you to style DataFrames?\n\nThey have a `style` attribute which opens doors to customizations and styles only limited by your HTML and CSS knowledge. I won't discuss the full details of what you can do with `style` but only show you my favorite functions:","metadata":{}},{"cell_type":"code","source":"## HIDE\ndiabetes = pd.read_csv(\"https://raw.githubusercontent.com/BexTuychiev/medium_stories/master/2021/august/1_pandas_funcs/data/diabetes.csv\")","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-08-03T11:59:16.485415Z","iopub.execute_input":"2021-08-03T11:59:16.485778Z","iopub.status.idle":"2021-08-03T11:59:16.615198Z","shell.execute_reply.started":"2021-08-03T11:59:16.485745Z","shell.execute_reply":"2021-08-03T11:59:16.614186Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"diabetes.describe().T.drop(\"count\", axis=1).style.highlight_max(color=\"darkred\")","metadata":{"execution":{"iopub.status.busy":"2021-08-03T11:59:16.616413Z","iopub.execute_input":"2021-08-03T11:59:16.616742Z","iopub.status.idle":"2021-08-03T11:59:16.664083Z","shell.execute_reply.started":"2021-08-03T11:59:16.616712Z","shell.execute_reply":"2021-08-03T11:59:16.663226Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Above, we are highlighting cells that hold the maximum value of a column. Another cool styler is `background_gradient` which can give columns a gradient background color based on their values:","metadata":{}},{"cell_type":"code","source":"diabetes.describe().T.drop(\"count\", axis=1).style.background_gradient(\n subset=[\"mean\", \"50%\"], cmap=\"Reds\"\n)","metadata":{"execution":{"iopub.status.busy":"2021-08-03T11:59:16.665196Z","iopub.execute_input":"2021-08-03T11:59:16.665663Z","iopub.status.idle":"2021-08-03T11:59:16.705767Z","shell.execute_reply.started":"2021-08-03T11:59:16.665598Z","shell.execute_reply":"2021-08-03T11:59:16.704987Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"This feature comes especially handy when you are using `describe` on a table with many columns and want to compare summary statistics. Check out the documentation of the styler [here](https://pandas.pydata.org/docs/reference/style.html).","metadata":{}},{"cell_type":"markdown","source":"## 9. Pandas options","metadata":{}},{"cell_type":"markdown","source":"Like Matplotlib, pandas has global settings that you can tweak to change the default behaviors:","metadata":{}},{"cell_type":"code","source":"dir(pd.options)","metadata":{"execution":{"iopub.status.busy":"2021-08-03T11:59:16.707771Z","iopub.execute_input":"2021-08-03T11:59:16.708192Z","iopub.status.idle":"2021-08-03T11:59:16.713746Z","shell.execute_reply.started":"2021-08-03T11:59:16.708148Z","shell.execute_reply":"2021-08-03T11:59:16.71269Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"These settings are divided into 5 modules. Let's see what settings are there under `display`:","metadata":{}},{"cell_type":"code","source":"dir(pd.options.display)","metadata":{"execution":{"iopub.status.busy":"2021-08-03T11:59:16.715776Z","iopub.execute_input":"2021-08-03T11:59:16.716121Z","iopub.status.idle":"2021-08-03T11:59:16.730707Z","shell.execute_reply.started":"2021-08-03T11:59:16.716093Z","shell.execute_reply":"2021-08-03T11:59:16.729625Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"There are options under `display` but I mostly use `max_columns` and `precision`:","metadata":{}},{"cell_type":"code","source":"# Remove the limit to display the number of cols\npd.options.display.max_columns = None\n\n# Only show 5 numbers after the decimal\npd.options.display.precision = 5 # gets rid of scientific notation","metadata":{"execution":{"iopub.status.busy":"2021-08-03T11:59:16.732148Z","iopub.execute_input":"2021-08-03T11:59:16.732464Z","iopub.status.idle":"2021-08-03T11:59:16.744672Z","shell.execute_reply.started":"2021-08-03T11:59:16.732405Z","shell.execute_reply":"2021-08-03T11:59:16.743533Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"You can check out the [documentation](https://pandas.pydata.org/pandas-docs/stable/user_guide/options.html) to dig deeper into this wonderful feature.","metadata":{}},{"cell_type":"markdown","source":"## 10. `convert_dtypes`","metadata":{}},{"cell_type":"markdown","source":"We all know that pandas has an annoying tendency to mark some columns as `object` data type. Instead of manually specifying their types, you can use `convert_dtypes` method which tries to infer the best data type:","metadata":{}},{"cell_type":"code","source":"sample = pd.read_csv(\n \"https://raw.githubusercontent.com/BexTuychiev/medium_stories/master/2021/august/1_pandas_funcs/data/station_day.csv\",\n usecols=[\"StationId\", \"CO\", \"O3\", \"AQI_Bucket\"],\n)\nsample.dtypes","metadata":{"execution":{"iopub.status.busy":"2021-08-03T11:59:16.746042Z","iopub.execute_input":"2021-08-03T11:59:16.746367Z","iopub.status.idle":"2021-08-03T11:59:17.075975Z","shell.execute_reply.started":"2021-08-03T11:59:16.746328Z","shell.execute_reply":"2021-08-03T11:59:17.075304Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sample.convert_dtypes().dtypes","metadata":{"execution":{"iopub.status.busy":"2021-08-03T11:59:17.077067Z","iopub.execute_input":"2021-08-03T11:59:17.077507Z","iopub.status.idle":"2021-08-03T11:59:17.122189Z","shell.execute_reply.started":"2021-08-03T11:59:17.077471Z","shell.execute_reply":"2021-08-03T11:59:17.121291Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Unfortunately, it can't pares dates due to the caveats of different date time formats.","metadata":{}},{"cell_type":"markdown","source":"## 11. `select_dtypes`","metadata":{}},{"cell_type":"markdown","source":"A function I use all the time is `select_dtypes`. I think it is obvious what the function does from its name. It has `include` and `exclude` parameters that you can use to select columns including or excluding certain data types.\n\nFor example, choose only numeric columns with `np.number`:","metadata":{}},{"cell_type":"code","source":"# Choose only numerical columns\ndiamonds.select_dtypes(include=np.number).head()","metadata":{"execution":{"iopub.status.busy":"2021-08-03T11:59:17.123448Z","iopub.execute_input":"2021-08-03T11:59:17.123731Z","iopub.status.idle":"2021-08-03T11:59:17.147313Z","shell.execute_reply.started":"2021-08-03T11:59:17.123704Z","shell.execute_reply":"2021-08-03T11:59:17.146405Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Or `exclude` them:","metadata":{}},{"cell_type":"code","source":"# Exclude numerical columns\ndiamonds.select_dtypes(exclude=np.number).head()","metadata":{"execution":{"iopub.status.busy":"2021-08-03T11:59:17.148572Z","iopub.execute_input":"2021-08-03T11:59:17.148845Z","iopub.status.idle":"2021-08-03T11:59:17.164779Z","shell.execute_reply.started":"2021-08-03T11:59:17.148818Z","shell.execute_reply":"2021-08-03T11:59:17.163614Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## 12. `mask`","metadata":{}},{"cell_type":"markdown","source":"\n\n \n Photo by \n Pixabay\n on \n Pexels.\n \n","metadata":{}},{"cell_type":"markdown","source":"`mask` allows you to quickly replace cell values where a custom condition is true. \n\nFor example, let's say we have a survey data collected from people aged 50-60.","metadata":{}},{"cell_type":"code","source":"# Create sample data\nages = pd.Series([55, 52, 50, 66, 57, 59, 49, 60]).to_frame(\"ages\")\n\nages","metadata":{"execution":{"iopub.status.busy":"2021-08-03T11:59:17.166169Z","iopub.execute_input":"2021-08-03T11:59:17.166533Z","iopub.status.idle":"2021-08-03T11:59:17.178284Z","shell.execute_reply.started":"2021-08-03T11:59:17.166503Z","shell.execute_reply":"2021-08-03T11:59:17.177285Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"We will treat ages that are outside 50-60 range (there are two, 49 and 66) as data entry mistakes and replace them with NaNs.","metadata":{}},{"cell_type":"code","source":"ages.mask(cond=~ages[\"ages\"].between(50, 60), other=np.nan)","metadata":{"execution":{"iopub.status.busy":"2021-08-03T11:59:17.179668Z","iopub.execute_input":"2021-08-03T11:59:17.179997Z","iopub.status.idle":"2021-08-03T11:59:17.201771Z","shell.execute_reply.started":"2021-08-03T11:59:17.179968Z","shell.execute_reply":"2021-08-03T11:59:17.200776Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"So, `mask` replaces values that don't meet `cond` with `other`.","metadata":{}},{"cell_type":"markdown","source":"## 13. `min` and `max` along the columns axis","metadata":{}},{"cell_type":"markdown","source":"Even though `min` and `max` functions are well-known, they have another useful property for some edge-cases. Consider this dataset:","metadata":{}},{"cell_type":"code","source":"index = [\"Diamonds\", \"Titanic\", \"Iris\", \"Heart Disease\", \"Loan Default\"]\nlibraries = [\"XGBoost\", \"CatBoost\", \"LightGBM\", \"Sklearn GB\"]\ndf = pd.DataFrame(\n {lib: np.random.uniform(90, 100, 5) for lib in libraries}, index=index\n)\n\ndf","metadata":{"tags":[],"execution":{"iopub.status.busy":"2021-08-03T11:59:17.203491Z","iopub.execute_input":"2021-08-03T11:59:17.204015Z","iopub.status.idle":"2021-08-03T11:59:17.223235Z","shell.execute_reply.started":"2021-08-03T11:59:17.203971Z","shell.execute_reply":"2021-08-03T11:59:17.222315Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"The above fake DataFrame is a point-performance of 4 different gradient boosting libraries on 5 datasets. We want to find the library that performed best at each dataset. Here is how you do it elegantly with `max`:","metadata":{}},{"cell_type":"code","source":"df.max(axis=1)","metadata":{"execution":{"iopub.status.busy":"2021-08-03T11:59:17.224984Z","iopub.execute_input":"2021-08-03T11:59:17.225667Z","iopub.status.idle":"2021-08-03T11:59:17.239609Z","shell.execute_reply.started":"2021-08-03T11:59:17.225602Z","shell.execute_reply":"2021-08-03T11:59:17.238568Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Just change the axis to 1 and you get a row-wise max/min. ","metadata":{}},{"cell_type":"markdown","source":"## 14. `nlargest` and `nsmallest`","metadata":{}},{"cell_type":"markdown","source":"Sometimes you don't just want the min/max of a column. You want to see the top N or ~(top N) values of a variable. This is where `nlargest` and `nsmallest` comes in handy.\n\nLet's see the top 5 most expensive and cheapest diamonds:","metadata":{}},{"cell_type":"code","source":"diamonds.nlargest(5, \"price\")","metadata":{"execution":{"iopub.status.busy":"2021-08-03T11:59:17.241114Z","iopub.execute_input":"2021-08-03T11:59:17.241974Z","iopub.status.idle":"2021-08-03T11:59:17.270834Z","shell.execute_reply.started":"2021-08-03T11:59:17.241931Z","shell.execute_reply":"2021-08-03T11:59:17.269851Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"diamonds.nsmallest(5, \"price\")","metadata":{"execution":{"iopub.status.busy":"2021-08-03T11:59:17.272237Z","iopub.execute_input":"2021-08-03T11:59:17.272826Z","iopub.status.idle":"2021-08-03T11:59:17.298461Z","shell.execute_reply.started":"2021-08-03T11:59:17.272779Z","shell.execute_reply":"2021-08-03T11:59:17.297328Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## 15. `idxmax` and `idxmin`","metadata":{}},{"cell_type":"markdown","source":"When you call `max` or `min` on a column, pandas returns the value that is largest/smallest. However, sometimes you want the *position* of the min/max, which is not possible with these functions.\n\nInstead, you should use `idxmax`/`idxmin`:","metadata":{}},{"cell_type":"code","source":"diamonds.price.idxmax()","metadata":{"execution":{"iopub.status.busy":"2021-08-03T11:59:17.299862Z","iopub.execute_input":"2021-08-03T11:59:17.300234Z","iopub.status.idle":"2021-08-03T11:59:17.307143Z","shell.execute_reply.started":"2021-08-03T11:59:17.300202Z","shell.execute_reply":"2021-08-03T11:59:17.306073Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"diamonds.carat.idxmin()","metadata":{"execution":{"iopub.status.busy":"2021-08-03T11:59:17.311978Z","iopub.execute_input":"2021-08-03T11:59:17.312325Z","iopub.status.idle":"2021-08-03T11:59:17.323406Z","shell.execute_reply.started":"2021-08-03T11:59:17.312297Z","shell.execute_reply":"2021-08-03T11:59:17.322502Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"You can also specify the `columns` axis, in which case the functions return the index number of the column.","metadata":{}},{"cell_type":"markdown","source":"## 16. `value_counts` with `dropna=False`","metadata":{}},{"cell_type":"markdown","source":"A common operation to find the percentage of missing values in a column is to chain `isnull` and `sum` and divide by the length of the array. \n\nBut, you can do the same thing with `value_counts` with relevant arguments:","metadata":{}},{"cell_type":"code","source":"ames_housing = pd.read_csv(\"../input/house-prices-advanced-regression-techniques/train.csv\")\n\names_housing[\"FireplaceQu\"].value_counts(dropna=False, normalize=True)","metadata":{"execution":{"iopub.status.busy":"2021-08-03T11:59:17.325348Z","iopub.execute_input":"2021-08-03T11:59:17.325765Z","iopub.status.idle":"2021-08-03T11:59:17.380141Z","shell.execute_reply.started":"2021-08-03T11:59:17.325735Z","shell.execute_reply":"2021-08-03T11:59:17.379372Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Fireplace quality of Ames housing dataset consists of 47% nulls.","metadata":{}},{"cell_type":"markdown","source":"## 17. `clip`","metadata":{}},{"cell_type":"markdown","source":"\n\n \n Photo by \n Ann H\n on \n Pexels\n","metadata":{}},{"cell_type":"markdown","source":"Outlier detection and removal is common in data analysis. \n\n`clip` function makes it really easy to find outliers outside a range and replacing them with the hard limits. \n\nLet's go back to the ages example:","metadata":{}},{"cell_type":"code","source":"ages","metadata":{"execution":{"iopub.status.busy":"2021-08-03T11:59:17.381142Z","iopub.execute_input":"2021-08-03T11:59:17.381559Z","iopub.status.idle":"2021-08-03T11:59:17.389633Z","shell.execute_reply.started":"2021-08-03T11:59:17.381529Z","shell.execute_reply":"2021-08-03T11:59:17.388864Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"This time, we will replace the out-of-range ages with the hard limits of 50 and 60:","metadata":{}},{"cell_type":"code","source":"ages.clip(50, 60)","metadata":{"execution":{"iopub.status.busy":"2021-08-03T11:59:17.390753Z","iopub.execute_input":"2021-08-03T11:59:17.391188Z","iopub.status.idle":"2021-08-03T11:59:17.412086Z","shell.execute_reply.started":"2021-08-03T11:59:17.391158Z","shell.execute_reply":"2021-08-03T11:59:17.410962Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Fast and efficient!","metadata":{}},{"cell_type":"markdown","source":"## 18. `at_time` and `between_time`","metadata":{}},{"cell_type":"markdown","source":"These two can be useful when working with time-series that have high granularity. \n\n`at_time` allows you to subset values at a specific date or time. Consider this time series:","metadata":{}},{"cell_type":"code","source":"index = pd.date_range(\"2021-08-01\", periods=100, freq=\"H\")\ndata = pd.DataFrame({\"col\": list(range(100))}, index=index)\n\ndata.head()","metadata":{"execution":{"iopub.status.busy":"2021-08-03T11:59:17.413521Z","iopub.execute_input":"2021-08-03T11:59:17.414159Z","iopub.status.idle":"2021-08-03T11:59:17.434168Z","shell.execute_reply.started":"2021-08-03T11:59:17.414113Z","shell.execute_reply":"2021-08-03T11:59:17.43288Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Let's select all rows at 3 PM:","metadata":{}},{"cell_type":"code","source":"data.at_time(\"15:00\")","metadata":{"execution":{"iopub.status.busy":"2021-08-03T11:59:17.43574Z","iopub.execute_input":"2021-08-03T11:59:17.436046Z","iopub.status.idle":"2021-08-03T11:59:17.454199Z","shell.execute_reply.started":"2021-08-03T11:59:17.436019Z","shell.execute_reply":"2021-08-03T11:59:17.453036Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Cool, huh? Now, let's use `between_time` to select rows within a custom interval:","metadata":{}},{"cell_type":"code","source":"from datetime import datetime\n\ndata.between_time(\"09:45\", \"12:00\")","metadata":{"execution":{"iopub.status.busy":"2021-08-03T11:59:17.455788Z","iopub.execute_input":"2021-08-03T11:59:17.456185Z","iopub.status.idle":"2021-08-03T11:59:17.467511Z","shell.execute_reply.started":"2021-08-03T11:59:17.456142Z","shell.execute_reply":"2021-08-03T11:59:17.466672Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Note that both functions require a DateTimeIndex and they only work with times (as in *o'clock*). If you want to subset within a DateTime interval, use `between`.","metadata":{}},{"cell_type":"markdown","source":"## 19. `bdate_range`","metadata":{}},{"cell_type":"markdown","source":"`bdate_range` is a short-hand function to create TimeSeries indices with business-day frequency:","metadata":{}},{"cell_type":"code","source":"series = pd.bdate_range(\"2021-01-01\", \"2021-01-31\") # A period of one month\nlen(series)","metadata":{"execution":{"iopub.status.busy":"2021-08-03T11:59:17.468832Z","iopub.execute_input":"2021-08-03T11:59:17.469495Z","iopub.status.idle":"2021-08-03T11:59:17.4847Z","shell.execute_reply.started":"2021-08-03T11:59:17.469454Z","shell.execute_reply":"2021-08-03T11:59:17.483566Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Business-day frequencies are common in the financial world. So, this function may come in handy when reindexing existing time-series with `reindex` function.","metadata":{}},{"cell_type":"markdown","source":"## 20. `autocorr`","metadata":{}},{"cell_type":"markdown","source":"One of the critical components in time-series analysis is examining the autocorrelation of a variable. \n\nAutocorrelation is the plain-old correlation coefficient but it is calculated with the lagging version of a time series. \n\nIn more detail, autocorrelation of a time series at `lag=k` is calculated as follows:\n\n1. The time-series is shifted till `k` periods:","metadata":{}},{"cell_type":"code","source":"## HIDE\n# Prep the data for an example\ndt = pd.date_range(\"2021-01-01\", periods=len(tips))\ntips.index = dt\n\ntime_series = tips[[\"tip\"]]","metadata":{"execution":{"iopub.status.busy":"2021-08-03T11:59:17.486337Z","iopub.execute_input":"2021-08-03T11:59:17.486681Z","iopub.status.idle":"2021-08-03T11:59:17.499567Z","shell.execute_reply.started":"2021-08-03T11:59:17.486649Z","shell.execute_reply":"2021-08-03T11:59:17.49855Z"},"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"time_series[\"lag_1\"] = time_series[\"tip\"].shift(1)\ntime_series[\"lag_2\"] = time_series[\"tip\"].shift(2)\ntime_series[\"lag_3\"] = time_series[\"tip\"].shift(3)\ntime_series[\"lag_4\"] = time_series[\"tip\"].shift(4)\n# time_series['lag_k'] = time_series['tip'].shift(k)\n\ntime_series.head()","metadata":{"execution":{"iopub.status.busy":"2021-08-03T11:59:17.501103Z","iopub.execute_input":"2021-08-03T11:59:17.501607Z","iopub.status.idle":"2021-08-03T11:59:17.532703Z","shell.execute_reply.started":"2021-08-03T11:59:17.501549Z","shell.execute_reply":"2021-08-03T11:59:17.531767Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"2. Correlation is calculated between the original `tip` and each `lag_*`. \n\nInstead of doing all this manually, you can use the `autocorr` function of Pandas:","metadata":{}},{"cell_type":"code","source":"# Autocorrelation of tip at lag_10\ntime_series[\"tip\"].autocorr(lag=8)","metadata":{"execution":{"iopub.status.busy":"2021-08-03T11:59:17.53398Z","iopub.execute_input":"2021-08-03T11:59:17.534274Z","iopub.status.idle":"2021-08-03T11:59:17.561055Z","shell.execute_reply.started":"2021-08-03T11:59:17.534247Z","shell.execute_reply":"2021-08-03T11:59:17.560316Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"You can read more about the importance of autocorrelation in time-series analysis from this [post](https://towardsdatascience.com/advanced-time-series-analysis-in-python-decomposition-autocorrelation-115aa64f475e).","metadata":{}},{"cell_type":"markdown","source":"## 21. `hasnans`","metadata":{}},{"cell_type":"markdown","source":"Pandas offers a quick method to check if a given series contains any nulls with `hasnans` attribute:","metadata":{}},{"cell_type":"code","source":"series = pd.Series([2, 4, 6, \"sadf\", np.nan])\nseries.hasnans","metadata":{"execution":{"iopub.status.busy":"2021-08-03T11:59:17.562031Z","iopub.execute_input":"2021-08-03T11:59:17.562396Z","iopub.status.idle":"2021-08-03T11:59:17.568261Z","shell.execute_reply.started":"2021-08-03T11:59:17.562368Z","shell.execute_reply":"2021-08-03T11:59:17.567562Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"According to its [documentation](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.Series.hasnans.html), it enables various performance increases. Note that the attribute works only on `pd.Series`.","metadata":{}},{"cell_type":"markdown","source":"## 22. `at` and `iat`","metadata":{}},{"cell_type":"markdown","source":"These two accessors are much faster alternatives to `loc` and `iloc` with a disadvantage. They only allow selecting or replacing a single value at a time:","metadata":{}},{"cell_type":"code","source":"# [index, label]\ndiamonds.at[234, \"cut\"]","metadata":{"execution":{"iopub.status.busy":"2021-08-03T11:59:17.569194Z","iopub.execute_input":"2021-08-03T11:59:17.569587Z","iopub.status.idle":"2021-08-03T11:59:17.583958Z","shell.execute_reply.started":"2021-08-03T11:59:17.569558Z","shell.execute_reply":"2021-08-03T11:59:17.583286Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# [index, index]\ndiamonds.iat[1564, 4]","metadata":{"execution":{"iopub.status.busy":"2021-08-03T11:59:17.58513Z","iopub.execute_input":"2021-08-03T11:59:17.585407Z","iopub.status.idle":"2021-08-03T11:59:17.595782Z","shell.execute_reply.started":"2021-08-03T11:59:17.585381Z","shell.execute_reply":"2021-08-03T11:59:17.594797Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Replace 16541th row of the price column\ndiamonds.at[16541, \"price\"] = 10000","metadata":{"execution":{"iopub.status.busy":"2021-08-03T11:59:17.597077Z","iopub.execute_input":"2021-08-03T11:59:17.597342Z","iopub.status.idle":"2021-08-03T11:59:17.607368Z","shell.execute_reply.started":"2021-08-03T11:59:17.597317Z","shell.execute_reply":"2021-08-03T11:59:17.606511Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## 23. `argsort`","metadata":{}},{"cell_type":"markdown","source":"You should use this function when you want to extract the indices that would sort an array:","metadata":{}},{"cell_type":"code","source":"tips.reset_index(inplace=True, drop=True)\n\nsort_idx = tips[\"total_bill\"].argsort(kind=\"mergesort\")\n\n# Now, sort `tips` based on total_bill\ntips.iloc[sort_idx].head()","metadata":{"execution":{"iopub.status.busy":"2021-08-03T11:59:17.609122Z","iopub.execute_input":"2021-08-03T11:59:17.609563Z","iopub.status.idle":"2021-08-03T11:59:17.635215Z","shell.execute_reply.started":"2021-08-03T11:59:17.609532Z","shell.execute_reply":"2021-08-03T11:59:17.634275Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## 24. `cat` accessor","metadata":{}},{"cell_type":"markdown","source":"It is common knowledge that Pandas enables to use built-in Python functions on dates and strings using accessors like `dt` or `str`. \n\nPandas also has a special `category` data type for categorical variables as can be seen below:","metadata":{}},{"cell_type":"code","source":"diamonds.dtypes","metadata":{"execution":{"iopub.status.busy":"2021-08-03T11:59:17.636366Z","iopub.execute_input":"2021-08-03T11:59:17.636682Z","iopub.status.idle":"2021-08-03T11:59:17.644399Z","shell.execute_reply.started":"2021-08-03T11:59:17.636653Z","shell.execute_reply":"2021-08-03T11:59:17.643245Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"When a column is `category`, you can use several special functions using the `cat` accessor. For example, let's see the unique categories of diamond cuts:","metadata":{}},{"cell_type":"code","source":"diamonds[\"cut\"].cat.categories","metadata":{"execution":{"iopub.status.busy":"2021-08-03T11:59:17.646155Z","iopub.execute_input":"2021-08-03T11:59:17.646655Z","iopub.status.idle":"2021-08-03T11:59:17.658988Z","shell.execute_reply.started":"2021-08-03T11:59:17.64661Z","shell.execute_reply":"2021-08-03T11:59:17.658074Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"There are also functions like `remove_categories` or `rename_categories`, etc.:","metadata":{}},{"cell_type":"code","source":"diamonds[\"new_cuts\"] = diamonds[\"cut\"].cat.rename_categories(list(\"ABCDE\"))\ndiamonds[\"new_cuts\"].cat.categories","metadata":{"execution":{"iopub.status.busy":"2021-08-03T11:59:17.660535Z","iopub.execute_input":"2021-08-03T11:59:17.661266Z","iopub.status.idle":"2021-08-03T11:59:17.673693Z","shell.execute_reply.started":"2021-08-03T11:59:17.661221Z","shell.execute_reply":"2021-08-03T11:59:17.672667Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"You can see the full list of functions under the `cat` accessor [here](https://pandas.pydata.org/pandas-docs/stable/reference/series.html#categorical-accessor).","metadata":{}},{"cell_type":"markdown","source":"## 25. `GroupBy.nth`","metadata":{}},{"cell_type":"markdown","source":"This function only works with `GroupBy` objects. Specifically, after grouping, `nth` returns the nth row from each group:","metadata":{}},{"cell_type":"code","source":"diamonds.groupby(\"cut\").nth(5)","metadata":{"execution":{"iopub.status.busy":"2021-08-03T11:59:17.675371Z","iopub.execute_input":"2021-08-03T11:59:17.676104Z","iopub.status.idle":"2021-08-03T11:59:17.711978Z","shell.execute_reply.started":"2021-08-03T11:59:17.67605Z","shell.execute_reply":"2021-08-03T11:59:17.710952Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Summary","metadata":{}},{"cell_type":"markdown","source":"Even though libraries like Dask and datatable are slowly winning over Pandas with their shiny new features for handling massive datasets, Pandas still remains the most widely-used data manipulation tool in Python data science ecosystem.\n\nThe library still remains as a role-model for other packages to imitate and improve upon, as it integrates into the modern SciPy stack so well. Thank you for reading!","metadata":{}},{"cell_type":"markdown","source":"## You might also be interested...\n- [My 6-part Powerful EDA Template](https://www.kaggle.com/bextuychiev/my-6-part-powerful-eda-template)\n- [\nLasso regression with Pipelines (Tutorial)](https://www.kaggle.com/bextuychiev/lasso-regression-with-pipelines-tutorial)\n- [Awesome EDA + XGBoost CV Baseline](https://www.kaggle.com/bextuychiev/relevant-eda-xgboost-cv-baseline)","metadata":{}}]}