Exploring raw data

Exploring Raw Data¶

In this notebook, we will illustrate exploring raw data as under :

Loading data
Create summary statistics
Data visualization
Interactive querying using powerpivot

Loading data¶

In [1]:

Copied!





# Import required package
from corridor import DataTable 

# read in the Application Level Dataset
application_table = DataTable('application').to_spark()

# get the first 1000 records 
application_table = application_table.limit(1000)

# take a look at the first 5 rows of the application_table dataframe by runningthe line below:
application_table.limit(5).toPandas()

# To read raw data from external data source, following command can be used:
# import findspark; findspark.init(); import pyspark
# spark = pyspark.sql.SparkSession.builder.getOrCreate()
# application_table = spark.read.parquet('s3a://corridor.dev/master/sampleAppData.parquet')
# Import required package
from corridor import DataTable 

# read in the Application Level Dataset
application_table = DataTable('application').to_spark()

# get the first 1000 records 
application_table = application_table.limit(1000)

# take a look at the first 5 rows of the application_table dataframe by runningthe line below:
application_table.limit(5).toPandas()

# To read raw data from external data source, following command can be used:
# import findspark; findspark.init(); import pyspark
# spark = pyspark.sql.SparkSession.builder.getOrCreate()
# application_table = spark.read.parquet('s3a://corridor.dev/master/sampleAppData.parquet')

Out[1]:

	open_acc_6m	acc_open_past_24mths	annual_inc	simulated_age	avg_cur_bal	bc_open_to_buy	bc_util	...	simulated_gender	emp_title	home_ownership	initial_list_status	verification_status	verification_status_joint	purpose	corridor_application_id	corridor_application_date	earliest_cr_line
0	0.0	2.0	75000.0	41.0	74141.0	0.0	100.4	...	male	Director	MORTGAGE	f	Not Verified	None	debt_consolidation	20000018440	2015-11-24 07:00:00	1996-08-01 07:00:00
1	2.0	8.0	125000.0	36.0	16626.0	10506.0	48.2	...	male	Accountant/Office Manager	MORTGAGE	f	Source Verified	None	debt_consolidation	225770004638	2018-07-18 07:00:00	1999-10-01 07:00:00
2	NaN	3.0	113536.0	45.0	29224.0	5789.0	84.2	...	female	Sr. SQA Engineer	MORTGAGE	f	Verified	None	credit_card	20000077834	2015-10-23 07:00:00	2001-07-01 07:00:00
3	NaN	7.0	140000.0	37.0	13468.0	69544.0	35.1	...	female	Vice President	MORTGAGE	w	Source Verified	None	home_improvement	20000267750	2015-05-22 07:00:00	1989-03-01 07:00:00
4	1.0	8.0	130000.0	47.0	8449.0	44177.0	22.4	...	male	business initiatives consultant	MORTGAGE	w	Not Verified	None	debt_consolidation	225769878100	2018-04-25 07:00:00	1982-06-01 07:00:00

5 rows × 88 columns

Create summary statistics¶

In [2]:

Copied!

# Dataframe dimension
print(f'Rows = {application_table.count()}')
print(f'Columns = {len(application_table.columns)}')
# Dataframe dimension
print(f'Rows = {application_table.count()}')
print(f'Columns = {len(application_table.columns)}')

Rows = 1000
Columns = 88

In [3]:

Copied!

# Available fields in data
application_table.columns[:5]
# Available fields in data
application_table.columns[:5]

Out[3]:

['acc_now_delinq',
 'open_acc_6m',
 'acc_open_past_24mths',
 'annual_inc',
 'simulated_age']

In [4]:

Copied!

# Summary statistics for selected variables (This can be done for both Numerical and Categorical Features)
application_table.describe("avg_cur_bal", "emp_length", "fico_range_high", "emp_title").show()

# To describe all columns (numerical and categorical)
# application_table.describe().toPandas()
# Summary statistics for selected variables (This can be done for both Numerical and Categorical Features)
application_table.describe("avg_cur_bal", "emp_length", "fico_range_high", "emp_title").show()

# To describe all columns (numerical and categorical)
# application_table.describe().toPandas()

+-------+-----------------+------------------+-----------------+------------+
|summary|      avg_cur_bal|        emp_length|  fico_range_high|   emp_title|
+-------+-----------------+------------------+-----------------+------------+
|  count|             1000|               931|             1000|         931|
|   mean|        13311.087| 6.039742212674543|          712.785|        null|
| stddev|15883.31064395718|3.6775297836752197|36.94744766406592|        null|
|    min|             36.0|               0.0|            664.0|A&P mechanic|
|    max|         186479.0|              10.0|            844.0|      welder|
+-------+-----------------+------------------+-----------------+------------+

