<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 Modules and Connection to MongoDb
Step1:  Extraction of Data from MongoDB and Creating DataFrame
Step2:  First five rows from the dataframe
Step3:  Data filterxation
Deck, Substructure, Superstructure - survery record with 'N', 'NA' are removed
Step4:  Data Exploration
The following attributes are explored in futher detail
Step5:  Number of bridges with valid year recorded for year reconstructed.
Step6:  2. Deck, Substructure, and Supertructure
Finding mean time interval before any intervention, Intervention can be described as Repair, Reconstruction, or Rehabilitation.
Construction of dictionary consisting of 'structureNumber' as key and a list of condition rating of that particular bridge component as value. This construction of dictionary is carried for all the bridge components such as Deck, Substructure and Superstructure.
Step7:  we have a set of three dictionary for substructure, superstructure and deck of all the bridges from 1992 - 2016.
Following is the example for a dictionary explained previously.
Step8:  Dividing the time interval of deterioration on basis of suspected intervention. if the deterioration of condition rating is abruptly interfered by sudden increase in condition rating, we will consider a possible case of intervention (Repair, Reconstruction and Repair) and hence the time interval will be divided at the point of intervention, which is considered as splitting point, and second interval will be calculated from next point.
A sudden increase of two or more condition rating is considered as possible intervention. Since, An increase in condition rating of 1 is not considered a possible intervention as the rating is given by inspectors which could be subjective.
The Following function will split all possible intervals and then return a list of condition rating and return number of intervals.
Step9:  Calculating the mean time interval for first and second time interval for Deck
Step10:  As opposed to the hypothesis the mean time for second interval is greater than first time interval for deck. but not all intervals start from rating 9 and we might not consider that some of the bridges might have be reconstructed before 1992
Calculation of first condition rating of the first time interval and second time intervals.
Step11:  Calculating the mean time interval for first and second time interval for Superstructure
Step12:  As opposed to the hypothesis the mean time for second interval is greater than first time interval for superstructure.
Step13:  Calculating the mean time interval for first and second time interval for Substructure
Step14:  Consideration
Step15:  Superstructure built after 1992
Step16:  Superstructure built after 1992
Step17:  Substructure built after 1992
<ASSISTANT_TASK:>
Python Code:
from pymongo import MongoClient
import time
import pandas as pd
import numpy as np
import seaborn as sns
import matplotlib.pyplot as plt
from matplotlib.pyplot import *
import datetime as dt
import random as rnd
import warnings
import datetime as dt
import csv
%matplotlib inline
warnings.filterwarnings(action="ignore")
Client = MongoClient("mongodb://bridges:readonly@nbi-mongo.admin/bridge")
db = Client.bridge
collection = db["bridges"]
Explanation: Modules and Connection to MongoDb
End of explanation
years = [1992,1993,1994,1995,1996,1997,1998,1999,2000,2001,2002,2003,
        2004,2005,2006,2007,2008,2009,2010,2011,2012,2013,2014,2015,2016]
## convert following list to 
#states = ['25','04','08','38','09','19', '26', '48','35', '17', '51', 
#          '23','16', '36','56','29', '39','28','11', '21', '18','06','47','12',
#          '24','34','46','13','55','30','54','15', '32', '37','10','33','44',
#          '50', '42','05','20','45','22','40','72','41','53', '01', '31','02','49']
states = ['31']
masterdec = []
for yr in years:
    for state in states:
        #print(state + str(year))
        pipeline = [{"$match":{"$and":[{"year":yr},{"stateCode":state}]}},
                    {"$project":{"_id":0,
                         "year":1,
                         "stateCode":1,        
                         "structureNumber":1,
                         "yearBuilt":1,
                         "yearReconstructed":1,
                         "deck":1,
                         "substructure":1, ## rating of substructure
                         "superstructure":1, ## rating of superstructure
                         }}]
        dec = collection.aggregate(pipeline)
        for i in list(dec):
            masterdec.append(i)
        #masterdec.append(list(dec))
