# printing verions of Python modules and packages with **watermark** 
# documention for installing watermark: https://github.com/rasbt/watermark

%load_ext watermark
%watermark -v -p numpy,pandas,matplotlib,seaborn

# reading in our data as a pandas dataframe called building_df
path = 'https://data.cityofnewyork.us/api/views/qb5r-6dgf/rows.csv?accessType=DOWNLOAD'
building_df = pd.read_csv(path)

# preview the data
building_df.head()

# previewing the first five rows
building_df.head()

# previewing the last five rows
building_df.tail()

# printing the shape or dimensions of our dataframe (i.e. rows, columns)
building_df.shape

# more readable
rows, columns = building_df.shape
print('This dataset has {:,} rows and {:,} columns.'.format(rows, columns))

# the object's type
type(building_df)

# printing the columns
building_df.columns

# printing the data types of our columns
building_df.dtypes

# printing the column names, non-null counts, and datatypes of our columns
building_df.info()

# printing index type
building_df.index

# printing True/False if column is unique on our unique key (DOITT_ID)
unique = building_df['DOITT_ID'].is_unique
print('our unique id is truly unique: {}'.format(unique))

# printing descriptive statistics of our numeric columns
building_df.describe()

# printing descriptive statistics of our non-numeric columns
building_df.describe(include=['O'])

# returning the mode of CNSTRCT_YR
(building_df
 .loc[:, ['CNSTRCT_YR']]
 .mode(numeric_only=True)
 .rename(index={0: "mode"})
)

# value counts of the NAME column - top 20
(building_df['NAME']
 .value_counts()
 .head(20)
)

# compute pairwise correlation of columns, excluding NA/null values
building_df.corr()

# correlation in the form of heat map
fig, ax = plt.subplots(figsize=(10, 6))

sns.heatmap(
    building_df.corr(), 
    annot=True, 
    ax=ax
)

plt.tight_layout()

# correlation in the form of heat map
fig, ax = plt.subplots(figsize=(10,6))

# dropping columns of all nulls
corr_df = building_df.drop(columns=['SHAPE_AREA', 'SHAPE_LEN'])

sns.heatmap(
    corr_df.corr(), 
    annot=True, 
    ax=ax
)

plt.tight_layout()

# nulls per column
(building_df
 .isnull()
 .sum()
)

# total nulls in dataframe
null_count = (
    building_df
    .isnull()
    .sum()
    .sum()
)

print('total nulls in dataframe: {:,}'.format(null_count))

# return a boolean same-sized object of nulls
building_df.isnull()

# return a boolean if any of the values are null in column
(building_df
 .isnull()
 .any()
)

# calculating a percentage of the number of nulls to total number of records of each column
missing_data = (building_df.isnull().sum() / len(building_df)) * 100

# creating a dataframe
missing_data = pd.DataFrame(missing_data, columns=['perc_missing']) 
missing_data = missing_data.sort_values(by='perc_missing', ascending=False)

missing_data

# printing the object's type of a column
type(building_df['CNSTRCT_YR'])

# notice the difference of type from dataframe to series
print(type(building_df["CNSTRCT_YR"]))
print(type(building_df[["CNSTRCT_YR"]]))

# returning a series of the 'CNSTRCT_YR' column
building_df["CNSTRCT_YR"]

# returning a dataframe of the 'CNSTRCT_YR' column double brackets
building_df[["CNSTRCT_YR"]]

# first five rows sorted by the 'CNSTRCT_YR' column in ascending order
building_df.sort_values('CNSTRCT_YR').head()

# first five rows sorted by the 'CNSTRCT_YR' column in descending order
building_df.sort_values('CNSTRCT_YR', ascending=False).head()

"""
Descriptive statistics include those that summarize the central tendency, 
dispersion and shape of a dataset's distribution, excluding ``NaN`` values.

Reset index for readability.
"""

(building_df['CNSTRCT_YR']
 .describe()
 .reset_index()
 .rename(columns={'index': "statistic"})
)