In [5]:

Copied!

# CrossTab for categorical variables
application_table.stat.crosstab("purpose", "simulated_gender").show()
# CrossTab for categorical variables
application_table.stat.crosstab("purpose", "simulated_gender").show()

+------------------------+------+----+
|purpose_simulated_gender|female|male|
+------------------------+------+----+
|          major_purchase|    13|  10|
|      debt_consolidation|   290| 208|
|             credit_card|   150| 118|
|        home_improvement|    37|  37|
|          small_business|     4|   2|
|                  moving|     4|   0|
|                     car|     4|   3|
|                vacation|     3|   4|
|                   house|    10|   9|
|                 medical|     7|   7|
|                   other|    47|  33|
+------------------------+------+----+

In [6]:

Copied!

# Correlation calculation 
print(f"Corr(fico_range_high, emp_length) = {application_table.stat.corr('fico_range_high', 'emp_length')}")
print(f"Corr(fico_range_high, fico_range_high) = {application_table.stat.corr('fico_range_high', 'fico_range_high')}")
# Correlation calculation 
print(f"Corr(fico_range_high, emp_length) = {application_table.stat.corr('fico_range_high', 'emp_length')}")
print(f"Corr(fico_range_high, fico_range_high) = {application_table.stat.corr('fico_range_high', 'fico_range_high')}")

Corr(fico_range_high, emp_length) = 0.08291026487696629
Corr(fico_range_high, fico_range_high) = 1.0

Data visualization¶

In [7]:

Copied!





# For plotly to show figure
import plotly.offline as pyo
import plotly.graph_objs as go
# Set notebook mode to work in offline
pyo.init_notebook_mode()
# For plotly to show figure
import plotly.offline as pyo
import plotly.graph_objs as go
# Set notebook mode to work in offline
pyo.init_notebook_mode()

In [8]:

Copied!





application_table_pd = application_table.toPandas()
variables = ("fico_range_high", "annual_inc", "avg_cur_bal", "emp_length")

fig=go.Figure()
var_plot_names = []
buttons=[]
default_var = "fico_range_high"

for var in variables:
    fig.add_trace(go.Box(y=application_table_pd[var], visible=(var==default_var)))
    var_plot_names.extend([var])
    
for var in variables:
    buttons.append(dict(method='update',
                        label=var,
                        args = [{'visible': [var==r for r in var_plot_names]}]))

# Add dropdown menus to the figure
fig.update_layout(showlegend=False, 
                  updatemenus=[{"buttons": buttons, "direction": "down", 
                                "active": variables.index(default_var), "showactive": True}]) 
# fig.show()
pyo.iplot(fig, filename = 'box-plot')
application_table_pd = application_table.toPandas()
variables = ("fico_range_high", "annual_inc", "avg_cur_bal", "emp_length")

fig=go.Figure()
var_plot_names = []
buttons=[]
default_var = "fico_range_high"

for var in variables:
    fig.add_trace(go.Box(y=application_table_pd[var], visible=(var==default_var)))
    var_plot_names.extend([var])
    
for var in variables:
    buttons.append(dict(method='update',
                        label=var,
                        args = [{'visible': [var==r for r in var_plot_names]}]))

# Add dropdown menus to the figure
fig.update_layout(showlegend=False, 
                  updatemenus=[{"buttons": buttons, "direction": "down", 
                                "active": variables.index(default_var), "showactive": True}]) 
# fig.show()
pyo.iplot(fig, filename = 'box-plot')

Interactive querying using powerpivot¶

In [9]:

Copied!

from pivottablejs import pivot_ui
from IPython.display import HTML
from IPython.display import IFrame
from pivottablejs import pivot_ui
from IPython.display import HTML
from IPython.display import IFrame

In [10]:

Copied!

application_table_pd = application_table.toPandas()
application_table_pd = application_table.toPandas()

In [11]:

Copied!





pivot_ui(application_table_pd, outfile_path="pivottablejs.html")
HTML('pivottablejs.html')
# No output sometimes - More details on the issue can be found here
# https://stackoverflow.com/questions/48415690/pivottablejs-does-not-display-any-result-in-the-jupyter-notebook
pivot_ui(application_table_pd, outfile_path="pivottablejs.html")
HTML('pivottablejs.html')
# No output sometimes - More details on the issue can be found here
# https://stackoverflow.com/questions/48415690/pivottablejs-does-not-display-any-result-in-the-jupyter-notebook

Out[11]:

PivotTable.js