<jupyter_start><jupyter_text>Data munging with Pandas IntroductionData rarely arrives in the form we need for analysis. In fact, it is has been estimated that data scientists spend 80% of their time preparing data. This process of integrating and filtering data is commonly know as data munging. This notebook contains exercises in using the Pandas package for data munging. The Pandas contains a widely used, powerful, flexible and efficient set of functions for data munging. Further, Pandas methods (operators) can be chained to define complex data munging  Why Pandas?As already stated Pandas is a powerful, flexible and efficient Python package, specifically designed for data munging. Additionally, Panadas has execellent graphics capabilities which are discussed in another lesson. Excellent documentation is available for the Pandas package:- The index to the comprehensive Pandas documentation can be found at: http://pandas.pydata.org/pandas-docs/stable/index.html - A number of good Pandas tutorials are availble online, including the one on the Pandas official site: http://pandas.pydata.org/pandas-docs/stable/tutorials.html- <NAME>, the author of Pandas, has written a book, *Python for Data Analysis*, which is a comprehensive resource for learning Pandas: http://shop.oreilly.com/product/0636920023784.do  Data Frames and SeriesThe Python Pandas package is built on two data structures **series** and **data frames**. A series holds a single list of values, whereas a data frame is a two dimensional table. Before digging into any details, it will help to have a feel for these two data structures.The code in the cell below creates a Pandas series from a Python list. Run this code and<jupyter_code>import pandas as pd
my_series = pd.Series([1, 2, 3, 4, 5, 6])
my_series<jupyter_output><empty_output><jupyter_text>Notice the following properties of the Pandas series:- The elements of the  series have lables or indices. In this case the default default indicies are integers starting with `0`.- The Pandas series is a one dimensional structure with some special attributes and methods.The code in the cell below creates a Pandas data frame with three columns. Run this code and examine the results.<jupyter_code>my_df = pd.DataFrame({'x': [1, 2, 3], 'y': [1, 4, 9], 'Strings': ['String 1', 'String 2', 'String 3']})
my_df<jupyter_output><empty_output><jupyter_text>Notice the following properties of the Pandas data frame:- The data frame is a rectagular data structure with Python lists in the  columns.- The columns can be of any Python data type, but are all of the same length.- The rows and columns of the data frame have labels or indices. In this case the columnn lables are the ones specfied. The rows are the default integer indicies starting at '0'.- The data frame is a two dimensional table with additional attirbutes and  methods.   Load the Data SetIt is time to get started with some in-depth examples. The function shown in the cell below loads the data as a .csv file from Azure Machine Learning. Some minimal clean up is performed on the data. Rows with missing values are dropped and some columns are converted from strings containing numbers to numeric data. The code executes the following steps using Pandas:- The for loop iterates over the list of numeric column labels (names). - These numeric columns are coded as character strings. If the string is coded as '?' indicating a missing value, that value is set to `numpy.nan`, a missing value.- The pandas `drop_na` method is used to remove the rows with missing values. - The second loop uses the Pandas `to_numeric` method to convert the character string to numeric.Details of using Pandas methods are discussed in the rest of this tutorial. Run the code in the cell below to prepare the data set.<jupyter_code>from azureml import Workspace

ws = Workspace()
ds = ws.datasets['Automobile price data (Raw)']
auto_prices = ds.to_dataframe()

def clean_auto_data(auto_prices):
    'Function to load the auto price data set from a .csv file' 
    import pandas as pd
    import numpy as np
    
    ## Remove rows with missing values, accounting for mising values coded as '?'
    cols = ['price', 'bore', 'stroke', 
          'horsepower', 'peak-rpm']
    for column in cols:
        auto_prices.loc[auto_prices[column] == '?', column] = np.nan
    auto_prices.dropna(axis = 0, inplace = True)

    ## Convert some columns to numeric values
    for column in cols:
        auto_prices[column] = pd.to_numeric(auto_prices[column])
        
    return auto_prices
