CODEWITHSUNDEEP
python-3.xcsvmultiple-columns
prasanthG
prasanthG

Reputation: 25

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

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

Upvotes: 1

Views: 69

Answers (3)

prasanthG
prasanthG

Reputation: 25

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

Upvotes: 0

Kushan Gunasekera
Kushan Gunasekera

Reputation: 8566

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()])

Upvotes: 1

TomNash
TomNash

Reputation: 3288

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.

Upvotes: 0

Related Questions