

In [2]:

    
Table = pd.read_csv('C:\Users\Ty Dickinson\Downloads\ClimoFlooding.csv', usecols=[3, 4, 7, 8, 9, 10])

Table['BEGIN_DATE'] = pd.to_datetime(Table.BEGIN_DATE)
Table['Total_Damages'] = Table['DAMAGE_PROPERTY_NUM'] + Table['DAMAGE_CROPS_NUM']
Table['Z_Time'] = Table['BEGIN_TIME']
Table['Z_Day'] = Table['BEGIN_DATE']

pd.options.mode.chained_assignment = None

for i in range(0, len(Table.BEGIN_TIME)):
    if Table.BEGIN_TIME[i] <= 1759:
        Table.Z_Time[i] = Table.BEGIN_TIME[i] + 600
        Table.Z_Day[i] = Table.BEGIN_DATE[i] 
    else:
        Table.Z_Time[i] = (Table.BEGIN_TIME[i] + 600) - 2400
        Table.Z_Day[i] = Table.BEGIN_DATE[i] + pd.DateOffset(days=1)

WinterReports = []
SpringReports = []
SummerReports = []
FallReports = []

WinterDeath = []
SpringDeath = []
SummerDeath = []
FallDeath = []

WinterInjury = []
SpringInjury = []
SummerInjury = []
FallInjury = []

WinterCost = []
SpringCost = []
SummerCost = []
FallCost = []

WinterReportDaysList = []
SpringReportDaysList = []
SummerReportDaysList = []
FallReportDaysList = []

#setting the index as date to use the df.loc expression 
df = Table.set_index(['BEGIN_DATE'])
#Winter_82 = df.loc['1981-12-01':'1982-02-28']

#defining date ranges and converting them to a Datetime data type
Date1 = pd.to_datetime('12-01-1995')
Date2 = pd.to_datetime('02-28-1996')
Date3 = pd.to_datetime('03-01-1996')
Date4 = pd.to_datetime('05-31-1996')
Date5 = pd.to_datetime('06-01-1996')
Date6 = pd.to_datetime('08-31-1996')
Date7 = pd.to_datetime('09-01-1996')
Date8 = pd.to_datetime('11-30-1996')
WinterCount = 0
SpringCount = 0
SummerCount = 0
FallCount = 0

#while statements to cycle through seasons and calculate reports, deaths, injuries, cost, and report days
while WinterCount <= 14:
    x = df.loc[Date1:Date2]
    WinterReports.insert(WinterCount,len(x.index))
    y = x.DEATHS_DIRECT.sum()
    WinterDeath.insert(WinterCount,y)
    z = x.INJURIES_DIRECT.sum()
    WinterInjury.insert(WinterCount,z)
    c = x.Total_Damages.sum()
    WinterCost.insert(WinterCount,c)
    
    WinterReportDays = 1
    #nested if statements to calculate report days based on 12Z to 12Z the next day as 1 day
    if len(x.index) == 0:
        WinterReportDaysList.insert(WinterCount, 0)
    else: 
        for i in range(1, len(x.index)):
            if ((x.Z_Day[i] == x.Z_Day[i-1]) and ((x.Z_Time[i] < 1200) and (x.Z_Time[i-1] < 1200))):
                continue
            elif ((x.Z_Day[i] == x.Z_Day[i-1]) and ((x.Z_Time[i] >= 1200) and (x.Z_Time[i-1] >= 1200))):
                continue
            elif (x.Z_Day[i] == (x.Z_Day[i-1] + pd.DateOffset(days=1))) and ((x.Z_Time[i] < 1200) and (x.Z_Time[i-1] >= 1200)):
                continue
            else:
                WinterReportDays = WinterReportDays + 1
        WinterReportDaysList.insert(WinterCount, WinterReportDays)
        
    #if the year was a leap year, then this sets the end date back to Feb. 28
    if Date2.is_leap_year:
        Date2 = Date2 - pd.DateOffset(days=1)
    #adds a year to the end date range for the next cycle of the while statement
    Date1 = Date1 + pd.DateOffset(years=1)
    Date2 = Date2 + pd.DateOffset(years=1)
    #if the upcoming year is a leap year, add 1 day to account for Feb. 29
    if Date2.is_leap_year:
        Date2 = Date2 + pd.DateOffset(days=1)
    WinterCount = WinterCount + 1
    
while SpringCount <= 14:
    x = df.loc[Date3:Date4]
    SpringReports.insert(SpringCount,len(x.index))
    y = x.DEATHS_DIRECT.sum()
    SpringDeath.insert(SpringCount,y)
    z = x.INJURIES_DIRECT.sum()
    SpringInjury.insert(SpringCount,z)
    c = x.Total_Damages.sum()
    SpringCost.insert(WinterCount,c)
    
    SpringReportDays = 1
    if len(x.index) == 0:
        SpringReportDaysList.insert(SpringCount, 0)
    else: 
        for i in range(1, len(x.index)):
            if ((x.Z_Day[i] == x.Z_Day[i-1]) and ((x.Z_Time[i] < 1200) and (x.Z_Time[i-1] < 1200))):
                continue
            elif ((x.Z_Day[i] == x.Z_Day[i-1]) and ((x.Z_Time[i] >= 1200) and (x.Z_Time[i-1] >= 1200))):
                continue
            elif (x.Z_Day[i] == (x.Z_Day[i-1] + pd.DateOffset(days=1))) and ((x.Z_Time[i] < 1200) and (x.Z_Time[i-1] >= 1200)):
                continue
            else:
                SpringReportDays = SpringReportDays + 1
        SpringReportDaysList.insert(SpringCount, SpringReportDays)
        
    Date3 = Date3 + pd.DateOffset(years=1)
    Date4 = Date4 + pd.DateOffset(years=1)
    SpringCount = SpringCount + 1
    
