python multiline string in a column to row in csv

Question

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

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

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

Can you post some code that you've worked on?

Mike Lawson
– Mike Lawson

2016-07-03 19:42:31 +00:00
Commented Jul 3, 2016 at 19:42 — Mike Lawson
– Mike Lawson, Commented Jul 3, 2016 at 19:42

Michael Hoff · Accepted Answer · 2016-07-03 21:04:45Z

1

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 :)

edited Jul 3, 2016 at 21:04

answered Jul 3, 2016 at 20:01

Michael Hoff

6,3581 gold badge16 silver badges38 bronze badges

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

3 Comments

lkv Over a year ago

Thank you Michael fro your quick response. i just copied your code and test it and get this msg C:\\data>python test.py <map object at 0x0000000000DCD128>

Michael Hoff Over a year ago

Ah. You are using python3. I updated my answer to cope with that. In essence, list forces python to execute the map operation and gather the elements in a list.

Michael Hoff Over a year ago

To begin with, you can search for "python lambda" in Google. This should bring up quite a few basic tutorials. A "lambda" is nothing more than a anonymous function: It takes some parameters, does some computation and returns some result. In the case of expand_columns you start with looking at line[col].split("\n"). This is the list ["care one", " bike two", " jeep one"]. map applies a function to every element of that list. Here it strips the surrounding whitespaces and splits the string again. From here on, just Google if something interests you and ask specific questions :)

Jeff Mercado · Accepted Answer · 2016-07-03 21:05:17Z

0

Newline characters in CSV are valid characters, there is no escape sequence for them. You need to interpret those literal \n strings as delimiters. Then split the values and build out your rows.

import csv
with open(inpath, 'r') as infile,\
     open(outpath, 'w', newline='') as outfile:
    reader = csv.reader(infile, delimiter=',')
    writer = csv.writer(outfile, delimiter=',')
    for line in reader:
        data = line[:-1]
        for last in line[-1].split(r'\n '):
            writer.writerow(data + last.split(' '))

answered Jul 3, 2016 at 21:05

Jeff Mercado

136k34 gold badges268 silver badges282 bronze badges

Collectives™ on Stack Overflow

python multiline string in a column to row in csv

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related