conditionRatings = pd.DataFrame(masterdec)
Explanation: Extraction of Data from MongoDB and Creating DataFrame
End of explanation
conditionRatings.head()
Explanation: First five rows from the dataframe
End of explanation
before = len(conditionRatings)
print("Total Records before filteration: ",len(conditionRatings))
conditionRatings = conditionRatings.loc[~conditionRatings['deck'].isin(['N','NA'])]
conditionRatings = conditionRatings.loc[~conditionRatings['substructure'].isin(['N','NA'])]
conditionRatings = conditionRatings.loc[~conditionRatings['superstructure'].isin(['N','NA'])]
after = len(conditionRatings)
print("Total Records after filteration: ",len(conditionRatings))
print("Difference: ", before - after)
Explanation: Data filterxation
Deck, Substructure, Superstructure - survery record with 'N', 'NA' are removed
End of explanation
conditionRatings['yearReconstructed'].unique()
Explanation: Data Exploration
The following attributes are explored in futher detail:
1. Year reconstructed 
2. Deck condition, Substructure condition and Superstructure condition
1. Year reconstructed
List of years in which bridges went under reconstruction
Note: The list contains 0 and -1 , these values indicate that year of reconstruction was either recorded incorrectly or was absent
End of explanation
yrCR = conditionRatings.loc[~conditionRatings['yearReconstructed'].isin([0,-1])]
len(yrCR)
yrCR = yrCR.groupby('yearReconstructed').agg(["count"])
column = ['deck','stateCode','structureNumber','substructure','superstructure','year','yearBuilt']
yrCR.columns = column
yrCR.sort_values(by='deck',ascending = False)['structureNumber'][:15]
Explanation: Number of bridges with valid year recorded for year reconstructed.
End of explanation
deckCondition = {k: g["deck"].tolist() for k,g in conditionRatings.groupby("structureNumber")}
substructureCondition = {k: g["substructure"].tolist() for k,g in conditionRatings.groupby("structureNumber")}
superstructureCondition = {k: g["superstructure"].tolist() for k,g in conditionRatings.groupby("structureNumber")}
Explanation: 2. Deck, Substructure, and Supertructure
Finding mean time interval before any intervention, Intervention can be described as Repair, Reconstruction, or Rehabilitation.
Construction of dictionary consisting of 'structureNumber' as key and a list of condition rating of that particular bridge component as value. This construction of dictionary is carried for all the bridge components such as Deck, Substructure and Superstructure.
End of explanation
deckCondition
Explanation: we have a set of three dictionary for substructure, superstructure and deck of all the bridges from 1992 - 2016.
Following is the example for a dictionary explained previously.
End of explanation
def findAllIntervals(lst):
    fList = []
    temp = []
    i = 0
    j = 1
    for k in lst:
        if j == len(lst):
            temp.append(int(lst[i]))
            fList.append(temp)
            return (fList,len(fList))
        if lst[i] == lst[j]:
            pass
        temp.append(int(lst[i]))
        if lst[i] < lst[j]:
            diff = int(lst[j]) - int(lst[i])
            if diff > 1:
               #break
               fList.append(temp)
               temp = []
            pass
        i = i + 1
        j = j + 1
        
Explanation: Dividing the time interval of deterioration on basis of suspected intervention. if the deterioration of condition rating is abruptly interfered by sudden increase in condition rating, we will consider a possible case of intervention (Repair, Reconstruction and Repair) and hence the time interval will be divided at the point of intervention, which is considered as splitting point, and second interval will be calculated from next point.
A sudden increase of two or more condition rating is considered as possible intervention. Since, An increase in condition rating of 1 is not considered a possible intervention as the rating is given by inspectors which could be subjective.
The Following function will split all possible intervals and then return a list of condition rating and return number of intervals.
End of explanation
deckIntervals = []
deckIntervalSize = []
for i in deckCondition.values():
    lst, size = findAllIntervals(i)
    deckIntervalSize.append(size)
    deckIntervals.append(lst)
        