auto_prices = clean_auto_data(auto_prices)<jupyter_output><empty_output><jupyter_text>Explore the data frameWith the data frame loaded, you can now explore some aspects of the data set. You will use Pandas data frame attributes and methods. Like every Python object Pandas data frames have attributes and methods. The syntax for accessing or setting attributes is simple:$$DataFrame.attribute$$The code in the cell below uses the `columns` attribute to display the column labels of the data frame. Run this code to see the column labels.<jupyter_code>auto_prices.columns<jupyter_output><empty_output><jupyter_text>Data frame methods are invoked using the same dot notation as you have just seen for attributes:$$DataFrame.method\_name(arguments)$$The Pandas `info` method provides a summary of the dimensions of the data set, the column names, and the data types of the columns. Run the code in the cell below and note the result.<jupyter_code>auto_prices.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Int64Index: 195 entries, 0 to 204
Data columns (total 26 columns):
symboling            195 non-null int64
normalized-losses    195 non-null object
make                 195 non-null object
fuel-type            195 non-null object
aspiration           195 non-null object
num-of-doors         195 non-null object
body-style           195 non-null object
drive-wheels         195 non-null object
engine-location      195 non-null object
wheel-base           195 non-null float64
length               195 non-null float64
width                195 non-null float64
height               195 non-null float64
curb-weight          195 non-null int64
engine-type          195 non-null object
num-of-cylinders     195 non-null object
engine-size          195 non-null int64
fuel-system          195 non-null object
bore                 195 non-null float64
stroke               195 non-null float64
compression-ratio    195 non-null float64
horsepower           195 non-n[...]<jupyter_text>**Your Turn:** In the cell below create and execute the code to find the dimensions of the data frame using the `shape` attribute.  Filter a Data FrameHere is a first example to get you started with Pandas. The code below applies a Pandas filter to the auto pricing data. The result will only be the cases or rows of autos made by the Audi company. The syntax of the operation shown is simple. $$DataFrame[logical\_expresion]$$A single column in a Pandas data frame can be addressed as either `DataFrame.column_name` or `DataFrame['column_name']`.Execute the code in the cell below and note the result.<jupyter_code>df = auto_prices[auto_prices.make == 'audi']
print(df)<jupyter_output>symboling normalized-losses  make fuel-type aspiration num-of-doors  \
3          2               164  audi       gas        std         four   
4          2               164  audi       gas        std         four   
5          2                 ?  audi       gas        std          two   
6          1               158  audi       gas        std         four   
7          1                 ?  audi       gas        std         four   
8          1               158  audi       gas      turbo         four   

  body-style drive-wheels engine-location  wheel-base  ...    engine-size  \
