<jupyter_start><jupyter_text>Importing Pandas<jupyter_code>import pandas as pd
pd.__version__<jupyter_output><empty_output><jupyter_text>Pandas `Series` ClassA Pandas Series object is like a generalized array where the indexes are explicit, don't have to be contiguous or increasing, and in fact don't have to be integers.  The object also behaves like a specialised dictionary where the keys are in order (though not necessarily sorted).  Implicit indexes<jupyter_code># Simple series
data = pd.Series([0.25, 0.5, 0.75, 1.0])
data
data.index # a pd.Index object
data.values # a NumPy array<jupyter_output><empty_output><jupyter_text>Explicit indexesThe difference between a Pandas Series and a NumPy array is that the Pandas object has an **explicit** index. Indexes need not be contiguous or even monotonic.  Here, we specify an explicit index which are not contiguous or uniformly increasing. Series will preserve the order in which we specified the index.<jupyter_code>data = pd.Series([0.25, 0.5, 0.75, 1.0], index=[2,5,3,7])
data
data.index<jupyter_output><empty_output><jupyter_text>Explicit, Non-integer indexes Any data type can be used as an index.<jupyter_code>data = pd.Series([0.25, 0.5, 0.75, 1.], index=['a', 'b', 'c', 'd'])
data
data.index<jupyter_output><empty_output><jupyter_text>We can construct a Series object directly from a Python dictionary. The dict's keys will be the Series' index. The dict's keys will be **sorted**.  Note below: `Indonesia` comes before `USA` in the Series.<jupyter_code>pop_dict = {'China': 13888170000,
            'India': 1325460000,
            'USA': 326309000,
            'Indonesia': 261890900}
pop = pd.Series(pop_dict)
pop
pop['China']<jupyter_output><empty_output><jupyter_text>Constructing Series<jupyter_code># From a list
pd.Series([1,2,3,4])
# From a scalar, repeated to fill the specified index
pd.Series(10, index=[100, 200, 300])
# From a dictionary, indexes are sorted dictionary keys
pd.Series({2:'a', 1:'b', 3:'c'})
# Index can be explicitly set with a dictionary
pd.Series({2:'a', 1:'b', 3:'c'}, index=[3,2]) # Note: indexes in order as specified, not sorted<jupyter_output><empty_output><jupyter_text>Pandas `DataFrame` Class A Pandas DataFrame is like a generalized two-dimensional matrix, where the row indices are flexible like a Series and column have names. It can be thought of a a collection of Series objects, which have a shared index.<jupyter_code>pop_dict = {'China': 13888170000,
            'India': 1325460000,
            'USA': 326309000,
            'Indonesia': 261890900}
pop = pd.Series(pop_dict)
pop
area_dict = {'China': 9596960,
             'India': 3287590,
             'Indonesia': 1905000,
             'USA': 9631418}
