Formatting Results so that They Can be Nicely Imported into a Spreadsheet.

mensanator at aol.com mensanator at aol.com
Sun Aug 5 19:40:56 CEST 2007


On Aug 5, 4:06?am, SMERSH009 <SMERSH0... at gmail.com> wrote:
> On Aug 4, 8:25 pm, "mensana... at aol.com" <mensana... at aol.com> wrote:
>
>
>
>
>
> > On Aug 4, 9:21?pm, "Jim Langston" <tazmas... at rocketmail.com> wrote:
>
> > > <mensana... at aol.com> wrote in message
>
> > >news:1186278638.931477.39760 at z24g2000prh.googlegroups.com...
>
> > > > On Aug 4, 6:35?pm, SMERSH009 <SMERSH0... at gmail.com> wrote:
> > > >> Hi All.
> > > >> Let's say I have some badly formatted text called doc:
>
> > > >> doc=
> > > >> """
> > > >> friendid
> > > >> Female
>
> > > >>                             23 years old
>
> > > >>                             Los Gatos
>
> > > >>                             United States
> > > >> friendid
> > > >> Male
>
> > > >>                             24 years old
>
> > > >>                             San Francisco, California
>
> > > >>                             United States
> > > >> """
>
> > > >> How would I get these results to be displayed in a format similar to:
> > > >> friendid;Female;23 years old;Los Gatos;United States
> > > >> friendid;Male; 24 years old;San Francisco, California;United States
>
> > > >> The latter is a lot easier to organize and can be quickly imported
> > > >> into Excel's column format.
>
> > > >> Thanks Much,
> > > >> Sam
>
> > > > d = doc.split('\n')
>
> > > > f = [i.split() for i in d if i]
>
> > > > g = [' '.join(i) for i in f]
>
> > > > rec = []
> > > > temprec = []
> > > > for i in g:
> > > >    if i:
> > > >        if i == 'friendid':
> > > >            rec.append(temprec)
> > > >            temprec = [i]
> > > >        else:
> > > >            temprec.append(i)
> > > > rec.append(temprec)
>
> > > > output = [';'.join(i) for i in rec if i]
>
> > > > for i in output: print i
>
> > > > ##    friendid;Female;23 years old;Los Gatos;United States
> > > > ##    friendid;Male;24 years old;San Francisco, California;United States
>
> > > also, I would suggest you use CSV format.
>
> > Well, the OP asked for a specific format. One is not
> > always at liberty to change it.
>
> > > CSV stands for "Comma Seperated
> > > Variable" and Excel can load such a sheet directly.
>
> > And Excel can load the shown format directly also,
> > just specify the delimiter.
>
> > > Instead of seperating using ; seperate using ,  Of course, this provides a
> > > problem when there is a , in a string.
>
> > Which explains the popularity of using tabs as delimiters.
> > The data deliverable specification I use at work
> > uses the pipe character | which never appears as data
> > in this particular application.
>
> > > Resolution is to quote the string.
>
> > Which makes the file bigger and isn't necessary
> > when tabs and pipes are used as delimiters.
>
> > > Being such, you can just go ahead and quote all strings.  So you would want
> > > the output to be:
>
> > > "friendid","Female","23 years old","Los Gatos","United States"
> > > "friendid","Male","24 years old","San Francisco, California","United States"
>
> > Which I would do if I had a specification that
> > demanded it or was making files for others. For my
> > own use, I wouldn't bother as it's unnecessary work.
>
> > > Numbers should not be quoted if you wish to treat them as numeric and not
> > > text.
>
> > A good reason not to use quotes at all. Besides which,
> > Excel can handle that also.
>
> Thanks for all your posts guys.
> mensanator's was the most helpful, and I only ended up needing to use
> a few lines from that code.
> The only question that remains for me--and this is just for my
> knowledge-- what does the "if i" mean in this code snippet?
> f = [i.split() for i in d if i]

d is a list of strings, some of which are empty (caused
by the previous split). "if i" evaluates False for
an empty string, so that particular i will not get
split and ends up ommitted from f.

> How is it helpful to leave a dangling "if i"? Why not just f =
> [i.split() for i in d]?

Compare the result of having "if i":
[[], ['friendid'], ['Female'], ['23', 'years', 'old'], ['Los',
'Gatos'], ['United', 'States'], ['friendid'], ['Male'], ['24',
'years', 'old'], ['San', 'Francisco,', 'California'], ['United',
'States']]

to not having it:
>>> ff = [i.split() for i in d]
>>> ff
[[], ['friendid'], ['Female'], [], [], ['23', 'years', 'old'], [], [],
['Los', 'Gatos'], [], [], ['United', 'States'], ['friendid'],
['Male'], [], [], ['24', 'years', 'old'], [], [], ['San',
'Francisco,', 'California'], [], [], ['United', 'States'], []]

All the blank lines became empty lists which actually
wouldn't change anything if they get joined. But that
won't always be the case. Knowing how to use conditionals
in list comprehensions is worth knowing.

Even with the "if i" included, we end up with an
empty list at the start. This because the first "blank"
line wasn't blank, it was a space, so it passes the
"if i" test.

>
> And yes John, this was indeed a "homework question." It was for my
> daughter's preschool. You are going to help her ace her beginner
> Python class! (No, this was not a homework question).




More information about the Python-list mailing list