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

# + [markdown] id="view-in-github" colab_type="text"
# <a href="https://colab.research.google.com/github/sampath11/DS-Unit-1-Sprint-1-Dealing-With-Data/blob/master/module4-makefeatures/Assignment_Sam_Kumar_LS_DS_114_Make_Features_Assignment.ipynb" target="_parent"><img src="https://colab.research.google.com/assets/colab-badge.svg" alt="Open In Colab"/></a>

# + [markdown] id="SnDJqBLi0FYW" colab_type="text"
# <img align="left" src="https://lever-client-logos.s3.amazonaws.com/864372b1-534c-480e-acd5-9711f850815c-1524247202159.png" width=200> 

# + [markdown] id="W5GjI1z5yNG4" colab_type="text"
# # ASSIGNMENT
#
# - Replicate the lesson code.
#
#  - This means that if you haven't followed along already, type out the things that we did in class. Forcing your fingers to hit each key will help you internalize the syntax of what we're doing.
#  - [Lambda Learning Method for DS - By <NAME>](https://docs.google.com/document/d/1ubOw9B3Hfip27hF2ZFnW3a3z9xAgrUDRReOEo-FHCVs/edit?usp=sharing)
# - Convert the `term` column from string to integer.
# - Make a column named `loan_status_is_great`. It should contain the integer 1 if `loan_status` is "Current" or "Fully Paid." Else it should contain the integer 0.
#
# - Make `last_pymnt_d_month` and `last_pymnt_d_year` columns.

# + [markdown] id="hW0I1oSjBgKr" colab_type="text"
# ##Replicating Lending Tree Code from Lecture and then making assignment specific modifications
#

# + id="gkY-pe-sCVLY" colab_type="code" colab={}
import pandas as pd
import numpy as num

# + id="AazB4eFwym2p" colab_type="code" colab={"base_uri": "https://localhost:8080/", "height": 202} outputId="bd6433eb-40f0-4390-b9c0-5ee396765fff"
##### Begin Working Here #####


# !wget https://resources.lendingclub.com/LoanStats_2018Q4.csv.zip


# + id="0U7SooK1B16K" colab_type="code" colab={"base_uri": "https://localhost:8080/", "height": 50} outputId="ebc6880b-4d93-49c0-c765-ad1d19f0006b"

# !unzip LoanStats_2018Q4.csv.zip


# + id="zRxIurkPB13V" colab_type="code" colab={"base_uri": "https://localhost:8080/", "height": 205} outputId="545342ea-d802-4aa6-8a9d-0c56cc871811"
# !head LoanStats_2018Q4.csv

# + id="v4ZNJRt5CSGW" colab_type="code" colab={"base_uri": "https://localhost:8080/", "height": 333} outputId="04ed131f-8067-4d48-e22d-a8ddd3f2c2f2"
loan_file = "LoanStats_2018Q4.csv"

df = pd.read_csv (loan_file)

df.head()

# + id="NDLsoSLYCR8n" colab_type="code" colab={"base_uri": "https://localhost:8080/", "height": 362} outputId="c6f73923-d69a-4ee7-a6f2-17fa19706269"
## Something wrong with header, fix it

df = pd.read_csv (loan_file, header=1)

df.head()

# + id="dTjT8VjsCRtV" colab_type="code" colab={"base_uri": "https://localhost:8080/", "height": 205} outputId="e990a322-17d4-4e02-d4b0-edb66e745e71"
# !tail $loan_file

# + id="Iw4S_dEZDOrT" colab_type="code" colab={"base_uri": "https://localhost:8080/", "height": 345} outputId="e20931e7-6dc8-4c6e-ed40-5a34fbe1c381"
## fix footer 

df = pd.read_csv (loan_file, header=1, skipfooter=2, engine='python')

df.tail()


# + id="eNXguRPWDtRM" colab_type="code" colab={"base_uri": "https://localhost:8080/", "height": 34} outputId="b3e7d26b-ff68-4133-db78-a08233195c89"
df.shape

# + id="f1XNJO0xDvLU" colab_type="code" colab={"base_uri": "https://localhost:8080/", "height": 1000} outputId="5ccae4cb-d654-4f68-86ce-c7fa7b67fc70"
# look for null columns
df.isnull().sum().sort_values(ascending=False)

# + id="YfKgGl25EEy7" colab_type="code" colab={}
## Remove cols that have all blank values for whole file, make sure to assign outoput back to df

df = df.drop(columns=['id', 'desc', 'member_id', 'url'])

# + id="NgLXyWhsEeOc" colab_type="code" colab={"base_uri": "https://localhost:8080/", "height": 328} outputId="745fa2cb-1900-4a0f-8ac4-598d992d8e3b"
df.head()