area = pd.Series(area_dict)
area
countries = pd.DataFrame({'pop': pop, 'area': area})
countries
countries.index
countries.columns
countries.values<jupyter_output><empty_output><jupyter_text>DataFrame as a specialized dictionaryA DataFrame also behaves like a dictionary.  Its keys are *column names*.  This is different from a numpy array, where `data[0]` would return the first row.  In a DataFrame you index by columns: `data['colname']`.<jupyter_code>countries['area']
countries['pop']<jupyter_output><empty_output><jupyter_text>Constructing DataFrames<jupyter_code># From a list of dicts; each element is a row
lst_dicts = [ {'a': i, 'b': i+1} for i in range(3)]
lst_dicts
pd.DataFrame(lst_dicts)
# From a NumPy 2-dimensional array
import numpy as np
np_array = np.random.rand(3,2)
np_array
pd.DataFrame(np_array, columns=['foo', 'bar'], index=['a', 'b', 'c'])
# From a single Series object
pd.DataFrame(area, columns=['area'])
# From a dictionary of Series objects
pd.DataFrame({'pop': pop, 'area': area})<jupyter_output><empty_output><jupyter_text>Pandas `Index` Class<jupyter_code>idx = pd.Index([2, 3, 5, 7, 11])
idx
idx[1]
idx[::3]
(idx.size, idx.shape, idx.ndim, idx.dtype)
# Indexes are immutable
# idx[1] = 0 ## Will raise error<jupyter_output><empty_output><jupyter_text>Indexes as ordered sets<jupyter_code>idx1 = pd.Index([1, 3, 5, 7, 9])
idx2 = pd.Index([2, 3, 5, 7, 11])
idx1 & idx2 # Intersection
idx1 | idx2 # union
idx1 ^ idx2 # symmetric difference, or xor<jupyter_output><empty_output><jupyter_text>Indexing `Series` and `DataFrame` objects  Series indexing<jupyter_code>data = pd.Series({'a':1, 'b':2, 'c':3}, dtype='f8')
data<jupyter_output><empty_output><jupyter_text>Series as a dictionary<jupyter_code>data.keys()
data['b']
# Series can be treated as a dictionary
data['e'] = 1.25
data
# Series as a collection of keys
'a' in data
list(data.items()) # the list() is important; items() returns a lazy iterator<jupyter_output><empty_output><jupyter_text>Series as a one-dimensional arrayPandas style indexing.<jupyter_code>data['a':'c'] # the `stop` values is included<jupyter_output><empty_output><jupyter_text>Python style indexing.<jupyter_code>data[0:2] # the `stop` value is not inclueded
# masking, like numpy
data[(data > 1) & (data < 2)]
# fancy indexing, like numpy
data[['a', 'b']] # but you can't use a 2-d array, like [['a'], ['b']]<jupyter_output><empty_output><jupyter_text>Explicit index or Python style index?If the Series object has explicit *integer* indexes, then:`data[i]` will use the explicit index, while `data[i:j]` will use Python style indexes.  This can be confusing.<jupyter_code>data = pd.Series(['a', 'b', 'c', 'd', 'e'], index=[1,2,3,4,5])
data
data[1] # explicit index
data[1:3] # implicit, 0-based, Python style index; {1:'a'} not inclued in output because its implicit index is 0.<jupyter_output><empty_output><jupyter_text>Choosing Pandas vs NumPy indexing: `loc`, `iloc`<jupyter_code># loc always uses explicit indexes, and includes the final index value
data.loc[1:3]
# iloc always uses the position-based index, and exludes the final index value
data.iloc[1:3]<jupyter_output><empty_output><jupyter_text>DataFrame indexing<jupyter_code>pop_dict = {'China': 1388817000,
            'India': 1325460000,
            'USA': 326309000,
            'Indonesia': 261890900}
pop = pd.Series(pop_dict)
area_dict = {'China': 9596960,
             'India': 3287590,
             'Indonesia': 1905000,
             'USA': 9631418}
