CODEWITHSUNDEEP
pythondatetimepandas
WheninSeattle
WheninSeattle

Reputation: 81

Convert dataframe column to datetime for re-mapping

I have a bunch of weather data that I need to remap with Pandas. I'm struggling to convert the first column 'Time' to a datetime index. I've been scanning forums, and have not been able to fix the problem.

Here is a sample of the data:

Time    TemperatureF    DewpointF

1/1/2015 0:01   31.7    27.1

1/1/2015 0:06   31.7    27.4

1/1/2015 0:11   31.6    27.3

1/1/2015 0:16   31.6    27.3

1/1/2015 0:21   31.5    26.9

1/1/2015 0:26   31.5    26.9

1/1/2015 0:31   31.5    26.9

and here is the most recent version of the code I'm using to re-index.

Any help would be greatly appreciated!

df = df.set_index('Time')
df.index = df.index.to_datetime()

Upvotes: 2

Views: 2469

Answers (3)

Stefan
Stefan

Reputation: 42885

Try using pd.to_datetime() instead of df.index.to_datetime(), and use the result as your index.

If the format does not convert correctly, you'll need to add a format parameter that uses these naming conventions, e.g. format='%m/%d/%Y %H:%M'.

df['Time'] = pd.to_datetime(df[['Time']]) # Convert 'Time' column to datetime, possibly using format keyword
df.set_index(df.Time, inplace=True) # set result as index

Upvotes: 1

jezrael
jezrael

Reputation: 862751

I think you can try to_datetime and set_index:

print df
            Time  TemperatureF  DewpointF
0  1/1/2015 0:01          31.7       27.1
1  1/1/2015 0:06          31.7       27.4
2  1/1/2015 0:11          31.6       27.3
3  1/1/2015 0:16          31.6       27.3
4  1/1/2015 0:21          31.5       26.9
5  1/1/2015 0:26          31.5       26.9
6  1/1/2015 0:31          31.5       26.9

#check column names
print df.columns
Index([u'Time', u'TemperatureF', u'DewpointF'], dtype='object')

#first number is month
df['Time'] = pd.to_datetime(df['Time'], format='%m/%d/%Y %H:%M') #no double []

df.set_index('Time', inplace=True)
print df
                     TemperatureF  DewpointF
Time                                        
2015-01-01 00:01:00          31.7       27.1
2015-01-01 00:06:00          31.7       27.4
2015-01-01 00:11:00          31.6       27.3
2015-01-01 00:16:00          31.6       27.3
2015-01-01 00:21:00          31.5       26.9
2015-01-01 00:26:00          31.5       26.9
2015-01-01 00:31:00          31.5       26.9

print df.index
DatetimeIndex(['2015-01-01 00:01:00', '2015-01-01 00:06:00',
               '2015-01-01 00:11:00', '2015-01-01 00:16:00',
               '2015-01-01 00:21:00', '2015-01-01 00:26:00',
               '2015-01-01 00:31:00'],
              dtype='datetime64[ns]', name=u'Time', freq=None)

If still ValueError, try add parameter errors='coerce' for converting strings not matching format to NaT:

df['Time'] = pd.to_datetime(df['Time'], format='%d/%m/%Y %H:%M', errors='coerce')

Upvotes: 1

Alexander
Alexander

Reputation: 109546

df.set_index(pd.DatetimeIndex(pd.to_datetime(df.Time)), inplace=True)

Upvotes: 1

Related Questions