# + id="gK1743GTFLU-" colab_type="code" colab={"base_uri": "https://localhost:8080/", "height": 34} outputId="cb5c9520-ce07-43ea-f0ce-54ceb74496da"
df.shape

# + id="5GcMs3qzFbrq" colab_type="code" colab={"base_uri": "https://localhost:8080/", "height": 218} outputId="c04fba04-a139-4bb8-86be-c02659a94bb1"
# compile all hardship specific columns

hardship_columns = [ col for col in df.columns if col.startswith ('hardship_')]

hardship_columns

# + id="rRCeOElZFm4O" colab_type="code" colab={}
df = df.drop(columns=hardship_columns)

# + id="nT8uEB_EFsTg" colab_type="code" colab={"base_uri": "https://localhost:8080/", "height": 328} outputId="eddbdbdf-051a-4e5f-bad6-25d190af8819"
df.head()

# + id="a7YTIgGEFu2Z" colab_type="code" colab={"base_uri": "https://localhost:8080/", "height": 34} outputId="5bd579d3-e834-457d-8f2a-d8e44e76c396"
df.shape

# + id="fJiJbw8EF3r1" colab_type="code" colab={"base_uri": "https://localhost:8080/", "height": 1000} outputId="0da2dcd5-afcd-4342-b998-a1e5b4d8d92a"
## Convert int_"rate column to float from string

df['int_rate']


# + id="VezCWVA6F9LP" colab_type="code" colab={}
## write fancy function
def remove_percent_to_float(x):
  return float(x[:-1])



# + id="wr4rbAvxGExS" colab_type="code" colab={}
#call fancy function for all rows and assign back to int_rate
df['int_rate'] = df['int_rate'].apply(remove_percent_to_float)

# + id="HePY1HQUGMPZ" colab_type="code" colab={"base_uri": "https://localhost:8080/", "height": 1000} outputId="82344ce7-0f7e-41a4-e7fc-55c4103f0a08"
df['int_rate']

# + id="_RzoMvZXGPO9" colab_type="code" colab={"base_uri": "https://localhost:8080/", "height": 538} outputId="06d70528-cf77-4ae4-a40b-94a242eeef71"
## Cleanup title

df['emp_title'].value_counts(dropna=False).head(30)


# + id="qWPJdiEMGW-x" colab_type="code" colab={}
# function to strip blanks, convert to lower case and if null setup sgtring "unknown"
def clean_title(title):
  if isinstance(title, str):
    return title.strip().lower()
  else:
    return 'unknown'


# + id="wz2O23JtGlT_" colab_type="code" colab={}
df['emp_title'] = df['emp_title'].apply(clean_title)

# + id="o7CUKMbvGm-X" colab_type="code" colab={"base_uri": "https://localhost:8080/", "height": 538} outputId="97366adf-ffe8-4140-e817-f875d8b1ab2a"
df['emp_title'].value_counts(dropna=False).head(30)

# + id="LOcoJ8WCGtIn" colab_type="code" colab={}
## Create another column
df['emp_title_manager'] = df['emp_title'].str.contains('manager')

# + id="nENbWJRVGwPK" colab_type="code" colab={"base_uri": "https://localhost:8080/", "height": 328} outputId="a9da9f57-0114-448b-ccf9-7e280f085eea"
df.head()

# + id="qJSDpQUhG3SB" colab_type="code" colab={"base_uri": "https://localhost:8080/", "height": 151} outputId="eea36530-d7fd-42c8-9638-c4f1698edb6f"
df.columns

# + id="pTmg76TMG5Vj" colab_type="code" colab={"base_uri": "https://localhost:8080/", "height": 328} outputId="2d223b5c-11b2-488e-d19d-1130369f8fad"
df.head()

# + id="93rYmULUHLoE" colab_type="code" colab={}
mask = df['emp_title_manager'] == True
managers = df[mask]
plebians = df[~mask]

# + id="sqN9haE2HNL1" colab_type="code" colab={"base_uri": "https://localhost:8080/", "height": 151} outputId="9a845ac2-fbde-4e41-c73a-cf4da911416f"
df.columns

# + id="o80PpAFMHbOF" colab_type="code" colab={"base_uri": "https://localhost:8080/", "height": 34} outputId="828ce65f-4b49-453d-81e9-c5648db0e882"
managers.shape

# + id="-M8UHCceHeSp" colab_type="code" colab={"base_uri": "https://localhost:8080/", "height": 34} outputId="10059360-127b-4690-812a-349edae8b87a"
plebians.shape[0]

