CODEWITHSUNDEEP
python-3.xpandasdatetime
AK88
AK88

Reputation: 3026

Cannot convert object to date after groupby

I have been successful with converting while working with a different dataset a couple days ago. However, I cannot apply the same technique to my current dataset. The set looks as:

totalHist.columns.values[[0, 1]] = ['Datez', 'Volumez']
totalHist.head()

Datez   Volumez
0   2016-09-19  6.300000e+07
1   2016-09-20  3.382694e+07
2   2016-09-26  4.000000e+05
3   2016-09-27  4.900000e+09
4   2016-09-28  5.324995e+08


totalHist.dtypes

Datez       object
Volumez    float64
dtype: object

This used to do the trick:

totalHist['Datez'] = pd.to_datetime(totalHist['Datez'], format='%d-%m-%Y')
totalHist.dtypes

which now is giving me:

KeyError: 'Datez'
During handling of the above exception, another exception occurred:

How can I fix this? I am doing this groupby before trying:

totalHist = df.groupby('Date', as_index = False).agg({"Trading_Value": "sum"})
totalHist.head()

totalHist.columns.values[[0, 1]] = ['Datez', 'Volumez']
totalHist.head()

Upvotes: 1

Views: 75

Answers (1)

edesz
edesz

Reputation: 12406

You can just use .rename() to rename your columns

Generate some data (in same format as OP)

d = ['1/1/2018','1/2/2018','1/3/2018',
     '1/3/2018','1/4/2018','1/2/2018','1/1/2018','1/5/2018']
df = pd.DataFrame(d, columns=['Date'])
df['Trading_Value'] = [1000,1005,1001,1001,1002,1009,1010,1002]
print(df)

       Date  Trading_Value
0  1/1/2018           1000
1  1/2/2018           1005
2  1/3/2018           1001
3  1/3/2018           1001
4  1/4/2018           1002
5  1/2/2018           1009
6  1/1/2018           1010
7  1/5/2018           1002

GROUP BY

totalHist = df.groupby('Date', as_index = False).agg({"Trading_Value": "sum"})
print(totalHist.head())

       Date  Trading_Value
0  1/1/2018           2010
1  1/2/2018           2014
2  1/3/2018           2002
3  1/4/2018           1002
4  1/5/2018           1002

Rename columns

totalHist.rename(columns={'Date':'Datez','totalHist':'Volumez'}, inplace=True)
print(totalHist)

      Datez  Trading_Value
0  1/1/2018           2010
1  1/2/2018           2014
2  1/3/2018           2002
3  1/4/2018           1002
4  1/5/2018           1002

Finally, convert to datetime

totalHist['Datez'] = pd.to_datetime(totalHist['Datez'])
print(totalHist.dtypes)

Datez            datetime64[ns]
Trading_Value             int64
dtype: object

This was done with python --version = 3.6.7 and pandas (0.23.4).

Upvotes: 2

Related Questions