English 中文(简体)
I am getting this sort of CSV data while making Http request to the CSV file. Very malformed string [closed]
原标题:
  • 时间:2009-11-16 13:35:43
  •  标签:
  • python
  • csv

I am getting this sort of CSV data while making Http request to the CSV file. Very malformed string.

response =  "Subject";"Start Date";"Start Time";"End Date";"End Time";"All day event";"Description""Play football";"16/11/2009";"10:00 PM";"16/11/2009";"11:00 PM";"false";"""Watch 2012";"20/11/2009";"07:00 PM";"20/11/2009";"08:00 PM";"false";""  

And i want to convert this into list of dictionary

[{"Subject": "Play football", "Start Date": "16/11/2009", "Start Time": "10:00 PM", "End Date": "16/11/2009", "End Time": "11:00 PM", "All day event", false, "Description": ""},
 {"Subject": "Watch 2012", "Start Date": "20/11/2009", "Start Time": "07:00 PM", "End Date": "20/11/2009", "End Time": "08:00 PM", "All day event", false, "Description": ""}]

I tried solving this using python csv module but didn t work.

import csv
from cStringIO import StringIO

>>> str_obj = StringIO(response)
>>> reader = csv.reader(str_obj, delimiter= ; )
>>> [x for x in reader] 
    [[ Subject ,
       Start Date ,
       Start Time ,
       End Date ,
       End Time ,
       All day event ,
       Description"Play football ,
       16/11/2009 ,
       10:00 PM ,
       16/11/2009 ,
       11:00 PM ,
       false ,
       "Watch 2012 ,
       20/11/2009 ,
       07:00 PM ,
       20/11/2009 ,
       08:00 PM ,
       false ,
        ]]

I get the above result.

Any sort of help will be appreciated. Thanks in advance.

最佳回答

Here s a pyparsing solution:

from pyparsing import QuotedString, Group, delimitedList, OneOrMore

# a row of headings or data is a list of quoted strings, delimited by  ; s
qs = QuotedString( " )
datarow = Group(delimitedList(qs,  ; ))

# an entire data set is a single data row containing the headings, followed by
# one or more data rows containing the data
dataset_parser = datarow("headings") + OneOrMore(datarow)("rows")

# parse the returned response
data = dataset_parser.parseString(response)

# create dict by zipping headings with each row s data values
datadict = [dict(zip(data.headings, row)) for row in data.rows]

print datadict

Prints:

[{ End Date :  16/11/2009 ,  Description :   ,  All day event :  false , 
   Start Time :  10:00 PM ,  End Time :  11:00 PM ,  Start Date :  16/11/2009 , 
   Subject :  Play football }, 
 { End Date :  20/11/2009 ,  Description :   ,  All day event :  false , 
   Start Time :  07:00 PM ,  End Time :  08:00 PM ,  Start Date :  20/11/2009 , 
   Subject :  Watch 2012 }]

This will also handle the case if the quoted strings contain embedded semicolons.

问题回答

Here s one approach.

I notice there is no delimiter between rows. In an effort to clean up the input data, I make a few assumptions:

  • The first "row" is the "heading" of a "table", these will be our dictionary keys
  • There are no empty fields in the first row (ie: no "")
  • Any other field can be empty (ie: "")
  • The first occurrence of two successive " indicates the end of the heading row

First I create a response based on your input string:

>>> response =  "Subject";"Start Date";"Start Time";"End Date";"End Time";"All day event";"Description""Play football";"16/11/2009";"10:00 PM";"16/11/2009";"11:00 PM";"false";"""Watch 2012";"20/11/2009";"07:00 PM";"";"08:00 PM";"false";"""";"17/11/2009";"9:00 AM";"17/11/2009";"10:00 AM";"false";""     

Note that

  • the "End Date" for "Watch 2012" is empty
  • there is a third event with an empty "Subject" heading

These two modifications illustrate some "edge cases" I m concerned about.

First I will replace all occurrences of two consecutive " with a pipe (|) and strip out all other " characters because I don t need them:

>>> response.replace( "" ,  | ).replace( " ,   )
 Subject;Start Date;Start Time;End Date;End Time;All day event;Description|Play football;16/11/2009;10:00 PM;16/11/2009;11:00 PM;false;|Watch 2012;20/11/2009;07:00 PM;|;08:00 PM;false;||;17/11/2009;9:00 AM;17/11/2009;10:00 AM;false;| 

If we had any empty cells not at the start or end of a row (ie: Watch 2012 s End Date), it looks like this: ;|; -- let s simply leave it blank:

>>> response.replace( "" ,  | ).replace( " ,   ).replace( ;|; ,  ;; )
 Subject;Start Date;Start Time;End Date;End Time;All day event;Description|Play football;16/11/2009;10:00 PM;16/11/2009;11:00 PM;false;|Watch 2012;20/11/2009;07:00 PM;;08:00 PM;false;||;17/11/2009;9:00 AM;17/11/2009;10:00 AM;false;| 

Now the | indicates the split between the heading row and the next row. What happens if we split our string on |?

>>> response.replace( "" ,  | ).replace( " ,   ).replace( ;|; ,  ;; ).split( | )
[ Subject;Start Date;Start Time;End Date;End Time;All day event;Description ,
  Play football;16/11/2009;10:00 PM;16/11/2009;11:00 PM;false; ,
  Watch 2012;20/11/2009;07:00 PM;;08:00 PM;false; ,
   ,
  ;17/11/2009;9:00 AM;17/11/2009;10:00 AM;false; ,
   ]

Looks like we re getting somewhere. There s a problem, though; there are two items in that list that are just the empty string . They re there because we sometimes have a | at the end of a row and the beginning of the next row, and splitting creates an empty element:

>>> "a|b||c".split( | )
[ a ,  b ,   ,  c ]

Same goes for a lone delimited at the end of a line, too:

>>> "a||b|c|".split( | )
[ a ,   ,  b ,  c ,   ]

Let s filter our list to drop those empty "rows":

>>> rows = [row for row in response.replace( "" ,  | ).replace( " ,   ).replace( ;|; ,  ;; ).split( | ) if row]
>>> rows
[ Subject;Start Date;Start Time;End Date;End Time;All day event;Description ,
  Play football;16/11/2009;10:00 PM;16/11/2009;11:00 PM;false; ,
  Watch 2012;20/11/2009;07:00 PM;;08:00 PM;false; ,
  ;17/11/2009;9:00 AM;17/11/2009;10:00 AM;false; ]

That s it for massaging the input; now we just need to build the dictionary. First, let s get the dictionary keys:

>>> dict_keys = rows[0].split( ; )
>>> dict_keys
[ Subject ,
  Start Date ,
  Start Time ,
  End Date ,
  End Time ,
  All day event ,
  Description ]

And build a list of dictionaries, one for each event:

>>> import itertools
>>> events = []
>>> for row in rows[1:]:
...     d = {}
...     for k, v in itertools.izip(dict_keys, row.split( ; )):
...         d[k] = v
...     events.append(d)
... 
>>> events
[{ All day event :  false ,
   Description :   ,
   End Date :  16/11/2009 ,
   End Time :  11:00 PM ,
   Start Date :  16/11/2009 ,
   Start Time :  10:00 PM ,
   Subject :  Play football },
 { All day event :  false ,
   Description :   ,
   End Date :   ,
   End Time :  08:00 PM ,
   Start Date :  20/11/2009 ,
   Start Time :  07:00 PM ,
   Subject :  Watch 2012 },
 { All day event :  false ,
   Description :   ,
   End Date :  17/11/2009 ,
   End Time :  10:00 AM ,
   Start Date :  17/11/2009 ,
   Start Time :  9:00 AM ,
   Subject :   }]

Hope that helps!

Some notes:

  • if you expect | to appear in your data, you might want to encode it first; or use a different delimiter
  • supporting quotes in the data might be tricky (ie: Subject : Watching "2012" )
  • I leave conversion of All day event values from string to boolean as an exercise to the reader :D

Are you sure, you got this response.

Looks corrupted to me. In this case, no reader will be able to make sense of it.

First fix the response, then parsing will be better ....

response = response.split( ; ) # split it into words
response = [w[1:-1] for w in response] # strip off the quotes 
response = [w.replace( "" , "
" ) for w in response] # add in the newlines
response = [ "%s" %w for w in response] # add the quotes back
response =  ; .join(response) 

But it won t work if you have a ";" character in the data that should have been escaped. You should find what happened to the missing newlines in the first place.





相关问题
Can Django models use MySQL functions?

Is there a way to force Django models to pass a field to a MySQL function every time the model data is read or loaded? To clarify what I mean in SQL, I want the Django model to produce something like ...

An enterprise scheduler for python (like quartz)

I am looking for an enterprise tasks scheduler for python, like quartz is for Java. Requirements: Persistent: if the process restarts or the machine restarts, then all the jobs must stay there and ...

How to remove unique, then duplicate dictionaries in a list?

Given the following list that contains some duplicate and some unique dictionaries, what is the best method to remove unique dictionaries first, then reduce the duplicate dictionaries to single ...

What is suggested seed value to use with random.seed()?

Simple enough question: I m using python random module to generate random integers. I want to know what is the suggested value to use with the random.seed() function? Currently I am letting this ...

How can I make the PyDev editor selectively ignore errors?

I m using PyDev under Eclipse to write some Jython code. I ve got numerous instances where I need to do something like this: import com.work.project.component.client.Interface.ISubInterface as ...

How do I profile `paster serve` s startup time?

Python s paster serve app.ini is taking longer than I would like to be ready for the first request. I know how to profile requests with middleware, but how do I profile the initialization time? I ...

Pragmatically adding give-aways/freebies to an online store

Our business currently has an online store and recently we ve been offering free specials to our customers. Right now, we simply display the special and give the buyer a notice stating we will add the ...

Converting Dictionary to List? [duplicate]

I m trying to convert a Python dictionary into a Python list, in order to perform some calculations. #My dictionary dict = {} dict[ Capital ]="London" dict[ Food ]="Fish&Chips" dict[ 2012 ]="...

热门标签