<jupyter_start><jupyter_text>Data Science Boot Camp  Introduction to Pandas Part 2  Data Wrangling with Pandas * Now that we have been exposed to the basic functionality of Pandas, lets explore some more advanced features that will be useful when addressing more complex data management tasks.* As most statisticians/data analysts will admit, often the lion's share of the time spent implementing an analysis is devoted to preparing the data itself, rather than to coding or running a particular model that uses the data.* This is where Pandas and  Python's standard library are beneficial, providing high-level, flexible, and efficient tools for manipulating your data as needed.<jupyter_code>%matplotlib inline
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt

# Set some Pandas options
pd.set_option('display.notebook_repr_html', False)
pd.set_option('display.max_columns', 20)
pd.set_option('display.max_rows', 25)<jupyter_output><empty_output><jupyter_text>Date/Time data handling * Date and time data are inherently problematic:    * There are an unequal number of days in every month, an unequal number of days in a year (due to leap years)        * Time zones that vary over space. * But information about time is essential in many analyses, particularly in the case of time series analysis. * The `datetime` built-in library handles temporal information down to the nanosecond.<jupyter_code>from datetime import datetime
now = datetime.now()
now
now.day
now.weekday()<jupyter_output><empty_output><jupyter_text>* In addition to `datetime` there are simpler objects for date and time information only, respectively.<jupyter_code>from datetime import date, time
time(3, 24)
date(1987, 8, 8)<jupyter_output><empty_output><jupyter_text>* Having a custom data type for dates and times is convenient because we can perform operations on them easily. For example, we may want to calculate the difference between two times:<jupyter_code>my_age = now - datetime(1987, 8, 8)
my_age
my_age.days/365.<jupyter_output><empty_output><jupyter_text>Merging and joining DataFrame objects * Data contained in pandas objects can be combined together in a number of built-in ways: * `pandas.merge` connects rows in DataFrames based on one or more keys. This will be familiar to users of SQL or other relational databases, as it implements database join operations. * `pandas.concat` glues or stacks together objects along an axis. * `combine_first` instance method enables splicing together overlapping data to fillin missing values in one object with values from another.  Database-style DataFrame Merges * Merge or join operations combine data sets by linking rows using one or more keys.<jupyter_code>df1 = pd.DataFrame({'key': ['b', 'b', 'a', 'c', 'a', 'a', 'b'], 'data1': range(7)})
df2 = pd.DataFrame({'key': ['a', 'b', 'd'], 'data2': range(3)})
print(df1)
print(df2)
pd.merge(df1, df2)<jupyter_output><empty_output><jupyter_text>* Note that I didn’t specify which column to join on. If not specified, merge uses the overlapping column names as the keys. * If the column names are different in each object, you can specify them separately:<jupyter_code>df3 = pd.DataFrame({'lkey': ['b', 'b', 'a', 'c', 'a', 'a', 'b'], 'data1': range(7)})
df4 = pd.DataFrame({'rkey': ['a', 'b', 'd'], 'data2': range(3)})
pd.merge(df3, df4, left_on='lkey', right_on='rkey')<jupyter_output><empty_output><jupyter_text>* You probably noticed that the 'c' and 'd' values and associated data are missing from the result.* By default merge does an 'inner' join; the keys in the result are the intersection. * Other possible options are 'left', 'right', and 'outer'.<jupyter_code>pd.merge(df1, df2, how='outer')
pd.merge(df1, df2, on='key', how='left')
pd.merge(df1, df2, on='key', how='right')<jupyter_output><empty_output><jupyter_text>* In some cases, the merge key or keys in a DataFrame will be found in its index. In this case, you can pass left_index=True or right_index=True (or both) to indicate that the index should be used as the merge key.<jupyter_code>df5 = pd.DataFrame({'group_val': [3.5, 7]}, index=['a', 'b'])
df5
pd.merge(df4, df5, left_on='rkey', right_index=True)<jupyter_output><empty_output><jupyter_text>* DataFrame has a more convenient join instance for merging by index. * It can also be used to combine together many DataFrame objects having the same or similar indexes but non-overlapping columns.<jupyter_code>df6 = df4.copy()
df6.index = df6['rkey']
df6.join(df5, how='outer')<jupyter_output><empty_output><jupyter_text>Concatenation * A common data manipulation is appending rows or columns to a dataset that already conform to the dimensions of the exsiting rows or colums, respectively. In NumPy, this is done either with `concatenate` `vstack`, `np.hstack`.<jupyter_code>np.concatenate([np.random.random(5), np.random.random(5)])
np.hstack([np.random.random(5), np.random.random(5)])<jupyter_output><empty_output><jupyter_text>* This operation is also called *binding* or *stacking*.* With Pandas' indexed data structures, there are additional considerations as the overlap in index values between two data structures affects how they are concatenate.<jupyter_code>s1 = pd.Series([0, 1], index=['a', 'b'])
s2 = pd.Series([2, 3, 4], index=['c', 'd', 'e'])
s3 = pd.Series([5, 6], index=['f', 'g'])
pd.concat([s1, s2, s3])<jupyter_output><empty_output><jupyter_text>* If you pass axis=1, the result will instead be a DataFrame (axis=1 is the columns):<jupyter_code>pd.concat([s1, s2, s3], axis=1)<jupyter_output><empty_output><jupyter_text>* In this case there is no overlap on the other axis, which as you can see is the sorted union (the 'outer' join) of the indexes. You can instead intersect them by passing join='inner':<jupyter_code>s4 = pd.concat([s1 * 5, s3])
s4
pd.concat([s1, s4], axis=1, join='inner')<jupyter_output><empty_output><jupyter_text>* One issue is that the concatenated pieces are not identifiable in the result. Suppose instead you wanted to create a hierarchical index on the concatenation axis. To do this, use the keys argument:<jupyter_code>result = pd.concat([s1, s2, s3], keys=['one', 'two', 'three'])
result.unstack()<jupyter_output><empty_output><jupyter_text>Reshaping and Pivoting * There are a number of fundamental operations for rearranging tabular data. These are alternatingly referred to as reshape or pivot operations.  Reshaping with Hierarchical Indexing * Hierarchical indexing provides a consistent way to rearrange data in a DataFrame. There are two primary actions:    * stack: this “rotates” or pivots from the columns in the data to the rows        * unstack: this pivots from the rows into the columns<jupyter_code>data = pd.DataFrame(np.arange(12).reshape((3, 4)),
                 index=['Istanbul', 'Ankara', 'Izmir'],
                 columns=['one', 'two', 'three', 'four'])
