Convert date/time columns in Pandas dataframe
<p>I have data sets containing the date (Julian day, column 1), hour (HHMM, column 2) and seconds (column 3) in individual columns:</p>

<pre><code>1  253  2300   0    2.9  114.4  18.42  21.17
1  253  2300  10   3.27  111.2  18.48  21.12
1  253  2300  20   3.22  111.3  18.49  21.09
1  253  2300  30   3.84  106.4  18.52     21
1  253  2300  40   3.75  104.4  18.53  20.85
</code></pre>

<p>I'm reading the text file using <code>Pandas</code> as:</p>

<pre><code>columns = ['station','julian_day','hours','seconds','U','Ud','T','RH']
df = pd.read_table(file_name, header=None, names=columns, delim_whitespace=True)
</code></pre>

<p>Now I want to convert the date to something more convenient like <code>YYYY-MM-DD HH:MM:SS</code> (<em>The year isn't provided in the data set, but is fixed at 2001</em>). </p>

<p>I tried combining the three columns into one using <code>parse_dates</code>:</p>

<pre><code>df = pd.read_table(file_name, header=None, names=columns, delim_whitespace=True, 
                   parse_dates={'datetime' : ['julian_day','hours','seconds']}) 
</code></pre>

<p>which converts the three columns into one string:</p>

<pre><code>In [38]: df['datetime'][0]
Out[38]: '253 2300 0'
</code></pre>

<p>I next tried to convert them using <code>date_parser</code>; following <a href="http://stackoverflow.com/a/20500819/3581217">this post</a> using something like:</p>

<pre><code>date_parser = lambda x: datetime.datetime.strptime(x, '%j %H%M %s')
</code></pre>

<p>The <code>date_parser</code> itself works, but I can't get this to combine with <code>read_table</code>, and I'm pretty much stuck at this point. Is there an easy way to achieve the conversion?</p>

<p>The full minimal (not-so) working example:</p>

<pre><code>import pandas as pd
import datetime
from io import StringIO

data_file = StringIO("""\
 1  253  2300   0    2.9  114.4  18.42  21.17
 1  253  2300  10   3.27  111.2  18.48  21.12
 1  253  2300  20   3.22  111.3  18.49  21.09
 1  253  2300  30   3.84  106.4  18.52     21
 1  253  2300  40   3.75  104.4  18.53  20.85
""")

date_parser = lambda x: datetime.datetime.strptime(x, '%j %H%M %S')

columns = ['station','julian_day','hours','seconds','U','Ud','T','RH']
df = pd.read_table(data_file, header=None, names=columns, delim_whitespace=True,\
                   parse_dates={'datetime' : ['julian_day','hours','seconds']})
</code></pre>

<p>Not sure if I am missing something but this seems to work:</p>

<pre><code>import pandas as pd
import datetime
from io import StringIO

data_file = StringIO("""\
 1  253  2300   0    2.9  114.4  18.42  21.17
 1  253  2300  10   3.27  111.2  18.48  21.12
 1  253  2300  20   3.22  111.3  18.49  21.09
 1  253  2300  30   3.84  106.4  18.52     21
 1  253  2300  40   3.75  104.4  18.53  20.85
""")

date_parser = lambda x: datetime.datetime.strptime(("2001 " + x), '%Y %j %H%M %S')

columns = ['station','julian_day','hours','seconds','U','Ud','T','RH']
df = pd.read_table(data_file, header=None, names=columns, delim_whitespace=True,\
                   date_parser = date_parser,parse_dates={'datetime' : ['julian_day','hours','seconds']})
</code></pre>

<p>I just add the <strong>date_parser</strong> parameter in read_table and hard codded <strong>2001</strong> in parsing function. </p>

python pandas