I am trying to combine two appended CSV files but I keep getting in return a KeyError: -1. I'm not sure why as I have been following a coding tutorial and for him, it works perfectly fine. The two different csv file groups have different formats and so this code will remove the 2 empty columns from one of the formats.
import glob
import os
import pandas as pd
appended_data = []
for f in glob.glob ('C:\\Users\\dylan\\PycharmProjects\\TradeBox\\raw\\*.csv'):
df = pd.read_csv(f, header = None)
appended_data.append(df)
df = pd.concat(appended_data)
df.rename(columns={0: "volume",
1: "weighted volume",
2: "open",
3: "close",
4: "high",
5: "low",
6: "timestamp",
7: "transactions",
8: "date"},
inplace=True)
appended_data_new = []
for f in glob.glob ('C:\\Users\\dylan\\PycharmProjects\\TradeBox\\*.csv'):
df_new = pd.read_csv(f, header = None)
appended_data_new.append(df_new)
df_new = pd.concat(appended_data_new)
df_new.dropna(axis=1, inplace = True)
df_new.rename(columns={0: "volume",
1: "weighted volume",
2: "open",
3: "close",
4: "high",
5: "low",
6: "timestamp",
7: "transactions",
10: "date"},
inplace=True)
df_final = pd.concat([df, df_new])
path = 'C:\\Users\\dylan\\PycharmProjects\\TradeBox\\check point\\'
path = path 'cp1-' df_final.iloc[-1][-1][0:10] '.csv'
df_final.to_csv(path, header=True, index=None)
The following traceback error occurs:
> Traceback (most recent call last): File
> "C:/Users/dylan/PycharmProjects/TradeBox/Clean_data.py", line 46, in
> <module>
> path = 'C:\\Users\\dylan\\PycharmProjects\\TradeBox\\check point\\' 'cp1-' df_final.iloc[-1][-1][0:10] '.csv' File
> "C:\Users\dylan\PycharmProjects\TradeBox\venv\lib\site-packages\pandas\core\series.py",
> line 942, in __getitem__
> return self._get_value(key) File "C:\Users\dylan\PycharmProjects\TradeBox\venv\lib\site-packages\pandas\core\series.py",
> line 1051, in _get_value
> loc = self.index.get_loc(label) File "C:\Users\dylan\PycharmProjects\TradeBox\venv\lib\site-packages\pandas\core\indexes\base.py",
> line 3363, in get_loc
> raise KeyError(key) from err KeyError: -1
>
> Process finished with exit code 1
CodePudding user response:
The indexing into your data frame elements to make the file name is causing the error. In this line:
path = path 'cp1-' df_final.iloc[-1][-1][0:10] '.csv'
To get the last date from your data frame using iloc
, you need to put both the row index and the column index inside the brackets.
df_final.iloc[-1, -1]