data<jupyter_output><empty_output><jupyter_text>* Using the stack method on this data pivots the columns into the rows, producing a Series:<jupyter_code>data.stack()
data.stack().unstack()<jupyter_output><empty_output><jupyter_text>* By default the innermost level is unstacked (same with stack). You can unstack a different level by passing a level number or name:<jupyter_code>data
data.stack().unstack(0)<jupyter_output><empty_output><jupyter_text>Pivoting “long” to “wide” Format * A common way to store multiple time series in databases and CSV is in so-called long or stacked format:<jupyter_code>data = pd.DataFrame({'date': ["1959-03-31 00:00:00", "1959-03-31 00:00:00", "1959-03-31 00:00:00", 
                              "1959-06-30 00:00:00", "1959-06-30 00:00:00", "1959-06-30 00:00:00", 
                              "1959-09-30 00:00:00", "1959-09-30 00:00:00", "1959-09-30 00:00:00", 
                              "1959-12-31 00:00:00"],
                    'item': ['realgdp','infl','unemp','realgdp','infl','unemp','realgdp','infl','unemp','realgdp'],
                    'value': [2710.349, 0.000, 5.800, 2778.801, 2.340, 5.100, 2775.488, 2.740, 5.300, 2785.204]
                    })
data<jupyter_output><empty_output><jupyter_text>* Data is frequently stored this way in relational databases like MySQL as a fixed schema(column names and data types) allows the number of distinct values in the item column to increase or decrease as data is added or deleted in the table.* In the above example date and item would usually be the primary keys (in relational database parlance), offering both relational integrity and easier joins and programmatic queries in many cases.* The downside, of course, is that the data may not be easy to work with in long format; you might prefer to have a DataFrame containing one column per distinct item value indexed by timestamps in the date column. DataFrame’s pivot method performs exactly this transformation:<jupyter_code>pivoted = data.pivot('date', 'item', 'value')
pivoted<jupyter_output><empty_output><jupyter_text>* Note that pivot is just a shortcut for creating a reshaping with unstack.  Data Transformation * So far in this chapter we’ve been concerned with rearranging data.* Filtering, cleaning, and other tranformations are another class of important operations.  Removing Duplicates * Duplicate rows may be found in a DataFrame for any number of reasons. Here is an example:<jupyter_code>data = pd.DataFrame({'k1': ['one'] * 3 + ['two'] * 4,'k2': [1, 1, 2, 3, 3, 4, 4]})
data<jupyter_output><empty_output><jupyter_text>* The DataFrame method duplicated returns a boolean Series indicating whether each row is a duplicate or not:<jupyter_code>data.duplicated()<jupyter_output><empty_output><jupyter_text>* Also, we can use this as a mask for finding duplicates.<jupyter_code>data[data.duplicated()]<jupyter_output><empty_output><jupyter_text>* Other way to get duplicated values is using drop_duplicates which returns a DataFrame where the duplicated array is True<jupyter_code>data.drop_duplicates()<jupyter_output><empty_output><jupyter_text>* Also, you can filter duplicates based on a column.<jupyter_code>data.drop_duplicates(['k1'])<jupyter_output><empty_output><jupyter_text>Transforming Data Using a Function or Mapping  Mapping * For many data sets, you may wish to perform some transformation based on the values in an array, Series, or column in a DataFrame.  * Consider the following hypothetical data collected about customers.<jupyter_code>data = pd.DataFrame({'names': ['ali', 'Burcu', 'Volkan', 'Filiz', 'Caner', 
                               'Emre', 'Dilek', 'Gamze'],
                    'birthYear': [1984, 1993, 1992, 1976, 1985, 1978, 1973, 1995]})
