pythonpandasdatetimestrptime

Make time series by reading time zone data from excel in python

I thought this would be an easy issue to solve but it took quite some time for me to figure it out and no luck so far.

I have data in format of 2024-05-21T08:58:14.9769876Z in excel and I would like to get the time out of it to plot in python.

I have tried this so far but not succesful.

import os
import pandas as pd
import matplotlib.pyplot as plt
import csv
import numpy as np
import datetime
from datetime import datetime

dt_format='%Y-%m-%d %H:%M:%S.%f'
data=pd.read_excel(r"test.xlsx", sheet_name='Sheet1')
timestamps =list(map(lambda timestamps: datetime.strptime(timestamps, dt_format) , data['time']))

but I get this error:

timestamps =list(map(lambda timestamps: datetime.strptime(timestamps, dt_format) , data['time']))

  File _strptime.py:568 in _strptime_datetime

  File _strptime.py:352 in _strptime

ValueError: unconverted data remains: 6Z

Can anyone help me with this issue?

Solution

  • You can use pd.to_datetime() and strftime():

    from datetime import date

data = {
    'time': ['2024-05-21T08:58:14.9769876Z', '2023-12-15T12:34:56.1234567Z']
}

df = pd.DataFrame(data)

df['time'] = pd.to_datetime(df['time']).dt.strftime('%Y-%m-%d')
print(df)

    Results:

         time
0  2024-05-21
1  2023-12-15