while SummerCount <= 14:
    x = df.loc[Date5:Date6]
    SummerReports.insert(SummerCount,len(x.index))
    y = x.DEATHS_DIRECT.sum()
    SummerDeath.insert(SummerCount,y)
    z = x.INJURIES_DIRECT.sum()
    SummerInjury.insert(SummerCount,z)
    c = x.Total_Damages.sum()
    SummerCost.insert(SummerCount,c)
    
    SummerReportDays = 1
    if len(x.index) == 0:
        SummerReportDaysList.insert(SummerCount, 0)
    else: 
        for i in range(1, len(x.index)):
            if ((x.Z_Day[i] == x.Z_Day[i-1]) and ((x.Z_Time[i] < 1200) and (x.Z_Time[i-1] < 1200))):
                continue
            elif ((x.Z_Day[i] == x.Z_Day[i-1]) and ((x.Z_Time[i] >= 1200) and (x.Z_Time[i-1] >= 1200))):
                continue
            elif (x.Z_Day[i] == (x.Z_Day[i-1] + pd.DateOffset(days=1))) and ((x.Z_Time[i] < 1200) and (x.Z_Time[i-1] >= 1200)):
                continue
            else:
                SummerReportDays = SummerReportDays + 1
        SummerReportDaysList.insert(SummerCount, SummerReportDays)
        
    Date5 = Date5 + pd.DateOffset(years=1)
    Date6 = Date6 + pd.DateOffset(years=1)
    SummerCount = SummerCount + 1
    
while FallCount <= 14:
    x = df.loc[Date7:Date8]
    FallReports.insert(FallCount,len(x.index))
    y = x.DEATHS_DIRECT.sum()
    FallDeath.insert(FallCount,y)
    z = x.INJURIES_DIRECT.sum()
    FallInjury.insert(FallCount,z)
    c = x.Total_Damages.sum()
    FallCost.insert(FallCount,c)  
    
    FallReportDays = 1
    if len(x.index) == 0:
        FallReportDaysList.insert(FallCount, 0)
    else: 
        for i in range(1, len(x.index)):
            if ((x.Z_Day[i] == x.Z_Day[i-1]) and ((x.Z_Time[i] < 1200) and (x.Z_Time[i-1] < 1200))):
                continue
            elif ((x.Z_Day[i] == x.Z_Day[i-1]) and ((x.Z_Time[i] >= 1200) and (x.Z_Time[i-1] >= 1200))):
                continue
            elif (x.Z_Day[i] == (x.Z_Day[i-1] + pd.DateOffset(days=1))) and ((x.Z_Time[i] < 1200) and (x.Z_Time[i-1] >= 1200)):
                continue
            else:
                FallReportDays = FallReportDays + 1
        FallReportDaysList.insert(FallCount, FallReportDays)
        
    Date7 = Date7 + pd.DateOffset(years=1)
    Date8 = Date8 + pd.DateOffset(years=1)
    FallCount = FallCount + 1

    
#Create dataframes for each list
df1 = pd.DataFrame({'Winter Reports': WinterReports})
df2 = pd.DataFrame({'Spring Reports': SpringReports})
df3 = pd.DataFrame({'Summer Reports': SummerReports})
df4 = pd.DataFrame({'Fall Reports': FallReports})
df5 = pd.DataFrame({'Winter Fatalities': WinterDeath})
df6 = pd.DataFrame({'Spring Fatalities': SpringDeath})
df7 = pd.DataFrame({'Summer Fatalities': SummerDeath})
df8 = pd.DataFrame({'Fall Fatalities': FallDeath})
df9 = pd.DataFrame({'Winter Injuries': WinterInjury})
df10 = pd.DataFrame({'Spring Injuries': SpringInjury})
df11 = pd.DataFrame({'Summer Injuries': SummerInjury})
df12 = pd.DataFrame({'Fall Injuries': FallInjury})
df13 = pd.DataFrame({'Winter Damages': WinterCost})
df14 = pd.DataFrame({'Spring Damages': SpringCost})
df15 = pd.DataFrame({'Summer Damages': SummerCost})
df16 = pd.DataFrame({'Fall Damages': FallCost})
df17 = pd.DataFrame({'Winter Report Days': WinterReportDaysList})
df18 = pd.DataFrame({'Spring Report Days': SpringReportDaysList})
df19 = pd.DataFrame({'Summer Report Days': SummerReportDaysList})
df20 = pd.DataFrame({'Fall Report Days': FallReportDaysList})

#concatenate the dataframes together
dftot = pd.concat([
    df1, df2, df3, df4, df5, df6, df7, df8, df9, df10,
    df11, df12, df13, df14, df15, df16, df17, df18, df19, df20
], axis=1)

years = []
for i in range(1996,2011):
    years.insert(i,i)
dftot = dftot.set_index([years])

#export to csv
dftot.to_csv('Flooding Impacts.csv')