<jupyter_start><jupyter_text>Pandas Techniques for Data Manipulation in Python<NAME> [Github](https://github.com/tem-ctrl) ![image](data_science.jpg)  IntroductionWithout **data**, there won't be neither **data science** nor **data scientists**, that is why efficient data manipulation skills come in very handy. However, real-world data is always dirty, besides some tasks require us to use only part of data or to combine two or more datasets into one and so on. All these tasks have been made quite easy in Python by the **Pandas library**. In this post, we will present five powerful techniques offered by Pandas for manipulating data in order to draw useful insights from it. These techniques, in order of appearence in the text are: **Importing data, Apply functions, Imputing missing values, working with categorical data** and **Manipulating text data**. The Ukrainian coffee market dataset downloaded from [Kaggle](https://www.kaggle.com/dimitryzub/10-coffee-places-from-ukrainian-cities) will be used for illustration.  1. Importing data with PandasRegarding the size, type, user's preferences, the data is stored in many different file formats including but not limited to:- CSV (Comma Separated Values);- HDF5 (Hierarchical Data Format, version 5);- SQL databases;- JSON;- Excel spreadsheets;With pandas reading functions, the data stored in each of these file formats is loaded into a pandas DataFrame. For the file formats listed above, the corresponding reading functions are listed below, in the same order posterior to loading pandas under its common alias **pd**.<jupyter_code>import pandas as pd
import numpy as np
data_path = 'coffee_shops.csv'
df = pd.read_csv(data_path)
# df = pd.read_csv('path/to/data.csv') # Load a CSV file
# df = pd.read_hdf('path/to/data.hdf5') # Load a hdf5 file
# df = pd.read_sql('SQL query')
# df = pd.read_json('path/to/data.json')
# df = pd.read_excel('path/to/data.xlsx')<jupyter_output><empty_output><jupyter_text>**Note:** For online data scraping, we just need to replace **path/to/data.ext** by its corresponding URL.  Once the data is loaded, good practices expect us to learn to know our data, number of columns, rows, their data types, the summary statistics, check for missing values and so on.<jupyter_code>df.head()
df.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 200 entries, 0 to 199
Data columns (total 9 columns):
 #   Column           Non-Null Count  Dtype  
---  ------           --------------  -----  
 0   Region           200 non-null    object 
 1   Place name       200 non-null    object 
 2   Place type       200 non-null    object 
 3   Rating           198 non-null    float64
 4   Reviews          198 non-null    float64
 5   Price            122 non-null    object 
 6   Delivery option  129 non-null    object 
 7   Dine in option   140 non-null    object 
 8   Takeout option   144 non-null    object 
dtypes: float64(2), object(7)
memory usage: 14.2+ KB<jupyter_text>2. Apply functionsPandas offers the possibility to apply one or many functions at once to certain parts of the dataframe. These functions can either be python built-ins or user defined. There are several ways to apply functions to dataframes but in this tutorial we will talk about two functions.- `apply()` which allows to manipulate the data as a whole;- `applymap()` which is used to aplly functions to the data elementwise. 2.1 Apply a single functionThese functions include `min()`, `mean()`, `max()`, `std` and many others that permit to draw useful insights from the data they are applied on. For illustration, let's evaluate the mean of the numerical columns(`Rating` and `Reviews`) of our dataset on one hand, on the other hand we will create a new column `len_place_type` to store the number of characters of each `Place type`.<jupyter_code>df[['Reviews', 'Rating']].apply(np.mean)
f = lambda x: len(x)
df['len_place_type'] = df[['Place type']].applymap(f)
df.len_place_type.head()<jupyter_output><empty_output><jupyter_text>2.2 Apply multiple functionsTo agregate data with more than one function using the `apply()` method, we simply pass the functions as a list. Let's compute the `min` and `max` values of both `Reviews` and `rating` columns.<jupyter_code>df[['Reviews', 'Rating']].apply([np.min, np.max])<jupyter_output><empty_output><jupyter_text>As can be seen, pandas gives the result as a new dataframe. *Important note: when applying functions to data, missing values are ignored*.  3. Imputing missing valuesUnless specially specified, missing values are represented by **NaN** or **NA** (More recent).The `info()` method used to retrieve general information of the dataset shows that there are some missing values but the specialized methods for detecting missing values are `isna()` and `isnull`, both somewhat equivalent. We use `isna()` here, combinbed with the method `any()`, it yields a pandas series of type `Boolean` with the value `True` for each column with missing values and `False` otherwise.<jupyter_code>df.isna().any()<jupyter_output><empty_output><jupyter_text>Combined with `sum()`, it gives a series with all the dataframe column names and their corresponding number of missing values.<jupyter_code>df.isna().sum()<jupyter_output><empty_output><jupyter_text>There are numerous ways of dealing with missing data, the most drastical one being dropping every row with missing values. this is achieved by the pandas function `dropna()`. For datasets with many missing values, this approached should definitely be avoided, as can be confirmed by the example below.<jupyter_code>df_drop = df.dropna()
len(df_drop)<jupyter_output><empty_output><jupyter_text>Another common way of treating missing values is to replace them by a certain value, the aim being to reproduce the data original data distribution as fidelly as possible. To do so, we use the pandas method `fillna()`, the first parameter being the value with which we want to replace missing values. The most common technique is to replace missing values in a dataset column with the most frequent value of the column in question, however the imputation strategy completely depends on the data scientist, their data distribution and their expectations. In our dataframe, the missing values in `Dine in option` and `Takeout option` columns correspond to the boolean value `False`, so let's fix that. The parameter `inplace` set to `True` allows the modification to be done inplce, no need to create a copy of the dataframe.<jupyter_code># Replace missing values by False
df['Dine in option'].fillna(False, axis=0, inplace=True)
df['Takeout option'].fillna(False, axis=0, inplace=True)
df[['Dine in option', 'Takeout option']].isna().any()<jupyter_output><empty_output><jupyter_text>4. Working with categorical dataAs indicated by its name, categorical data type is the pandas equivalent of categorical data in statistics. This data type is used to describe data that usually takes a limited, fixed amount of possible values. e.g. the days of a the week, the months of the year... The categorical data type is best suited in the following situations:- A string variable taking few values, eg: the `Place type` and `Region` column of our dataset.- The logical order (`1, 2, 3`) order of a variable is different from the lexical order (`'one', 'three', 'two'`). - The other python libraries (**Matplotlib, seaborn...**) must interpret the variable as categorical, for plotting for example. 4.1. Set column data type to categoricalTo change the data type of a variable in pandas, we use the method `astype()` which takes the new data type as parameter.Let's convert the data type of `Region` and `Place type` columns of our dataframe from `object` to `category`, which best describe the data in these columns. We will then have $10$ categories for `Region` and $14$ for `Place type` corresponding to the number of distinct values of each column.<jupyter_code>df[['Region', 'Place type']] = df[['Region', 'Place type']].astype('category')
df[['Region', 'Place type']].dtypes<jupyter_output><empty_output><jupyter_text>4.2. Label encodingOnce the column data defined as categorical, Pandas offers the possibility throught the function `get_dummies()`, to encode the different values of that column as binary data, allowing it to be used in model training, evaluation and prediction. This function exploites the method known as **one-hot encoding**, that is every value of the column variable is reprensented by a binary word with length, the number of distinct values and all the bits but the one corresponding to the true value of the row, are set to `0`. Let's encode the values of the `Region` column as binary.<jupyter_code>pd.get_dummies(df['Region'], drop_first=True).iloc[[0, 25, 45, 65, 85]] #, prefix=None)<jupyter_output><empty_output><jupyter_text>5. Manipulating text dataPandas offers many method for an efficient text data management. Most importantly these methods automatically ignore missing values, avoiding annoying errors. The access to these useful method is made possible to dataframes throught the attribute `str`. 5.1. some string methodsBelow are some common methods to used to deal with text.- `df.str.upper()` to convert text data to uppercase.- `df.str.lower()` to convert to lowercase.- `df.str.lstrip(), df.str.rstrip(), df.str.strip()` to trim white space at the begining, the end and both respectively.- `df.str.len()` to eveluate the length of text data.- `df.str.match('text')` to check whether or not the text data matches the text passed as parameter.Following are few example of usage of these methods on the `Place name` column of our dataset.<jupyter_code>places = df['Place name'].astype('string')
places_low = places.str.lower()
places_up = places.str.upper()
places_len = places.str.len()
# print the first value of each variant of dataframe
display(places_low.iloc[0])
display(places_up.iloc[0])
places_len.iloc[0]<jupyter_output><empty_output><jupyter_text>5.2. Split and replace textUsing `str.split(delimiter)` on text data yields a list of different pices oc the text that can be accessed using `get()` method or `[]` notation. For example, if we split the first value of `places` series with the delimiter being the whitespace, we get a list of two words: `Dim` and `Kavu`.<jupyter_code>places.iloc[0].split(' ')<jupyter_output><empty_output><jupyter_text>The method `replace()` takes two mandatory parameters, the original text/character and the replavement text respectively. for illustration let's replace the white spaces by underscores in the `places` series.<jupyter_code>places_und = places.str.replace(' ', '_')
places_und[0]<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08870363431997003, 'fraction_numerical': 0.009554140127388535, 'mean_word_length': 4.892384105960265, 'pattern_counts': {'":': 0, '<': 54, '<?xml version=': 0, '>': 54, 'https://': 2, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '13', 'dir': 'jupyter-structured-clean-dedup', 'id': '216785', 'license': 'MIT', 'n_tokens_mistral': 3004, 'n_tokens_neox': 2849, 'n_words': 1394, 'path': 'Pandas techniques for data manipulation.ipynb', 'repo_name': 'tem-ctrl/data_manipulation_pandas', 'size': '10680'}