area = pd.Series(area_dict)
data = pd.DataFrame({'pop': pop, 'area': area})
data<jupyter_output><empty_output><jupyter_text>DataFrame as a dictionary DataFrame can be treated as a dictionary, mapping column names to a Series (not an array of raw values!):<jupyter_code>data['area']
type(data['area'])<jupyter_output><empty_output><jupyter_text>If the column names are simple strings, it can be convenient to access them as attributes:<jupyter_code>data.area<jupyter_output><empty_output><jupyter_text>The two styles access the same underlying object:<jupyter_code>data.area is data['area']<jupyter_output><empty_output><jupyter_text>**Unless**, the column name is the same as a DataFrame method name. In that case, the attribute refers to the method.<jupyter_code>data.pop is data['pop']
type(data.pop)<jupyter_output><empty_output><jupyter_text>To be safe, at least for assignment, use the dictionary style access, otherwise you may end up over-writing a method attribute and create hard to debug bugs.<jupyter_code>data['density'] = data['pop'] / data['area']
data<jupyter_output><empty_output><jupyter_text>DataFrame as a 2-dimensional array Some array-like operations can be done on the DataFrame.<jupyter_code>data.T # Transpose<jupyter_output><empty_output><jupyter_text>But DataFrame itself is not a pure array.  For e.g., in `data[idx]`, `idx` has to be a column name. In an array, it would be the row index.The underlying data is a NumPy array.<jupyter_code>data.values<jupyter_output><empty_output><jupyter_text>We can do use all indexing methods supported by NumPy on the values, but we lose the nice row and column labels.<jupyter_code>data.values[0]
data.values[:,1]<jupyter_output><empty_output><jupyter_text>Choosing Pandas vs NumPy indexing: `loc`, `iloc` We should use the `loc` and `iloc` attributes, instead.  These will preserve row and column labels.<jupyter_code>data.iloc[:3, :2]
data.loc['India', :'pop']<jupyter_output><empty_output><jupyter_text>`iloc` and `loc` support all NumPy style indexing methods.<jupyter_code>data.loc[data.density > 100, ['density', 'pop']]<jupyter_output><empty_output><jupyter_text>Note: indexing vs slicing/masking without `loc` or `iloc`When indexing DataFrame directly, a single index refers to a column name<jupyter_code>data['area']<jupyter_output><empty_output><jupyter_text>But a *slice* refers to rows:<jupyter_code>data['China':'India']<jupyter_output><empty_output><jupyter_text>*Direct* masking operations also refers to rows:<jupyter_code>data[data.density > 100]<jupyter_output><empty_output><jupyter_text>DataFrame does not support fancy indexing directly. Use `loc` or `iloc` for that.<jupyter_code># data[[1, 2, 3]] ## will raise error<jupyter_output><empty_output><jupyter_text>OperationsNumPy *ufuncs* work on Pandas Series and DataFrame objects (not merely on the underlying numpy array holding the data).  Index PreservationUnary operations preserve the *index* and *column labels* in the output.<jupyter_code>rng = np.random.RandomState(42)
# A Series
ser = pd.Series(rng.randint(0, 10, 4))
ser
# A DataFrame
df = pd.DataFrame(rng.randint(0, 10, (3, 4)), columns=['A', 'B', 'C', 'D'])
df
# Operation on series
np.exp(ser)
# operation on DataFrame
np.sin(df * np.pi / 4)
df.mean() # axis=0 by default, that is, column-wise operation
df.mean(axis=1) # row-wise operation<jupyter_output><empty_output><jupyter_text>Index AlignmentBinary operations on Series or DataFrames *align* the indices. Index Alignment in Series<jupyter_code>area = pd.Series({'Alaska': 1723337, 'Texas': 695662}, name='area')
pop = pd.Series({'California': 38332521, 'Texas': 26448192})
pop / area<jupyter_output><empty_output><jupyter_text>Note the row indices are the union of the indices of the two inputs.  Any index for which one or the other input does not have an entry is marked with `NaN`.  This is how missing values are handled for built-in Python operators.<jupyter_code>area.index | pop.index<jupyter_output><empty_output><jupyter_text>For handling missing data differently, object methods need to be used in place of the operators.<jupyter_code>pop.divide(area, fill_value=0)<jupyter_output><empty_output><jupyter_text>Index Alignment in DataFrame<jupyter_code>df1 = pd.DataFrame(rng.randint(0, 20, (2, 2)), columns=['A', 'B'], index=['a', 'b'])
df1
df2 = pd.DataFrame(rng.randint(0, 10, (2, 3)), columns=['B', 'A', 'C'], index=['b', 'a'])
df2
df1 + df2 # Note: indexes are aligned regardless of order, and are sorted in the output<jupyter_output><empty_output><jupyter_text>Instead of using built-in Python operators, we can use equivalen Pandas methods, which gives us control on how to handle missing values.<jupyter_code>df1.add(df2, fill_value=1000)<jupyter_output><empty_output><jupyter_text>Operations between DataFrame and Series<jupyter_code>A = rng.randint(10, size=(3, 4)) # A NumPy array
A
A - A[0] # Operation between a 2-dimensional and 1-dimensional NumPy array
df = pd.DataFrame(A, columns=list('QRST'))
df
df.stack() # convert to hierarchically indexed series
df - df.iloc[0] # Operation between DataFrame and Series. Note: row-wise operations
df.subtract(df['R'], axis=0) # Column-wise operations: needs explicit method call instead of using operator
# Different sized DataFrame and Series
halfrow = df.iloc[0, ::2]
halfrow
df - halfrow<jupyter_output><empty_output><jupyter_text>Missing DataMissing data can be handled by using Python `None`, or by `NaN` for floating point data (only).  `None` amd `Nan` in NumPyHaving `None` in a NumPy array means that the array's dtype is `object` and all operations will take place in Python, not in optimized native code.<jupyter_code>for t in ['object', 'int32', 'float32']:
    print("dtype =", t)
    arr = np.arange(1E6, dtype=t) # create an array of type t, containing 1 million numbers
    %timeit arr.sum()<jupyter_output>dtype = object