#
from collections import Counter
keys =list(Counter(deckIntervalSize).keys())
values =list(Counter(deckIntervalSize).values())
plt.figure(figsize=(10,8))
plt.bar(keys,values)
plt.xlabel("No. of Intervals")
plt.ylabel("No. of Records")
plt.title("No. of Records vs No. of Intervals")
plt.show()
## Filter records with only two or more interval
deckIntervalGt2 = []
for index in range(0,len(deckIntervalSize)-1,1):
    if deckIntervalSize[index] > 1:
        deckIntervalGt2.append(deckIntervals[index])
first_interval = []
second_interval = []
for intervals in deckIntervalGt2:
    first_interval.append(len(intervals[0]))
    second_interval.append(len(intervals[1]))
print("Mean of the first time intervals :", np.mean(first_interval))
print("Mean of the second time intervals :", np.mean(second_interval))
Explanation: Calculating the mean time interval for first and second time interval for Deck
End of explanation
avgStartOfFirstInterval = []
avgStartOfSecondInterval = []
for intervals in deckIntervalGt2:
    avgStartOfFirstInterval.append(intervals[0][0])
    avgStartOfSecondInterval.append(intervals[1][0])
print("Mean of the first condition rating of first time intervals :", np.mean(avgStartOfFirstInterval))
print("Mean of the first condition rating of second time intervals :", np.mean(avgStartOfSecondInterval))
Explanation: As opposed to the hypothesis the mean time for second interval is greater than first time interval for deck. but not all intervals start from rating 9 and we might not consider that some of the bridges might have be reconstructed before 1992
Calculation of first condition rating of the first time interval and second time intervals.
End of explanation
superstructureIntervals = []
superstructureIntervalSize = []
for i in superstructureCondition.values():
    lst, size = findAllIntervals(i)
    superstructureIntervalSize.append(size)
    superstructureIntervals.append(lst)
        
#
from collections import Counter
keys_superstructure =list(Counter(superstructureIntervalSize).keys())
values_superstructure =list(Counter(superstructureIntervalSize).values())
plt.figure(figsize=(10,8))
plt.bar(keys_superstructure,values_superstructure)
plt.xlabel("No. of Intervals")
plt.ylabel("No. of Records")
plt.xticks([1,2,3,4,5])
plt.title("No. of Records vs No. of Intervals")
plt.show()
## Filter records with only two or more interval
superstructureIntervalGt2 = []
for index in range(0,len(superstructureIntervalSize)-1,1):
    if superstructureIntervalSize[index] > 1:
        superstructureIntervalGt2.append(superstructureIntervals[index])
superstructureFirstInterval = []
superstructureSecondInterval = []
for intervals in superstructureIntervalGt2:
    superstructureFirstInterval.append(len(intervals[0]))
    superstructureSecondInterval.append(len(intervals[1]))
print("Mean of the first time intervals :", np.mean(superstructureFirstInterval))
print("Mean of the second time intervals :", np.mean(superstructureSecondInterval))
Explanation: Calculating the mean time interval for first and second time interval for Superstructure
End of explanation
avgStartOfFirstInterval_sp = []
avgStartOfSecondInterval_sp = []
for intervals in superstructureIntervalGt2:
    avgStartOfFirstInterval_sp.append(intervals[0][0])
    avgStartOfSecondInterval_sp.append(intervals[1][0])
print("Mean of the first condition rating of first time intervals :", np.mean(avgStartOfFirstInterval_sp))
print("Mean of the first condition rating of second time intervals :", np.mean(avgStartOfSecondInterval_sp))
Explanation: As opposed to the hypothesis the mean time for second interval is greater than first time interval for superstructure.
End of explanation
substructureIntervals = []
substructureIntervalSize = []
for i in substructureCondition.values():
    lst, size = findAllIntervals(i)
    substructureIntervalSize.append(size)
    substructureIntervals.append(lst)
        
