CODEWITHSUNDEEP
pythoncsvimport-from-csv
JPKab
JPKab

Reputation: 231

Split delimited string/cell value into multiple rows

I can solve this problem in many ways, but I want to use Python. Here is the question:

I have a CSV file which contains values like so:

Name         Address                 Skills

Napoleon     Preston, Idaho          Bowhunting, Computer Hacking, Drawing Unicorns

I want to transform it to this: 

Name         Address                 Skill 
Napoleon     Preston, Idaho          Bowhunting
Napoleon     Preston, Idaho          Computer Hacking
Napoleon     Preston, Idaho          Drawing Unicorns

I have researched, and I've read up on the CSV library, but I feel like I'll end up doing something very ugly and overly complicated.

Upvotes: 3

Views: 3519

Answers (1)

dm03514
dm03514

Reputation: 55942

Right now you have a flat file, I believe the first step to manipulating your data is to load it into python so that you can begin to use it.

You're on the right track. The csv module was designed for this

Assuming your data is tab separated

import csv

with open('your_csv.csv') as f:
  f.readline() # these are headings should remove them
  csv_reader = csv.reader(f, delimiter='\t')
  for line_list in csv_reader:
     # line_list is a list of each row of your csv       
     # line_list[0] Contains 'Name' data   'Napolean'
     # line_list[1] Cotinas Address 'Preston, Idaho'
     # line_list[2] Contains skills 'Bowhunting, Computer Hacking, Drawing Unicorns'
     skills_list = [x.strip() for x in line_list[2].split(',')]
     for skill in skills_list:
        #line_list[0]   line_list[1],   skill


        Napoleon     Preston, Idaho          Bowhunting
        Napoleon     Preston, Idaho          Computer Hacking
        Napoleon     Preston, Idaho          Drawing Unicorns

Upvotes: 4

Related Questions