combine-csv



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 [ ]: