import pandas as pd

df = pd.read_csv('./banklist.csv')

"""
Explanation: Pandas Exercises
Time to test your new pandas skills! Use the two csv files in this folder to complete the tasks in bold below!
 NOTE: ALL TASKS MUST BE DONE IN ONE LINE OF PANDAS CODE. GOT STUCK? NO PROBLEM! CHECK OUT THE SOLUTIONS LECTURE! 
 Import pandas and read in the banklist.csv file into a dataframe called banks.
End of explanation
"""


# CODE HERE
df.head()

"""
Explanation: Show the head of the dataframe
End of explanation
"""


# CODE HERE
df.columns

"""
Explanation: What are the column names?
End of explanation
"""


# CODE HERE
df['ST'].unique().shape[0]
# or
# len(df['ST'].unique())

"""
Explanation: How many States (ST) are represented in this data set?
End of explanation
"""


# CODE HERE
df['ST'].unique()

"""
Explanation: Get a list or array of all the states in the data set.
End of explanation
"""


# CODE HERE
df['ST'].value_counts().head()

"""
Explanation: What are the top 5 states with the most failed banks?
End of explanation
"""


# CODE HERE
df['Acquiring Institution'].value_counts().head()

"""
Explanation: What are the top 5 acquiring institutions?
End of explanation
"""


# CODE HERE
df[df['Acquiring Institution'] == 'State Bank of Texas']

len(df[df['Acquiring Institution'] == 'State Bank of Texas'])

len(df[(df['Acquiring Institution'] == 'State Bank of Texas') & (df['ST'] == 'TX')])

"""
Explanation: How many banks has the State Bank of Texas acquired? How many of them were actually in Texas?
End of explanation
"""


# CODE HERE
df[df['ST'] == 'CA']['City'].value_counts()[0]

"""
Explanation: What is the most common city in California for a bank to fail in?
End of explanation
"""


# CODE HERE
len(df[~df['Bank Name'].str.contains('Bank')]['Bank Name'].unique())

"""
Explanation: How many failed banks don't have the word "Bank" in their name?
End of explanation
"""


# CODE HERE
df['Bank Name'].apply(lambda name: name[0].lower() == 's').value_counts()

"""
Explanation: How many bank names start with the letter 's' ?
End of explanation
"""


# CODE HERE
sum(df['CERT'] > 20000)

"""
Explanation: How many CERT values are above 20000 ?
End of explanation
"""


# CODE HERE
sum(df['Bank Name'].apply(lambda name: len(name.split()) == 2))

"""
Explanation: How many bank names consist of just two words? (e.g. "First Bank" , "Bank Georgia" )
End of explanation
"""


# CODE HERE
sum(df['Closing Date'].apply(lambda date: date[-2:] == '08'))

"""
Explanation: Bonus: How many banks closed in the year 2008? (this is hard because we technically haven't learned about time series with pandas yet! Feel free to skip this one!
End of explanation
"""


