create a timestamp column in a pandas dataframe

[SophMC]

@aaren
I am reading in a txt file with the format
year month day hour ws
and want to create an extra column which takes the first four columns and makes a timestamp out of them and makes a new column.
This is proving difficult for several reasons:

• The hour column is in hundreds, i.e 600, 1200, 1800 and if I use read_csv to try and make a datetime object, it can do it with year, month and day, but not + hour.

date_spec = {'date_time': [0,1,2]} this works in wind below.
date_spec = {'date_time': [0,1,2,3]} this doesn't. It creates a plain object for date_time, and we want a datetime object.

wind = pd.read_csv('/home/sophie/projects/windspeed/data/61401BirMoghrein_allwinds.txt', sep=" ", names=column_names, parse_dates=date_spec, keep_date_col=True)

• Trying to use datetime() to create a timestamp with year, month, day, hour doesn't work either, as it appears it doesn't take lists so:

d = datetime(2013, 12, 22, 11, 30, 59); print d works
2013-12-22 11:30:59

years = [2012, 2013, 2014]
months=[5, 4, 3]
days=[6, 5, 4]
e = datetime(years, months, days); print e doesn't work

As far as I can see you can only input for value at a time - it doesn't take lists.

• I've also tried using to_datetime but I've no idea how to get that to work in pandas as it only takes strings and I can't seem to input the data as strings whatever I do.
• These two following suggestions are in 0.18.1 pandas - so I just updated, but conda update pandas only took me up to 0.18.0 so they still don't work.
  https://pandas-docs.github.io/pandas-docs-travis/timeseries.html

pd.to_datetime(df[['year', 'month', 'day']]) doesn't work, substituting df=wind

This other suggestion from the documentation (I copied exactly)
df = pd.DataFrame({'year': [2015, 2016], 'month': [2, 3],'day': [4, 5],'hour': [2, 3]})
pd.to_datetime(df) doesn't work

Maybe I can update to 0.18.1 some other way?
Any ideas most welcome. I'll continue the fight tomorrow.

[SophMC]

Solution!
#specify the columns you want to group together. Can't include hour at this point as it is not in the right format.
date_spec = {'date_time': [0,1,2]}

#Read and parse date_time simultaneously.
#When you use keep_dat_col it keeps them as objects, not as the dtype you read them in as.
wind = pd.read_csv(datafile, sep=" ", names=column_names, parse_dates=date_spec, keep_date_col=True, index_col=False )

#Dealing with hour - going from 600, 1200 etc to 6,12, 18
wind["hour"]=(wind["hour"]/100).astype(int)

#combining date_time with hour, which is now in the correct format.
wind['date_time'] = pd.to_datetime(wind.date_time) + wind.hour.astype('timedelta64[h]')

print wind.date_time[0:5]

0 1984-03-01 06:00:00
1 1984-03-01 12:00:00
2 1984-03-01 18:00:00
3 1984-03-02 06:00:00
4 1984-03-02 12:00:00
Name: date_time, dtype: datetime64[ns]

The light bulb moment came from discovering the line wind.hour.astype('timedelta64[h]')