lkv lkv - 5 months ago 43
Python Question

python multiline string in a column to row in csv

Need some idea on how to handle this scenario in python
i am using python version 3.5.2

And i am 2 days old in python skills.

I have a csv file with a multiline string in a column

Example: I have 3 column and the 3rd column contains multiline strings

what i have

column1 column2 column3

AppZok | wendy1 | car one\n bike two\n jeep one


enter image description here

The thrid column contains whitespace between car' 'one
and newlinechar between car one\nbike

And below is what i am expecting the output to be.

what i need

AppZok wendy1 car one
AppZok wendy2 bike two
AppZok wendy3 jeep one


enter image description here

This is code i have now and i just print the 3rd column. I know this is not the way may be, but i am keep updating the code by looking for answers

import csv
import re
import string

with open('data_post.csv','r') as csvfile:
lines =csv.reader(csvfile,delimiter=',')
out_file=open('multi_line_out.csv','w')
for x in lines :
#print (x[3])
data_write=csv.writer(out_file,delimiter='\n')
#final_data=x[1]
data_write.writerow(x[2])


And i result i get is

c
a
r

o
n
e
\
n

b
i
k
e

t
w
o
\
n

j
e
e
p

o
n
e


Any help on this is highly appreciated

Answer

The following example code can expand a line of your csv file to multiple lines.

line = ["AppZok", "wendy1", "car one\n bike two\n jeep one"]

def expand_column(line, col):
    new_rows = map(lambda p: p.strip().split(" "), line[col].split("\n"))
    return map(lambda row: line[:col] + row + line[col+1:], new_rows)

if __name__ == '__main__':
    print(list(expand_column(line, 2)))

I think from here on you should be able to do it yourself :)