20.4 ms ± 197 µs per loop (mean ± std. dev. of 7 runs, 10 loops each)
dtype = int32
562 µs ± 5.84 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)
dtype = float32
209 µs ± 3.13 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)<jupyter_text>Some operations, especially aggregations, done't like `None` values.<jupyter_code>vals1 = np.array([1, None, 5, 6])
vals1, vals1.dtype
# vals1.sum() ## Cannot handle `None`, will raise TypeError<jupyter_output><empty_output><jupyter_text>Arrays containing only floating point values can use the `NaN` value.<jupyter_code>vals2 = np.array([1., np.nan, 5., 6.])
vals2, vals2.dtype<jupyter_output><empty_output><jupyter_text>Now aggregations won't throw an error, but won't contain anything useful either.<jupyter_code>vals2.sum()<jupyter_output><empty_output><jupyter_text>There are `NaN` friendly versions of the aggregation methods as NumPy functions.<jupyter_code>np.nansum(vals2)
arr1 = np.arange(1e6)
arr2 = np.arange(1e6); arr2[0] = np.nan
%timeit arr1.sum()
%timeit np.nansum(arr1)
%timeit arr2.sum() # note: will return nan
%timeit np.nansum(arr2)<jupyter_output>1.51 ms ± 47.3 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)<jupyter_text>The `NaN` friendly versions are ~5x slower.  `None` and `NaN` in Pandas SeriesPandas will convert `None` to `NaN` if all other values are floats.  Ints<jupyter_code>pd.Series([1, np.nan, 2, None])<jupyter_output><empty_output><jupyter_text>Pandas will automatically typecast non-floats to floats to accomodate `NaN`s when possible.<jupyter_code>pd.Series([1, 2, 3, None])
x = pd.Series(range(2), dtype=int)
x # Note dtype
x[0] = None
x # Note new dtype<jupyter_output><empty_output><jupyter_text>Bools<jupyter_code>y = pd.Series([True, False, True])
y
y[0] = None # None is treated as False, not as a missing value
y
y[0] = np.nan # NaN is not treated as False; it is a true missing value
y<jupyter_output><empty_output><jupyter_text>Objects<jupyter_code>z = pd.Series(['A', 'B', 'C'])
z
z[0] = None
z
z[0] = np.nan
z
z.isnull()
z.notnull()
z.dropna()
z.fillna(value='OMG')
z.fillna(method='bfill') # Replace NaN's with objects from higher index<jupyter_output><empty_output><jupyter_text>`None` and `NaN` in Pandas DataFrameDataFrame's `dropna` method has options to specify the axis, and also to specify how many values must be null for a row or col to be dropped, or how many non-null values must exist for a row or col with a null value to be preserved.<jupyter_code>df = pd.DataFrame([[1,         np.nan,       np.nan],
                   [2,         np.nan,       np.nan],
                   [3,         4,            np.nan]
                ])
