In [1]:
%matplotlib inline
import matplotlib.pyplot as plt
import numpy as np
import re
import pandas as pd

In [2]:
df = pd.read_csv('techrpt083.csv', skiprows=15)

In [3]:
df.shape


Out[3]:
(35046, 20)

In [4]:
def df_station(df, station):
    df2 = df[df['Site'].str.contains(station)]
    df3 = df2[df2['Scenario'].str.contains("1.0 - HIGH")]
    df4 = pd.melt(df3, id_vars=df3.columns.values[0:6], 
                  var_name="Date", value_name="Value")
    df5 = df4.copy(deep=True)
    for scenario in df2['Scenario'].values:
        df3 = df2[df2['Scenario'].str.contains(scenario)]
        var = scenario.replace(' ','')
        df4 = pd.melt(df3, id_vars=df3.columns.values[0:6], 
                      var_name="Date", value_name=var)
        df5[var] = df4[var]
    return df5

In [5]:
df3 = df[~df['Site'].str.contains('GMSL')]

In [6]:
dfs = [df_station(df3,station) for station in df3['Site'].unique()]

In [7]:
type(dfs)


Out[7]:
list

In [8]:
df6 = pd.concat(dfs)

In [9]:
del df6['Value']
del df6['Scenario']

In [14]:
del df6['PSMSL ID']

In [10]:
df6['Date'] = [int(re.findall(r'\d+', v)[0]) for v in df6['Date'].values]
df6.rename(columns = {'Site':'id'}, inplace = True)

In [15]:
df6.to_csv('all_stations.csv', index=False)

In [12]:
df6


