# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3 (ipykernel)
#     language: python
#     name: python3
# ---

# # Data Wrangling
#
# **Data wrangling** is a wide range of **methods** which are used to **prepare the data** for further **analysis**.
#
# Data wrangling includes:
# - Combining data
# - Joining data
# - Re-arranging data
#
# In data science, data wrangling is very important.

# ## 1. Data Wrangling: Hierarchical Indexing
#
# ### 1.1. Hierarchical Indexing
#
# - **Hierarchial indexing** means to have **multiple index level**, whether it is a **row** index or a **column** index.
# - **Hierarchial indexing** is usually used to **regroup the data** or to **lower** the **dimension of the data**.

import numpy as np
import pandas as pd

s =  pd.Series(np.random.randn(9),
                 index=[['2010','2010','2010','2011','2011','2011','2012','2012','2012'],
                        ['one', 'two','three', 'one', 'two','three','one', 'two','three'] ])
s

s.index

# We can select all data that belong to 2010.

s['2010']

s['2010':'2012']

s[['2010', '2012']]

# We can also **select data** based on the **second level of index**

s.loc[:,'one']

# **unstack()**: To **convert** a **multi-index series** to a **dataframe**

df = s.unstack()
df

# **stack()**: To **convert** a **dataframe** into a **multi-index series**

df.stack()

df = pd.read_csv('data/ex5.csv')
df

df = df.set_index(['year', 'quarters'])
df

df.index

df.loc[2010]

df.loc[(2010, 'A')]

# **Multi-index** can also be applied for **columns**. And you can do that by passing **two lists** for the columns’ argument:

df = pd.DataFrame(np.random.randint(100, size = (4,4)),
                    columns = [['green', 'green', 'black','black'], ['one', 'two','one','two']])
df

# ### 1.2. Reordering and Sorting Index Levels

df = pd.DataFrame(np.random.randint(100, size = (6,4)),
                    index = [['green', 'green', 'black','black','yellow', 'yellow'],
                             ['one', 'two','one','two','one','two']])
df

# **swaplevel()**: To **swap** the **index levels**

df = df.swaplevel(0, 1)
df

# **After swapping** the index levels, we can **sort** the **index** using the function **sort_index()**

df.sort_index(level=0)

df.swaplevel(0, 1).sort_index(level=0)

# ### 1.3. Summary Statistics by Level
#
# In **multi-index dataframe** we can calculate **statistics** separately for each values of a **specific index level**.

df = pd.DataFrame(np.random.randint(100, size = (6,4)),
                    index = [['green', 'green', 'black','black','yellow', 'yellow'],
                             ['one', 'two','one','two','one','two']])
df

df.mean()

df.index.names

df.index.names = ['color', 'number']

df.index.names

df

df.mean(level='color')

df.sum(level='number')

# ### 1.4. Indexing with Columns in Dataframe
#
# **Setting a column** from a **dataframe** to be its **index** is a common task in dataset prepration.

df = pd.read_csv('data/ex5.csv')
df

df.columns

df1 = df.set_index(['year', 'quarters'])
df1

# To **keep** the **index column** in the **dataframe** as a **normal column**, we use the argument **drop = False**

df.set_index('year', drop=False)

# **reset_index()**: To **reset** the **index** back to the **original index** that is generated by pandas

df1.reset_index()

# ## 2. Data Wrangling: Combining and Merging Datasets
#
# ### 2.1. Merging Datasets on Keys (common columns)
#
# The **merge** method is used to merge **two dataframes** based on a **common columns**.

df1 = pd.DataFrame({'states': ['California', 'Georgia', 'Florida', 'Arizona'],
                      'population': [40, 10, 21,7]})
df1

df2 = pd.DataFrame({'states': ['Arizona', 'Colorado', 'Indiana', 'Florida', ],
                      'area': [113, 104, 36, 65]})
df2

