how to append to empty dataframe in for loop

Question

I'm using pandas as pd and python 2.7

I have a bunch of queries and each query is returning serial numbers. Then I'm using that serial number in a bunch more queries. I want to append all the results to a list. When I append to an empty list, it just returns nothing. I don't know what I'm doing wrong.

 for query in list_of_queries:
     results  = pd.read_sql_query(query,connection,index_col=None)
     for serial_number in results['SerialNumbers']:
        a = []
        new_query = """ SELECT * FROM blah b where b.SerialNumber = '{}' """
        new_query = new_query.format(serial_number)
        results = pd.read_sql_query(new_query,connection,index_col = None)
        a.append(results)

Looks like this is resolved, but I wanted to point something out. Anyway you could run one query that joins the two tables instead of the loop? — Bob Haffner
– Bob Haffner, Commented May 4, 2017 at 2:07

AetherUnbound · Accepted Answer · 2017-05-04 01:08:47Z

1

You are resetting the list to be empty at the beginning of each for loop. This should be:

a = []
for serial_number in results['SerialNumbers']:        
    new_query = """ SELECT * FROM blah b where b.SerialNumber = '{}' """
    new_query = new_query.format(serial_number)
    results = pd.read_sql_query(new_query,connection,index_col = None)
    a.append(results)
# 'a' will now have all the results

Furthermore, it looks like you might be clobbering results because you use it as a variable name twice (once in each loop). I would suggest changing that too!

answered May 4, 2017 at 1:08

AetherUnbound

1,78412 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

how to append to empty dataframe in for loop

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related