data<jupyter_output><empty_output><jupyter_text>* And consider that the following hypothetical data also collected about same customers from different source.<jupyter_code>birthPlaces = {'ali': 'Istanbul', 'burcu': 'Bursa', 'volkan': 'Bursa', 
                  'filiz': 'Ankara', 'caner': 'Izmir', 'emre': 'Izmir',
                  'dilek': 'Antalya', 'gamze': 'Istanbul'
}
birthPlaces<jupyter_output><empty_output><jupyter_text>Suppose you wanted to add a column to `DataFrame` which indicate the birth place of customers.<jupyter_code>data['birthPlace'] = data['names'].map(str.lower).map(birthPlaces)
data<jupyter_output><empty_output><jupyter_text>* We can also do same operation with lambda expressions.<jupyter_code>data['names'].map(lambda x: birthPlaces[x.lower()])<jupyter_output><empty_output><jupyter_text>Replacing Values * Filling in missing data with the fillna method can be thought of as a special case of more general value replacement.* While map, as you’ve seen above, can be used to modify a subset of values in an object.* Replace provides a simpler and more flexible way to do so.<jupyter_code>data = pd.Series([1., -999., 2., -999., -1000., 3.])
data<jupyter_output><empty_output><jupyter_text>* The -999 values might be sentinel values for missing data. * To replace these with NaN values that pandas understands, we can use replace.<jupyter_code>data.replace(-999, np.nan)<jupyter_output><empty_output><jupyter_text>* Notice that replace method return new Series.<jupyter_code>data
data = data.replace(-999, np.nan)
data<jupyter_output><empty_output><jupyter_text>Renaming Axis Indexes * Like values in a Series, axis labels can be similarly transformed by a function or mapping of some form to produce new, differently labeled objects.* The axes can also be modified in place without creating a new data structure.<jupyter_code>data = pd.DataFrame(np.arange(12).reshape((3, 4)),
                 index=['Istanbul', 'Ankara', 'Izmir'],
                 columns=['one', 'two', 'three', 'four'])
