Most efficient way to iterate through list of lists
<p>I'm currently collecting data from quandl and is saved as a list of lists. The list looks something like this (Price data):</p>

<pre><code>['2', 1L, datetime.date(1998, 1, 2), datetime.datetime(2016, 9, 26, 1, 35, 3, 830563), datetime.datetime(2016, 9, 26, 1, 35, 3, 830563), '82.1900', '83.6200', '81.7500', '83.5000', '28.5183', 1286500.0]
</code></pre>

<p>This is typically 1 of about 5000 lists, and every once in awhile Quandl will spit back some <code>NaN</code> values that don't like being saved into the database.</p>

<pre><code>['2', 1L, datetime.date(1998, 1, 2), datetime.datetime(2016, 9, 26, 1, 35, 3, 830563), datetime.datetime(2016, 9, 26, 1, 35, 3, 830563), 'nan', 'nan', 'nan', 'nan', 'nan', 0]
</code></pre>

<p>What would be the most efficient way of iterating through the list of lists to change 'nan' values into zeros?</p>

<p>I know I could do something like this, but it seems rather inefficient. This operation will need to be performed on 11 different values * 5000 different dates * 500 companies:</p>

<pre><code>def screen_data(data):
    new_data = []
    for d in data:
        new_list = []
        for x in d:
            new_value = x
            if math.isNan(x):
                new_value = 0
            new_list.append(new_value)

        new_data.append(new_list)
    return new_data
</code></pre>

<p>I would be interested in any solution that could reduce the time. I know DataFrames might work, but not sure how it would solve the NaN issue.</p>

<p>Or if there is a way to include NaN values in an SQLServer5.6 database along with floats, changing the database is also a viable option.</p>

<p>I haven't timed it but have you tried using <a href="https://docs.python.org/3.5/tutorial/datastructures.html#nested-list-comprehensions" rel="nofollow">nested list comprehension</a> with <a href="https://docs.python.org/3.5/reference/expressions.html#conditional-expressions" rel="nofollow">conditional expressions</a>Â ?</p>

<p>For example:</p>

<pre><code>import datetime

data = [
    ['2', 1, datetime.date(1998, 1, 2),
     datetime.datetime(2016, 9, 26, 1, 35, 3, 830563),
     datetime.datetime(2016, 9, 26, 1, 35, 3, 830563),
     '82.1900', '83.6200', '81.7500', '83.5000',
     '28.5183', 1286500.0],
    ['2', 1, datetime.date(1998, 1, 2),
     datetime.datetime(2016, 9, 26, 1, 35, 3, 830563),
     datetime.datetime(2016, 9, 26, 1, 35, 3, 830563),
     'nan', 'nan', 'nan', 'nan', 'nan', 0],
]

new_data = [[y if str(y).lower() != 'nan' else 0 for y in x] for x in data]

print(new_data)
</code></pre>

<p>I did not use <code>math.isnan(y)</code> because you have to be sure that <code>y</code> is a <a href="https://docs.python.org/3.5/library/functions.html#float" rel="nofollow">float number</a> or you'll get an error. This is much more difficult to do while almost everything has a string representation. But I still made sure that I did the lower case comparison to 'nan' (with <a href="https://docs.python.org/3.5/library/stdtypes.html#str.lower" rel="nofollow">.lower()</a>) since 'NaN' or 'Nan' are legal ways to express "Not a Number". </p>

python performance list mysql-python