Parsing and reformatting CSV/text data using Python

Question

sorry if this a bit of a beginner's question, but I haven't had much experience with python, and could really use some help in figuring this out. If there is a better programming language for tackling this, I'd be more than open to hearing it

I'm working on a small project, and I have two blocks of data, formatted differently from each other. They're all spreadsheets saved as CSV files, and I'd really like to make one group match the other without having to manually edit all the data.

What I need to do is go through a CSV, and format any data saved like this:

10W

20E

15-16N

17-18S

To a format like this (respective line to respective format):

10,W

20,E

,,15,16,N

,,17,18,S

So that they can just be copied over when opened as spreadsheets

I'm able to get the files into a string in python, but I'm unsure of how to properly write something to search for a number-hyphen-number-letter format.

I'd be immensely grateful for any help I can get. Thanks

senderle · Accepted Answer · 2012-06-01 20:14:40Z

1

This sounds like a good use-case for regular expressions. Once you've split the lines up into individual strings and stripped the whitespace (using s.strip()) these should work (I'm assuming those are cardinal directions; you'll need to change [NESW] to something else if that assumption is incorrect.):

>>> import re
>>> re.findall('\A(\d+)([NESW])', '16N')
[('16', 'N')]
>>> re.findall('\A(\d+)([NESW])', '15-16N')
[]
>>> re.findall('\A(\d+)-(\d+)([NESW])', '15-16N')
[('15', '16', 'N')]
>>> re.findall('\A(\d+)-(\d+)([NESW])', '16N')
[]

The first regex '\A(\d+)([NESW])' matches only a string that begins with a sequence of digits followed by a capital letter N, E, S, or W. The second matches only a string that begins with a sequence of digits followed by a hyphen, followed by another sequence of digits, followed by a capital letter N, E, S, or W. Forcing it to match at the beginning ensures that these regexes don't match a suffix of a longer string.

Then you can do something like this:

>>> vals = re.findall('\A(\d+)([NESW])', '16N')[0]
>>> ','.join(vals)
'16,N'
>>> vals = re.findall('(\d+)-(\d+)([NESW])', '15-16N')[0]
>>> ',,' + ','.join(vals)
',,15,16,N'

edited Jun 1, 2012 at 20:14

answered Jun 1, 2012 at 20:05

senderle

152k36 gold badges218 silver badges244 bronze badges

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

3 Comments

UpstairsDownstairs Over a year ago

Wow, thanks for the quick response. I'm still trying to wrap my head around it a bit, but I think I've got the idea. I assume once I've gotten the CSV broken down into strings and can run those through the second bit of code you have there, I can have it write every line into a new CSV? I'm still pretty new to this, I just want to make sure I've gotten everything down correctly, haha. Thank you again for the help.

senderle Over a year ago

@UpstairsDownstairs, yes, that sounds like it would work. Also, although I think the approach I've stated here is sufficient, you could use Python's built-in csv module. If the format of your files is more complex than the example text you've given, csv will help you get at the simplified strings more quickly.

UpstairsDownstairs Over a year ago

Thanks! I was playing around with the csv module, so once I get that figured out, I'll use it here. This is great, thanks again for helping me out. I'll probably need to spend a couple hours on it this weekend figuring everything out, but this was immensely helpful. I really appreciate it.

daedalus · Accepted Answer · 2012-06-01 20:15:13Z

1

This is a whole solution that uses regexs. @senderle has beat me to the answer, so feel free to tick his response. This is just added here as I know how difficult it was to wrap my head around re in my code at first.

import re

dash = re.compile('(\d{2})-(\d{2})([WENS])')
no_dash = re.compile( '(\d{2})([WENS])' )

raw = '''10W
20E
15-16N
17-18S'''
lines = raw.split('\n')

data = []

for l in lines:
    if '-' in l:
        match = re.search(dash, l).groups()
        data.append( ',,%s,%s,%s' % (match[0], match[1], match[2] ) )
    else:
        match = re.search(no_dash, l).groups()
        data.append( '%s,%s' % (match[0], match[1] ) )

print '\n'.join(data)

answered Jun 1, 2012 at 20:15

daedalus

10.9k5 gold badges52 silver badges71 bronze badges

Comments

Scharron · Accepted Answer · 2012-06-01 20:03:52Z

0

In your case, I think the quick solution would involve regexps

You can either use the match method to extract your different tokens when they match a given regular expression, or the split method to split your string into tokens given a separator.

However, in your case, the separator would be a single character, so you can use the split method from the str class.

answered Jun 1, 2012 at 20:03

Scharron

17.9k6 gold badges46 silver badges63 bronze badges

Collectives™ on Stack Overflow

Parsing and reformatting CSV/text data using Python

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related