df
df.isnull()
df.dropna() # drop all rows that contain even a single NaN
df.dropna(axis=1) # drop columns, not rows
df.dropna(axis=1, how='all') # drop columns, if all their values are NaN
df.dropna(axis=1, how='any') # same as dropna(axis=1)
df.dropna(thresh=2) # drop rows with NaN, keeping those that have 2 or more non-NaN values
df.fillna(value=pd.Series([1000, 2000, 3000]))
# df.fillna(value=pd.Series([1000, 2000, 3000]), axis=1) ## Not implemented yet
df2 = pd.DataFrame([[1000, 1000, 1000],
                    [2000, 2000, 2000],
                    [3000, 3000, 3000]
                   ])
df.fillna(value=df2)
df.fillna(method='ffill', axis=1) # Fill-forward along rows<jupyter_output><empty_output><jupyter_text>Pandas MultiIndex: Hierarchical Indexing<jupyter_code>index = pd.MultiIndex.from_product([['California', 'Texas', 'New York'], [2000, 2010]], names=['State', 'Year'])
index
pop = pd.Series([12345, 23456, 34567, 45678, 56789, 67890], index=index, name='Population')
pop
pop['California']
pop[:, 2010]
df = pop.unstack() # convert to 2-dimensional DataFrame
df
df.stack() # convert 2-dimensional DataFrame to a hierarchically indexed Series
# Multiple Series
df = pd.DataFrame({'Total': pop, 'Under18': [2345, 3456, 4567, 5678, 6789, 7890]})
df
f_u18 = df['Under18'] / df['Total']
f_u18
f_u18.unstack()<jupyter_output><empty_output><jupyter_text>Creating MultiIndex explicitlyYou can create a MultiIndex object and give it to the `index` argument of a Series or DataFrame. There are 4 ways of creating MultiIndexes.<jupyter_code># From tuples
pd.MultiIndex.from_tuples([('a', 1), ('a', 2), ('b', 1), ('b', 2)])
# From arrays
pd.MultiIndex.from_arrays([list('aabb'), [1, 2, 1, 2]])
# From a cross-product, when every first level has every second level
pd.MultiIndex.from_product([['a', 'b'], [1, 2]])
# Explicitly using `levels` and `labels` arguments to constructor
pd.MultiIndex(levels=[['a', 'b'], [1, 2]],
             labels=[[0, 0, 1, 1], [0, 1, 0, 1]])<jupyter_output><empty_output><jupyter_text>Creating MuiltiIndexes implicitlyYou can make Series and DataFrame compute a MultiIndex automatically<jupyter_code># From arrays
data = np.random.rand(4, 2)
pd.DataFrame(data,
            index=[['a', 'a', 'b', 'b'], [1, 2, 1, 2]],
            columns=['data1', 'data2'])
# From data, where the keys are tuples
pd.Series({('a', 1): 12345,
             ('a', 2): 23456,
             ('b', 1): 34567,
             ('b', 2): 45678})<jupyter_output><empty_output><jupyter_text>MultiIndexed Columns<jupyter_code>rows = pd.MultiIndex.from_product([[2010, 2011], [1, 2]], names=['Year', 'Visit#'])
cols = pd.MultiIndex.from_product([['Bob', 'Amy', 'Sue'], ['B.P.', 'Temp']], names=['Subject', 'Type'])

