In [17]:
import pandas as pd
import dateutil
%matplotlib inline
In [53]:
df = pd.read_csv("case_shiller_sf.csv", header=None, names=['month_year', 'csi'])
In [54]:
df['month'] = df.month_year.map(lambda x: x[:3])
df['year'] = df.month_year.map(lambda x: int(x[4:]))
df.head()
Out[54]:
month_year
csi
month
year
0
Jan-1980
31.00
Jan
1980
1
Feb-1980
30.99
Feb
1980
2
Mar-1980
31.24
Mar
1980
3
Apr-1980
31.48
Apr
1980
4
May-1980
31.81
May
1980
In [55]:
frame = df[df.month == 'Jan'][['year', 'csi']].set_index('year')
In [56]:
frame
Out[56]:
csi
year
1980
31.00
1981
34.68
1982
35.33
1983
35.24
1984
36.83
1985
39.07
1986
42.25
1987
46.61
1988
52.24
1989
62.66
1990
72.87
1991
71.17
1992
69.67
1993
67.77
1994
65.98
1995
67.73
1996
66.15
1997
68.80
1998
76.19
1999
85.03
2000
100.00
2001
131.16
2002
125.13
2003
141.90
2004
155.93
2005
189.35
2006
214.78
2007
211.78
2008
183.81
2009
124.40
2010
135.63
2011
133.37
2012
125.47
2013
147.45
2014
181.52
2015
195.76
In [57]:
frame.csi.plot()
Out[57]:
<matplotlib.axes._subplots.AxesSubplot at 0x10dee05f8>
In [58]:
frame['years'] = frame.index - 1980
frame.csi /= frame.csi.iloc[0]
frame['cum_csi'] = frame.csi ** (1 / frame.years)
frame
Out[58]:
csi
years
cum_csi
year
1980
1.000000
0
1.000000
1981
1.118710
1
1.118710
1982
1.139677
2
1.067557
1983
1.136774
3
1.043658
1984
1.188065
4
1.044023
1985
1.260323
5
1.047361
1986
1.362903
6
1.052957
1987
1.503548
7
1.059992
1988
1.685161
8
1.067407
1989
2.021290
9
1.081331
1990
2.350645
10
1.089228
1991
2.295806
11
1.078481
1992
2.247419
12
1.069811
1993
2.186129
13
1.062011
1994
2.128387
14
1.055437
1995
2.184839
15
1.053484
1996
2.133871
16
1.048511
1997
2.219355
17
1.048012
1998
2.457742
18
1.051227
1999
2.742903
19
1.054542
2000
3.225806
20
1.060308
2001
4.230968
21
1.071101
2002
4.036452
22
1.065480
2003
4.577419
23
1.068372
2004
5.030000
24
1.069626
2005
6.108065
25
1.075069
2006
6.928387
26
1.077288
2007
6.831613
27
1.073763
2008
5.929355
28
1.065632
2009
4.012903
29
1.049081
2010
4.375161
30
1.050428
2011
4.302258
31
1.048194
2012
4.047419
32
1.044658
2013
4.756452
33
1.048392
2014
5.855484
34
1.053356
2015
6.314839
35
1.054065
In [59]:
inf = pd.read_csv("inflation_index.csv", parse_dates=['day'])
inf['year'] = inf.day.dt.year
inf = inf[['year', 'inflation_index']].set_index('year')
inf
Out[59]:
inflation_index
year
1947
22.332
1948
24.045
1949
23.809
1950
24.063
1951
25.973
1952
26.567
1953
26.768
1954
26.865
1955
26.796
1956
27.191
1957
28.113
1958
28.881
1959
29.150
1960
29.585
1961
29.902
1962
30.253
1963
30.633
1964
31.038
1965
31.528
1966
32.471
1967
33.375
1968
34.792
1969
36.683
1970
38.842
1971
40.483
1972
41.808
1973
44.425
1974
49.317
1975
53.825
1976
56.933
...
...
1985
107.600
1986
109.692
1987
113.617
1988
118.275
1989
123.942
1990
130.658
1991
136.167
1992
140.308
1993
144.475
1994
148.225
1995
152.383
1996
156.858
1997
160.525
1998
163.008
1999
166.583
2000
172.192
2001
177.042
2002
179.867
2003
184.000
2004
188.908
2005
195.267
2006
201.558
2007
207.344
2008
215.254
2009
214.565
2010
218.076
2011
224.930
2012
229.600
2013
232.962
2014
236.712
68 rows × 1 columns
In [60]:
inf = inf.loc[1980:2014].copy()
inf.inflation_index /= inf.inflation_index.loc[1980]
inf['years'] = inf.index - 1980
inf['cum_inf_rate'] = inf.inflation_index ** (1 / inf.years)
inf
Out[60]:
inflation_index
years
cum_inf_rate
year
1980
1.000000
0
1.000000
1981
1.103784
1
1.103784
1982
1.171759
2
1.082478
1983
1.208781
3
1.065244
1984
1.261583
4
1.059812
1985
1.306095
5
1.054860
1986
1.331488
6
1.048873
1987
1.379132
7
1.046993
1988
1.435672
8
1.046241
1989
1.504461
9
1.046427
1990
1.585983
10
1.047200
1991
1.652853
11
1.046742
1992
1.703118
12
1.045371
1993
1.753699
13
1.044157
1994
1.799218
14
1.042846
1995
1.849690
15
1.041853
1996
1.904009
16
1.041069
1997
1.948521
17
1.040019
1998
1.978661
18
1.038640
1999
2.022056
19
1.037754
2000
2.090140
20
1.037549
2001
2.149011
21
1.037101
2002
2.183302
22
1.036130
2003
2.233470
23
1.035555
2004
2.293046
24
1.035183
2005
2.370234
25
1.035122
2006
2.446597
26
1.035010
2007
2.516830
27
1.034776
2008
2.612845
28
1.034896
2009
2.604482
29
1.033559
2010
2.647100
30
1.032981
2011
2.730296
31
1.032931
2012
2.786983
32
1.032548
2013
2.827792
33
1.032001
2014
2.873311
34
1.031530
In [62]:
frame['cum_inf_rate'] = inf.cum_inf_rate
frame['inf_index'] = inf.inflation_index
frame
Out[62]:
csi
years
cum_csi
cum_inf_rate
inf_index
year
1980
1.000000
0
1.000000
1.000000
1.000000
1981
1.118710
1
1.118710
1.103784
1.103784
1982
1.139677
2
1.067557
1.082478
1.171759
1983
1.136774
3
1.043658
1.065244
1.208781
1984
1.188065
4
1.044023
1.059812
1.261583
1985
1.260323
5
1.047361
1.054860
1.306095
1986
1.362903
6
1.052957
1.048873
1.331488
1987
1.503548
7
1.059992
1.046993
1.379132
1988
1.685161
8
1.067407
1.046241
1.435672
1989
2.021290
9
1.081331
1.046427
1.504461
1990
2.350645
10
1.089228
1.047200
1.585983
1991
2.295806
11
1.078481
1.046742
1.652853
1992
2.247419
12
1.069811
1.045371
1.703118
1993
2.186129
13
1.062011
1.044157
1.753699
1994
2.128387
14
1.055437
1.042846
1.799218
1995
2.184839
15
1.053484
1.041853
1.849690
1996
2.133871
16
1.048511
1.041069
1.904009
1997
2.219355
17
1.048012
1.040019
1.948521
1998
2.457742
18
1.051227
1.038640
1.978661
1999
2.742903
19
1.054542
1.037754
2.022056
2000
3.225806
20
1.060308
1.037549
2.090140
2001
4.230968
21
1.071101
1.037101
2.149011
2002
4.036452
22
1.065480
1.036130
2.183302
2003
4.577419
23
1.068372
1.035555
2.233470
2004
5.030000
24
1.069626
1.035183
2.293046
2005
6.108065
25
1.075069
1.035122
2.370234
2006
6.928387
26
1.077288
1.035010
2.446597
2007
6.831613
27
1.073763
1.034776
2.516830
2008
5.929355
28
1.065632
1.034896
2.612845
2009
4.012903
29
1.049081
1.033559
2.604482
2010
4.375161
30
1.050428
1.032981
2.647100
2011
4.302258
31
1.048194
1.032931
2.730296
2012
4.047419
32
1.044658
1.032548
2.786983
2013
4.756452
33
1.048392
1.032001
2.827792
2014
5.855484
34
1.053356
1.031530
2.873311
2015
6.314839
35
1.054065
NaN
NaN
In [64]:
frame['cum_real_return'] = frame.cum_csi - frame.cum_inf_rate
frame['yoy_return'] = frame.csi / frame.csi.shift(1)
frame['yoy_inflation'] = frame.inf_index / frame.inf_index.shift(1)
frame['yoy_real_return'] = frame.yoy_return - frame.yoy_inflation
frame
Out[64]:
csi
years
cum_csi
cum_inf_rate
inf_index
cum_real_return
yoy_return
yoy_inflation
yoy_real_return
year
1980
1.000000
0
1.000000
1.000000
1.000000
0.000000
NaN
NaN
NaN
1981
1.118710
1
1.118710
1.103784
1.103784
0.014926
1.118710
1.103784
0.014926
1982
1.139677
2
1.067557
1.082478
1.171759
-0.014921
1.018743
1.061584
-0.042841
1983
1.136774
3
1.043658
1.065244
1.208781
-0.021587
0.997453
1.031595
-0.034143
1984
1.188065
4
1.044023
1.059812
1.261583
-0.015789
1.045119
1.043682
0.001437
1985
1.260323
5
1.047361
1.054860
1.306095
-0.007499
1.060820
1.035282
0.025538
1986
1.362903
6
1.052957
1.048873
1.331488
0.004085
1.081392
1.019442
0.061950
1987
1.503548
7
1.059992
1.046993
1.379132
0.012999
1.103195
1.035782
0.067413
1988
1.685161
8
1.067407
1.046241
1.435672
0.021166
1.120790
1.040997
0.079792
1989
2.021290
9
1.081331
1.046427
1.504461
0.034904
1.199464
1.047914
0.151550
1990
2.350645
10
1.089228
1.047200
1.585983
0.042027
1.162943
1.054187
0.108756
1991
2.295806
11
1.078481
1.046742
1.652853
0.031739
0.976671
1.042164
-0.065493
1992
2.247419
12
1.069811
1.045371
1.703118
0.024440
0.978924
1.030411
-0.051487
1993
2.186129
13
1.062011
1.044157
1.753699
0.017854
0.972729
1.029699
-0.056970
1994
2.128387
14
1.055437
1.042846
1.799218
0.012590
0.973587
1.025956
-0.052369
1995
2.184839
15
1.053484
1.041853
1.849690
0.011631
1.026523
1.028052
-0.001529
1996
2.133871
16
1.048511
1.041069
1.904009
0.007443
0.976672
1.029367
-0.052695
1997
2.219355
17
1.048012
1.040019
1.948521
0.007993
1.040060
1.023378
0.016683
1998
2.457742
18
1.051227
1.038640
1.978661
0.012587
1.107413
1.015468
0.091945
1999
2.742903
19
1.054542
1.037754
2.022056
0.016788
1.116026
1.021931
0.094094
2000
3.225806
20
1.060308
1.037549
2.090140
0.022758
1.176056
1.033671
0.142385
2001
4.230968
21
1.071101
1.037101
2.149011
0.034000
1.311600
1.028166
0.283434
2002
4.036452
22
1.065480
1.036130
2.183302
0.029350
0.954026
1.015957
-0.061931
2003
4.577419
23
1.068372
1.035555
2.233470
0.032818
1.134021
1.022978
0.111043
2004
5.030000
24
1.069626
1.035183
2.293046
0.034443
1.098872
1.026674
0.072199
2005
6.108065
25
1.075069
1.035122
2.370234
0.039946
1.214327
1.033662
0.180665
2006
6.928387
26
1.077288
1.035010
2.446597
0.042278
1.134302
1.032217
0.102084
2007
6.831613
27
1.073763
1.034776
2.516830
0.038986
0.986032
1.028706
-0.042674
2008
5.929355
28
1.065632
1.034896
2.612845
0.030736
0.867929
1.038149
-0.170220
2009
4.012903
29
1.049081
1.033559
2.604482
0.015522
0.676786
0.996799
-0.320013
2010
4.375161
30
1.050428
1.032981
2.647100
0.017447
1.090273
1.016363
0.073910
2011
4.302258
31
1.048194
1.032931
2.730296
0.015263
0.983337
1.031429
-0.048092
2012
4.047419
32
1.044658
1.032548
2.786983
0.012110
0.940766
1.020762
-0.079996
2013
4.756452
33
1.048392
1.032001
2.827792
0.016391
1.175181
1.014643
0.160538
2014
5.855484
34
1.053356
1.031530
2.873311
0.021827
1.231061
1.016097
0.214964
2015
6.314839
35
1.054065
NaN
NaN
NaN
1.078449
NaN
NaN
In [65]:
frame.yoy_real_return.plot()
Out[65]:
<matplotlib.axes._subplots.AxesSubplot at 0x10e0d1ba8>
In [66]:
frame.yoy_real_return.describe()
Out[66]:
count 34.000000
mean 0.028672
std 0.115886
min -0.320013
25% -0.050639
50% 0.021110
75% 0.100087
max 0.283434
Name: yoy_real_return, dtype: float64
In [67]:
frame.cum_real_return.plot()
Out[67]:
<matplotlib.axes._subplots.AxesSubplot at 0x10e147e80>
In [68]:
frame.csi.plot()
Out[68]:
<matplotlib.axes._subplots.AxesSubplot at 0x10e21ca58>
In [79]:
frame['5yr_return'] = (frame.csi / frame.csi.shift(5) - 1)
frame['5yr_inf'] = (frame.inf_index / frame.inf_index.shift(5) - 1)
frame['5yr_real_return'] = frame['5yr_return'] - frame['5yr_inf']
frame
Out[79]:
csi
years
cum_csi
cum_inf_rate
inf_index
cum_real_return
yoy_return
yoy_inflation
yoy_real_return
5yr_return
5yr_inf
5yr_real_return
year
1980
1.000000
0
1.000000
1.000000
1.000000
0.000000
NaN
NaN
NaN
NaN
NaN
NaN
1981
1.118710
1
1.118710
1.103784
1.103784
0.014926
1.118710
1.103784
0.014926
NaN
NaN
NaN
1982
1.139677
2
1.067557
1.082478
1.171759
-0.014921
1.018743
1.061584
-0.042841
NaN
NaN
NaN
1983
1.136774
3
1.043658
1.065244
1.208781
-0.021587
0.997453
1.031595
-0.034143
NaN
NaN
NaN
1984
1.188065
4
1.044023
1.059812
1.261583
-0.015789
1.045119
1.043682
0.001437
NaN
NaN
NaN
1985
1.260323
5
1.047361
1.054860
1.306095
-0.007499
1.060820
1.035282
0.025538
0.260323
0.306095
-0.045772
1986
1.362903
6
1.052957
1.048873
1.331488
0.004085
1.081392
1.019442
0.061950
0.218281
0.206295
0.011987
1987
1.503548
7
1.059992
1.046993
1.379132
0.012999
1.103195
1.035782
0.067413
0.319275
0.176976
0.142300
1988
1.685161
8
1.067407
1.046241
1.435672
0.021166
1.120790
1.040997
0.079792
0.482406
0.187703
0.294704
1989
2.021290
9
1.081331
1.046427
1.504461
0.034904
1.199464
1.047914
0.151550
0.701330
0.192518
0.508812
1990
2.350645
10
1.089228
1.047200
1.585983
0.042027
1.162943
1.054187
0.108756
0.865114
0.214294
0.650820
1991
2.295806
11
1.078481
1.046742
1.652853
0.031739
0.976671
1.042164
-0.065493
0.684497
0.241358
0.443139
1992
2.247419
12
1.069811
1.045371
1.703118
0.024440
0.978924
1.030411
-0.051487
0.494744
0.234921
0.259823
1993
2.186129
13
1.062011
1.044157
1.753699
0.017854
0.972729
1.029699
-0.056970
0.297282
0.221518
0.075764
1994
2.128387
14
1.055437
1.042846
1.799218
0.012590
0.973587
1.025956
-0.052369
0.052984
0.195922
-0.142938
1995
2.184839
15
1.053484
1.041853
1.849690
0.011631
1.026523
1.028052
-0.001529
-0.070537
0.166274
-0.236810
1996
2.133871
16
1.048511
1.041069
1.904009
0.007443
0.976672
1.029367
-0.052695
-0.070535
0.151953
-0.222488
1997
2.219355
17
1.048012
1.040019
1.948521
0.007993
1.040060
1.023378
0.016683
-0.012487
0.144090
-0.156578
1998
2.457742
18
1.051227
1.038640
1.978661
0.012587
1.107413
1.015468
0.091945
0.124244
0.128278
-0.004034
1999
2.742903
19
1.054542
1.037754
2.022056
0.016788
1.116026
1.021931
0.094094
0.288724
0.123852
0.164872
2000
3.225806
20
1.060308
1.037549
2.090140
0.022758
1.176056
1.033671
0.142385
0.476451
0.129995
0.346456
2001
4.230968
21
1.071101
1.037101
2.149011
0.034000
1.311600
1.028166
0.283434
0.982766
0.128677
0.854090
2002
4.036452
22
1.065480
1.036130
2.183302
0.029350
0.954026
1.015957
-0.061931
0.818750
0.120492
0.698258
2003
4.577419
23
1.068372
1.035555
2.233470
0.032818
1.134021
1.022978
0.111043
0.862449
0.128779
0.733670
2004
5.030000
24
1.069626
1.035183
2.293046
0.034443
1.098872
1.026674
0.072199
0.833823
0.134017
0.699806
2005
6.108065
25
1.075069
1.035122
2.370234
0.039946
1.214327
1.033662
0.180665
0.893500
0.134007
0.759493
2006
6.928387
26
1.077288
1.035010
2.446597
0.042278
1.134302
1.032217
0.102084
0.637542
0.138476
0.499066
2007
6.831613
27
1.073763
1.034776
2.516830
0.038986
0.986032
1.028706
-0.042674
0.692480
0.152763
0.539717
2008
5.929355
28
1.065632
1.034896
2.612845
0.030736
0.867929
1.038149
-0.170220
0.295349
0.169859
0.125490
2009
4.012903
29
1.049081
1.033559
2.604482
0.015522
0.676786
0.996799
-0.320013
-0.202206
0.135817
-0.338024
2010
4.375161
30
1.050428
1.032981
2.647100
0.017447
1.090273
1.016363
0.073910
-0.283707
0.116809
-0.400517
2011
4.302258
31
1.048194
1.032931
2.730296
0.015263
0.983337
1.031429
-0.048092
-0.379039
0.115957
-0.494996
2012
4.047419
32
1.044658
1.032548
2.786983
0.012110
0.940766
1.020762
-0.079996
-0.407546
0.107339
-0.514884
2013
4.756452
33
1.048392
1.032001
2.827792
0.016391
1.175181
1.014643
0.160538
-0.197813
0.082266
-0.280079
2014
5.855484
34
1.053356
1.031530
2.873311
0.021827
1.231061
1.016097
0.214964
0.459164
0.103218
0.355946
2015
6.314839
35
1.054065
NaN
NaN
NaN
1.078449
NaN
NaN
0.443338
NaN
NaN
In [74]:
frame['5yr_real_return'].plot()
Out[74]:
<matplotlib.axes._subplots.AxesSubplot at 0x10cfeffd0>
In [77]:
frame['5yr_real_return'].sort(inplace=False)
Out[77]:
year
2012 -0.514884
2011 -0.494996
2010 -0.400517
2009 -0.338024
2013 -0.280079
1995 -0.236810
1996 -0.222488
1997 -0.156578
1994 -0.142938
1985 -0.045772
1998 -0.004034
1986 0.011987
1993 0.075764
2008 0.125490
1987 0.142300
1999 0.164872
1992 0.259823
1988 0.294704
2000 0.346456
2014 0.355946
1991 0.443139
2006 0.499066
1989 0.508812
2007 0.539717
1990 0.650820
2002 0.698258
2004 0.699806
2003 0.733670
2005 0.759493
2001 0.854090
1980 NaN
1981 NaN
1982 NaN
1983 NaN
1984 NaN
2015 NaN
Name: 5yr_real_return, dtype: float64
In [80]:
frame['5yr_return'].plot()
Out[80]:
<matplotlib.axes._subplots.AxesSubplot at 0x10e4fee10>
In [81]:
frame['5yr_return'].sort(inplace=False)
Out[81]:
year
2012 -0.407546
2011 -0.379039
2010 -0.283707
2009 -0.202206
2013 -0.197813
1995 -0.070537
1996 -0.070535
1997 -0.012487
1994 0.052984
1998 0.124244
1986 0.218281
1985 0.260323
1999 0.288724
2008 0.295349
1993 0.297282
1987 0.319275
2015 0.443338
2014 0.459164
2000 0.476451
1988 0.482406
1992 0.494744
2006 0.637542
1991 0.684497
2007 0.692480
1989 0.701330
2002 0.818750
2004 0.833823
2003 0.862449
1990 0.865114
2005 0.893500
2001 0.982766
1980 NaN
1981 NaN
1982 NaN
1983 NaN
1984 NaN
Name: 5yr_return, dtype: float64
In [ ]:
Content source: bembom/bay_area_home_prices
Similar notebooks: