Split a row into multiple rows in csv based on column values

Question

I have a csv as shown below and need to parse the csv into multiple rows based on value in column 3 to load into db...

Due to restrictions I can use only import csv module to do this function and that is where I am stuck and problem i am facing is if i write a insert query.. it's not fetching all the rows.. it's fetching only the last record in each for loop and inserting into table

1,2,3,4,5
10,20,30,50
100,200,300,400

Possible code:

if column 3 = 'y' else 'n' in column 4 in table

Output:

1,2,3,y
1,2,4,n
1,2,5,n
10,20,30,y
10,20,50,n
100,200,300,y
100,200,400,n

here is my code

import csv

import os

#Test-new to clean.csv
fRead=open("clean.csv")
csv_r=csv.reader(fRead)
#to skip first two lines
leave=0
for record in csv_r:
    if leave<2:
        leave+=1
        continue
    #storing the values of column 3,4,5 as an array
    JMU=[]

    for t in [2, 3, 4]:
        if not(record[t] in ["", "NA"]):
            JMU.append(record[t].strip())
            #print len(JMU)
            #print "2"
    if len(JMU)==0:
        #print "0"
        pass
    else:

#check if the name contains WRK
        isWRK1 = "Table"
        for data in JMU:
            print data
            if data[:3].lower()=="wrk" or data[-3:].lower()=="wrk":
                isWRK1="Work"
                print isWRK
            else:
                isWRK = "table"
        #check if column 2 value is "Yes" or "No"       
        fourthColumn="N"
        if not(record[2] in ["", "NA"]):
            #print record[2]
            if record[3].strip().lower()=="no":
              #  print record[3]
                fourthColumn = "I"
            else:
                fourthColumn = "N"

        for i in JMU:
            iWRK = "Table"
            if record[2]==i:
                newRecord = [record[0], record[1], i, fourthColumn, isWRK,]
                #print newRecord
            elif record[3] == i:


                newRecord = [record[0], record[1], i, "N", isWRK]
                #print newRecord
            else:

                newRecord = [record[0], record[1], i, "N", isWRK]
        print ("insert into table (column_a,column_b,column_c,column_d,column_e) values (%s,%s,%s,%s,%s)"% (record[0],record[1],record[2],record[3],record[4]))



fRead.close()
fWrite.close()

Is there a problem with the solution you have, or are you looking for a place to get started? — Patrick Haugh
– Patrick Haugh, Commented Oct 26, 2016 at 0:39
You need to attempt this yourself. This is not a place for people to write code for you. — bravosierra99
– bravosierra99, Commented Oct 26, 2016 at 0:40
Welcome to StackOverflow. Please read and follow the posting guidelines in the help documentation. on topic and how to ask apply here. StackOverflow is not a coding or tutorial service. From what you've posted, it appears that you don't know how to read a CSV file -- which is something you look up on line. — Prune
– Prune, Commented Oct 26, 2016 at 0:40
If you haven't already, I recommend that you skim the official documentation for the csv module. Pay especial attention to the code examples there: docs.python.org/3/library/csv.html — Patrick Haugh
– Patrick Haugh, Commented Oct 26, 2016 at 0:52
What on Earth is this supposed to mean: if column 3 = 'y else 'n' in column 4 in table? — ShadowRanger
– ShadowRanger, Commented Oct 26, 2016 at 1:50

riteshtch · Accepted Answer · 2016-10-26 02:17:13Z

1

I'm assuming you want to keep the 1st 2 columns as constant and make a new row for every next number present on the same input line.

Initially I came up with this 1-liner awk command:

$ cat data 
1,2,3,4,5
10,20,30,50
100,200,300,400
$ awk -F, -v OFS=, '{for(i=3;i<=NF;i++) print $1, $2, $i, (i==3?"y":"n")}' data 
1,2,3,y
1,2,4,n
1,2,5,n
10,20,30,y
10,20,50,n
100,200,300,y
100,200,400,n

and then I replicated the same into python using the csv module:

import csv
with open('data', 'r') as f:
    reader=csv.reader(f)
    for row in reader:
        l=list(map(int, row))
        for i in range(2, len(l)):
            print(l[0], l[1], l[i], 'y' if i==2 else 'n', sep=',')

and here is a sample run which is same as awk's output:

1,2,3,y
1,2,4,n
1,2,5,n
10,20,30,y
10,20,50,n
100,200,300,y
100,200,400,n

answered Oct 26, 2016 at 2:17

riteshtch

8,7994 gold badges28 silver badges39 bronze badges

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

Collectives™ on Stack Overflow

Split a row into multiple rows in csv based on column values

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