In [20]:
import pandas as pd
import glob
In [21]:
fileList = glob.glob('*INX.csv')
dfList = []
for filename in fileList:
#print(filename)
name = filename.split('.')[3]
df = pd.read_csv(filename,header=False,index_col='date',names=['date',str(name)])
# df['year'] = df['date'].apply(lambda x:int(x.split('-')[0]))
# df['month'] = df['date'].apply(lambda x:int(x.split('-')[1]))
# df = df.loc[:,['year','month',name]]
dfList.append(df)
concatDf=pd.concat(dfList,axis=1)
In [22]:
concatDf.head()
df = concatDf.copy()
In [23]:
df = df.reset_index()
In [24]:
df.head()
Out[24]:
index
000000
010000
011000
011100
011200
011300
011400
011500
011600
...
125300
125400
125500
126000
126200
127000
1270_0
ADMFUL
ADMIN0
ADMMAI
0
1996-01
71.08
70.73
69.96
69.12
70.42
62.14
74.99
73.96
62.32
...
NaN
70.72
NaN
70.08
72.95
65.62
81.04
59.35
67.06
71.43
1
1996-02
71.40
71.00
70.25
69.23
70.43
60.49
75.14
75.46
63.49
...
NaN
70.86
NaN
70.13
72.95
66.23
81.87
59.60
67.26
71.59
2
1996-03
71.66
71.41
70.72
69.28
70.36
59.91
75.43
76.30
64.12
...
NaN
70.92
NaN
70.14
73.02
66.32
81.95
59.69
67.27
71.54
3
1996-04
71.77
71.71
71.03
69.31
70.81
60.46
75.57
76.68
64.51
...
NaN
70.99
NaN
70.15
73.02
66.35
81.95
59.82
67.32
71.55
4
1996-05
71.94
71.97
71.33
69.34
71.42
60.47
75.48
76.92
67.55
...
NaN
70.97
NaN
70.17
73.02
66.53
82.20
59.95
67.39
71.58
5 rows × 153 columns
In [25]:
df['year'] = df['index'].apply(lambda x:int(x.split('-')[0]))
df['month'] = df['index'].apply(lambda x:int(x.split('-')[1]))
#df = df.loc[:,['year','month',name]]
In [26]:
df.head()
Out[26]:
index
000000
010000
011000
011100
011200
011300
011400
011500
011600
...
125500
126000
126200
127000
1270_0
ADMFUL
ADMIN0
ADMMAI
year
month
0
1996-01
71.08
70.73
69.96
69.12
70.42
62.14
74.99
73.96
62.32
...
NaN
70.08
72.95
65.62
81.04
59.35
67.06
71.43
1996
1
1
1996-02
71.40
71.00
70.25
69.23
70.43
60.49
75.14
75.46
63.49
...
NaN
70.13
72.95
66.23
81.87
59.60
67.26
71.59
1996
2
2
1996-03
71.66
71.41
70.72
69.28
70.36
59.91
75.43
76.30
64.12
...
NaN
70.14
73.02
66.32
81.95
59.69
67.27
71.54
1996
3
3
1996-04
71.77
71.71
71.03
69.31
70.81
60.46
75.57
76.68
64.51
...
NaN
70.15
73.02
66.35
81.95
59.82
67.32
71.55
1996
4
4
1996-05
71.94
71.97
71.33
69.34
71.42
60.47
75.48
76.92
67.55
...
NaN
70.17
73.02
66.53
82.20
59.95
67.39
71.58
1996
5
5 rows × 155 columns
In [27]:
cols = list(df)
In [28]:
cols.insert(0, cols.pop(cols.index('month')))
cols.insert(0, cols.pop(cols.index('year')))
cols.pop(cols.index('index'))
Out[28]:
'index'
In [29]:
df = df.ix[:, cols]
df
Out[29]:
year
month
000000
010000
011000
011100
011200
011300
011400
011500
...
125300
125400
125500
126000
126200
127000
1270_0
ADMFUL
ADMIN0
ADMMAI
0
1996
1
71.08
70.73
69.96
69.12
70.42
62.14
74.99
73.96
...
NaN
70.72
NaN
70.08
72.95
65.62
81.04
59.35
67.06
71.43
1
1996
2
71.40
71.00
70.25
69.23
70.43
60.49
75.14
75.46
...
NaN
70.86
NaN
70.13
72.95
66.23
81.87
59.60
67.26
71.59
2
1996
3
71.66
71.41
70.72
69.28
70.36
59.91
75.43
76.30
...
NaN
70.92
NaN
70.14
73.02
66.32
81.95
59.69
67.27
71.54
3
1996
4
71.77
71.71
71.03
69.31
70.81
60.46
75.57
76.68
...
NaN
70.99
NaN
70.15
73.02
66.35
81.95
59.82
67.32
71.55
4
1996
5
71.94
71.97
71.33
69.34
71.42
60.47
75.48
76.92
...
NaN
70.97
NaN
70.17
73.02
66.53
82.20
59.95
67.39
71.58
5
1996
6
71.95
71.83
71.17
69.40
71.71
60.33
75.40
77.00
...
NaN
70.97
NaN
70.18
73.02
66.54
82.28
60.12
67.46
71.59
6
1996
7
71.95
71.70
71.04
69.46
71.94
60.98
75.43
77.03
...
NaN
70.61
NaN
70.23
73.10
66.71
82.45
60.30
67.31
71.22
7
1996
8
71.91
71.11
70.44
69.54
72.18
61.16
75.41
76.91
...
NaN
70.61
NaN
70.28
73.17
67.15
82.94
60.33
67.39
71.34
8
1996
9
72.05
70.97
70.29
69.64
72.30
61.05
75.40
76.92
...
NaN
70.73
NaN
70.49
73.39
67.17
83.02
60.40
67.52
71.50
9
1996
10
72.11
70.89
70.21
69.66
72.17
60.94
75.46
76.98
...
NaN
70.78
NaN
70.52
73.39
67.19
83.02
60.54
67.51
71.41
10
1996
11
72.13
70.96
70.25
69.67
72.15
61.46
75.48
76.92
...
NaN
70.77
NaN
70.54
73.39
67.36
83.27
60.62
67.60
71.50
11
1996
12
72.31
71.12
70.47
69.71
72.20
61.80
75.53
76.35
...
NaN
70.80
NaN
70.55
73.46
67.37
83.27
60.72
67.73
71.65
12
1997
1
72.54
71.93
71.32
69.88
72.00
62.91
75.69
75.10
...
NaN
71.70
NaN
70.42
73.32
68.29
84.42
61.23
68.66
72.83
13
1997
2
72.70
71.63
71.00
69.98
71.83
62.09
75.77
73.86
...
NaN
71.84
NaN
70.48
73.39
68.53
84.67
61.41
68.85
73.03
14
1997
3
72.76
71.37
70.70
70.02
71.76
61.92
75.74
72.72
...
NaN
71.94
NaN
70.44
73.32
68.51
84.67
61.51
68.98
73.17
15
1997
4
72.72
71.60
70.91
70.02
71.81
61.92
75.63
71.99
...
NaN
72.01
NaN
70.46
73.32
68.62
84.75
61.47
69.00
73.23
16
1997
5
72.92
72.07
71.33
70.06
72.68
62.14
75.51
71.40
...
NaN
72.01
NaN
72.17
75.15
68.79
85.00
61.46
69.06
73.34
17
1997
6
72.94
72.12
71.30
70.10
73.00
61.92
75.47
70.85
...
NaN
71.97
NaN
72.19
75.15
68.75
84.92
61.49
69.08
73.34
18
1997
7
73.02
72.05
71.19
70.16
73.14
62.60
75.50
70.47
...
NaN
72.09
NaN
72.20
75.15
68.87
85.08
61.61
69.24
73.53
19
1997
8
73.14
71.81
70.89
70.18
73.38
62.70
75.60
70.50
...
NaN
72.15
NaN
72.31
75.29
68.87
85.08
61.74
69.30
73.55
20
1997
9
73.18
71.97
71.05
70.24
73.55
63.10
75.59
70.45
...
NaN
72.13
NaN
72.31
75.29
68.90
85.17
61.79
69.39
73.66
21
1997
10
73.21
71.98
71.06
70.27
73.36
63.09
75.67
70.38
...
NaN
72.11
NaN
72.34
75.29
68.90
85.17
61.89
69.47
73.73
22
1997
11
73.30
72.32
71.41
70.34
73.51
63.90
75.73
70.31
...
NaN
72.07
NaN
72.34
75.29
68.94
85.17
62.03
69.55
73.77
23
1997
12
73.38
72.47
71.59
70.44
73.66
64.71
75.83
70.28
...
NaN
72.04
NaN
72.34
75.29
68.98
85.25
62.05
69.53
73.72
24
1998
1
73.32
72.83
71.99
70.52
73.49
66.50
75.99
70.38
...
NaN
70.02
NaN
72.81
75.80
69.22
85.58
62.24
69.87
74.16
25
1998
2
73.52
72.69
71.84
70.63
73.33
64.79
75.99
70.27
...
NaN
70.03
NaN
72.86
75.80
69.52
85.99
62.35
70.01
74.32
26
1998
3
73.58
72.69
71.81
70.71
73.12
65.30
75.98
70.12
...
NaN
70.03
NaN
72.90
75.88
69.55
85.99
62.44
70.05
74.32
27
1998
4
73.73
73.06
72.24
70.75
73.13
65.93
76.03
70.14
...
NaN
69.83
NaN
72.66
75.66
69.67
86.16
62.49
70.19
74.53
28
1998
5
73.88
73.40
72.59
70.84
72.97
65.92
75.95
70.23
...
NaN
69.75
NaN
72.68
75.66
69.88
86.40
62.28
70.13
74.56
29
1998
6
73.91
73.49
72.68
70.87
72.90
66.18
75.92
70.25
...
NaN
69.70
NaN
72.70
75.66
69.90
86.40
62.37
70.21
74.63
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
215
2013
12
100.11
99.69
99.75
99.54
100.48
97.61
101.34
99.67
...
95.45
100.05
96.34
97.37
101.36
99.06
121.66
96.89
97.91
98.37
216
2014
1
98.99
100.04
100.11
99.54
100.45
99.24
101.65
99.62
...
97.78
100.00
97.43
98.66
102.70
99.56
122.27
98.08
98.67
98.88
217
2014
2
99.30
99.83
99.87
99.52
100.43
99.35
101.65
99.12
...
97.76
100.79
97.59
98.67
102.71
99.73
122.47
98.38
98.89
99.05
218
2014
3
100.23
99.59
99.62
99.54
100.18
98.06
101.87
98.14
...
97.81
100.89
97.59
98.77
102.82
99.49
122.19
98.37
98.94
99.13
219
2014
4
100.38
99.49
99.53
99.53
100.00
97.67
102.11
97.33
...
98.07
100.76
97.71
98.89
102.94
99.58
122.30
98.45
98.96
99.13
220
2014
5
100.27
99.40
99.41
99.56
100.11
97.76
102.20
97.11
...
98.12
100.72
97.60
98.92
102.97
99.64
122.37
98.44
98.97
99.15
221
2014
6
100.38
99.35
99.35
99.52
100.18
97.57
102.14
96.57
...
98.11
100.84
97.60
98.96
103.02
99.72
122.47
98.19
98.90
99.18
222
2014
7
99.72
99.01
98.94
99.55
100.28
97.91
102.19
96.31
...
97.89
100.50
97.63
99.07
103.13
99.68
122.39
97.97
98.94
99.36
223
2014
8
99.84
98.66
98.56
99.56
100.30
98.60
102.29
96.25
...
97.89
100.24
97.63
99.12
103.18
99.86
122.62
98.16
98.98
99.32
224
2014
9
100.28
98.84
98.75
99.52
100.21
98.75
102.06
95.99
...
97.27
100.24
97.63
99.13
103.19
99.92
122.71
98.61
99.21
99.42
225
2014
10
100.22
99.00
98.93
99.62
100.17
98.64
101.96
96.25
...
97.26
99.75
97.76
98.99
103.05
99.98
122.77
98.83
99.48
99.73
226
2014
11
100.04
99.07
98.99
99.60
100.12
98.83
101.36
96.27
...
97.17
99.68
97.79
98.99
103.04
99.69
122.42
98.83
99.49
99.74
227
2014
12
99.94
99.12
99.04
99.60
100.13
99.30
101.24
96.40
...
98.26
100.18
97.79
99.00
103.05
99.90
122.67
98.86
99.51
99.76
228
2015
1
98.40
99.51
99.45
99.78
100.04
100.30
101.04
97.07
...
99.81
100.50
98.57
99.40
103.48
100.34
123.22
99.86
100.02
100.08
229
2015
2
99.03
99.92
99.89
99.74
99.90
99.43
100.86
97.63
...
99.84
100.38
99.03
99.43
103.50
100.37
123.26
100.01
100.12
100.16
230
2015
3
100.15
99.87
99.83
99.84
99.85
99.33
100.68
98.60
...
99.87
100.19
99.03
99.62
103.70
100.52
123.44
99.98
100.08
100.11
231
2015
4
100.39
100.14
100.14
99.94
99.89
99.37
100.59
99.26
...
100.22
99.91
99.10
99.74
103.82
100.53
123.45
99.92
99.91
99.91
232
2015
5
100.61
100.31
100.31
99.97
99.97
99.10
100.13
99.11
...
100.21
99.88
100.06
100.10
104.20
100.69
123.65
99.99
99.96
99.95
233
2015
6
100.60
100.19
100.19
100.05
99.90
99.31
99.94
99.36
...
100.21
99.80
100.07
100.09
104.19
99.72
122.47
99.88
99.80
99.76
234
2015
7
99.96
99.52
99.46
100.07
100.00
99.47
99.67
99.84
...
100.04
99.46
100.09
100.22
104.33
99.62
122.36
99.82
99.83
99.83
235
2015
8
99.97
99.64
99.61
100.23
100.15
100.35
99.54
100.22
...
100.04
99.72
100.38
100.35
104.46
99.69
122.44
100.17
99.90
99.80
236
2015
9
100.19
99.98
100.00
100.07
100.04
100.71
99.30
101.07
...
99.47
99.80
100.38
100.64
104.77
99.62
122.36
100.09
99.94
99.89
237
2015
10
100.34
100.43
100.51
100.11
100.10
100.53
99.51
102.16
...
99.47
99.89
100.48
100.69
104.82
99.62
122.36
100.05
100.15
100.19
238
2015
11
100.19
100.38
100.45
100.08
100.02
100.50
99.43
102.80
...
100.18
99.95
101.26
100.00
104.10
99.58
122.31
100.07
100.12
100.14
239
2015
12
100.17
100.11
100.15
100.11
100.13
101.60
99.31
102.87
...
100.63
100.52
101.53
99.71
103.80
99.70
122.46
100.16
100.17
100.17
240
2016
1
98.72
100.25
100.26
100.14
100.06
104.09
99.41
102.97
...
102.01
100.80
102.39
100.10
NaN
99.98
NaN
100.92
100.33
100.13
241
2016
2
98.88
100.27
100.27
100.16
99.91
102.40
99.39
102.97
...
102.29
100.09
102.40
100.33
NaN
99.98
NaN
101.13
100.44
100.20
242
2016
3
100.10
100.48
100.50
100.20
99.84
101.89
99.35
102.16
...
102.40
100.02
102.40
100.35
NaN
100.05
NaN
101.09
100.43
100.21
243
2016
4
100.15
100.73
100.77
100.29
99.85
102.47
99.11
102.03
...
102.74
100.99
102.39
99.41
NaN
100.20
NaN
101.05
100.01
99.66
244
2016
5
100.51
100.93
101.00
100.26
99.91
102.39
98.30
102.16
...
102.72
101.01
102.39
99.63
NaN
100.22
NaN
101.08
100.02
99.65
245 rows × 154 columns
In [32]:
df.to_csv('combined.csv',header=True,index=False)
In [33]:
dfnew = pd.read_csv('combined.csv')
dfnew.head()
Out[33]:
year
month
000000
010000
011000
011100
011200
011300
011400
011500
...
125300
125400
125500
126000
126200
127000
1270_0
ADMFUL
ADMIN0
ADMMAI
0
1996
1
71.08
70.73
69.96
69.12
70.42
62.14
74.99
73.96
...
NaN
70.72
NaN
70.08
72.95
65.62
81.04
59.35
67.06
71.43
1
1996
2
71.40
71.00
70.25
69.23
70.43
60.49
75.14
75.46
...
NaN
70.86
NaN
70.13
72.95
66.23
81.87
59.60
67.26
71.59
2
1996
3
71.66
71.41
70.72
69.28
70.36
59.91
75.43
76.30
...
NaN
70.92
NaN
70.14
73.02
66.32
81.95
59.69
67.27
71.54
3
1996
4
71.77
71.71
71.03
69.31
70.81
60.46
75.57
76.68
...
NaN
70.99
NaN
70.15
73.02
66.35
81.95
59.82
67.32
71.55
4
1996
5
71.94
71.97
71.33
69.34
71.42
60.47
75.48
76.92
...
NaN
70.97
NaN
70.17
73.02
66.53
82.20
59.95
67.39
71.58
5 rows × 154 columns
In [ ]:
Content source: BadWizard/Inflation
Similar notebooks: