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>Your <code>to-csv</code> operation does not convert all specified date fields because as you mention, not all datetime columns are read in as datetime format but show as string (<em>object</em> dtype) in current dataframe. This is the unfortunate side effect of reading from external sources as the imported system --this includes Python, SAS, Stata, R, Excel, etc.-- attempts to define columns usually by first few rows unless otherwise explicitly defined.</p>

<p>Fortunately, pandas's <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_sql_query.html#pandas.read_sql_query" rel="nofollow"><code>read_sql_query()</code></a> maintains a parameter for <code>parse_dates</code>. So consider defining the dates during the read in operation as this argument takes a list or dictionary:</p>

<pre><code>df = read_sql_query('select * from DOMMAGE_INTERET where rownum &lt; 100', engine, 
                    parse_dates = ['doi_date_decision', 'doi_date_mod', 'doi_date_update'])
</code></pre>

<p>Alternatively, convert with <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.to_datetime.html" rel="nofollow"><code>pd.to_datetime()</code></a> just after reading in and before <code>to_csv</code>:</p>

<pre><code>df['doi_date_decision'] = pd.to_datetime(df['doi_date_decision'])
</code></pre>

<p>And most RDMS maintains datetime in <code>YYYY-MM-DD HH:MM:SS</code> format, aligning to pandas format.</p>

python sql csv pandas dataframe