<jupyter_start><jupyter_text>8.1 Hierarchical Indexing<jupyter_code>import pandas as pd
import numpy as np
# creating hierarchical indexing
data = pd.Series(np.random.randn(9),
                index = [['a', 'a', 'a', 'b', 'b', 'c', 'c', 'd', 'd'],
                        [1, 2, 3, 1, 3, 1, 2, 2, 3]])

data
data.index
# partial indexing
data['a']
data['b']
data['c']
data['b':'c']
data.loc[['b', 'd']]
# selection from an 'inner' level
data.loc[:, 2]
# from hierarchical to standard DataFrame
data.unstack()
# inverse of unstack
data.unstack().stack()<jupyter_output><empty_output><jupyter_text>**Either Axis can have Hierarchical Indexing**<jupyter_code>frame = pd.DataFrame(np.arange(12).reshape((4, 3)),
                    index = [['a', 'a', 'b', 'b'], [1, 2, 1, 2]],
                    columns = [['Ohio', 'Ohio', 'Colorado'],
                              ['Green', 'Red', 'Green']])
frame
frame.index.names=['key1', 'key2']
frame.columns.names=['state', 'color']
frame
# partial indexing
frame['Ohio']
# creating columns with pd.MultiIndexing
pd.MultiIndex.from_arrays([['Ohio', 'Ohio', 'Colorado'], ['Green', 'Red', 'Green']],
                         names = ['state', 'color'])

pd.DataFrame(np.arange(12).reshape((4, 3)),
             columns = pd.MultiIndex.from_arrays([['Ohio', 'Ohio', 'Colorado'], ['Green', 'Red', 'Green']],
                         names = ['state', 'color'])
            )<jupyter_output><empty_output><jupyter_text>Reordering and Sorting Levels<jupyter_code># swapping indexes
frame.swaplevel('key1', 'key2')
# sorting index
frame.sort_index(level=1)
# sorting with swap level
frame.swaplevel(0, 1).sort_index(level=0)<jupyter_output><empty_output><jupyter_text>Summary Statistics by Level<jupyter_code># many descriptive functions support level parameter
frame.sum(level='key2')
frame.sum(level=1)<jupyter_output><empty_output><jupyter_text>Indexing with a DataFrame's columns<jupyter_code>frame = pd.DataFrame({'a': range(7), 'b': range(7, 0, -1),
                    'c': ['one', 'one', 'one', 'two', 'two', 'two', 'two'],
                    'd': [0, 1, 2, 0, 1, 2, 3]})
frame
# frame.set_index
frame.set_index(['c', 'd'])
# if we want to keep the original columns
frame.set_index(['c', 'd'], drop=False)
# reset index
frame.set_index(['c', 'd']).reset_index()<jupyter_output><empty_output><jupyter_text>8.2 Combining and Merging Datasets* pandas.merge* pandas.concat* combine_first  Database-Style DataFrame Joins  **merge()**<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)})
df1
df2
# many to one join
pd.merge(df1, df2) # identifies common column in both dataframes and performs inner join
# explicitly defining common column
pd.merge(df1, df2, on='key')
# with different column names we can use left_on and right_on
df3 = pd.DataFrame({'lkey': list('bbacaab'),
                  'data1': range(7)})

df4 = pd.DataFrame({'rkey': list('abd'),
                   'data': range(3)})
pd.merge(df3, df4, left_on='lkey',
        right_on='rkey')
# inner join only considers intersecting keys
# to consider union of keys we can use outer join
pd.merge(df1, df2, how='outer')
# many to many merges
df1 = pd.DataFrame({'key': list('bbacab'),
                   'data': range(6)})
df2 = pd.DataFrame({'key': list('ababd'),
                   'data2': range(5)})
df1
df2
pd.merge(df1, df2, on='key', how='left')
# example of inner join
pd.merge(df1, df2, on='key', how='inner')
# merge with multiple keys
left = pd.DataFrame({'key1': 'foo,foo,bar'.split(','),
                    'key2': 'one,two,one'.split(','),
                    'lval': [1, 2, 3]})
right = pd.DataFrame({'key1': 'foo,foo,bar,bar'.split(','),
                     'key2': 'one,one,one,two'.split(','),
                     'rval': [4, 5, 6, 7]})

pd.merge(left, right, on=['key1', 'key2'], how='outer')
# for overlapping column names
pd.merge(left, right, on='key1')
# adding suffixes manually
pd.merge(left, right, on='key1', suffixes=['_left', '_right'])<jupyter_output><empty_output><jupyter_text>Merging on Index<jupyter_code>left1 = pd.DataFrame({'key': list('abaabc'),
                     'value': range(6)})
