<jupyter_start><jupyter_text>Data Wrangling in Python> CIT Club event blog & Notebook. The aim of this blog/notebook was to introduce CIT Club members in data wrangling. - author: <NAME>- toc: true- comments: true- badges: true- image: images/event_banner.png- categories: [data-wrangling, python, cit-club] ![Wikipedia Definition](https://github.com/VictorOmondi1997/data_wrangling_cit/blob/master/img/wikipedia_def.gif?raw=true)  Introductions Data wrangling is the process of gathering, selecting, and transforming data to answer an analytical question. Also known as data cleaning or “munging”, legend has it that this wrangling costs analytics professionals as much as 80% of their time, leaving only 20% for exploration and modeling.Hello and welcome to **Data Wrangling in Python**. This is a notebook/presentatation/blog used in *Data Science CIT club event*, it depends on how you've accessed it. Depending on how you got this notebook, this is how to work around with it to access the data sets and the entire project.- **Github**  - clone the entire project, instructions are on the [`README.md`](https://github.com/VictorOmondi1997/data_wrangling_cit/blob/master/README.md) file- **Blog**  - click on any of the banners shown at the top of the project    - [![Open In Collab](https://colab.research.google.com/assets/colab-badge.svg)](https://colab.research.google.com/github/VictorOmondi1997/blog/blob/master/_notebooks/2021-11-06-data_wrangling_cit.ipynb)    - [![Binder](https://binder.pangeo.io/badge_logo.svg)](https://mybinder.org/v2/gh/VictorOmondi1997/blog/master?filepath=_notebooks%2F2021-11-06-data_wrangling_cit.ipynb)   Data Sets- [`client status`](https://raw.githubusercontent.com/VictorOmondi1997/data_wrangling_cit/master/data/client_status.csv): Shows the client status and loan status at the end of the month their status remained the same.- [`kenya_subcounties`](https://raw.githubusercontent.com/VictorOmondi1997/data_wrangling_cit/master/data/kenya_subcounty.csv): shows the county name and sub counties  1. Prerequisites Before starting data wrangling, we need to first install the necessary libraries needed. - Pandas: Pandas is the popular library used for data analysis and data transformation- numpy: Numpy is a popular library for scientific computingWe must import these libraries first, as a convention during importing pandas is aliased to `pd` and numpy as `np`. For the purpose of viewing, some datasets might be having very long or very many columns, therefore we need to make sure pandas will be able to show all columns> Note: Pandas usually truncates columns if they are many ![outline](https://github.com/VictorOmondi1997/data_wrangling_cit/blob/master/img/outline.gif?raw=true)  2. Reading Data ![Reading Data](https://github.com/VictorOmondi1997/data_wrangling_cit/blob/master/img/read_csv.gif?raw=true)Data might come in very many formats. But mainly, for many data scientists data, the popular used are CSV files. We will use CSV file for our data. Below is how to read CSV with pandas. In pandas, we use `pd.read_csv` to create pandas dataframes from csv. You pass the csv location as a string/variablestring as the first argument. e.g. `pd.read_csv("file.csv")`  **EXERCISE****Create a dataframe known as `counties`, the csv location is https://raw.githubusercontent.com/VictorOmondi1997/data_wrangling_cit/master/data/kenya_subcounty.csv**<jupyter_code>#collapse-hide<jupyter_output><empty_output><jupyter_text>3. Data Exploration ![Data Exploration](https://github.com/VictorOmondi1997/data_wrangling_cit/blob/master/img/explore_data.gif?raw=true)Before doing data wrangling or any data cleaning, it is important as a data scientist/analyst to explore your data. This will help you know how the data looks like, how many rows and columns it has. To check the first few rows we use `df.head(n)` where df is the dataframe name and `n` is the number of rows you want to be returned. The same is for `df.tail` and `df.sample`.  3.1 First few rows By Default, without passing any argument in `head()`, it will return the first `5` rows. Passing the number of rows you want  **EXERCISE****For the dataframe we created (`counties`) return the first 15 rows.**<jupyter_code>#collapse-hide<jupyter_output><empty_output><jupyter_text>3.2 Last few rows The `tail` method returns the last rows of a dataframe, by default it returns the 5 last rows, but you can specify the number of last rows that you need to return. > Tip: As you can see the last `4` rows have metadata on when the data was generated. We will hand this situation in the Reshaping data part.  **EXERCISE****For the dataframe (`counties`) we created return the last 15 rows.**<jupyter_code>#collapse-hide<jupyter_output><empty_output><jupyter_text>3.3 sample `df.sample` mainly is used during sampling techniques, it a random sample of items from an axis of object.  **EXERCISE****For the dataframe (`counties`) we created return a sample of `10` rows**<jupyter_code>#collapse-hide<jupyter_output><empty_output><jupyter_text>3.4. Number of Rows and columns In pandas, there are various ways to know how many columns (_variables_) and rows (_observations_) a dataframe has.1. `len(df)` - returns number of rows2. `df.shape` - returns number of rows and columns as a tuple3. `df.info()` - returns data frame info, non null values, columns and data columns and the data type of columns.  **EXERCISE****For the dataframe (`counties`) we created how many rows and columns does it have?**<jupyter_code>#collapse-hide<jupyter_output><empty_output><jupyter_text>3.5 Info `df.info` prints a summary of the data frame, ie, number of rows, columns, data column and their non-null values and the dtype  **EXERCISE****For the dataframe (`counties`) we created how many missing values are there in the `name` columns?**<jupyter_code>#collapse-hide<jupyter_output><empty_output><jupyter_text>3.6. Describe We use `df.describe()` to get summary statistics, by default it returns the summary statistics of the numerical columns. To show all summary statistics including those for objects (strings) you can pass `include="all"` to the `df.describe`.  **EXERCISE****For the dataframe (`counties`) we created how many unique sub counties are there in Kenya?**<jupyter_code>#collapse-hide<jupyter_output><empty_output><jupyter_text>4. Reshaping Data ![Reshaping Data](https://github.com/VictorOmondi1997/data_wrangling_cit/blob/master/img/reshape_data.gif?raw=true)From the tail that we looked above, we saw that the last 4 rows have the metadata on the generation of the data. Those metadata are not very important in our analysis. We will first remove them.  4.1. Droping Irrelevant Data. Let's check some of the irrelevant data. we can drop these 4 last columns by using `df.drop` and assigning the `index` argument to a list of indeces we want to drop.> Important: If you want to drop columns, use `df.drop(columns=cols)` were cols is a list of column names you want to drop. > Note: We can also drop inplace, no need of assigning it to a variable. This can be done by specifying the `inplace` argument to `True`  4.2. Set Index For our data frame, `Client ID` column uniquely identifies rows, for our analysis, we won't be doing analysis on this column, we can set `Client ID` to  be the index of our dataframe.   4.3. Sorting Sorting is arranging values/index in data either in ascending or descending order. To sort index we use `df.sort_index`, to sort values in columns we use `df.sort_values(cols)` where `cols` is column name or list of column names we want to sort their values. By defauld the sorting are in ascending order, we can also sort in descending order. To do this, we use `ascending=False` argument.  **EXERCISE****For the dataframe (`counties`) we created sort the data frame as follows**1. **`name`: descending**2. **`subCounty`: ascending**> Tip: you can use `ascending=[False, True]`.<jupyter_code>#collapse-hide<jupyter_output><empty_output><jupyter_text>4.3. Duplicates Duplicates are rows that are identical to each other, ie, they have the same values. to check for duplicates we use `df.duplicated` this will return a **boolean series** we can use the boolean series to filter out the repeting values and we can use `df.drop_duplicates` to remove any duplicates in the data frame.> Important: the first occurence won't be removed by default.to check for duplicates, we will first reset the index, this will make `Client ID` to be a column as it was before.  **EXERCISE****For the dataframe (`counties`), are there any duplicates in that dataframe**<jupyter_code>#collapse-hide<jupyter_output><empty_output><jupyter_text>4.4. Drop Duplicates `df.drop_duplicates` removes duplicates keeping first occurence by default.  4.5. Missing Values Majorly involed during data cleaning. Missing values are values that are missing, alaaa!. In this session we won't go deeper on how to handle missing values. In python, to check for missing values we use `df.isnull()` or `df.isna`, to remove missing values we use `df.dropna` That returns a dataframe with boolean showing if a value is null `df.isnull().sum()` is the most widely used way of cheking the number of missing values per column.  5. Advanced Data Wrangling ![Advanced Data Wrangling](https://github.com/VictorOmondi1997/data_wrangling_cit/blob/master/img/advanced.gif?raw=true)  5.1. Value counts As a data scientist it is good to check the counts of unique values, these is mainly used to check for imbalance at later states. To check for the number of unique values, we use `df[col].value_counts()` where `col` is the column name.> Note: `df[col]` is selecting a column returning a series, where `col` is the column name By default `.value_counts` drops values which are missing values, we can include the counts of missing values by setting `dropna=False`  **EXERCISE****For the dataframe (`counties`) Show the value counts of the `name` variable**<jupyter_code>#collapse-hide<jupyter_output><empty_output><jupyter_text>5.2. Data Filteration In pandas - we can use comparison operators to filter data meeting a certain condition, - to filter columns we can use `df[[col1....coln]`, - to filter rows based on their index we can use   - `iloc[i]` or `loc[strn]` for integer based or label based indexing respectively. We can use comparison operators for filtering values that meet a certain condition.- `>` - Greater than- `<` - Less than- `>=` - Greater or equal to- `<=` - Less than or equal to- `==` equal to- `!=` not equal to  `df[col]>value` returns a boolean series, we can use the boolean series to filter out values in dataframe not meeting that condition.  5.2.1. Multiple conditions filterring We can use logical conditions to support two or more expressions. In pandas we we can use the following operators:- `&`: for logical **and**- `|`: for logical **or**- `~`: for logical **not**> Important: Paranthesis are very important. enclose expressions in a paranthesis `(.....)`  **EXERCISE****For the dataframe (`counties`) Filter the data to select dataset where `name` is equal to `'Nakuru'` & `subCounty` is not equal to `'Naivasha'`**<jupyter_code>#collapse-hide<jupyter_output><empty_output><jupyter_text>5.3. Grouping Data Grouping data is a critical step in data wrangling. in pandas we can group DataFrame using a mapper or by a Series of columns.A groupby operation involves some combination of splitting the object, applying a function, and combining the results. This can be used to group large amounts of data and compute operations on these groups. The groupby returns an object, we can use the object to perform actions in the groups. To check for the size of each group, we can use `size()` method of a groupby object. Pivot table (derived from excel) is an advanced pandas grouping method.Here you can decide on the index, columns, values and the aggregate functions. The levels in the pivot table will be stored in MultiIndex objects (hierarchical indexes) on the index and columns of the result DataFrame.the aggfunc by default is mean. You can pass string aggregates eg `'mean'`, `'sum'` ect or aggregate functions such as `np.mean`, `np.sum` etc setting `margins=True` will result to row aggregate and column column aggregate.   5.4. Combining Data the are varies ways of combining data sets- append - add rows to the end of the caller.- concatenate- merge   - inner join  - left join  - right join  Type of merge to be performed.- left: use only keys from left frame, similar to a SQL left outer join; preserve key order.- right: use only keys from right frame, similar to a SQL right outer join; preserve key order.- outer: use union of keys from both frames, similar to a SQL full outer join; sort keys lexicographically.- inner: use intersection of keys from both frames, similar to a SQL inner join; preserve the order of the left keys.- cross: creates the cartesian product from both frames, preserves the order of the left keys.We use `df.merge`/`pd.merge` to import columns from other datasets. We can merge `status` and `counties` using `Office` and `subCounty` (this is because that is the key present in both columns. Since we have diffent column names in both data frames, we have to use `left_on` and `right_on` to specify the columns.if the columns are the same on both columns, we can use `on` argument. Another method is using the `pd.merge`  **EXERCISE****For the dataframe (`counties`) do a full outer join with `status` dataframe, how many rows does it have?**<jupyter_code>#collapse-hide<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.085659429921725, 'fraction_numerical': 0.009378230689706099, 'mean_word_length': 4.973974415527128, 'pattern_counts': {'":': 0, '<': 52, '<?xml version=': 0, '>': 62, 'https://': 14, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '12', 'dir': 'jupyter-structured-clean-dedup', 'id': '419314', 'license': 'Unlicense', 'n_tokens_mistral': 3935, 'n_tokens_neox': 3692, 'n_words': 1806, 'path': '[PRACTICE NOTEBOOK]-2021-11-06-data_wrangling_cit.ipynb', 'repo_name': 'VictorOmondi1997/data_wrangling_cit', 'size': '13549'}