3      sedan          fwd           front        99.8  ...            109   
4      sedan          4wd           front        99.4  ...            136   
5      sedan          fwd           front        99.8  ...            136   
6      sedan          fwd           front       105.8  ...            136   
7      wagon          fwd           front       105.8  ...            136   
8      sedan       [...]<jupyter_text>Basic Data Frame OperationsNow that you have some feel for Pandas data frames, you will explore some basic operations for manipulation of data frame.  Take slices from the data frameThere are several methods for taking a subset or slice of a Pandas data frame. The basic approach is simply to use the following syntax:$$DataFrame[start:end:stride]$$There are defaults for all the index values; `start = 0`, `end = number of rows - 1`, and `stride = 1`. The code in the cell below uses two of these defaults to display the first 5 rows of the data frame. Run this code and examine the result.<jupyter_code>auto_prices[:5]<jupyter_output><empty_output><jupyter_text>Notice that Panadas uses `0` based indexing. The first 5 rows are displayed. You can take an arbitrary slice from a data frame using this syntax. Run the code in the cell below and examine the result.<jupyter_code>df_slice = auto_prices[9:14]
df_slice<jupyter_output><empty_output><jupyter_text>Keeping in mind that Pandas data frames use zero based indexing, notice the row indices of the resulting data fame range from 10 to 14. As an alternative the Pandas `iloc` method can be used to take a slice of rows from a data frame, as well. Run the code in the cell below and examine the result.<jupyter_code>auto_prices.iloc[9:14]<jupyter_output><empty_output><jupyter_text>**Your Turn:** In the cell below create and exectue the code to subset the data frame so that you extract every 10th row.  loc, iloc and ixAt this point you may be wondering about how and when to use the `loc` and `iloc` methods. To add to the story there is the `ix` method. In simple terms these methods can be summarized as:- The **`loc`** method allows subsetting a Pandas data frame by the labels (names) of rows and columns. Rows and columns can be spcified in a list, or by a range with a stride using the `:` opertor. Note that the default row indices are integers starting at `0`.- The **`iloc`** method allows subsetting a Pandas data frame using zero based integer positioning of rows and columns. Rows and columns can be spcified in a list, or by a range and stride using the `:` opertor. - The **`ix`** method is a hybrid between `loc` and `iloc`. The indices can be the labels or the integer positions. While this flexibility can be useful, be careful when using  `ix`. There can be some ambiquity between lables and integer positions, particularly when using the default row and column labels.Specifing ranges of rows and  columns with `loc` and `iloc` following a similar convention to that used for the data frame `[]` opertor:$$DataFrame.iloc[row-start\ : row-end\ : row-stride,\ column-start: column-end: column-stride]$$Keep this convention in mind when doing any of these operations.   Random sample a data frameData scientists often need randomly chosen subsets of data. The Pandas `sample` method can be used to sample a certain number of rows with the `n` argument, or a fraction of the rows using the `frac` argument. Run the code in the cell below to take a random sample of 5 rows data frame.<jupyter_code>df_rand = auto_prices.sample(n = 5)
df_rand<jupyter_output><empty_output><jupyter_text>**Your Turn:** In the cell below create and execute the code to randomly sample 5% of the rows in the data frame.  Select column from data frameAs with the selection of rows, there are several useful methods to select columns with Pandas. A single column can be selected by name. Run the code in the cell below and examine the result.<jupyter_code>df['price']<jupyter_output><empty_output><jupyter_text>Notice that only the values of the price column have been selected.If you wish to select multiple columns by name, place the names of the columns in a list. Execute the code in the cell below and examine the results.<jupyter_code>df_2 = df[['drive-wheels', 'wheel-base', 'curb-weight', 'horsepower', 'price']]
df_2<jupyter_output><empty_output><jupyter_text>You can see that the subset of the data frame inlcudes only the columns named in the list.Alternatively, you can use the Pandas `loc` method to subset a list of columns by name. The code in the cell below uses the following syntax:$$DataFrame.loc[:,\ list\_of\_column\_names]$$Within the square bracket of this line of code the range of rows is sepecified with `:` using the default value for all rows, followed by a comman seperator, and then a list of column names. Since all rows are selected the `:` seperator is used without specifing a start and end.Execute this code and examine the results.<jupyter_code>df.loc[:, ['drive-wheels', 'wheel-base', 'curb-weight', 'horsepower', 'price']]<jupyter_output><empty_output><jupyter_text>Examine the results and notice that they are the same as before. But, what if you just want to delete a few rows and don't want to type a long list of the column names you wish to keep? Then, you can use the Pandas `drop` method. The drop method will work on rows, `axis = 0`, or columns, `axis = 1`. Run the code in the cell below to remove the three columns, with labels in the list, from the data frame.<jupyter_code>df_drop = df.drop(['symboling', 'normalized-losses', 'engine-size'], axis = 1)
df_drop<jupyter_output><empty_output><jupyter_text>Notice that the data frame now has 23 columns, 3 less than the original data frame.  **Your Turn:** Use the Pandas `iloc` method to select the first 5 columns of the `df` data frame.  Sort a data frame Data scientist often need to sort the rows or columns in a data set by the values of one or more columns or rows. The Pandas `sort_values` method does just this. You can use the `sort_values` method to sort columns, `axis = 1`, or rows,`axis = 0`. The default is to sort in assending order. Run the code in the cell below to sort the data frame first by drive wheels type and then by price.<jupyter_code>df_sort = df_2.sort_values(by = ['drive-wheels', 'price'], axis = 0)
df_sort<jupyter_output><empty_output><jupyter_text>The data frame is now sorted first by drive wheels and then by price. **Your Turn:** In the cell below create and exectue the code to sort the `df_2` data frame in decending order, first by drive wheels and then by housepower. Hint, use the `ascending=False` argument.  Computing on Data FramesYou have learned some of the basic Pandas methods for manipulating data frames. You will now explore methods for computing on data frames. Compute new columnsVery often data scientist need to transform columns in a data frame. Pandas provides simple and powerful facilities for computing new columns or substituting transformed values in the existing columns of a data frame. In many cases, you will will want to append a new column to an existing data frame. The alternative is to create a new copy of the data frame. To prevent making unexpected copy of the data frame use the `loc` or `iloc` methods to append the column to the existing data frame. The code in the cell below uses the Pandas `divide` method to compute the curb weight of the autos in killograms. Notice the use of the `loc` method on the left side of the `=` symbol, which ensures the new column is appended to the existing `df_2` data frame. Run this code and examine the result. ******Note:** You may get a copy warning message the first time you run this code. This warning is misleading in this case, and can be safely ignored.<jupyter_code>df_2.loc[:, 'curb-weight-kg'] = df_2['curb-weight'].divide(2.205)
df_2<jupyter_output>/home/nbuser/anaconda2_20/lib/python2.7/site-packages/pandas/core/indexing.py:296: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy
  self.obj[key] = _infer_fill_value(value)
/home/nbuser/anaconda2_20/lib/python2.7/site-packages/pandas/core/indexing.py:476: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy
  self.obj[item] = s<jupyter_text>Examine the results above and notice the new column with the computed values has been appended to the data frame. In some cases, you may wish to replace the values in an existing column with new transformed values. You can use the `loc` and `iloc` methods to do just this. When a single column is selected from a data frame the result is a Pandas series. The Pandas `apply` operator is used to compute the transformed values of the series. If a transformation is to be applied to the values of multiple columns of a Pandas data frame the `applymap` method is used. The code in the cell below computes the natural log of several numeric columns. The transformed values are saved in the original columns using the `loc` method. Run this code and examine the results.<jupyter_code>import math
