<jupyter_start><jupyter_text>_Lambda School Data Science_ Make featuresObjectives-  understand the purpose of feature engineering-  work with strings in pandas- work with dates and times in pandasLinks- [Feature Engineering](https://en.wikipedia.org/wiki/Feature_engineering)- Python Data Science Handbook  - [Chapter 3.10](https://jakevdp.github.io/PythonDataScienceHandbook/03.10-working-with-strings.html), Vectorized String Operations  - [Chapter 3.11](https://jakevdp.github.io/PythonDataScienceHandbook/03.11-working-with-time-series.html), Working with Time Series<jupyter_code>!wget https://resources.lendingclub.com/LoanStats_2018Q4.csv.zip<jupyter_output>--2019-03-28 22:14:58--  https://resources.lendingclub.com/LoanStats_2018Q4.csv.zip
Resolving resources.lendingclub.com (resources.lendingclub.com)... 172.16.17.32
Connecting to resources.lendingclub.com (resources.lendingclub.com)|172.16.17.32|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: unspecified [application/zip]
Saving to: ‘LoanStats_2018Q4.csv.zip.4’

LoanStats_2018Q4.cs     [                <=> ]  21.29M   876KB/s    in 26s     

2019-03-28 22:15:24 (844 KB/s) - ‘LoanStats_2018Q4.csv.zip.4’ saved [22329081]<jupyter_text>Get LendingClub data[Source](https://www.lendingclub.com/info/download-data.action)<jupyter_code>!unzip LoanStats_2018Q4.csv.zip<jupyter_output>Archive:  LoanStats_2018Q4.csv.zip
replace LoanStats_2018Q4.csv? [y]es, [n]o, [A]ll, [N]one, [r]ename: y
  inflating: LoanStats_2018Q4.csv<jupyter_text>linux commands to view the head or tail of data<jupyter_code>!head LoanStats_2018Q4.csv
!tail LoanStats_2018Q4.csv<jupyter_output>"","","5600","5600","5600"," 36 months"," 13.56%","190.21","C","C1","","n/a","RENT","15600","Not Verified","Oct-2018","Current","n","","","credit_card","Credit card refinancing","836xx","ID","15.31","0","Aug-2012","0","","97","9","1","5996","34.5%","11","w","5083.61","5083.61","750.29","750.29","516.39","233.90","0.0","0.0","0.0","Feb-2019","190.21","Mar-2019","Feb-2019","0","","1","Individual","","","","0","0","5996","0","0","0","1","20","0","","0","2","3017","35","17400","1","0","0","3","750","4689","45.5","0","0","20","73","13","13","0","13","","20","","0","3","5","4","4","1","9","10","5","9","0","0","0","0","100","25","1","0","17400","5996","8600","0","","","","","","","","","","","","N","","","","","","","","","","","","","","","Cash","N","","","","","",""
"","","23000","23000","23000"," 36 months"," 15.02%","797.53","C","C3","Tax Consultant","10+ years","MORTGAGE","75000","Source Verified","Oct-2018","Charged Off","n","","","debt_consolidation","Debt consolidation","352xx","AL","[...]<jupyter_text>Load LendingClub datapandas documentation- [`read_csv`](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_csv.html)- [`options.display`](https://pandas.pydata.org/pandas-docs/stable/options.htmlavailable-options)<jupyter_code>import pandas as pd
df = pd.read_csv('LoanStats_2018Q4.csv',skiprows=1)
df.info()
#one column - notes offered by prospectus, no comma separated value, value is an entire string
#we want to skip row in beginning
#refer to pandas.csv about skipping rows
df.shape
df.columns.tolist()
df.isnull().sum().sort_values()
df.isnull()
df.tail()
import pandas as pd
df = pd.read_csv('LoanStats_2018Q4.csv', skiprows=1 , skipfooter=2, engine='python')
pd.options.display.max_columns = 500
pd.options.display.max_rows = 500 #otherwise you don't want to crash your machine on cloud or locally. 
df.head().T
df.tail()<jupyter_output><empty_output><jupyter_text>Work with strings<jupyter_code>df.describe(exclude='number')#shows me everything not a number
#d3 library for data visualization, does not do datatype and inference for you. whereas pands does a lot of conversions for you<jupyter_output><empty_output><jupyter_text>For machine learning, we usually want to replace strings with numbers.We can get info about which columns have a datatype of "object" (strings)<jupyter_code><jupyter_output><empty_output><jupyter_text>Convert `int_rate`Define a function to remove percent signs from strings and convert to floats<jupyter_code># strip to convert value into number
'13.56%'.strip('%')
type('13.56%'.strip('%'))
float('13.56')
type(float('13.56%'.strip('%')))
df['int_rate'].str.strip('%').head()
df['int_rate'] = df['int_rate'].str.strip('%').astype(float)
df.int_rate.head()

#when you make new features, you have to use the square brackets[] rather than the dot syntax<jupyter_output><empty_output><jupyter_text>Apply the function to the `int_rate` column<jupyter_code>(df['int_rate'] / 100).head()
#float('13.56%').strip is similar to below:

string = '13.56%'

def remove_percent(string):
    return float(string.strip('%'))
  
remove_percent(string)
df['int_rate'] = df['int_rate'].apply(remove_percent)# can only be applied once. Otherwise you already have floats, this function takes strings<jupyter_output><empty_output><jupyter_text>Clean `emp_title`Look at top 20 titles<jupyter_code>df['emp_title'].head(20)
df['emp_title'].value_counts().head(20)
df['int_rate'].apply(remove_percent)<jupyter_output><empty_output><jupyter_text>How often is `emp_title` null?<jupyter_code>df['emp_title'].isnull().sum()<jupyter_output><empty_output><jupyter_text>Clean the title and handle missing values* Capitalize* Strip Spaces* Replace 'Nan' with 'Missing'<jupyter_code>examples = ['owner', 'Supervisor ',
           ' Project Manager', np.nan]

def clean_title(x):
    if instance(x,str):
        if str == "RN":
            return "Registered Nurse"
        return x.strip().title()#if it is a string do this, if not string, do something else
    else:
        return 'Unknown'
[clean_title(x) for x in examples]
      
df['emp_title'] = df['emp_title'].apply(clean_title)
df['emp_title'].head(10)
df['emp_title'].apply(clean_title).head(10)
df['emp_title'].value_counts().head(20)<jupyter_output><empty_output><jupyter_text>Create `emp_title_manager`pandas documentation: [`str.contains`](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.str.contains.html)<jupyter_code>df['emp_title_manager'] = df['emp_title'].str.contains('Manager')#adding a new columns for manager
df['emp_title_manager'].head(20)
df['emp_title_manager'].value_counts(normalize=True)
df.groupby('emp_title_manager')['int_rate'].mean()
df['emp_title_manager'].nunique()
df['emp_title_manager'].value_counts()
df.isnull().sum().sort_values(ascending=False)/len(df)
#is it missing or is it not applicable?
#looking at occurrences of 1 for a work title, there's a lot of errors.<jupyter_output><empty_output><jupyter_text>Work with dates<jupyter_code>df['issue_d'].head().values
df['issue_d'].describe()
pd.to_datetime(df['issue_d'], infer_dataframe_format=True)#pandas infers date and time automatically
#pandas docs infer_datetime_format defaults to False, but True, attempt to infer format, switch to a faster method for parsing.if you don't envoke this, it will run slightly slower
df['issue_d'].head().values
df['issue_d'].describe()#this table that gets outputted is an object. Pay attention to additional two rows. 

#datetime components in pandas datetime allows you to PULL OUT specific times - such as weekday. 
df['issue_year'] = df['issue_d'].dt.year
df['issue_month'] = df['issue_d'].dt.month
df.shape
df['issue_month'].value_counts()
df['issue_month'].sample(n=10).values<jupyter_output><empty_output><jupyter_text>pandas documentation- [to_datetime](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.to_datetime.html)- [Time/Date Components](https://pandas.pydata.org/pandas-docs/stable/timeseries.htmltime-date-components) "You can access these properties via the `.dt` accessor"  Datetime Math<jupyter_code>df['earlierst_cr_line'].head()
df['earlierst_cr_line'] = pd.to_datetime(df['earlierst_cr_line'], infer_datetime_format=True)#overwrite column to convert to datetime
df['earlierst_cr_line'].head().values
df['days_from_earliest_credit_to_issue'] = (df['issue_d'] - df['earlierst_cr_line']).dt.days
df['days_from_earliest_credit_to_issue'].describe()
25171 / 365
#what if we want to convert multiple columns
#for loop over df, outputs string
[for col in df if col.endswith('_d')]
for col in ['last_pymnt_d', 'next_paymnt_d', 'last_credit_pull_d']:
    df[col] = pd.to_datetime(df[col], infer_datetime_format=True)#for each column, convert to datefram and assign back to column
df.describe(include='datetime')<jupyter_output><empty_output><jupyter_text>ASSIGNMENT- Replicate the lesson code.- 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.<jupyter_code><jupyter_output><empty_output><jupyter_text><jupyter_code>df['term'] = df['term'].strip('months').astype(int)<jupyter_output><empty_output><jupyter_text>Convert the term column from string to integer.<jupyter_code>df['term'].head()
df['term'].value_count(dropna=False)
df['term'].isnull()
df['loan_status'].value_counts(dropna=False)<jupyter_output><empty_output><jupyter_text>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.<jupyter_code>def to1sand0s(x):
    if x == 'Current' or x == 'Fully Paid':
        return 1
    else:
        return 0
df['loan_status_is_great'] = df['loan_status'].apply(to1sand0s)
df['loan_status_is_great'].head()
df.head()<jupyter_output><empty_output><jupyter_text>Make last_pymnt_d_month and last_pymnt_d_year columns.<jupyter_code>df['last_pymnt_d'] = pd.to_datetime(df['last_pymnt_d'])
df['last_pymnt_d'].head()
df['last_pymnt_d_month'] = df['last_pymnt_d'].dt.month
df['last_pymnt_d_year'] = df['last_pymnt_d'].dt.year
df.head()<jupyter_output><empty_output><jupyter_text>STRETCH OPTIONSYou 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! You can uncomment and run the cells below to re-download and extract the Instacart data<jupyter_code># !wget https://s3.amazonaws.com/instacart-datasets/instacart_online_grocery_shopping_2017_05_01.tar.gz
# !tar --gunzip --extract --verbose --file=instacart_online_grocery_shopping_2017_05_01.tar.gz
# %cd instacart_2017_05_01<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.16552946389011963, 'fraction_numerical': 0.04519273371732388, 'mean_word_length': 6.987261146496815, 'pattern_counts': {'":': 1, '<': 79, '<?xml version=': 0, '>': 79, 'https://': 13, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 2, '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': '20', 'dir': 'jupyter-structured-clean-dedup', 'id': '396456', 'license': 'MIT', 'n_tokens_mistral': 4251, 'n_tokens_neox': 3861, 'n_words': 819, 'path': 'Lily_Su_Assignment8_LS_DS3_124_Make_features.ipynb', 'repo_name': 'LilySu/DS-Unit-1-Sprint-2-Data-Wrangling', 'size': '11281'}