# + id="FlFobzrxHiwQ" colab_type="code" colab={"base_uri": "https://localhost:8080/", "height": 34} outputId="58b069dc-a549-4f0a-8530-6773cb5e3c1c"
df.shape[0]

# + id="45sD6EcCHj5T" colab_type="code" colab={"base_uri": "https://localhost:8080/", "height": 34} outputId="9ba4e257-6349-4a30-9e13-29f0b504f98c"
df.shape

# + id="M2ciD0A0Hu4N" colab_type="code" colab={"base_uri": "https://localhost:8080/", "height": 1000} outputId="e30ad121-34e2-4d08-f555-e6d79edbbb2c"
## fix some obvious date isssues


df['issue_d']

# + id="dQxnW3KaHx_I" colab_type="code" colab={}
df['issue_d'] = pd.to_datetime(df['issue_d'], infer_datetime_format=True)

# + id="1W2DyiJqH8B-" colab_type="code" colab={"base_uri": "https://localhost:8080/", "height": 34} outputId="87c1a75b-cc05-4d79-d76a-7fb01fb36dfa"
type(df['issue_d'])

# + id="E5xusCpUIDWD" colab_type="code" colab={"base_uri": "https://localhost:8080/", "height": 1000} outputId="285e4280-1f2a-4158-e6a9-666fd620792d"
df['issue_d']

# + id="3QQfEuRzIJFI" colab_type="code" colab={}
df['issue_year'] = df['issue_d'].dt.year
df['issue_month'] = df['issue_d'].dt.month

# + id="4NgWIedmIK0z" colab_type="code" colab={"base_uri": "https://localhost:8080/", "height": 151} outputId="5b669379-d8de-490d-ae85-e5e657f5035f"
df.columns

# + id="5YOWaxvTIM6a" colab_type="code" colab={"base_uri": "https://localhost:8080/", "height": 34} outputId="4a5f6d10-d831-4a67-d00b-9963d78ebf4d"
df.shape

# + id="xCV_6EVbIUpE" colab_type="code" colab={}
df['earliest_cr_line'] = pd.to_datetime(df['earliest_cr_line'], infer_datetime_format=True)

# + id="KmG68YKXIZV1" colab_type="code" colab={}
df['days_from_cr_line_to_loan'] = (df['issue_d'] - df['earliest_cr_line']).dt.days

# + id="YAWKc_oFIctK" colab_type="code" colab={"base_uri": "https://localhost:8080/", "height": 151} outputId="7a3b4983-cec5-4b3e-efd3-80e212aa3496"
df.columns

# + id="is3S74WNIahD" colab_type="code" colab={"base_uri": "https://localhost:8080/", "height": 328} outputId="55583ba3-28e2-4e4f-b0ab-65557f99cbd2"
df.head()

# + id="vz-rFC-5IxXv" colab_type="code" colab={"base_uri": "https://localhost:8080/", "height": 1000} outputId="017ac1ac-5cee-43f0-a1a0-cd84ac98f9ec"
## Convert term column to int

df['term']


# + id="U8LphJaMI145" colab_type="code" colab={}
# write a fancy fn
def remove_months_str(x):
  return int(x[:-6])


# + id="HN3Ufh4dJPnl" colab_type="code" colab={"base_uri": "https://localhost:8080/", "height": 50} outputId="6fe79f23-5663-4542-9817-1c9e26acca32"
#test function
a = '120 months'
a = remove_months_str(a)

print (a)
type(a)

# + id="SRbpZ-NLJjZ9" colab_type="code" colab={}
#now change term 

df['term'] = df['term'].apply(remove_months_str)

# + id="SF_-Dt4NJxae" colab_type="code" colab={"base_uri": "https://localhost:8080/", "height": 1000} outputId="af6532e2-3361-46f1-aaf5-e125a7b0d8f0"
# test it
df['term']

# + id="biW16dLFJ1Vo" colab_type="code" colab={"base_uri": "https://localhost:8080/", "height": 1000} outputId="11e9bcee-1c03-4257-e824-a43ff2873db4"
## Now lets do this : Make a column named loan_status_is_great. It should contain the integer 1 if loan_status is "Current" or "Fully Paid." Else it should contain the integer 0.


df['loan_status']


# + id="RfAxVPWaKPOY" colab_type="code" colab={}
## better write a func
def loan_status_validate(var):
  if (var == 'Current' or var == 'Fully Paid'):
    return (1)
  else:
    return(0)


# + id="QF3UHfOyK3h5" colab_type="code" colab={"base_uri": "https://localhost:8080/", "height": 34} outputId="e9873d0f-c39d-45b5-a62b-6c34e41c9e84"
## test function

var = 'Late (31-120 days)'
print ( loan_status_validate (var))

# + id="oqUWP46oLrjq" colab_type="code" colab={}
#now call function and create a new column

df['loan_status_is_great'] = df['loan_status'].apply(loan_status_validate)

# + id="RnwIwjbaLzKA" colab_type="code" colab={"base_uri": "https://localhost:8080/", "height": 328} outputId="1f788846-9ac2-47e8-84ed-283a72f02cce"
df.head()


# + id="1HY3UvabMNxI" colab_type="code" colab={"base_uri": "https://localhost:8080/", "height": 151} outputId="8de3c3be-49c7-4c7b-b9b9-174ed3a9f39b"
df.columns

# + id="JxIXy5ZgMPty" colab_type="code" colab={"base_uri": "https://localhost:8080/", "height": 1000} outputId="3abb5e1d-6281-4acc-c60b-471576505997"
df['loan_status_is_great']

# + id="xZSjbMewMcrs" colab_type="code" colab={"base_uri": "https://localhost:8080/", "height": 1000} outputId="85642b49-9e0b-4897-9dfb-98d43cefd70e"
## Lets do the last one

####Make last_pymnt_d_month and last_pymnt_d_year columns.

df['last_pymnt_d']

# + id="pgWoE4mlMt4T" colab_type="code" colab={}
# convert it to date from srtring 
df['last_pymnt_d'] = pd.to_datetime(df['last_pymnt_d'], infer_datetime_format=True)



# + id="erYRbaH_M4kU" colab_type="code" colab={"base_uri": "https://localhost:8080/", "height": 1000} outputId="6108bb49-28e9-406f-85cd-5e2bba813249"
df['last_pymnt_d']

# + id="Uu-GtGj1M79Q" colab_type="code" colab={}
# Nice, now create two columns for year and month
df['last_pymnt_d_month'] = df['last_pymnt_d'].dt.month
df['last_pymnt_d_year'] = df['last_pymnt_d'].dt.year

# + id="v2FVLJxjNcVu" colab_type="code" colab={"base_uri": "https://localhost:8080/", "height": 328} outputId="4fa13224-3df5-4161-8f22-5ba45b4bf779"
df.head()

# + id="dlkytnSkPPuG" colab_type="code" colab={"base_uri": "https://localhost:8080/", "height": 1000} outputId="181be9e4-1759-4aa1-ac73-4f2a14aec275"
df['last_pymnt_d_year']

# + id="jZ0DC1xaPVZ_" colab_type="code" colab={"base_uri": "https://localhost:8080/", "height": 1000} outputId="bc4a9bdb-f2aa-4ba3-92e1-497a52228a79"
df['last_pymnt_d_month']

# + [markdown] colab_type="text" id="L8k0LiHmo5EU"
# # STRETCH OPTIONS
#
# You can do more with the LendingClub or Instacart datasets.
#
# LendingClub options:
# - There's one other column in the dataframe with percent signs. Remove them and convert to floats. You'll need to handle missing values.
# - Modify the `emp_title` column to replace titles with 'Other' if the title is not in the top 20. 
# - Take initiatve and work on your own ideas!
#
# Instacart options:
# - Read [Instacart Market Basket Analysis, Winner's Interview: 2nd place, Kazuki Onodera](http://blog.kaggle.com/2017/09/21/instacart-market-basket-analysis-winners-interview-2nd-place-kazuki-onodera/), especially the **Feature Engineering** section. (Can you choose one feature from his bulleted lists, and try to engineer it with pandas code?)
# - Read and replicate parts of [Simple Exploration Notebook - Instacart](https://www.kaggle.com/sudalairajkumar/simple-exploration-notebook-instacart). (It's the Python Notebook with the most upvotes for this Kaggle competition.)
# - Take initiative and work on your own ideas!

# + [markdown] colab_type="text" id="0_7PXF7lpEXg"
# You can uncomment and run the cells below to re-download and extract the Instacart data

# + id="urIePNa0yNG6" colab_type="code" colab={}
# # !wget https://s3.amazonaws.com/instacart-datasets/instacart_online_grocery_shopping_2017_05_01.tar.gz

# + id="X9zEyu-uyNG8" colab_type="code" colab={}
# # !tar --gunzip --extract --verbose --file=instacart_online_grocery_shopping_2017_05_01.tar.gz

# + id="Y3IqrhlpyNG-" colab_type="code" colab={}
# # %cd instacart_2017_05_01

StarcoderdataJupyterScriptsDedupFiltered