data = np.round(np.random.randn(4, 6), 1)
health_df = pd.DataFrame(data, index=rows, columns=cols)
health_df<jupyter_output><empty_output><jupyter_text>Column Indexing<jupyter_code>health_df['Bob']
health_df['Amy', 'Temp']<jupyter_output><empty_output><jupyter_text>Tuples can be used too.<jupyter_code>health_df[('Amy', 'Temp')]<jupyter_output><empty_output><jupyter_text>Row IndexingTo index rows, `loc` or `iloc` must be used.  Top-level indexes can be specified directly.<jupyter_code>health_df.loc[2010]<jupyter_output><empty_output><jupyter_text>Multi-level indexes can be specified using tuples.<jupyter_code>health_df.loc[(2010, 1)]<jupyter_output><empty_output><jupyter_text>Row and Column Indexing<jupyter_code>health_df.loc[(2010, 1), 'Bob']
health_df.loc[(2010, 1), ('Amy', 'Temp')]<jupyter_output><empty_output><jupyter_text>Slices with `loc`For slices to work with `loc`, the rows or columns being sliced must be **ordered**.Column slicing:<jupyter_code># health_df.loc[(2010, 1), 'Amy':] ## Will raise error.
health_df.sort_index(axis=1, inplace=True)
health_df
health_df.loc[(2010, 1), 'Bob':] # Won't raise error now
health_df.loc[:, 'Bob']<jupyter_output><empty_output><jupyter_text>Second level slices need an explicit slice object, because creating slices in tuples leads to a syntax error.<jupyter_code># health_df.loc[(:, 1), 'Bob']  # (:, 1) => syntax error
idx = pd.IndexSlice
health_df.loc[idx[:, 1], 'Bob']
idx = pd.IndexSlice
health_df.loc[idx[:, 1], idx[:, 'Temp']]<jupyter_output><empty_output><jupyter_text>Slices with `iloc`Same as `loc`, except that all indices are integers.Examplles: TODO.  Creating MultiIndexes from 2-dimensional DataFrames<jupyter_code># A 2-D DataFrame, such as one you'd get by reading a CSV file
df = pd.DataFrame([['California', 2000, 12345],
                  ['California', 2010, 23456],
                  ['New York', 2000, 34567],
                  ['New York', 2010, 45678],
                  ['Texas', 2000, 56789],
                  ['Texas', 2010, 67890]],
                 columns=['State', 'Year', 'Population'])
df
# Convert to MultiIndex
df.set_index(['State', 'Year'], inplace=True)
df
# And back
df.reset_index()<jupyter_output><empty_output><jupyter_text>Simple Data AggregationPandas' `Series` and `DataFrame` objects have numerours methods to compute aggregates, such as the sum, min, max, of quantities.  All the aggregations available in NumPy are also available in Pandas.Given a `DataFrame`:<jupyter_code>health_df<jupyter_output><empty_output><jupyter_text>The `describe()` method gives several important aggregations in one shot, to give us a feel of the data.<jupyter_code>health_df.describe()<jupyter_output><empty_output><jupyter_text>We can compute individual aggregations:<jupyter_code>health_df.mean()<jupyter_output><empty_output><jupyter_text>Choosing the level in hierarchical indexesWith hierarchical indexes, we can choose what level to preserve (aggregating others).<jupyter_code>health_df.mean(level='Visit#')
health_df.mean(level='Year')<jupyter_output><empty_output><jupyter_text>We can specify the axis along which to apply the aggregation; we can also have successive aggregations.<jupyter_code>health_df.mean(level='Year').mean(level='Type', axis=1)<jupyter_output><empty_output><jupyter_text>ConcatenationWe can concat two `DataFrame` or `Series` objects.<jupyter_code>ser1 = pd.Series(['A', 'B', 'C'], index=[1,2,3])
ser2 = pd.Series(['D', 'E', 'F'], index=[4,5,6])
print(ser1); print(ser2); print(pd.concat([ser1, ser2]));<jupyter_output>1    A
2    B
3    C
dtype: object
4    D
5    E
6    F
dtype: object
1    A
2    B
3    C
4    D
5    E
6    F
dtype: object<jupyter_text>We can also concatenate `DataFrame` objects.First, a convenience function:<jupyter_code># A function to generate DataFrames quickly
def make_df(cols, ind):
    """Quickly make a DataFrame for tests."""
    data = {c: [str(c) + str(i) for i in ind] for c in cols}
    return pd.DataFrame(data, ind)
make_df('ABC', range(3))
df1 = make_df('AB', [1, 2])
df2 = make_df('AB', [3, 4])
print(df1); print(df2); print(pd.concat([df1, df2]))<jupyter_output>A   B
1  A1  B1
2  A2  B2
    A   B
3  A3  B3
4  A4  B4
    A   B
