I'm working on a script to retreive data from Quandl which manipulates the data before saving. I'm stuck on 1 step which is id like to reorder my columns using pandas like df = df[['Code','Date','Open','High','Low','Close','Volume']] or in simpler terms, swap date with Code
I thought this was because the system is auto setting Date as an index, however the traceback says ['Date'] not in index
I have observed that the date label drops out of the dataframe when df.index = df.index.strftime('%Y%m%d'), Print 2 output. If I comment this line out the error code persists.
I tried inserting df.set_index('Code') to see if changing the index would help but did not.
Does anyone know what the issue is here?
Code
myArr = ['CHRIS/CME_AD1']
cDate=int(time.strftime("%Y%m%d"))
# get quandl data, edit dataframe & save to csv
for qCode in myArr:
data = qdl.get(qCode, start_date=StartDate)
df = pd.DataFrame(data)
# df.to_dict()
qID=(str(qCode[qCode.find('/')+1:]))
print(df)
# format data & save
df.insert(loc=0, column='Code', value=qID)
df=df.drop(columns=['Change','Settle','Previous Day Open Interest'])
df.rename(columns={'Last':'Close'}, inplace=True)
df.index = df.index.strftime('%Y%m%d')
print(df)
df = df[['Code','Date','Open','High','Low','Close','Volume']] #reorder columns
df.to_csv(path + qID + '_' + str(cDate) + '.txt', sep=',', index=True, header=1)
print('Quandl Download Complete')
Traceback
Traceback (most recent call last):
File "\\progsql\SQL\Script\Python\EOD_Quandl - Copy.py", line 29, in <module>
df = df[['Code','Date','Open','High','Low','Close','Volume']] #reorder columns
File "C:\Python35\lib\site-packages\pandas\core\frame.py", line 2679, in __getitem__
return self._getitem_array(key)
File "C:\Python35\lib\site-packages\pandas\core\frame.py", line 2723, in _getitem_array
indexer = self.loc._convert_to_indexer(key, axis=1)
File "C:\Python35\lib\site-packages\pandas\core\indexing.py", line 1327, in _convert_to_indexer
.format(mask=objarr[mask]))
KeyError: "['Date'] not in index"
Print 1 - from Quandl
Open ... Previous Day Open Interest
Date ...
2018-05-10 0.7460 ... 159970.0
2018-05-11 0.7534 ... 164459.0
2018-05-14 0.7543 ... 167173.0
2018-05-15 0.7526 ... 169622.0
2018-05-16 0.7473 ... 170863.0
2018-05-17 0.7516 ... 164165.0
2018-05-18 0.7513 ... 164628.0
2018-05-21 0.7529 ... 161673.0
2018-05-22 0.7586 ... 147136.0
2018-05-23 0.7575 ... 147454.0
2018-05-24 0.7568 ... 153308.0
2018-05-25 0.7578 ... 151199.0
[12 rows x 8 columns]
Print 2 - post manipulation
Code Open High Low Close Volume
20180510 CME_AD1 0.7460 0.7540 0.7455 0.7534 125257.0
20180511 CME_AD1 0.7534 0.7567 0.7522 0.7538 93512.0
20180514 CME_AD1 0.7543 0.7565 0.7525 0.7529 72806.0
20180515 CME_AD1 0.7526 0.7538 0.7449 0.7471 129077.0
20180516 CME_AD1 0.7473 0.7524 0.7448 0.7514 125867.0
20180517 CME_AD1 0.7516 0.7548 0.7498 0.7509 108841.0
20180518 CME_AD1 0.7513 0.7529 0.7489 0.7511 87656.0
20180521 CME_AD1 0.7529 0.7588 0.7504 0.7583 118843.0
20180522 CME_AD1 0.7586 0.7607 0.7567 0.7576 104227.0
20180523 CME_AD1 0.7575 0.7584 0.7523 0.7558 149203.0
20180524 CME_AD1 0.7568 0.7584 0.7543 0.7579 102328.0
20180525 CME_AD1 0.7578 0.7591 0.7543 0.7549 85082.0
I spent some time working on solutions with pd.Dataframe.set_index and pd.Dataframe.reset_index but the original index kept persisting.
Since the index cannot be treated as a column and the df must have an index I came up with the idea to:
- renaming the index
- copying it to a column
- reorder column
- save to_csv with index=False
It a little long winded but achieve the task in this case.
# format data & save
df.index.names = ['ID']
df['Date'] = df.index
df.insert(loc=0, column='Code', value=qID)
df=df.drop(columns=['Change','Settle','Previous Day Open Interest'])
df.rename(columns={'Last':'Close'}, inplace=True)
df.index = df.index.strftime('%Y%m%d')
df = df[['Code','Date','Open','High','Low','Close','Volume']] #reorder columns
df.to_csv(path + qID + '_' + str(cDate) + '.txt', sep=',', index=False, header=0)
print(df)