# detecting any nulls in our 'CNSTRCT_YR' column
(building_df['CNSTRCT_YR']
 .isnull()
 .values
 .any()
)

# summing all the null values in our 'CNSTRCT_YR' column
(building_df['CNSTRCT_YR']
 .isnull()
 .sum()
)

# summing all the not null values in our 'CNSTRCT_YR' column
(building_df['CNSTRCT_YR']
 .notnull()
 .sum()
)

# printing the percent total of all the null values in our 'CNSTRCT_YR' column.
nulls_sum = building_df['CNSTRCT_YR'].isnull().sum() 
nulls_perc = round(nulls_sum / len(building_df) * 100, 4)

print('percent nulls: {}%'.format(nulls_perc))

# printing the percent total of all the not null values in our 'CNSTRCT_YR' column.
nonnulls_sum = building_df['CNSTRCT_YR'].notnull().sum() 
nonnulls_perc = round(nonnulls_sum / len(building_df) * 100, 4)

print('percent not nulls: {}%'.format(nonnulls_perc))

# making a histogram of our 'CNSTRCT_YR' column with 25 bins

fig, ax = plt.subplots(figsize=(8, 6))

building_df['CNSTRCT_YR'].hist(ax=ax, bins=25)

# adding labels
plt.xlabel('Construction Year', fontsize=15)
plt.ylabel('Counts', fontsize=15) 
plt.title('Histogram of Construction Year Values', fontsize=15) 

plt.tight_layout()

# making a histogram of our 'CNSTRCT_YR' column with 50 bins
fig, ax = plt.subplots(figsize=(8, 6))

building_df['CNSTRCT_YR'].hist(ax=ax, bins=50)

plt.xlabel('Construction Year', fontsize=15)
plt.ylabel('Counts', fontsize=15)
plt.title('Histogram of Construction Year Values', fontsize=15)

plt.tight_layout()

# prints the counts of values in our 'CNSTRCT_YR' column in descending order
building_df['CNSTRCT_YR'].value_counts()

# counts of values sorted by the index (in this case, year) ascending order
building_df['CNSTRCT_YR'].value_counts().sort_index()

# value counts of the first ten rows sorted by the index ascending order
(building_df['CNSTRCT_YR']
 .value_counts()
 .sort_index(ascending=True)
 .head(10)
)

# value counts of the first ten rows sorted by the index descending order
(building_df['CNSTRCT_YR']
 .value_counts()
 .sort_index(ascending=False)
 .head(10)
)

# making a box plot from our 'CNSTRCT_YR' column
fig, ax = plt.subplots(figsize=(8, 6))

building_df.boxplot(column=['CNSTRCT_YR'], ax=ax)

plt.title('Box Plot of Construction Year Value', fontsize=13)

plt.tight_layout()

# printing the 25% and 75% quantiles of our 'CNSTRCT_YR' column
print(building_df['CNSTRCT_YR'].quantile(0.25))
print(building_df['CNSTRCT_YR'].quantile(0.75))

# saving the 25% (i.e. Q1) and 75% (i.e. Q3) quantiles of our 'CNSTRCT_YR' column
Q1 = building_df['CNSTRCT_YR'].quantile(0.25)
Q3 = building_df['CNSTRCT_YR'].quantile(0.75)

# Computing the interquartile range (IQR)
IQR = Q3 - Q1

# printing the IQR of our 'CNSTRCT_YR' column
IQR

# printing the lower 1.5*IQR whisker = Q1 - 1.5 * IQR
print((Q1 - 1.5 * IQR))

# printing the upper 1.5*IQR whisker = Q1 - 1.5 * IQR
print((Q3 + 1.5 * IQR))

# dropping all records where 'CNSTRCT_YR' as nan
building_df_dropna = building_df.dropna(subset=['CNSTRCT_YR'])

# printing number of records
print(building_df_dropna.shape[0])

# making a histogram of our 'CNSTRCT_YR' column with 10 bins
fig, ax = plt.subplots(figsize=(8, 6))

building_df_dropna['CNSTRCT_YR'].hist(bins=10, ax=ax)