1  A1  B1
2  A2  B2
3  A3  B3
4  A4  B4<jupyter_text>Handling Duplicate IndicesSince Pandas indices are explicit, the indices of the objects to be concatenate may *overlap*.  The default behaviour of `concat` is to preserve the indices in specified order.<jupyter_code>x = make_df('AB', [0, 1]); x
y = make_df('AB', [1, 2]); y
pd.concat([x, y]) # 1 repeated!<jupyter_output><empty_output><jupyter_text>Catching duplicates as errorsWe can tell Pandas to verify that the indices in the result do not overlap.<jupyter_code>try:
    pd.concat([x, y], verify_integrity=True) # will raise ValueError
except ValueError as e:
    print("ValueError: ", e)<jupyter_output>ValueError:  Indexes have overlapping values: [1]<jupyter_text>Ignoring the specified indicesWe can get Pandas to generate new integer indices in the result, ignoring the indices in the inputs.<jupyter_code>pd.concat([x, y], ignore_index=True)<jupyter_output><empty_output><jupyter_text>Adding MultiIndex keysWe can ask Pandas to use hierarchically indexed results.  We do this by specifying labels for the input data sources using the `keys` option.  Pandas will then nest the indices of a data source under a top level index of the specified keys.<jupyter_code>print(x); print(y); print(pd.concat([x, y], keys=['x', 'y']))<jupyter_output>A   B
0  A0  B0
1  A1  B1
    A   B
1  A1  B1
2  A2  B2
      A   B
x 0  A0  B0
  1  A1  B1
y 1  A1  B1
  2  A2  B2<jupyter_text>Handling different columns - concat with joinIf the input `DataFrame` objects have different columns, the default behaviour of `concat` is to do an *outer*-join like operation, producing a result that has the union of columns. It will fill missing fields with `NaN` values.<jupyter_code>df1 = make_df('ABC', [1,2]); df1
df2 = make_df('BCD', [3,4]); df2
pd.concat([df1, df2])<jupyter_output><empty_output><jupyter_text>Using inner joinWe can tell Pandas to keep only the columns that are common to both inputs; that is, do an *inner8*-join.<jupyter_code>pd.concat([df1, df2], join='inner')<jupyter_output><empty_output><jupyter_text>Specifying the index of the columns explicitlyWe can force Pandas to use a specific list of index objects.  Here, we specify the column indices of the first input.<jupyter_code>df1.columns
pd.concat([df1, df2], join_axes=[df1.columns])<jupyter_output><empty_output><jupyter_text>`append` as a shortcut to `concat`Concatenating dataframes and series in the default way is common, so `append` is a shortcut for that.  `append` does not modify the original objects but creates a new one.  It is less efficient that `concat` especially when concatenating multiple objects.<jupyter_code>df1 = make_df('AB', [0, 1])
df2 = make_df('AB', [1, 2])
df1.append(df2, ignore_index=True)<jupyter_output><empty_output><jupyter_text>MergesPandas offers high-performance in-memory join and merge operations, like database `JOIN`s, via the `pd.merge` function.<jupyter_code>df1 = pd.DataFrame({'employee': ['Bob', 'Lisa', 'George', 'Sue'],
                    'department': ['Accounting', 'Engineering', 'Engineering', 'HR']})
df1
df2 = pd.DataFrame({'name': ['Bob', 'George', 'Sue', 'Lisa'],
                    'hire_date': [2001, 2010, 2009, 1005]})
df2
df3 = pd.DataFrame({'department': ['Accounting', 'Engineering', 'HR'], 
                   'supervisor': ['Alex', 'Guido', 'Carla']})
df3
df4 = pd.DataFrame({'department': ['Accounting', 'Accounting', 'Engineering', 'Engineering', 'HR'],
                   'skill': ['Math', 'Excel', 'Excel', 'Programming', 'organization']})
