What's the best way to combine multiple columns as single datetime

user_6039830 0
Viewed 221

This is a question I come back to from time to time. I have a dataset where multiple columns (there are other columns, these are just the ones pertinent to the question) are used to indicate a date and time. After casting them from float to int I now have:

year    mo      dy  hr min sec Valid Mag
1234    1886    9   1   2   51  4.0 7.3
1286    1893    6   4   2   27  4.0 7.0
1329    1897    8   5   0   10  4.0 7.7
1366    1901    8   9   9   23  4.0 7.2
1368    1901    8   9   18  33  4.0 7.4

What is the clearest and most idiomatic way to convert this as a DateTime in a DataFrame that has more than just columns related to date and time?

With a different project I used this:

sun['Date'] = sun['Year'].map(str)+ '-' + sun['Month'].map(str) + '-' + sun['Day'].map(str)
pd.to_datetime(sun['Date'], utc=False)

While this works, I think there certainly has to be a better, more generalized way. Specifically, I'm looking to combine the relevant fields into a DateTime but, again, there are other fields in the data frame. I've seen good responses for this in SQL, but that's not what I'm looking for.

Edit: I've received some solid answers for DataFrames of just date and times. However, the problem is that all result in the same error "ValueError: Length mismatch: Expected axis has 19 elements, new values have 6 elements" so I've added in a couple of extra columns.

python-datetime pandas datetime python
4 Answers

Change column name then pd.to_datetime

df.columns = ['year','month','day','hour','minute','second']
out = pd.to_datetime(df)
Out[185]: 
1234   1886-09-01 02:51:00
1286   1893-06-04 02:27:00
1329   1897-08-05 00:10:00
1366   1901-08-09 09:23:30
1368   1901-08-09 18:33:45
dtype: datetime64[ns]
user_79645270

So here is one way to do it, I don't know if its idiomatic.

The solution I found is using these column names

df.columns = [ 'year', 'month' , 'day' , 'hour' , 'minute'  ,'seconds']

df = pd.DataFrame([[1886,9,1,2,51,0],
[1893,6,4,2,27,0],
[1897,8,5,0,10,0],
[1901,8,9,9,23,30],
[1901,8,9,18,33,45]])

#df.columns = [ 'Year', 'Mo' , 'Dy' , 'Hr' , 'Mn'  ,'Sec']

#use these column names instead of what you have
df.columns = [ 'year', 'month' , 'day' , 'hour' , 'minute'  ,'seconds']

#then you convert to date time
pd.to_datetime(df)

#output
0   1886-09-01 02:51:00
1   1893-06-04 02:27:00
2   1897-08-05 00:10:00
3   1901-08-09 09:23:30
4   1901-08-09 18:33:45
dtype: datetime64[ns]

Assembling a datetime from multiple columns of a DataFrame. The keys can be common abbreviations like [‘year’, ‘month’, ‘day’, ‘minute’, ‘second’, ‘ms’, ‘us’, ‘ns’]) or plurals of the same Source

user_73391290

Another way to achieve this would be to use datetime.strptime() to create a datetime object and df.apply() it to a new column.

df['time'] = df.apply(lambda r: datetime.strptime(f"{r['Year']} {r['Mo']} {r['Dy']} {r['Hr']}:{r['Mn']}:{r['Sec']}", '%Y %m %d %H:%M:%S'), axis=1)

Output:

df['time']
1234   1886-09-01 02:51:00
1286   1893-06-04 02:27:00
1329   1897-08-05 00:10:00
1366   1901-08-09 09:23:30
1368   1901-08-09 18:33:45
Name: time, dtype: datetime64[ns]
user_79428560

coerce df to string

use str.pad to fill values to minimum 2

Use str.cat to combine the values

Coerce the outcome to a datetime object.

Code below

pd.to_datetime(df.astype(str).apply(lambda x: (x.str.pad(width=2, side='left', fillchar='0')).str.cat(sep=''),axis=1))

1234   1886-09-01 02:51:00
1286   1893-06-04 02:27:00
1329   1897-08-05 00:10:00
1366   1901-08-09 09:23:30
1368   1901-08-09 18:33:45
user_89869750
Related