Ron Maor Ron Maor - 7 months ago 12
Python Question

Pandas: importing Date and 12 hour Time together

I have the following txt file:

Temp Hi Low Out Dew Wind Wind Wind Hi Hi Wind Heat THW THSW Rain Solar Solar Hi Solar UV UV Hi Heat Cool In In In In In In Air Wind Wind ISS Arc.
Date Time Out Temp Temp Hum Pt. Speed Dir Run Speed Dir Chill Index Index Index Bar Rain Rate Rad. Energy Rad. Index Dose UV D-D D-D Temp Hum Dew Heat EMC Density ET Samp Tx Recept Int.
01/01/16 12:30 a 13.8 13.8 13.6 88 11.9 0.0 --- 0.00 0.0 --- 13.8 13.8 13.8 12.4 1012.3 0.00 0.0 0 0.00 0 0.0 0.00 0.0 0.094 0.000 21.5 50 10.6 20.7 9.25 1.1823 0.00 702 1 100.0 30
01/01/16 1:00 a 13.6 13.8 13.2 88 11.7 0.0 --- 0.00 0.0 --- 13.6 13.6 13.6 12.2 1012.2 0.00 0.0 0 0.00 0 0.0 0.00 0.0 0.098 0.000 21.5 50 10.6 20.7 9.25 1.1823 0.00 702 1 100.0 30
01/01/16 1:30 a 14.5 14.5 13.6 81 11.3 0.0 --- 0.00 0.0 --- 14.5 14.4 14.4 12.9 1012.2 0.00 0.0 0 0.00 0 0.0 0.00 0.0 0.080 0.000 21.5 50 10.6 20.7 9.25 1.1822 0.00 703 1 100.0 30
01/01/16 2:00 a 15.2 15.2 14.5 75 10.8 0.0 --- 0.00 0.0 --- 15.2 14.9 14.9 13.4 1012.0 0.00 0.0 0 0.00 0 0.0 0.00 0.0 0.066 0.000 21.4 49 10.2 20.5 9.05 1.1829 0.00 702 1 100.0 30
01/01/16 2:30 a 14.4 15.2 14.0 79 10.8 0.0 --- 0.00 0.0 --- 14.4 14.2 14.2 12.8 1012.2 0.20 0.0 0 0.00 0 0.0 0.00 0.0 0.082 0.000 21.4 48 9.9 20.4 8.86 1.1834 0.00 703 1 100.0 30
01/01/16 3:00 a 15.1 15.1 14.1 76 10.9 0.0 --- 0.00 0.0 --- 15.1 14.8 14.8 13.4 1011.9 0.00 0.0 0 0.00 0 0.0 0.00 0.0 0.068 0.000 21.4 48 9.9 20.4 8.86 1.1830 0.00 700 1 100.0 30
01/01/16 3:30 a 14.9 15.2 14.9 73 10.1 0.0 --- 0.00 0.0 --- 14.9 14.6 14.6 13.2 1011.9 0.00 0.0 0 0.00 0 0.0 0.00 0.0 0.071 0.000 21.4 47 9.6 20.3 8.75 1.1833 0.00 702 1 100.0 30
01/01/16 4:00 a 15.2 15.3 14.9 68 9.4 0.0 --- 0.00 0.0 --- 15.2 14.8 14.8 13.3 1011.9 0.00 0.0 0 0.00 0 0.0 0.00 0.0 0.065 0.000 21.4 47 9.6 20.3 8.75 1.1833 0.00 700 1 100.0 30
01/01/16 4:30 a 14.9 15.2 14.6 72 9.9 0.0 --- 0.00 0.0 --- 14.9 14.6 14.6 13.1 1011.8 0.00 0.0 0 0.00 0 0.0 0.00 0.0 0.072 0.000 21.3 46 9.2 20.2 8.64 1.1838 0.00 703 1 100.0 30
01/01/16 5:00 a 14.1 15.1 14.0 76 9.9 0.0 --- 0.00 0.0 --- 14.1 13.8 13.8 12.3 1012.1 0.00 0.0 0 0.00 0 0.0 0.00 0.0 0.088 0.000 21.3 46 9.2 20.2 8.64 1.1842 0.00 702 1 100.0 30


and I want to import it into a Data Frame but with one column contating the date and the time in 24 hour display together:

Time
01/01/16 12:30
.....
01/01/16 13:30


Is there an easy way to do this ?

Thank you !!

Answer

try this:

For dd/mm/yy format:

def parse_dt(dt, tm, ap):
    return pd.to_datetime(dt + ' ' + tm + ap, dayfirst=True)

For mm/dd/yy format:

def parse_dt(dt, tm, ap):
    return pd.to_datetime(dt + ' ' + tm + ap)

Parse CSV:

df = pd.read_csv(filename, sep='\s+', skiprows=2, header=None,
                 parse_dates={'ts': [0,1,2] }, date_parser=parse_dt)
Comments