df4<jupyter_output><empty_output><jupyter_text>One-to-one joinsThis is the simplest case:<jupyter_code>df1.merge(df2, left_on='employee', right_on='name')<jupyter_output><empty_output><jupyter_text>We can get rid of the redundant column.<jupyter_code>df1.merge(df2, left_on='employee', right_on='name').drop('name', axis=1)<jupyter_output><empty_output><jupyter_text>One-to-Many joinsWhen one or the other data frame contains duplicate column values for the key column. Note also that Pandas automatically detect columns with the same name in both `DataFrame`s and uses them as the key column.<jupyter_code>df1.merge(df3)<jupyter_output><empty_output><jupyter_text>Many-to-Many joinsWhen key columns in both inputs contain duplicate values, we get a many-to-many join.<jupyter_code>df1.merge(df4)<jupyter_output><empty_output><jupyter_text>Joining on an index instead of column valueThe first data frame could have been like this:<jupyter_code>df1a = df1.set_index('employee'); df1a<jupyter_output><empty_output><jupyter_text>To join this to `df2`, we have to use indices on `df1` to match column values on `df2`.  We can use `left_index=True` (or `right_index=True`) to tell Pandas to use indices instead of column values for the left or right argument.<jupyter_code>df1a.merge(df2, left_index=True, right_on='name')
# Or even
df2.merge(df1a, left_on='name', right_index=True).set_index('name')<jupyter_output><empty_output><jupyter_text>When both inputs are keyed on the indicesIf both arguments are to be joined by their indices, we need to specify both `left_index` and `right_index` as `True`.  The `join` method is a short hand for this.<jupyter_code>df2a = df2.set_index('name'); df2a
df1a.merge(df2a, left_index=True, right_index=True)<jupyter_output><empty_output><jupyter_text>**Using `join`**:<jupyter_code>df1a.join(df2a)<jupyter_output><empty_output><jupyter_text>Inner, Outer, Left and Right joinsWhen the two inputs have some missing values in the columns to be joined, it becomes important to specify how to handle missing values using the `how` keyword, which takes the values `outer`, `inner`, `left`, `right`.<jupyter_code>df6 = pd.DataFrame({'name': ['Mary', 'Joseph', 'Peter'],
                   'food': ['Bread', 'Cheese', 'Fish']})
df6
df7 = pd.DataFrame({'name': ['Mary', 'Joseph', 'Paul'],
                   'drink': ['Wine', 'Beer', 'Mead']})
df7
df6.merge(df7) # default how=inner
df6.merge(df7, how='outer')
df6.merge(df7, how='left')
df6.merge(df7, how='right')<jupyter_output><empty_output><jupyter_text>Overlapping column namesWhen the two inputs have columns with the conflicting names (that is, the columns have different meanings but happen to have the same name), you can ask Pandas to generate unique column names using the `suffixes` option to `merge`.Also, since more than one column now has the same name in the inputs, we have to specify the name of the key column to join on.  (By default Pandas will use a composite key of all columns that have name in each input.)<jupyter_code>df8 = pd.DataFrame({'name': ['Mary', 'Joseph', 'Peter'],
                   'likes': ['Bread', 'Cheese', 'Fish']})
df8
df9 = pd.DataFrame({'name': ['Mary', 'Joseph', 'Paul'],
                   'likes': ['Wine', 'Beer', 'Mead']})
df9
df8.merge(df9, on='name', suffixes=['_eat', '_drink'])<jupyter_output><empty_output><jupyter_text>GroupBy: Aggregation, Filtering, Transform and grouping<jupyter_code>import seaborn as sns
planets = sns.load_dataset('planets')
planets.shape
planets.head()
decade = 10 * (planets['year'] // 10)
decade = decade.astype('str') + "s"
decade.name = 'decade'
planets.groupby(['method', decade])['number'].sum().unstack().fillna(0)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1314796218705634, 'fraction_numerical': 0.03355379341389937, 'mean_word_length': 5.462295815618707, 'pattern_counts': {'":': 0, '<': 348, '<?xml version=': 0, '>': 351, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '88', 'dir': 'jupyter-structured-clean-dedup', 'id': '187103', 'license': 'Apache-2.0', 'n_tokens_mistral': 10577, 'n_tokens_neox': 9808, 'n_words': 3008, 'path': 'machine_learning/jupyter_101/Pandas basics.ipynb', 'repo_name': 'parijatmishra/learn', 'size': '28879'}