plt.xlabel('Construction Year', fontsize=12)
plt.ylabel('Counts', fontsize=12)
plt.title('Histogram of Construction Year Values Excluding Nulls', fontsize=12)

plt.tight_layout()

# saving a new dataframe to fill nans with the median value of 'CNSTRCT_YR'
building_df_fillna = building_df.copy()

counts = building_df_fillna['CNSTRCT_YR'].isnull().sum()
print('number of nulls for CNSTRCT_YR: {:,}'.format(counts))

median = building_df_fillna['CNSTRCT_YR'].median()

print('median value of CNSTRCT_YR: {}'.format(median))

# saving the median of our 'CNSTRCT_YR' as the name 'median'
median = building_df_fillna['CNSTRCT_YR'].median()

# filling na values in our'CNSTRCT_YR' column with the column's median value
building_df_fillna["CNSTRCT_YR"] = building_df_fillna["CNSTRCT_YR"].fillna(value=median)
building_df_fillna.head()

counts = building_df_fillna['CNSTRCT_YR'].isnull().sum()
print('number of nulls for CNSTRCT_YR: {:,}'.format(counts))

# returning number of buildings by year as 'g'
g = (
    building_df_dropna
     .groupby('CNSTRCT_YR')['DOITT_ID']
     .count()
     .reset_index()
     .rename({'DOITT_ID': 'count'}, axis=1)
     .set_index('CNSTRCT_YR', drop=True)
)

g

# set index to int
g.index = g.index.astype('int64')

g.head()

fig, ax = plt.subplots(figsize=(8, 6))

# plotting line chart of number of buildings by 'CNSTRCT_YR'
ax.plot(g['count'], linewidth=2)

plt.xlabel('Construction Year', fontsize=12)
plt.ylabel('Number of Buildings', fontsize=12)
plt.title('Number of Buildings by Construction Year in NYC', fontsize=12)

plt.tight_layout()

# previewing number of buildings constructed after 1900
g.loc[g.index >= 1900].head()

fig, ax = plt.subplots(figsize=(8, 6))

# plotting line chart of number of buildings by 'CNSTRCT_YR' built after 1900
ax.plot(g.loc[g.index >= 1900], linewidth=2)

plt.xlabel('Construction Year', fontsize=12)
plt.ylabel('Number of Buildings', fontsize=12)
plt.title('Number of Buildings by Construction Year Built After 1900 in NYC', fontsize=12)

plt.tight_layout()

# calculating the cumulative sum of buildings by 'CNSTRCT_YR'
g['cumsum'] = g['count'].cumsum()

# previewing first five rows
g.head()

fig, ax = plt.subplots(figsize=(8,6))

# plotting line chart of cumulative number of buildings by 'CNSTRCT_YR'
ax.plot(g['cumsum'], linewidth=2)

plt.xlabel('Construction Year', fontsize=12)
plt.ylabel('Cumulative Number of Buildings', fontsize=12)
plt.title('Cumulative Number of Buildings by Construction Year in NYC', fontsize=12)

plt.tight_layout()

fig, ax = plt.subplots(figsize=(8, 6))

# plotting line chart of cumulative number of buildings by 'CNSTRCT_YR' built after 1900
ax.plot(g.loc[g.index >= 1900, ['cumsum']], linewidth=2)

plt.xlabel('Construction Year', fontsize=12)
plt.ylabel('Cumulative Number of Buildings', fontsize=12)
plt.title('Cumulative Number of Buildings by Construction Year Built After 1900 in NYC', fontsize=12)

plt.tight_layout()

# sorting by the 'CNSTRCT_YR' in ascending order and 
# dropping records that don't have a building name (i.e. NAME)

(building_df
 .sort_values(by='CNSTRCT_YR')
 .dropna(subset=['NAME'])
 .head()
)

# identifying the oldest building with a building name in this dataset and
oldest = (
    building_df
    .sort_values(by='CNSTRCT_YR')
    .dropna(subset=['NAME'])
    .head(1)
)

oldest