data<jupyter_output><empty_output><jupyter_text>* Like a Series, the axis indexes have a map method.<jupyter_code>data.index.map(str.upper)
data.index = data.index.map(str.upper)
data<jupyter_output><empty_output><jupyter_text>Discretization * Pandas' `cut` function can be used to group continuous or countable data in to bins.* Discretization is generally a very **bad idea** for statistical analysis, so use this function responsibly! * Suppose you have data about a group of people in a study, and you want to group them into discrete age buckets:<jupyter_code>ages = [20, 22, 25, 27, 21, 23, 37, 31, 61, 45, 41, 32]<jupyter_output><empty_output><jupyter_text>* Let’s divide these into bins of 18 to 25, 26 to 35, 35 to 60, and finally 60 and older. To do so, you have to use cut, a function in pandas:<jupyter_code>bins = [18, 25, 35, 60,100]
groups = pd.cut(ages, bins)
groups<jupyter_output><empty_output><jupyter_text>* The object pandas returns is a special Categorical object.* You can treat it like an array of strings indicating the bin name; internally it contains a levels array indicating the distinct category names along with a labeling for the ages data in the codes attribute:<jupyter_code>groups.codes
groups.categories
pd.value_counts(groups)<jupyter_output><empty_output><jupyter_text>* You can also pass your own bin names by passing a list or array to the labels option:<jupyter_code>group_names = ['Youth', 'YoungAdult', 'MiddleAged', 'Senior']
pd.cut(ages, bins, labels=group_names)<jupyter_output><empty_output><jupyter_text>* A closely related function, qcut, bins the data based on sample quantiles.<jupyter_code>data = np.random.randn(1000)
quantiles = pd.qcut(data, 4)
pd.value_counts(quantiles)<jupyter_output><empty_output><jupyter_text>Permutation and sampling * For some data analysis tasks, such as simulation, we need to be able to randomly reorder our data, or draw random values from it. * Calling NumPy's `permutation` function with the length of the sequence you want to permute generates an array with a permuted sequence of integers, which can be used to re-order the sequence.<jupyter_code>sampler = np.random.permutation(5)
sampler<jupyter_output><empty_output><jupyter_text>* That array can then be used in the take function:<jupyter_code>df = pd.DataFrame(np.arange(5 * 4).reshape(5, 4))
df
df.take(sampler)<jupyter_output><empty_output><jupyter_text>Data aggregation and GroupBy operations One of the most powerful features of Pandas is its **GroupBy** functionality. On occasion we may want to perform operations on *groups* of observations within a dataset. For exmaple:* **aggregation**, such as computing the sum of mean of each group, which involves applying a function to each group and returning the aggregated results* **slicing** the DataFrame into groups and then doing something with the resulting slices (*e.g.* plotting)* group-wise **transformation**, such as standardization/normalization * A common data analysis procedure is the **split-apply-combine** operation, which groups subsets of data together, applies a function to each of the groups, then recombines them into a new data table.* For example, we may want to aggregate our data with with some function. ![split-apply-combine](images/groupByVisual.png)*(figure taken from "Python for Data Analysis", p.251)* *  To get started, here is a very simple small tabular dataset as a DataFrame:<jupyter_code>df = pd.DataFrame({
    'key1' : ['<KEY>'],
    'key2' : ['one', 'two', 'one', 'two', 'one'],
    'data1' : np.random.randn(5), 
    'data2' : np.random.randn(5)
})
df<jupyter_output><empty_output><jupyter_text>* Suppose you wanted to compute the mean of the data1 column using the groups labels from key1.* There are a number of ways to do this. One is to access data1 and call groupby with the column (a Series) at key1.<jupyter_code>grouped = df['data1'].groupby(df['key1'])
grouped<jupyter_output><empty_output><jupyter_text>* This grouped variable is now a GroupBy object.* It has not actually computed anything yet except for some intermediate data about the group key df['key1']. * The idea is that this object has all of the information needed to then apply some operation to each of the groups.(lazy evulation) * For example, to compute group means we can call the GroupBy’s mean method:<jupyter_code>grouped.mean()
df.groupby(['key1', 'key2']).mean()<jupyter_output><empty_output><jupyter_text>* The `add_prefix` and `add_suffix` methods can be used to give the columns of the resulting table labels that reflect the transformation:<jupyter_code>df.groupby(['key1', 'key2']).mean().add_suffix('_mean')
grouped.mean().add_prefix('MeanOf')<jupyter_output><empty_output><jupyter_text>* The GroupBy object supports iteration, generating a sequence of 2-tuples containing the group name along with the chunk of data.<jupyter_code>for name, group in df.groupby('key1'): 
    print(name)
    print(group)<jupyter_output>a
      data1     data2 key1 key2
0  0.619755 -1.852475    a  one
1 -0.990023  0.152956    a  two
4  0.277437 -1.481521    a  one
b
      data1     data2 key1 key2
2  0.527230 -1.175463    b  one
3 -1.266556 -0.236212    b  two<jupyter_text>* In the case of multiple keys, the first element in the tuple will be a tuple of key values:<jupyter_code>for (k1, k2), group in df.groupby(['key1', 'key2']):
    print(k1, k2)
    print group<jupyter_output>('a', 'one')
      data1     data2 key1 key2
0  0.619755 -1.852475    a  one
4  0.277437 -1.481521    a  one
('a', 'two')
      data1     data2 key1 key2
1 -0.990023  0.152956    a  two
('b', 'one')
     data1     data2 key1 key2
2  0.52723 -1.175463    b  one
('b', 'two')
      data1     data2 key1 key2
3 -1.266556 -0.236212    b  two
{'fraction_non_alphanumeric': 0.1054925893635571, 'fraction_numerical': 0.033838273757628594, 'mean_word_length': 5.12787979966611, 'pattern_counts': {'":': 0, '<': 184, '<?xml version=': 0, '>': 184, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 3, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '46', 'dir': 'jupyter-structured-clean-dedup', 'id': '348722', 'license': 'Apache-2.0', 'n_tokens_mistral': 6093, 'n_tokens_neox': 5606, 'n_words': 2143, 'path': 'iPython Notebooks/Introduction to Pandas Part 2.ipynb', 'repo_name': 'AlpYuzbasioglu/Zeppelin-Notebooks', 'size': '18368'}