right1 = pd.DataFrame({'group_val': [3.5, 7]},
                      index=list('ab'))

left1
right1
pd.merge(left1, right1, left_on='key', right_index=True, how='outer')
lefth = pd.DataFrame({'key1': ['Ohio']*3 + ['Nevada']*2,
                     'key2': [2000, 2001, 2002, 2001, 2002],
                      'data': np.arange(5.)})
righth = pd.DataFrame(np.arange(12).reshape((6, 2)),
                     index = [[
                         'Nevada', 'Nevada', 'Ohio', 'Ohio', 'Ohio', 'Ohio' 
                     ],
                     [2001, 2000, 2000, 2000, 2001, 2002]],
                     columns = ['event1', 'event2'])

lefth
righth
pd.merge(lefth, righth, left_on=['key1', 'key2'],
        right_index=True, how='outer')
left2 = pd.DataFrame([[1., 2.], [3., 4.], [5., 6.]], 
                     index=['a', 'c', 'e'], columns=['Ohio', 'Nevada'])

right2 = pd.DataFrame([[7., 8.], [9., 10.], [11., 12.], [13, 14]],
                      index=['b', 'c', 'd', 'e'],
                      columns = ['Missouri', 'Alabama'])
                      
left2
right2
pd.merge(left2, right2, left_index=True, right_index=True,
        how='outer')<jupyter_output><empty_output><jupyter_text>**join()** is used for merging index without explicit index attributes<jupyter_code># columns should be non overlaping
left2.join(right2, how='outer')
left2.join(right2) # preserving left dataframes row index
left1.join(right1, on='key') # using a column to join<jupyter_output><empty_output><jupyter_text>We can Pass a list of DataFrames<jupyter_code>another = pd.DataFrame([[7., 8.], [9., 10.], [11., 12.], [16., 17.]],
                      index = list('acef'), columns = ['New York', 'Oregon'])
another
left2.join([right2, another])
left2.join([right2, another], how='outer')<jupyter_output><empty_output><jupyter_text>Concatenating Along an Axis Concatenation example in NumPy<jupyter_code>arr = np.arange(12).reshape((3, 4))
arr
np.concatenate([arr, arr], axis = 1)<jupyter_output><empty_output><jupyter_text>Concatenating in Pandas<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]) # default axis is 0
# with axis=1
pd.concat([s1, s2, s3], axis=1) # default join method is outer
s4 = pd.concat([s1, s3])
s4
pd.concat([s1, s4], axis=1)
pd.concat([s1, s4], axis=1, join='inner') # with inner join
# specifying axes to join
pd.concat([s1, s4], axis=1, join_axes=[list('acbe')])<jupyter_output><empty_output><jupyter_text>it's difficult to identify different pieces of concatentated object<jupyter_code>result = pd.concat([s1, s2, s3], keys=['one', 'two', 'three'])
result
result.unstack()<jupyter_output><empty_output><jupyter_text>for this we can do axis = 1<jupyter_code>pd.concat([s1, s2, s3], axis=1, keys=['one', 'two', 'three'])<jupyter_output><empty_output><jupyter_text>For DataFrames<jupyter_code>df1 = pd.DataFrame(np.arange(6).reshape(3, 2), index=['a', 'b', 'c'],
                   columns=['one', 'two'])
df2 = pd.DataFrame(5 + np.arange(4).reshape(2, 2), index=['a', 'c'],
                            columns=['three', 'four'])
df1
df2
pd.concat([df1, df2], axis=1, keys=['level1', 'level2'])<jupyter_output><empty_output><jupyter_text>Passing dict objects<jupyter_code>pd.concat({'level1': df1,
          'level2': df2}, axis=1)<jupyter_output><empty_output><jupyter_text>Giving names to the labels<jupyter_code>pd.concat([df1, df2], axis=1, keys=['level1', 'level2'], names=['upper', 'lower'])<jupyter_output><empty_output><jupyter_text>When Row index does not contain any relevant data<jupyter_code>df1 = pd.DataFrame(np.random.randn(3, 4), columns=list('abcd'))
df2 = pd.DataFrame(np.random.randn(2, 3), columns=list('bda'))
df1
df2
# use ignore_index
pd.concat([df1, df2], ignore_index=True)<jupyter_output><empty_output><jupyter_text>Combining Data with Overlap<jupyter_code>a = pd.Series([np.nan, 2.5, np.nan, 3.5, 4.5, np.nan],
              index=list('fedcba'))
b = pd.Series(np.arange(len(a), dtype=np.float64),
              index=list('fedcba'))