Out[12]:
id PSMSL ID Latitude Longitude Background RSL rate (mm/yr) Date 0.3-MED 0.3-LOW 0.3-HIGH 0.5-MED ... 1.0-HIGH 1.5-MED 1.5-LOW 1.5-HIGH 2.0-MED 2.0-LOW 2.0-HIGH 2.5-MED 2.5-LOW 2.5-HIGH
0 SAN FRANCISCO 10 37.81 -122.47 0.02 2000 0.0 0.0 0.0 0.0 ... 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
1 SAN FRANCISCO 10 37.81 -122.47 0.02 2010 3.0 1.0 4.0 3.0 ... 6.0 7.0 4.0 8.0 8.0 7.0 9.0 8.0 3.0 9.0
2 SAN FRANCISCO 10 37.81 -122.47 0.02 2020 6.0 3.0 8.0 8.0 ... 12.0 13.0 9.0 15.0 16.0 14.0 18.0 18.0 10.0 20.0
3 SAN FRANCISCO 10 37.81 -122.47 0.02 2030 10.0 6.0 13.0 12.0 ... 19.0 22.0 17.0 25.0 28.0 25.0 32.0 32.0 21.0 36.0
4 SAN FRANCISCO 10 37.81 -122.47 0.02 2040 13.0 8.0 18.0 17.0 ... 28.0 34.0 27.0 38.0 46.0 41.0 49.0 54.0 37.0 57.0
5 SAN FRANCISCO 10 37.81 -122.47 0.02 2050 17.0 10.0 23.0 22.0 ... 40.0 51.0 41.0 57.0 70.0 63.0 73.0 83.0 60.0 88.0
6 SAN FRANCISCO 10 37.81 -122.47 0.02 2060 21.0 13.0 28.0 28.0 ... 52.0 69.0 58.0 77.0 97.0 88.0 101.0 118.0 90.0 125.0
7 SAN FRANCISCO 10 37.81 -122.47 0.02 2070 25.0 15.0 33.0 33.0 ... 66.0 90.0 76.0 100.0 127.0 117.0 133.0 158.0 124.0 166.0
8 SAN FRANCISCO 10 37.81 -122.47 0.02 2080 28.0 17.0 38.0 38.0 ... 81.0 114.0 98.0 127.0 163.0 151.0 170.0 202.0 163.0 215.0
9 SAN FRANCISCO 10 37.81 -122.47 0.02 2090 31.0 19.0 43.0 43.0 ... 97.0 141.0 122.0 155.0 204.0 188.0 211.0 252.0 207.0 268.0
10 SAN FRANCISCO 10 37.81 -122.47 0.02 2100 34.0 19.0 49.0 48.0 ... 117.0 174.0 147.0 191.0 253.0 232.0 261.0 311.0 257.0 334.0
11 SAN FRANCISCO 10 37.81 -122.47 0.02 2120 40.0 23.0 55.0 56.0 ... 141.0 210.0 191.0 247.0 324.0 299.0 361.0 433.0 368.0 471.0
12 SAN FRANCISCO 10 37.81 -122.47 0.02 2150 47.0 25.0 65.0 70.0 ... 202.0 318.0 286.0 370.0 500.0 459.0 551.0 669.0 573.0 725.0
13 SAN FRANCISCO 10 37.81 -122.47 0.02 2200 53.0 17.0 83.0 92.0 ... 325.0 541.0 476.0 630.0 872.0 804.0 933.0 1130.0 1014.0 1207.0
0 NEW YORK 12 40.70 -74.01 1.38 2000 0.0 0.0 0.0 0.0 ... 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
1 NEW YORK 12 40.70 -74.01 1.38 2010 5.0 2.0 7.0 6.0 ... 11.0 12.0 7.0 15.0 14.0 13.0 17.0 14.0 6.0 15.0
2 NEW YORK 12 40.70 -74.01 1.38 2020 11.0 6.0 14.0 13.0 ... 23.0 25.0 14.0 31.0 31.0 25.0 35.0 29.0 14.0 33.0
3 NEW YORK 12 40.70 -74.01 1.38 2030 15.0 10.0 21.0 19.0 ... 34.0 39.0 24.0 45.0 48.0 43.0 53.0 50.0 28.0 55.0
4 NEW YORK 12 40.70 -74.01 1.38 2040 20.0 13.0 28.0 25.0 ... 47.0 53.0 36.0 61.0 67.0 63.0 75.0 76.0 45.0 82.0
5 NEW YORK 12 40.70 -74.01 1.38 2050 25.0 15.0 36.0 31.0 ... 60.0 71.0 51.0 80.0 92.0 85.0 100.0 105.0 68.0 114.0
6 NEW YORK 12 40.70 -74.01 1.38 2060 31.0 18.0 42.0 39.0 ... 76.0 92.0 68.0 104.0 124.0 107.0 130.0 144.0 97.0 150.0
7 NEW YORK 12 40.70 -74.01 1.38 2070 36.0 19.0 49.0 45.0 ... 93.0 114.0 87.0 130.0 154.0 134.0 166.0 185.0 130.0 194.0
8 NEW YORK 12 40.70 -74.01 1.38 2080 39.0 21.0 54.0 50.0 ... 111.0 139.0 108.0 159.0 189.0 168.0 206.0 230.0 170.0 241.0
9 NEW YORK 12 40.70 -74.01 1.38 2090 44.0 21.0 60.0 56.0 ... 130.0 167.0 130.0 191.0 234.0 205.0 250.0 286.0 214.0 296.0
10 NEW YORK 12 40.70 -74.01 1.38 2100 46.0 22.0 65.0 61.0 ... 150.0 197.0 154.0 225.0 279.0 248.0 298.0 342.0 262.0 357.0
11 NEW YORK 12 40.70 -74.01 1.38 2120 51.0 27.0 75.0 70.0 ... 176.0 247.0 195.0 297.0 368.0 334.0 405.0 459.0 373.0 530.0
12 NEW YORK 12 40.70 -74.01 1.38 2150 58.0 28.0 88.0 85.0 ... 238.0 353.0 281.0 420.0 561.0 493.0 586.0 695.0 574.0 772.0
13 NEW YORK 12 40.70 -74.01 1.38 2200 69.0 26.0 110.0 110.0 ... 359.0 567.0 442.0 673.0 926.0 826.0 958.0 1139.0 1011.0 1237.0
0 HALIFAX 96 44.67 -63.58 1.46 2000 0.0 0.0 0.0 0.0 ... 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
1 HALIFAX 96 44.67 -63.58 1.46 2010 5.0 2.0 7.0 6.0 ... 10.0 11.0 6.0 13.0 14.0 13.0 15.0 15.0 6.0 16.0
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
12 grid_-28.5_249.5 1011852495 -28.50 -110.50 0.12 2150 42.0 10.0 71.0 67.0 ... 214.0 317.0 277.0 359.0 420.0 383.0 473.0 544.0 486.0 636.0
13 grid_-28.5_249.5 1011852495 -28.50 -110.50 0.12 2200 47.0 -6.0 92.0 87.0 ... 347.0 534.0 462.0 614.0 742.0 676.0 828.0 1065.0 982.0 1177.0
0 grid_-28.5_250.5 1011852505 -28.50 -109.50 0.06 2000 0.0 0.0 0.0 0.0 ... 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
1 grid_-28.5_250.5 1011852505 -28.50 -109.50 0.06 2010 1.0 -1.0 4.0 3.0 ... 7.0 7.0 3.0 9.0 8.0 7.0 11.0 8.0 3.0 11.0
2 grid_-28.5_250.5 1011852505 -28.50 -109.50 0.06 2020 5.0 1.0 8.0 7.0 ... 13.0 13.0 7.0 16.0 16.0 12.0 18.0 15.0 8.0 19.0
3 grid_-28.5_250.5 1011852505 -28.50 -109.50 0.06 2030 8.0 3.0 13.0 11.0 ... 21.0 22.0 15.0 26.0 27.0 23.0 30.0 29.0 20.0 33.0
4 grid_-28.5_250.5 1011852505 -28.50 -109.50 0.06 2040 11.0 4.0 18.0 15.0 ... 30.0 33.0 25.0 39.0 42.0 38.0 47.0 47.0 37.0 52.0
5 grid_-28.5_250.5 1011852505 -28.50 -109.50 0.06 2050 15.0 6.0 23.0 20.0 ... 40.0 48.0 39.0 54.0 61.0 56.0 65.0 70.0 61.0 75.0
6 grid_-28.5_250.5 1011852505 -28.50 -109.50 0.06 2060 18.0 8.0 29.0 25.0 ... 53.0 65.0 56.0 72.0 84.0 75.0 90.0 97.0 90.0 104.0
7 grid_-28.5_250.5 1011852505 -28.50 -109.50 0.06 2070 21.0 9.0 33.0 30.0 ... 66.0 84.0 75.0 93.0 109.0 99.0 118.0 129.0 120.0 140.0
8 grid_-28.5_250.5 1011852505 -28.50 -109.50 0.06 2080 25.0 10.0 39.0 36.0 ... 81.0 107.0 97.0 117.0 140.0 126.0 151.0 168.0 154.0 182.0
9 grid_-28.5_250.5 1011852505 -28.50 -109.50 0.06 2090 28.0 11.0 45.0 40.0 ... 98.0 132.0 120.0 144.0 172.0 155.0 190.0 209.0 192.0 233.0
10 grid_-28.5_250.5 1011852505 -28.50 -109.50 0.06 2100 30.0 12.0 50.0 44.0 ... 115.0 160.0 148.0 174.0 210.0 192.0 232.0 255.0 233.0 290.0
11 grid_-28.5_250.5 1011852505 -28.50 -109.50 0.06 2120 37.0 13.0 58.0 53.0 ... 148.0 214.0 189.0 238.0 279.0 254.0 316.0 359.0 315.0 404.0
12 grid_-28.5_250.5 1011852505 -28.50 -109.50 0.06 2150 42.0 10.0 70.0 67.0 ... 213.0 317.0 277.0 358.0 419.0 382.0 472.0 543.0 484.0 635.0
13 grid_-28.5_250.5 1011852505 -28.50 -109.50 0.06 2200 47.0 -6.0 91.0 87.0 ... 346.0 534.0 460.0 613.0 741.0 675.0 826.0 1065.0 981.0 1175.0
0 grid_-28.5_251.5 1011852515 -28.50 -108.50 0.11 2000 0.0 0.0 0.0 0.0 ... 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
1 grid_-28.5_251.5 1011852515 -28.50 -108.50 0.11 2010 2.0 0.0 4.0 3.0 ... 7.0 7.0 3.0 9.0 8.0 7.0 10.0 8.0 3.0 11.0
2 grid_-28.5_251.5 1011852515 -28.50 -108.50 0.11 2020 5.0 1.0 8.0 7.0 ... 13.0 13.0 7.0 16.0 16.0 12.0 18.0 16.0 8.0 19.0
3 grid_-28.5_251.5 1011852515 -28.50 -108.50 0.11 2030 8.0 3.0 13.0 11.0 ... 21.0 22.0 15.0 26.0 27.0 23.0 30.0 29.0 20.0 33.0
4 grid_-28.5_251.5 1011852515 -28.50 -108.50 0.11 2040 11.0 4.0 18.0 15.0 ... 30.0 33.0 25.0 39.0 42.0 37.0 47.0 46.0 37.0 52.0
5 grid_-28.5_251.5 1011852515 -28.50 -108.50 0.11 2050 15.0 6.0 24.0 21.0 ... 41.0 48.0 39.0 55.0 61.0 56.0 66.0 70.0 61.0 76.0
6 grid_-28.5_251.5 1011852515 -28.50 -108.50 0.11 2060 18.0 8.0 29.0 25.0 ... 54.0 65.0 56.0 73.0 84.0 75.0 91.0 97.0 90.0 105.0
7 grid_-28.5_251.5 1011852515 -28.50 -108.50 0.11 2070 21.0 9.0 34.0 30.0 ... 67.0 84.0 75.0 94.0 109.0 99.0 119.0 129.0 120.0 141.0
8 grid_-28.5_251.5 1011852515 -28.50 -108.50 0.11 2080 25.0 10.0 40.0 36.0 ... 82.0 107.0 97.0 118.0 139.0 126.0 152.0 168.0 154.0 183.0
9 grid_-28.5_251.5 1011852515 -28.50 -108.50 0.11 2090 28.0 11.0 45.0 40.0 ... 98.0 132.0 120.0 144.0 172.0 155.0 190.0 209.0 191.0 233.0
10 grid_-28.5_251.5 1011852515 -28.50 -108.50 0.11 2100 32.0 12.0 50.0 44.0 ... 115.0 160.0 148.0 174.0 210.0 191.0 232.0 256.0 232.0 289.0
11 grid_-28.5_251.5 1011852515 -28.50 -108.50 0.11 2120 37.0 13.0 58.0 53.0 ... 147.0 214.0 188.0 238.0 279.0 252.0 316.0 358.0 313.0 403.0
12 grid_-28.5_251.5 1011852515 -28.50 -108.50 0.11 2150 42.0 11.0 71.0 67.0 ... 214.0 316.0 276.0 359.0 419.0 381.0 472.0 542.0 483.0 636.0
13 grid_-28.5_251.5 1011852515 -28.50 -108.50 0.11 2200 47.0 -6.0 92.0 87.0 ... 347.0 533.0 459.0 614.0 739.0 673.0 826.0 1064.0 980.0 1176.0

27314 rows × 24 columns


In [ ]: