How to convert format date from MM/DD/YYYY to YYYY-MM-DDT00:00:00.000Z in Python-CodePudding

I used pandas to create a list of dictionaries. The following codes is how I create the list:

sheetwork = client.open('RMA Daily Workload').sheet1
list_of_work = sheetwork.get_all_records()
dfr = pd.DataFrame(list_of_work, columns = ['date' , 'value']) 
rnow = dfrnow.to_dict('records')

The following is the output of my list:

rnow = 
[{'date': '01/02/2020', 'value': 13}, 
{'date': '01/03/2020', 'value': 2}, 
{'date': '01/06/2020', 'value': 5},
...
{'date': '01/07/2020', 'value': 6}]

I want to change the date format from MM/DD/YYYY to YYYY-MM-DDT00:00:00.000Z, so that my data will be compatible with my javascript file where I want to add my data.

I want my list to be shown as：

rnow = 
[{'date': '2020-01-02T00:00:00.000Z', 'value': 13}, 
{'date': '2020-01-03T00:00:00.000Z', 'value': 2}, 
{'date': '2020-01-06T00:00:00.000Z', 'value': 5},
...
{'date': '2020-01-07T00:00:00.000Z', 'value': 6}]

I tried so many methods but can only convert them into 2020-01-02 00:00:00 but not 2020-01-02T00:00:00.000Z. Please advise what should I do

CodePudding user response：

To make it easy and keeping UTC and since you are using pandas:

rnow = [{'date': '01/02/2020', 'value': 13}, 
{'date': '01/03/2020', 'value': 2}, 
{'date': '01/06/2020', 'value': 5},
{'date': '01/07/2020', 'value': 6}]

def get_isoformat(date):
    return pd.to_datetime(date, dayfirst=False, utc=True).isoformat()
for i in range (len(rnow)):
    rnow[i]['date'] = get_isoformat(rnow[i]['date'])
rnow

which outputs:

[{'date': '2020-01-02T00:00:00 00:00', 'value': 13},
 {'date': '2020-01-03T00:00:00 00:00', 'value': 2},
 {'date': '2020-01-06T00:00:00 00:00', 'value': 5},
 {'date': '2020-01-07T00:00:00 00:00', 'value': 6}]

in fact, you probably want to consider using the function get_isoformat() applied to your dataframe for simplicity. Also, if you use utc=None will get rid of the 00:00 part in case you don't want it or need it.

CodePudding user response：

If you need exact T00:00:00.000Z this string after the time, try to use string format after time conversion,

e.g.,

import datetime
# '2020-01-07T00:00:00.000Z'
datetime.datetime.strptime("07/02/2020", '%d/%m/%Y').strftime('%Y-%m-%dT00:00:00.000Z'))

How to apply to pandas:

def func(x):
    myDate = x.date
    return datetime.datetime.strptime(myDate, '%d/%m/%Y').strftime('%Y-%m-%dT00:00:00.000Z')


df['new_date'] = df.apply(func, axis=1)

CodePudding user response：

You can use the isoformat function of Python's builtin datetime package:

from datetime import datetime, timezone
formatted = datetime.strptime('01/02/2020', '%m/%d/%Y', tzInfo=timezone.utc).isoformat()

formatted
# Output: '2020-01-02T00:00:00 00:00'

Note that Python doesn't support the Z suffix for UTC timezone, instead it will be 00:00 which is according to ISO 8601 as well and should parse in other code just fine.

If this is a problem, you can omit the timezone and instead manually put a Z there:

from datetime import datetime
formatted = datetime.strptime('01/02/2020', '%m/%d/%Y').isoformat()   'Z'

formatted
# Output: '2020-01-02T00:00:00Z'

Alternatively (in a more "manual" approach), you could format the date using strftime:

from datetime import datetime
formatted = datetime.strptime('01/02/2020', '%m/%d/%Y').strftime('%Y-%m-%dT00:00:00Z')

formatted
# Output: '2020-01-02T00:00:00Z'