How to SPLIT the data in one column and store the values in new column in python3.x csv module functionalities

Question

I'm trying to split the data existing in one column and store that into a new Column

**Inputdata.csv**
Braund, Mr. Owen Harris ,1
Heikkinen, Miss. Laina ,0
Allen, Mr. William Henry ,0
**Expecting_output.csv**
Braund,Owen Harris ,1,Mr
Heikkinen,Laina ,0,Miss
Allen,William Henry ,0,Mr

I have tried and got the Find and replace functionality but unable to code the Find and store it into new column

import csv
print(dir(csv))
filename = "H:\\FairDealCustomerData.csv"
csvout = "H:\\FairDealCustomerDataOUT.csv"
with open(filename,"r",newline='') as file:
     file = ''.join([i for i in file]).replace("Mr.", "")
     file = ''.join([i for i in file]).replace("Miss.", "")
     file = ''.join([i for i in file]).replace("Mrs.", "")
with open(csvout,"w",newline='') as outfile:
     outfile.writelines(file)
     outfile.close()

Inputdata.csv
Braund, Mr. Owen Harris ,1
Heikkinen, Miss. Laina ,0
Allen, Mr. William Henry ,0
 Expecting_output.csv
Braund,Owen Harris ,1,Mr
Heikkinen,Laina ,0,Miss
Allen,William Henry ,0,Mr

score 1 · Accepted Answer · answered Jul 12 '19 at 19:05

Try this, same output as expect you.

import csv

filename = "H:\\FairDealCustomerData.csv"
csvout = "H:\\FairDealCustomerDataOUT.csv"

with open(filename, 'r', newline='') as read_file:
    readCSV = csv.reader(read_file, delimiter=',')
    with open(csvout, 'w', newline='') as write_file:
        writer = csv.writer(write_file)
        for row in readCSV:
            writer.writerow([row[0], row[1].split('.')[1].strip() + ' ', row[2], row[1].split('.')[0].strip()])

score 0 · Answer 2 · answered Jul 12 '19 at 18:53

Using regular expressions to extract the different groups and re-order them as desired.

import re
new_lines = []
with open('inputdata.txt', 'r') as file:
    for line in file:
        regex = re.compile(r'(\w+),\s?(Mr.|Miss.|Mrs.)\s(\w+\s?\w*\s),([01])')
        new_line = regex.findall(line)[0]
        print(f"{new_line[0]},{new_line[2]},{new_line[3]},{new_line[1]}")

Result:

Braund,Owen Harris ,1,Mr.
Heikkinen,Laina ,0,Miss.
Allen,William Henry ,0,Mr.

score 0 · Answer 3 · answered Jul 12 '19 at 21:20

Hooray I got it thanks @Kushan Gunasekera

import csv
filename = "F:\\FairDealCustomerData.csv"
csvout = "F:\\FairDealCustomerDataout.csv"
with open(filename,'r',newline='') as read_file:
    readCSV = csv.reader(read_file, delimiter=',')
    with open(csvout,"w",newline='') as write_file:
        writer = csv.writer(write_file)
        for row in readCSV:
            writer.writerow([row[0], row[1].split('.')[1].strip() + ' ', row[2], 
            row[1].split('.')[0].strip()])
write_file.close()

ALERT:please do care about with and for functionalities other wise we will get 
 ValueError: I/O operation on closed file

he he your're welcome @prasanthG, and one more you don't need to close in when you open with `with` statements. Please check these questions, [Is explicitly closing files important?](https://stackoverflow.com/q/7395542/6194097), [Is close necessary when using iterator on a Python file object](https://stackoverflow.com/q/1832528/6194097) and [Why should I close files in Python?](https://stackoverflow.com/q/25070854/6194097). Got it bro? So you don't need to close it. — Kushan Gunasekera, Jul 13 '19 at 02:49

How to SPLIT the data in one column and store the values in new column in python3.x csv module functionalities

3 Answers3