0

i have a data frame whose index i want to convert to a datetime format: %Y%m%d%H:%M:%S. i have tried the following code: pd.to_datetime(dfn.index,origin=pd.Timestamp('2012-01-01 00:00:00'))

but the DateTime format returned attributed the value of the index to milliseconds as seen.

enter image description here

When specifying the format, I obtain the error message: ValueError: time data '1325376000000000000' does not match format '%Y%m%d%H:%M:%S' (match)

I would like the index value to be attributed to the hours, how can I please do that?

The original data looks like this.

enter image description here

FObersteiner
  • 22,500
  • 8
  • 42
  • 72
vichel
  • 1
  • 2
  • how does your index look like *before* you try to convert it? dtype? unit? – FObersteiner May 28 '20 at 09:02
  • 1
    Welcome to Stackoverflow. Please take the time to read this post on [how to provide a great pandas example](http://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) as well as how to provide a [minimal, complete, and verifiable example](http://stackoverflow.com/help/mcve) and revise your question accordingly. These tips on [how to ask a good question](http://stackoverflow.com/help/how-to-ask) may also be useful. – jezrael May 28 '20 at 09:03
  • @MrFuppes i have uploaded a screenshot of the original data. The index i would like to convert is automatically generated by pandas. – vichel May 28 '20 at 09:20

1 Answers1

0

If you have a DataFrame with a numeric index which represents time in a certain unit, you should specify that unit if you want to convert to datetime (see here for possible unit specifiers). Otherwise, to_datetime will assume that the number represents nanoseconds by default. Ex:

import numpy as np
import pandas as pd

df = pd.DataFrame({'v': np.random.rand(10)}, index=np.arange(10))
# df.index
# Int64Index([0, 1, 2, 3, 4, 5, 6, 7, 8, 9], dtype='int64')

# e.g. assuming unit is seconds:
df.index = pd.to_datetime(df.index, origin=pd.Timestamp('2012-01-01 00:00:00'), unit='s')
# df
# 2012-01-01 00:00:00  0.393867
# 2012-01-01 00:00:01  0.252125
# 2012-01-01 00:00:02  0.639876
# 2012-01-01 00:00:03  0.804882
# 2012-01-01 00:00:04  0.582824
# 2012-01-01 00:00:05  0.719932
# 2012-01-01 00:00:06  0.491117
# 2012-01-01 00:00:07  0.299446
# 2012-01-01 00:00:08  0.495090
# 2012-01-01 00:00:09  0.553190

Note that if you want to display the datetime index in a certain format, you'll have to convert to string with strftime:

df.index.strftime('%Y%m%d% H:%M:%S')
# Index(['2012-01-01 00:00:00', '2012-01-01 00:00:01', '2012-01-01 00:00:02',
#        '2012-01-01 00:00:03', '2012-01-01 00:00:04', '2012-01-01 00:00:05',
#        '2012-01-01 00:00:06', '2012-01-01 00:00:07', '2012-01-01 00:00:08',
#        '2012-01-01 00:00:09'],
#       dtype='object')
FObersteiner
  • 22,500
  • 8
  • 42
  • 72
  • @vichel: glad I could help. if you like, mark the answer as solution. and in general, it is easier to provide an accurate answer if the question contains exemplary data as text instead of images ;-) – FObersteiner May 28 '20 at 09:47
  • thanks @MrFuppes. i will take note of that for the next times :) – vichel Jun 04 '20 at 13:22