cols = ['wheel-base', 'curb-weight', 'horsepower', 'price', 'curb-weight-kg']
df_2.loc[:, cols] = df_2[cols].applymap(lambda x: math.log(x))
df_2<jupyter_output><empty_output><jupyter_text>These results are as expected.  The apply, applymap and map methodsYou can use the  Pandas `apply` method to compute agregations of values in the columns in a data frame. You have already met the `applymap` method. There is also the `map` method for series. These methods are related but different:- **`apply`** iterates over the columns of a data frame and computes the agregated result of the funcation applied to all values in a column.- **`applymap`** iterates over the columns of a data frame and computes a result for each value of the column.- **`map`** is similar to `applymap` but it operates on the values of a series. The code in the cell below computes the sum of several numeric columns. Run this code and examine the results.<jupyter_code>auto_prices[['wheel-base', 'curb-weight', 'horsepower', 'price']].apply(lambda x : sum(x))<jupyter_output><empty_output><jupyter_text>Recall that a single column selected from a data frame is a Pandas series. The code in the cell below selects a single column from the data frame, computes the natural log of the values and prints the fist 10 values (a missing value is skipped). Execute this code and examine the result.<jupyter_code>import math
log_price = auto_prices['price'].map(lambda x : math.log(x))
log_price[:10]<jupyter_output><empty_output><jupyter_text>**Your Turn:** In the cell below, create and execute code to compute the variance of the wheel base and curb weight columns using the `numpy.var` method.  Chaining Pandas methodsUp until now you have only used one Pandas method at a time. However, you can create complex chains of operations by chaining methods. The dot operator adds each method to the chain:$$DataFrame.method\_1(arguments).method\_2(arguments)....$$By chaining Pandas methods you can create complex data munging operations. However, a few words of caution are in order when chaining Pandas methods. Most importantly, make sure the schema output from a method is compatible with the input of the next method in the chain.Jumping right in, the code in the cell below chains three Pandas methods to do the following: - Subset the data frame, using the `loc` method.- Compute the natural log of the values, using the `applymap` method.- Sort the rows by the natural log of the auto price, using the `sort_values` method.That is quite a lot for one line of code. Execute this  code and check the result.<jupyter_code>auto_prices.loc[auto_prices.make == 'audi', ['wheel-base', 'curb-weight', 'horsepower', 'price']].applymap(lambda x: math.log(x)).sort_values(by = 'price', axis = 0)<jupyter_output><empty_output><jupyter_text>Using groupby in Pandas chainsThe Pandas `groupby` method enables compution of statistics for some columns aggregated by other columns in the data frame. The `groupby` mehods aggregaes a data frame by the values in a list of categorical columns. The order of the aggregation is given by the order of the columns labels in the list.As an example, the code in the cell below does the following:- Subset the data frame to three columns.- Use the `groupby` method to order the cases first by body style and then by number of cylinders.- Counts the number of cases in the groups.Run this code and examine that results.<jupyter_code>auto_prices[['body-style', 'num-of-cylinders', 'price']].groupby(['body-style', 'num-of-cylinders']).count()<jupyter_output><empty_output><jupyter_text>Aggregation with groupbyAs an other example, consider the code in the cell below which uses the Pandas `aggregate` method. This code performs the folliwng operations:- Subsets the data frame to three columns.- Groups the cases by the body style.- Computes the mean of the cases using the `mean` method from the `numpy` package.- Sorts the results in assending order by the mean price.Run this code an exhamine the result.<jupyter_code>import numpy as np
auto_prices.loc[:, ['body-style','curb-weight', 'price']].groupby(['body-style']).aggregate(np.mean).sort_values('price')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.06658268010451661, 'fraction_numerical': 0.012318029115341545, 'mean_word_length': 3.92373076039513, 'pattern_counts': {'":': 0, '<': 88, '<?xml version=': 0, '>': 88, 'https://': 0, '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': '22', 'dir': 'jupyter-structured-clean-dedup', 'id': '210714', 'license': 'MIT', 'n_tokens_mistral': 5904, 'n_tokens_neox': 5639, 'n_words': 2960, 'path': '3 -  Data Science Research Methods/DAT203.1x/Mod4/Demos/DataFrames-Python.ipynb', 'repo_name': '2series/Professional-In-Artificial-Intelligence', 'size': '21439'}