a
b[-1] = np.nan
b
np.where(pd.isnull(a), b, a)
# combine_first equivalent to above statement
a.combine_first(b)<jupyter_output><empty_output><jupyter_text>combine_first can be used for column by column operation<jupyter_code>df1 = pd.DataFrame({'a': [1., np.nan, 5., np.nan], 'b': [np.nan, 2., np.nan, 6.],
                    'c': range(2, 18, 4)})
df2 = pd.DataFrame({'a': [5., 4., np.nan, 3., 7.],'b': [np.nan, 3., 4., 6., 8.]})
df1
df2
df1.combine_first(df2) # patching missing data<jupyter_output><empty_output><jupyter_text>8.3 Reshaping and Pivoting  Reshaping with Hierarchical Indexing * stack = rotates or pivots from columns to rows* unstack = pivots from rows to columns<jupyter_code>data = pd.DataFrame(np.arange(6).reshape((2, 3)), 
                    index=pd.Index(['Ohio', 'Colorado'],
                    name='state'), columns=pd.Index(['one', 'two', 'three'], name='number'))
data
data.index
# stack() -> columns to rows
result = data.stack() # by default inner most level is stacked
result
# to know different levels in the dataframe we can use .levels attribute
result.index.levels 
# unstack() -> rows to columns
result.unstack() # by default inner most level is unstacked
# explicitly unstacking different level
result.unstack(0)
result.unstack('state')<jupyter_output><empty_output><jupyter_text>Unstacking introduces missing data (in case of unknown values)<jupyter_code>s1 = pd.Series([0, 1, 2, 3], index=['a', 'b', 'c', 'd'])
s2 = pd.Series([4, 5, 6], index=['c', 'd', 'e'])
data2 = pd.concat([s1, s2], keys=['one', 'two'])
data2
data2.index
data2.unstack()
# stack by default drops NaNs
data2.unstack().stack()
# not droping NaNs
data2.unstack().stack(dropna=False)
# the level unstacked becomes the lowest level
df = pd.DataFrame({'left': result, 'right': result+5},
                 columns=pd.Index(['left', 'right'], name='side'))
df
df.index.levels
df.index.get_level_values(0)
df.index.get_level_values(1)
np.info(df.index)
df.index.levshape
df.index.nlevels
df.unstack('state')
# calling stack with specific level
df.unstack('state').stack('side')<jupyter_output><empty_output><jupyter_text>Pivoting "Long" to "Wide" Format<jupyter_code>data = pd.read_csv('../examples/macrodata.csv')
data.head()
periods = pd.PeriodIndex(year=data.year, quarter=data.quarter,
                        name='date')
columns = pd.Index(['realgdp', 'infl', 'unemp'], name='item')
data = data.reindex(columns = columns)
data.index = periods.to_timestamp('D', 'end')
ldata = data.stack().reset_index().rename(columns={0:'value'})
ldata[:10]<jupyter_output><empty_output><jupyter_text>Converting above format into one column per distinct item value indexed by timestamps<jupyter_code>pivoted = ldata.pivot('date', 'item', 'value') # reshape with index, column, values
pivoted.head()<jupyter_output><empty_output><jupyter_text>When we have two value columns<jupyter_code>ldata['value2'] = np.random.randn(len(ldata))
ldata[:10]
# we can omit last argument for hierarchical columns
pivoted = ldata.pivot('date', 'item')
pivoted.head()
pivoted['value'].head()
unstacked = ldata.set_index(['date', 'item']).unstack('item')
unstacked.head()<jupyter_output><empty_output><jupyter_text>Pivoting "Wide" to "Long" Format inverse of pivot<jupyter_code>df = pd.DataFrame({'key': ['foo', 'bar', 'baz'],
                   'A': [1, 2, 3],
                   'B': [4, 5, 6],
                   'C': [7, 8, 9]})
df
melted = pd.melt(df, ['key'])
melted
# we can get the previous shape using pivot
reshaped=melted.pivot('key', 'variable', 'value')
reshaped
# we need to delete columns name, which is derived from the melted df
reshaped.reset_index()
# specifying subset of columns to be used as value cols
pd.melt(df, id_vars=['key'], value_vars=['A', 'B', 'C'])
pd.melt(df, value_vars=['key', 'A', 'B'])<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.18748975241842925, 'fraction_numerical': 0.029758976881455977, 'mean_word_length': 4.093528183716075, 'pattern_counts': {'":': 0, '<': 101, '<?xml version=': 0, '>': 103, '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': '25', 'dir': 'jupyter-structured-clean-dedup', 'id': '50458', 'license': 'MIT', 'n_tokens_mistral': 4648, 'n_tokens_neox': 4381, 'n_words': 1022, 'path': 'ch08/ch08.ipynb', 'repo_name': 'PalashSharma15/python-for-data-analysis-2nd-ed-notes', 'size': '12198'}