# **pd.merge()**: To **merge** the **dataframes** based on a **common column**. Pandas automatically found common columns.

pd.merge(df1, df2)

# We can as well specify the common column for merging using the argument **(on = )**

pd.merge(df1, df2, on='states')

# To include **all rows** from both **dataframes** we use the argument **how = 'outer'**

pd.merge(df1,df2, how='outer')

# To include **only rows** from the **first dataframe**, we use the argument **how = 'left'**

pd.merge(df1, df2, how='left')

# To include **only rows** from the **second dataframe**, we use the argument **how = 'right'**

pd.merge(df1, df2, how='right')

# When there is **no common column**, like the dataframes df2 and df3, we **specify** the **common column** using the arguments **left_on = ''** and **right_on = ''**

df3 = pd.DataFrame({'states_name': ['California', 'Georgia', 'Florida', 'Arizona'],
                      'population': [40, 10, 21,7]})
df3

df2

pd.merge(df3, df2, left_on='states_name', right_on='states')

# **Note**: Both key columns are included in the merging.
#
# In the case where there are **many common columns**, we must specify the common column for merging using the argument **(on = 'common column')**, **pandas** will automatically **change** the **name** of the **other common columns** that was **not used** for **merging**

df4 = pd.DataFrame({'states': ['California', 'Georgia', 'Florida', 'Arizona'],
                      'population': [40, 10, 21,7], 'water': [4,6,7,2]})
df4

df5 = pd.DataFrame({'states': ['Arizona', 'Colorado', 'Indiana', 'Florida', ],
                      'area': [113, 104, 36, 65], 'water': [2,8,3,7 ]})
df5

pd.merge(df4, df5, on='states', how='outer')

# We can also decide which **suffixes** to be used for the **extra common column** using the argument **suffixes**

pd.merge(df4, df5, on='states', how='outer', suffixes=('_data4', '_data5'))

# ### 2.2. Merging Datasets on Index

df1 = pd.DataFrame(np.random.randint(100, size = (4,3)), index = ['b', 'c', 'e','f'], 
         columns = ['green', 'red', 'white'])
df1

df2 = pd.DataFrame(np.random.randint(100, size = (3,4)), index = ['a', 'b', 'e'],
                      columns = ['blue', 'yellow', 'purple', 'black'])
df2

# We can **merge** these two **dataframes** based on their **index** using the function merge() and the arguments **left_index = True** and **right_index = True**

pd.merge(df1, df2, left_index=True, right_index=True)

# To include **all rows** from both **dataframes** we add the argument **how = 'outer'**

pd.merge(df1, df2, left_index=True, right_index=True, how='outer')

# ### 2.3. Concatenating along an Axis

s1 = pd.Series([1,2,3], index = ['a', 'b', 'c'])
s1

s2 = pd.Series([4,5,6], index = ['d', 'e','f'])
s2

# **pd.concat()**: To **concatenate** these **two series** along the **row axis**

pd.concat([s1, s2])

# We can also **concatenate** these **two series** along the **column axis** by adding the argument **(axis = 1)**

pd.concat([s1, s2], axis=1)

s3 = pd.Series([7,8,9], index = ['a', 'b','g'])
s3

# **Concatenating series** with **common index** along the **rows** will include **duplicate index values**

pd.concat([s1, s3])

# **Concatenating** two **series** with **different index** along the **column axis**, will **create missing values**

pd.concat([s1, s3], axis=1)

# To include **only** the **common index values** when **concatenating along columns**, we add the argument **(join = 'inner')**

pd.concat([s1, s3], axis=1, join='inner')

df1 = pd.DataFrame({'states': ['California', 'Georgia', 'Florida', 'Arizona'],
                       'population': [40, 10, 21,7]})
df1

df2 = pd.DataFrame({'states': ['Hawaii', 'Colorado', 'Indiana', 'Alaska', ],
                       'population': [1.5, 10.4, 5.7, 0.7]})
df2

