Transform spaced dates in CSV file to datetime with python-CodePudding

I have a text file containing information about some events. The file has one row per event and looks like this

#year month day hour minute second latitude longitude magnitude depth
2008 01 02 04 42 57.53 -20.0950  -70.5300 43.90    4.0  52.97
2008 01 02 04 51 15.77 -20.0950  -70.5300 43.90    0.2  16.52
2008 01 02 04 56  0.73 -20.0950  -70.5300 43.90    0.1  19.09
2008 01 02 06 16 39.25 -20.0950  -70.5300 43.90    0.0  18.74
2008 01 09 15 05 44.52 -19.6830  -70.7610 35.00    1.1  12.98

I would like to read it in python (possibly with pandas) and directly extract the time (datetime) from the information in the first 5 columns

Many thanks

CodePudding user response：

Due to you being ambiguous I have made the assumption that you have, as you mention, a text file containing content of the form you specify that you want to extract into datetime objects.

Use strptime from the datetime library.

from datetime import datetime
with open("dates.txt", 'r') as f:
    dates = []
    for event in f.readlines()[1:]
        # " ".join(event.split()[:5]) will extract the first 5 columns
        date = datetime.strptime(" ".join(event.split()[:5]), "%Y %m %d %H %M")
        dates.append(date)

or shorter by using list comprehension

from datetime import datetime
with open("dates.txt", 'r') as f:
    dates = [datetime.strptime(" ".join(event.split()[:5]), "%Y %m %d %H %M") for event in f.readlines()[1:]]

To make the list into a Pandas dataframe you can do df = pd.DataFrame({'Events': dates})

               Events
0 2008-01-02 04:42:00
1 2008-01-02 04:51:00
2 2008-01-02 04:56:00
3 2008-01-02 06:16:00
4 2008-01-09 15:05:00

CodePudding user response：

You can combine the first 6 columns in a string and then convert it to a datetime object with pd.to_datetime:

df['time'] = pd.to_datetime(df.iloc[0:,:6].astype(int).astype(str).agg('-'.join, axis=1), format='%Y-%m-%d-%H-%M-%S')