#
from collections import Counter
keys_substructure =list(Counter(substructureIntervalSize).keys())
values_substructure =list(Counter(substructureIntervalSize).values())
plt.figure(figsize=(10,8))
plt.bar(keys_substructure,values_substructure)
plt.xlabel("No. of Intervals")
plt.ylabel("No. of Records")
plt.xticks([1,2,3,4,5])
plt.title("No. of Records vs No. of Intervals")
plt.show()
## Filter records with only two or more interval
substructureIntervalGt2 = []
for index in range(0,len(substructureIntervalSize)-1,1):
    if substructureIntervalSize[index] > 1:
        substructureIntervalGt2.append(substructureIntervals[index])
substructureFirstInterval = []
substructureSecondInterval = []
for intervals in substructureIntervalGt2:
    substructureFirstInterval.append(len(intervals[0]))
    substructureSecondInterval.append(len(intervals[1]))
print("Mean of the first time intervals :", np.mean(substructureFirstInterval))
print("Mean of the second time intervals :", np.mean(substructureSecondInterval))
avgStartOfFirstInterval_sb = []
avgStartOfSecondInterval_sb = []
for intervals in substructureIntervalGt2:
    avgStartOfFirstInterval_sb.append(intervals[0][0])
    avgStartOfSecondInterval_sb.append(intervals[1][0])
print("Mean of the first condition rating of first time intervals :", np.mean(avgStartOfFirstInterval_sb))
print("Mean of the first condition rating of second time intervals :", np.mean(avgStartOfSecondInterval_sb))
Explanation: Calculating the mean time interval for first and second time interval for Substructure
End of explanation
CR1992 = conditionRatings.loc[conditionRatings['yearBuilt'] >=1992] 
CR1992.head()
Explanation: Consideration: 
1. We might consider bridges built only after 1992 to get an unbaised mean years of first time interval.
End of explanation
deckCondition_1992 = {k: g["deck"].tolist() for k,g in CR1992.groupby("structureNumber")}
substructureCondition_1992 = {k: g["substructure"].tolist() for k,g in CR1992.groupby("structureNumber")}
superstructureCondition_1992 = {k: g["superstructure"].tolist() for k,g in CR1992.groupby("structureNumber")}
deckIntervals_1992 = []
deckIntervalSize_1992 = []
for i in deckCondition_1992.values():
    lst, size = findAllIntervals(i)
    deckIntervalSize_1992.append(size)
    deckIntervals_1992.append(lst)
        
#
from collections import Counter
keys_d1992 =list(Counter(deckIntervalSize_1992).keys())
values_d1992 =list(Counter(deckIntervalSize_1992).values())
plt.figure(figsize=(10,8))
plt.bar(keys_d1992,values_d1992)
plt.xlabel("No. of Intervals")
plt.ylabel("No. of Records")
plt.xticks([1,2,3,4,5])
plt.title("No. of Records vs No. of Intervals")
plt.show()
## Filter records with only two or more interval
deckIntervalGt2_1992 = []
for index in range(0,len(deckIntervalSize_1992)-1,1):
    if deckIntervalSize_1992[index] > 1:
        deckIntervalGt2_1992.append(deckIntervals_1992[index])
        
deckFirstInterval_1992 = []
deckSecondInterval_1992 = []
for intervals in deckIntervalGt2_1992:
    deckFirstInterval_1992.append(len(intervals[0]))
    deckSecondInterval_1992.append(len(intervals[1]))
print("Mean of the first time intervals :", np.mean(deckFirstInterval_1992))
print("Mean of the second time intervals :", np.mean(deckSecondInterval_1992))
print()
avgStartOfFirstInterval_1992 = []
avgStartOfSecondInterval_1992 = []
for intervals in deckIntervalGt2_1992:
    avgStartOfFirstInterval_1992.append(intervals[0][0])
    avgStartOfSecondInterval_1992.append(intervals[1][0])
print("Mean of the first condition rating of first time intervals :", np.mean(avgStartOfFirstInterval_1992))
print("Mean of the first condition rating of second time intervals :", np.mean(avgStartOfSecondInterval_1992))
Explanation: Superstructure built after 1992
End of explanation
superstructureIntervals_1992 = []
superstructureIntervalSize_1992 = []
for i in superstructureCondition_1992.values():
    lst, size = findAllIntervals(i)
    superstructureIntervalSize_1992.append(size)
    superstructureIntervals_1992.append(lst)
        
