python pandas dtypes detection from sql
<p>I am quite troubled by the behaviour of Pandas DataFrame about Dtype detection.</p>

<p>I use 'read_sql_query' to retrieve data from a database to build a DataFrame, and then dump it into a csv file.</p>

<p>I don't need any transformation. Just dump it into a csv file and change date fields in the form : <strong>'%d/%m/%Y'</strong></p>

<p>However :</p>

<pre><code>self.dataframe.to_csv(self.fic,
                   index=False,
                   header=False,
                   sep='|',
                   mode='a',
                   encoding='utf-8',
                   line_terminator='\n',
                   date_format='%d/%m/%Y
                   )
</code></pre>

<p>Would miss to transforme/format some date fields...</p>

<p>I tried to do it another way :</p>

<pre><code>l = list(self.dataframe.select_dtypes(include=['datetime64']).columns)
for i in l:
    self.dataframe[i] = self.dataframe[i].dt.strftime('%d/%m/%Y')
</code></pre>

<p>I was about to be satisfied, but some more tests showed a weird behaviour :</p>

<p><strong>if my sql request only selects two nuplets :</strong></p>

<pre><code>requete = 'select * from DOMMAGE_INTERET where doi_id in (176433, 181564)'
</code></pre>

<p>Everything works, whatever formating in the csv or in the DataFrame.</p>

<p>It detects date fields properly :</p>

<pre><code>df.dtypes
doi_id                         int64
aff_id                         int64
pdo_id                         int64
doi_date_decision     datetime64[ns]
doi_date_mod          datetime64[ns]
doi_montant                  float64
doi_reste_a_payer             object
doi_appliquer_taux             int64
doi_date_update       datetime64[ns]
afg_id                         int64
dtype: object
</code></pre>

<p><strong>But when using a different selection :</strong></p>

<pre><code>requete = 'select * from DOMMAGE_INTERET where rownum &lt; 100'
</code></pre>

<p>It miss again. And actually, fields types are detected differently :</p>

<pre><code>doi_id                         int64
aff_id                         int64
pdo_id                         int64
doi_date_decision             object
doi_date_mod          datetime64[ns]
doi_montant                  float64
doi_reste_a_payer             object
doi_appliquer_taux             int64
doi_date_update       datetime64[ns]
afg_id                         int64
dtype: object
</code></pre>

<p>As you can see : <strong>'doi_date_decision' type does change depending of the request selection</strong> but, of course, this is the same set of data !!!</p>

<p>Isn't it weird?</p>

<p>Do you have a explanation for this behaviour?</p>

<p>It is difficult to dig into your issue without some data samples. However, you probably face either of the two cases:</p>

<ul>
<li>Some of the rows you select in your second case contain <code>NULL</code> values, which stops pandas interpret automatically your column as a datetime</li>
<li>You have a different MDY convention in your database and some dates lower than 13rd of the month are parsed as dates while others aren't and are kept as strings until you convert manually in DMY</li>
</ul>

python sql csv pandas dataframe