to_datetime Value Error: at least that [year, month, day] must be specified Pandas
<p>I am reading from two different CSVs each having date values in their columns. After read_csv I want to convert the data to datetime with the to_datetime method. The formats of the dates in each CSV are slightly different, and although the differences are noted and specified in the to_datetime format argument, the one converts fine, while the other returns the following value error.</p>

<pre><code>ValueError: to assemble mappings requires at least that [year, month, day] be sp
ecified: [day,month,year] is missing
</code></pre>

<p>first dte.head()</p>

<pre><code>0  10/14/2016  10/17/2016  10/19/2016    8/9/2016  10/17/2016   7/20/2016
1   7/15/2016   7/18/2016   7/20/2016    6/7/2016   7/18/2016   4/19/2016
2   4/15/2016   4/14/2016   4/18/2016   3/15/2016   4/18/2016   1/14/2016
3   1/15/2016   1/19/2016   1/19/2016  10/19/2015   1/19/2016  10/13/2015
4  10/15/2015  10/14/2015  10/19/2015   7/23/2015  10/14/2015   7/15/2015
</code></pre>

<p>this dataframe converts fine using the following code:</p>

<pre><code>dte = pd.to_datetime(dte, infer_datetime_format=True)
</code></pre>

<p>or </p>

<pre><code>dte = pd.to_datetime(dte[x], format='%m/%d/%Y')
</code></pre>

<p>the second dtd.head()</p>

<pre><code>0   2004-01-02 2004-01-02  2004-01-09 2004-01-16  2004-01-23  2004-01-30
1   2004-01-05 2004-01-09  2004-01-16 2004-01-23  2004-01-30  2004-02-06
2   2004-01-06 2004-01-09  2004-01-16 2004-01-23  2004-01-30  2004-02-06
3   2004-01-07 2004-01-09  2004-01-16 2004-01-23  2004-01-30  2004-02-06
4   2004-01-08 2004-01-09  2004-01-16 2004-01-23  2004-01-30  2004-02-06
</code></pre>

<p>this csv doesn't convert using either: </p>

<pre><code>dtd = pd.to_datetime(dtd, infer_datetime_format=True)
</code></pre>

<p>or </p>

<pre><code>dtd = pd.to_datetime(dtd, format='%Y-%m-%d')
</code></pre>

<p>It returns the value error above. Interestingly, however, using the parse_dates and infer_datetime_format as arguments of the read_csv method work fine. What is going on here? </p>

<p>You can <code>stack</code> / <code>pd.to_datetime</code> / <code>unstack</code></p>

<pre><code>pd.to_datetime(dte.stack()).unstack()
</code></pre>

<p><a href="https://i.stack.imgur.com/V9daD.png" rel="nofollow"><img src="https://i.stack.imgur.com/V9daD.png" alt="enter image description here"></a></p>

<p><strong><em>explanation</em></strong><br>
<code>pd.to_datetime</code> works on a string, list, or <code>pd.Series</code>. <code>dte</code> is a <code>pd.DataFrame</code> and is why you are having issues. <code>dte.stack()</code> produces a a <code>pd.Series</code> where all rows are stacked on top of each other. However, in this stacked form, because it is a <code>pd.Series</code>, I can get a vectorized <code>pd.to_datetime</code> to work on it. the subsequent <code>unstack</code> simply reverses the initial <code>stack</code> to get the original form of <code>dte</code></p>

python csv pandas