#
from collections import Counter
keys_sp1992 =list(Counter(superstructureIntervalSize_1992).keys())
values_sp1992 =list(Counter(superstructureIntervalSize_1992).values())
plt.figure(figsize=(10,8))
plt.bar(keys_sp1992,values_sp1992)
plt.xlabel("No. of Intervals")
plt.ylabel("No. of Records")
plt.xticks([1,2,3,4,5])
plt.title("No. of Records vs No. of Intervals")
plt.show()
superstructureIntervalGt2_1992 = []
for index in range(0,len(superstructureIntervalSize_1992)-1,1):
    if superstructureIntervalSize_1992[index] > 1:
        superstructureIntervalGt2_1992.append(superstructureIntervals_1992[index])
        
superstructureFirstInterval_1992 = []
superstructureSecondInterval_1992 = []
for intervals in superstructureIntervalGt2_1992:
    superstructureFirstInterval_1992.append(len(intervals[0]))
    superstructureSecondInterval_1992.append(len(intervals[1]))
print("Mean of the first time intervals :", np.mean(superstructureFirstInterval_1992))
print("Mean of the second time intervals :", np.mean(superstructureSecondInterval_1992))
print()
avgStartOfFirstInterval_sp_1992 = []
avgStartOfSecondInterval_sp_1992 = []
for intervals in superstructureIntervalGt2_1992:
    avgStartOfFirstInterval_sp_1992.append(intervals[0][0])
    avgStartOfSecondInterval_sp_1992.append(intervals[1][0])
print("Mean of the first condition rating of first time intervals :", np.mean(avgStartOfFirstInterval_sp_1992))
print("Mean of the first condition rating of second time intervals :", np.mean(avgStartOfSecondInterval_sp_1992))
Explanation: Superstructure built after 1992
End of explanation
substructureIntervals_1992 = []
substructureIntervalSize_1992 = []
for i in substructureCondition_1992.values():
    lst, size = findAllIntervals(i)
    substructureIntervalSize_1992.append(size)
    substructureIntervals_1992.append(lst)
        
#
from collections import Counter
keys_sb1992 =list(Counter(substructureIntervalSize_1992).keys())
values_sb1992 =list(Counter(substructureIntervalSize_1992).values())
plt.figure(figsize=(10,8))
plt.bar(keys_sb1992,values_sb1992)
plt.xlabel("No. of Intervals")
plt.ylabel("No. of Records")
plt.xticks([1,2,3,4,5])
plt.title("No. of Records vs No. of Intervals")
plt.show()
substructureIntervalGt2_1992 = []
for index in range(0,len(substructureIntervalSize_1992)-1,1):
    if substructureIntervalSize_1992[index] > 1:
        substructureIntervalGt2_1992.append(substructureIntervals_1992[index])
        
substructureFirstInterval_1992 = []
substructureSecondInterval_1992 = []
for intervals in substructureIntervalGt2_1992:
    substructureFirstInterval_1992.append(len(intervals[0]))
    substructureSecondInterval_1992.append(len(intervals[1]))
print("Mean of the first time intervals :", np.mean(substructureFirstInterval_1992))
print("Mean of the second time intervals :", np.mean(substructureSecondInterval_1992))
print()
avgStartOfFirstInterval_sb_1992 = []
avgStartOfSecondInterval_sb_1992 = []
for intervals in substructureIntervalGt2_1992:
    avgStartOfFirstInterval_sb_1992.append(intervals[0][0])
    avgStartOfSecondInterval_sb_1992.append(intervals[1][0])
print("Mean of the first condition rating of first time intervals :", np.mean(avgStartOfFirstInterval_sb_1992))
print("Mean of the first condition rating of second time intervals :", np.mean(avgStartOfSecondInterval_sb_1992))
## Consider creating a deterioration model and curves
Explanation: Substructure built after 1992
End of explanation
<END_TASK>