Search code examples
pythonpandasdataframedata-science

How to convert date and time inside data frame to float64 datatype?

I have this Excel file data as in the image below enter image description here] following this tutorial with data mentioned (https://i.sstatic.net/kbI7C.png) I use colab notebook : by writing code down

import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
from numpy import nan

from tensorflow.keras import Sequential
from tensorflow.keras.layers import LSTM, Dense

from sklearn.metrics import mean_squared_error
from sklearn.preprocessing import MinMaxScaler
#Reading the dataset
data_path= "/content/data.xlsx"
data = pd.read_excel(data_path)
data.head()

When try to check all data columns type by using data.info() I got :

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 84960 entries, 0 to 84959
Data columns (total 9 columns):
 #   Column           Non-Null Count  Dtype         
---  ------           --------------  -----         
 0   Time             84960 non-null  datetime64[ns]
 1   Fridge           84960 non-null  float64       
 2   Lights           84960 non-null  float64       
 3   Microwave        84960 non-null  float64       
 4   Pump1            84960 non-null  float64       
 5   Pump2            84960 non-null  float64       
 6   TV               84960 non-null  float64       
 7   Washing Machine  84960 non-null  float64       
 8   Total Load       84960 non-null  float64       
dtypes: datetime64[ns](1), float64(8)
memory usage: 5.8 MB

I am trying to convert Time type datetime64 to float64 by

# data = data.astype('float')
x = data['Time'].values.astype("float64")
x

but got this issue :

3632             except TypeError:
   3633                 # If we have a listlike key, _check_indexing_error will raise

KeyError: 'Time'

## What I am expect : 
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 84960 entries, 0 to 84959
Data columns (total 9 columns):
 #   Column           Non-Null Count  Dtype         
---  ------           --------------  -----         
 0   Time             84960 non-null  float64
 1   Fridge           84960 non-null  float64       
 2   Lights           84960 non-null  float64       
 3   Microwave        84960 non-null  float64       
 4   Pump1            84960 non-null  float64       
 5   Pump2            84960 non-null  float64       
 6   TV               84960 non-null  float64       
 7   Washing Machine  84960 non-null  float64       
 8   Total Load       84960 non-null  float64       
dtypes: float64(9)
memory usage: 5.8 MB

The format of Time: dd/mm/yyyy hh:mm enter image description here

Solution

  • Try the below to convert:

    data['Time'] = data['Time'].apply(lambda x: x.timestamp())
data['Time'] = data['Time'].astype('float64')
data.info()