# They have the **same column labels**, so it is logical to use **concat** method along the **row axis**.

pd.concat([df1, df2])

pd.concat([df1, df2], ignore_index=True)

# When the dataframes **don't have common columns**, in this case we have to **concatenate** them along the **column axis**.

df3 = pd.DataFrame({'states': ['California', 'Georgia', 'Florida', 'Arizona'],
                       'population': [40, 10, 21,7]})
df3

df4 = pd.DataFrame({'water': [23, 54, 12, 45 ], 'area': [113, 104, 36, 65]})
df4

# We can **concatenate** these two **dataframes** along the **column axis** by using the argument **(axis = 1)**

pd.concat([df3, df4], axis=1)

# ## 3. Data Wrangling: Reshaping and Pivoting
#
# ### 3.1. Reshaping by Stacking and Unstacking
#
# **Reshaping** means **transforming** the **structure** of the **data** to make it **appropriate** for **further analysis**.
#
# Reshaping includes:
# - Stacking
# - Melting
# - Pivoting
#
# **Stacking**: is moving the innermost **column index** to become the innermost **row index**.
#
# **Unstacking**: is moving the innermost **row inex** to become the innermost **column index**.

df1 = pd.read_csv('data/ex6.csv', index_col = 'branch number')
df1

# **stack()**: To **reshape** the **dataframe** where the **columns** will be the **rows**.

df2 = df1.stack()
df2

# It turns out that stacking that dataframe transformed it into series.

df2.index

df2.loc[:, 'sale1']

# **unstack()**: To **remove** the **innermost row index** to be the **columns**

df2.unstack()

# We can **choose** which **index level** to be moved by specifying the **level number** ( 0 for outer index and 1 for inner index)

df2.unstack(0)

# We can also specify the **index level** by its **name**

df2.unstack('branch number')

df1 = pd.read_csv('data/ex5.csv', index_col = ['year', 'quarters'])
df1

df1.unstack()

df2 = df1.unstack('year')
df2

df2.stack()

# ### 3.2. Reshaping by Melting (Wide to Long)
#
# - The **melt()** method is used to **transform multiple columns** into a **single column**.
# - The **melt()** method is the **opposite** of the **pivot()** method.

df = pd.read_csv('data/ex7.csv')
df

# **melt()**: To **reshape** a **dataframe** by **transforming** the **values of all column**s into a **single column**
#
# **Notice**: The column that is assigned in the argument **id_vard** will **not be transformed**.

df2 = df.melt(id_vars='year')
df2

# We can **rename** the newly created **columns** ourselves by using the arguments **var_name** and **value_name**, instead of the default names (variable and value)

df2 = df.melt(id_vars='year', var_name='sales', value_name='amount')
df2

df = pd.read_csv('data/ex8.csv', index_col = 'date')
df

df.melt(var_name='company', value_name='closing price')

# **Notice**: The **index** is **automatically removed** after **melting**.
#
# To **keep** the **original index** after **melting**: we need to add the argument **ignore_index = False**.

df1 = df.melt(var_name='company', value_name='closing price', ignore_index=False)
df1

# The **problem** with this **index** is that it is **not ordered** after **reformating**.

df1.sort_index()

# ### 3.3. Reshaping by Pivoting (Long to Wide)
#
# **Pivoting** means **transforming** a **one column** into **multiple columns** in **dataframe**.

df = pd.read_csv('data/ex9.csv')
df

df.pivot(index='year', columns='sales', values='amount')

df = pd.read_csv('data/ex10.csv')
df

df.pivot(index='date', columns='company', values='closing price')

# We can write the previous code, omitting the names of the arguments for **short codes**, like this

df.pivot('date', 'company', 'closing price')

# **Pivoting** can be also done by using **two functions** which are **set_index()** and **unstack()**, like this:

df1 = df.set_index(['date', 'company'])
df1

df1.unstack('company')

StarcoderdataJupyterScriptsDedupFiltered