Python CSV multiple numbers in the list

Question

I wrote a python script that store outputs from PostgreSQL in a CSV file, the part of this CSV file looks like,

And I wrote some codes to extract and multiple every second number in the element row by row, for example(look above photo), for the second row, 0.844 * 0.0 = 0; the fourth row, 0.844 * 0.0 * 0.0 * 0.0 = 0,

import sys, os
os.chdir('C:\Users\Heinz\Desktop')
print os.getcwd()

#set up psycopg2 environment
import psycopg2

#driving_distance module
query = """
    select *
    from shortest_path ($$
        select
            gid as id,
            source::int4 as source,
            target::int4 as target,
            pi::double precision as cost,
            pi_rcost::double precision as reverse_cost
        from network
        $$, %s, %s, %s, %s
    )
"""

#make connection between python and postgresql
conn = psycopg2.connect("dbname = 'TC_area' user = 'postgres' host = 'localhost' password = 'xxxx'")
cur = conn.cursor()

#count rows in the table
cur.execute("select count(*) from network")
result = cur.fetchone()
k = result[0] + 1                #number of points = number of segments + 1

#run loops
#import csv module
import csv
import tempfile
import shutil
rs = []
ars = []
i = 1
l = 1
filename = 'test01.csv'
with open(filename, 'wb') as f:
    while i <= k:
        while l <= k:
            cur.execute(query, (i, l, False, True))
            rs.append(cur.fetchall())
            element = list(rs)
            [a[-1] for a in element]
            product = reduce(lambda x,y: x * y, [a[-1] for a in element])
            writer = csv.writer(f, delimiter = ',')
            writer.writerow(product)
            rs = []
            l = l + 1
        l = 1
        i = i + 1


conn.close()

And the code above may create a CSV file,

Because there are always 0 in the last position in every element in every row, I got only 0 in the third column.

How to edit the following 2 lines in my original script？

[a[-1] for a in element]
product = reduce(lambda x,y: x * y, [a[-1] for a in element])

The first line extracts every third element in every subtuple, but I don't want to extract the last one in the last subtuple in a row.

My goal is to multiply every third element in the bracket except the last one if there are more than one bracket in a row, for example, the second row we want to get 0.844 as result; and if there are only one bracket in a row, we assign one for this row.

---

UPDATE#1

I modified my code like this,

#run loops
#import csv module
import csv
import tempfile
import shutil
rs = []
ars = []
i = 1
l = 1
filename = 'test01.csv'
with open(filename, 'wb') as f:
    while i <= k:
        while l <= k:
            cur.execute(query, (i, l, False, True))
            rs.append(cur.fetchall())
            element = list(rs)
            [a[-1] for a in element]
            product = 1 if len(element) == 1 else reduce(lambda x,y: x*y, [a[-1] for a in element[:-1]])
            writer = csv.writer(f, delimiter = ',')
            writer.writerow([product])
            rs = []
            l = l + 1
        l = 1
        i = i + 1

I put "product" in a bracket in the line writer.writerow([product]) in order to prevent the sequence expected error, and I got the CSV file containing 1 in every column,

---

UPDATE#2

I did a little change in the part of my script to test the length of element,

#run loops
#import csv module
import csv
import tempfile

rs = []
i = 1
l = 1
filename = 'test02.csv'
with open(filename, 'wb') as f:
    cur.execute(query, (2, 3, False, True))
    rs.append(cur.fetchall())
    element = list(rs)
    print len(element)
    [a[-1] for a in element]
    product = reduce(lambda x,y: x*y, [a[-1] for a in element[:-1]], 1)
    writer = csv.writer(f, delimiter = ',')
    writer.writerow(element)
    rs = []

conn.close()

And here's the output,

len(element) = 1

Even though there are 2 elements in one row, but python returns len(element) = 1(Can also be proof by clicking field B1 in EXCEL, this field contains nothing)

I guess there are all 1 in the column A in last UPDATE because length of all elements are equal to 1, so the following line can't generate acceptable answers.

product = reduce(lambda x,y: x*y, [a[-1] for a in element[:-1]], 1)

How to edit the script to get the true length of elements？

---

UPDATE#3

I want to get a product matrix directly, thus I wrote this script,

import sys, os
os.chdir('C:\Users\Heinz\Desktop')
#print os.getcwd()

#set up psycopg2 environment
import psycopg2

#pgRouting module
query = """
    select *
    from shortest_path ($$
        select
            gid as id,
            source::int4 as source,
            target::int4 as target,
            pi::double precision as cost,
            pi_rcost::double precision as reverse_cost
        from network
        $$, %s, %s, %s, %s
    )
"""

#make connection between python and postgresql
conn = psycopg2.connect("dbname = 'TC_area' user = 'postgres' host = 'localhost' password = 'xxxx'")
cur = conn.cursor()

#count rows in the table
cur.execute("select count(*) from network")
result = cur.fetchone()
k = result[0] + 1                #number of points = number of segments + 1

#import csv module
import csv
import tempfile
import shutil

#run loops
rs = []
i = 1
l = 1
filename = 'rs.csv'
with open(filename, 'wb') as f:
    writer = csv.writer(f, delimiter = ',')
    while i <= k:
        while l <= k:
            cur.execute(query, (i, l, False, True))
            rs.append(cur.fetchall())
            l = l + 1
        l = 1
        writer.writerow(rs)
        rs = []
        i = i + 1


conn.close()

Here's the look in the CSV file,

This time every field in one row contains the output of the query line,

query, (i, l, False, True)

with different l, for example,

• field A1 contains [(1, -1, 0.0)]
• field B1 contains [(1, 1, 0.844), (2, -1, 0.0)]

So now I need to count how many elements in every field in every row and use the answer script provided by mtadd(thanks) to calculate products.

Can I get the number of element in a field?

Answer 1

After doing:

rs.append(cur.fetchall())
element = list(rs)

The element becomes a list of length 1, with the only item in it being the list of all the rows from the query. You need to change it to:

element = cur.fetchall()

To compute the product use:

product = reduce(lambda x,y: x*y, [a[-1] for a in element[:-1]],1)

Setting the initializer of reduce to 1

Answer 2

Since writerow takes a list as a parameter, the contents of element in your example is actually [[(2, 2, 0.0), (3, -1, 0.0)]], or a list of list of subtuples. The inner list of subtuples is returned from fetchall.

Let's take a look at your code, line-by-line, and what it generates for your example spreadsheet in update 2.

cur.execute(query, (2, 3, False, True))
temp = cur.fetchall()
print temp

This would return a list of tuples [(2,2,0.0), (3,-1,0.0)], what you actually are expecting. The problem starts at:

rs.append(temp)

When you call rs.append, you're encapsulating the list of tuples returned from the database in another list, creating a list of list of tuples, e.g. [[(2,2,0.0), (3,-1,0.0)]]. The outer list has a length of 1, i.e., it contains 1 list of tuples.

element = list(rs)

This creates a new list that is a shallow copy of rs, but otherwise, is the same structure as rs, containing a list of list of tuples.

So, changing your code to something like following should fix your problem:

filename = 'test02.csv'
with open(filename, 'wb') as f:
    cur.execute(query, (2, 3, False, True))
    element = cur.fetchall()
    print len(element)
    if len(element) == 1:
        product = [1]
    else:
        product = reduce(lambda x,y: x*y, [a[-1] for a in element[:-1]], 1)
    writer = csv.writer(f, delimiter = ',')
    writer.writerow(element)