More elegant conversion of Postgres data within pandas Dataframe
<p>I have some data in a PostgreSQL table.</p>

<p>I am pulling the data back to a notebook via code like the following:</p>

<pre><code>import numpy as np
import pandas as pd

%load_ext sql
%sql postgresql://foo:foo@localhost:5432/barbar

result_from_sql = %%sql SELECT Date, Year,Score, Cost FROM MyData;
result_df = result_from_sql.DataFrame()
</code></pre>

<p>In the PostgreSQL table all columns were typed accurately but <code>result_df</code> is as follows:</p>

<pre><code>result_df.dtypes

date          object
year          int64
score         object
cost          object
</code></pre>

<p>Converting the date column was fine:</p>

<pre><code>result_df['date'] = pd.to_datetime(result_df['date'])
</code></pre>

<p>As was ensuring all <code>None</code> values are now <code>NaN</code> values:</p>

<pre><code>result_df.replace([None], [np.nan], inplace=True)
</code></pre>

<p>But to convert the columns score &amp; cost to numeric I need to execute the following 3 lines of code:</p>

<pre><code>s = ['score', 'cost']
result_df[s] = pd.to_numeric(result_df[s].astype(str), errors = 'coerce')
result_df[s] = result_df[s].apply(pd.to_numeric, errors='coerce')
</code></pre>

<p>If I use only lines 1 and 2 then the typing is still object - if I use only lines 1 and 3 then all the data is converted to <code>NaN</code> as if all the data has not coerced.</p>

<p>Why do I have to use this code and is there a more elegant solution?</p>

<p>you can use the following solution to parse to numeric:</p>

<hr>

<pre><code>s = ['score', 'cost']

result_df[s] = result_df[s].astype(float) # incase you wanted to parse them to floats 
</code></pre>

<p>let me know if this works </p>

python postgresql pandas