CODEWITHSUNDEEP
pythonpostgresqlcsv
Heinz
Heinz

Reputation: 2467

Python CSV to extract and time values from element

I write a python script to store output of pgRouting(in PostgreSQL) in a CSV file, and the code and part of CSV file looks like,

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 = []
i = 1
l = 1
filename = 'test.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)
            writer = csv.writer(f, delimiter = ',')
            writer.writerow(element)
            rs = []
            l = l + 1
        l = 1
        i = i + 1

conn.close()

enter image description here

Now I want to do 3 things,

  1. extract every second object in the bracket from the element in every row, like 0.0 in the first row and 0.844, 0.0 in the second row, ...etc.
  2. times every object extracted from every row, take the second row as an example, 0.844 * 0.0 = 0, so there will be one result in each row.
  3. write these products in the CSV file

How to do this？ Can I do this task in just one step？

I am using python 2.7.4 and PostgreSQL 8.4 under Windows 8.1 x64.

UPDATE#1 I added the following 2 lines in my script (thanks, shaktimaan),

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

and here's the part of the output,

enter image description here

Upvotes: 0

Views: 135

Answers (1)

shaktimaan
shaktimaan

Reputation: 12092

In essence all you need to know is how to multiple all elements in a list. That can be done using the reduce() method like this:

reduce(lambda x,y: x * y, a_list)

Now, looking at your code, each entry in your csv is generated by the statement:

element = list(rs)

As an example, element can have this:

[(1, -1, 0.0), (1, 1, 0.844), (2, -1, 0)]

You need to multiply the third number of each of those inner tuples. So, create a list on the fly that extracts the third element of every sub tuple

[a[-1] for a in element]

Now plug this in the reduce() from earlier:

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

This prints 0.0 for the above example. Now that you have the product, use that as and how you want to print it in the output CSV.

Hope it helps.

Upvotes: 1

Related Questions