fixing an horrific formatted csv file.

F.R. anthra.norell at bluewin.ch
Tue Jul 1 22:49:14 CEST 2014


On 07/01/2014 04:04 PM, flebber wrote:
> What I am trying to do is to reformat a csv file into something more usable.
> currently the file has no headers, multiple lines with varying columns that are not related.
>
> This is a sample
>
> Meeting,05/07/14,RHIL,Rosehill Gardens,Weights,TAB,+3m Entire Circuit,          ,
> Race,1,CIVIC STAKES,CIVIC,CIVIC,1350,~         ,3U        ,~         ,QLT       ,54,0,0,5/07/2014,,          ,          ,          ,          ,No class restriction, Quality, For Three-Years-Old and Upwards, No sex restriction, (Listed),Of $100000. First $60000, second $20000, third $10000, fourth $5000, fifth $2000, sixth $1000, seventh $1000, eighth $1000
> Horse,1,Bennetta,0,"Grahame Begg",Randwick,,0,0,16-3-1-3 $390450.00,,0,0,0,,98.00,M,
> Horse,2,Breakfast in Bed,0,"David Vandyke",Warwick Farm,,0,0,20-6-1-5 $201250.00,,0,0,0,,81.00,M,
> Horse,3,Capital Commander,0,"Gerald Ryan",Rosehill,,0,0,43-9-9-3 $438625.00,,0,0,0,,85.00,M,
> Horse,4,Coup Ay Tee (NZ),0,"Chris Waller",Rosehill,,0,0,35-9-6-5 $519811.00,,0,0,0,,101.00,G,
> Horse,5,Generalife,0,"John O'Shea",Warwick Farm,,0,0,19-6-1-3 $235045.00,,0,0,0,,87.00,G,
> Horse,6,He's Your Man (FR),0,"Chris Waller",Rosehill,,0,0,13-2-3-1 $108110.00,,0,0,0,,93.00,G,
> Horse,7,Hidden Kisses,0,"Chris Waller",Rosehill,,0,0,40-8-8-5 $565750.00,,0,0,0,,96.00,M,
> Horse,8,Oakfield Commands,0,"Gerald Ryan",Rosehill,,0,0,22-7-4-6 $269530.00,,0,0,0,,94.00,G,
> Horse,9,Taxmeifyoucan,0,"Gregory Hickman",Warwick Farm,,0,0,18-2-4-4 $539730.00,,0,0,0,,91.00,G,
> Horse,10,The Peak,0,"Bart & James Cummings",Randwick,,0,0,15-6-1-0 $426732.00,,0,0,0,,95.00,G,
> Horse,11,Tougher Than Ever (NZ),0,"Chris Waller",Rosehill,,0,0,17-3-2-3 $321613.00,,0,0,0,,97.00,H,
> Horse,12,TROMSO,0,"Chris Waller",Rosehill,,0,0,47-8-11-2 $622300.00,,0,0,0,,103.00,G,
> Race,2,FLYING WELTER - BENCHMARK 95 HCP,BM95,BM95,1100,BM95      ,3U        ,~         ,HCP       ,54,0,0,5/07/2014,,          ,          ,          ,          ,BenchMark 95, Handicap, For Three-Years-Old and Upwards, No sex restriction,Of $85000. First $48750, second $16750, third $8350, fourth $4150, fifth $2000, sixth $1000, seventh $1000, eighth $1000, ninth $1000, tenth $1000
> Horse,1,Big Bonanza,0,"Don Robb",Wyong,,0,57.5,31-9-4-3 $366860.00,,0,0,0,,92.00,G,
> Horse,2,Casual Choice,0,"Joseph Pride",Warwick Farm,,0,54,8-2-3-0 $105930.00,,0,0,0,
>
> So what I am trying to so is end up with an output like this.
>
> Meeting, Date, Race, Number, Name, Trainer, Location
> Rosehill, 05/07/14, 1, 1,Bennetta,"Grahame Begg",Randwick,
> Rosehill, 05/07/14, 1, 2,Breakfast in Bed,"David Vandyke",Warwick Farm,
>
> So as a start i thought i would try inserting the Meeting and Race number however I am just not getting it right.
>
> import csv
>
> outfile = open("/home/sayth/Scripts/cleancsv.csv", "w")
> with open('/home/sayth/Scripts/test.csv') as f:
>      f_csv = csv.reader(f)
>      headers = next(f_csv)
>      for row in f_csv:
>          meeting = row[3] in row[0] == 'Meeting'
>          new = row.insert(0, meeting)
>          while row[1] in row[0] == 'Race' < 9:  # pref less than next found row[0]
>
>              # grab row[1] as id number
>              id = row[1]
>              # from row[0] and insert it in first position
>              new_lines = new.insert(1, id)
>              outfile.write(new_lines)
>          outfile.close()
>
> How should I go about this?
>
> Thanks
>
> Sayth

Reformatting is what I do most and over time I have acquired some 
practice. Complete solutions are not
often proposed, possibly sneered on for their officiousness. In that 
case I apologize. I couldn't resist. It is such a nice example. Having 
solved it, I figure why not share it . . .

Frederic

------------------------------------------------------------------------------------------------------------

def race_table (csv_text):
     input_table = [[item.strip(' "') for item in record.split (',')] 
for record in csv_text.splitlines ()]
     # At this point look at input_table to find the record indices
     output_table = []
     for record in input_table:
         if record [0] == 'Meeting':
             meeting = record [3]
         elif record [0] == 'Race':
             date = record [13]
             race = record [1]
         elif record [0] == 'Horse':
             number = record [1]
             name = record [2]
             trainer = record [4]
             location = record [5]
             output_table.append ((meeting, date, race, number, name, 
trainer, location))
     return output_table


 >>> for record in race_table (your_csv_text): print record

('Rosehill Gardens', '5/07/2014', '1', '1', 'Bennetta', 'Grahame Begg', 
'Randwick')
('Rosehill Gardens', '5/07/2014', '1', '2', 'Breakfast in Bed', 'David 
Vandyke', 'Warwick Farm')
('Rosehill Gardens', '5/07/2014', '1', '3', 'Capital Commander', 'Gerald 
Ryan', 'Rosehill')
('Rosehill Gardens', '5/07/2014', '1', '4', 'Coup Ay Tee (NZ)', 'Chris 
Waller', 'Rosehill')
('Rosehill Gardens', '5/07/2014', '1', '5', 'Generalife', "John O'Shea", 
'Warwick Farm')
('Rosehill Gardens', '5/07/2014', '1', '6', "He's Your Man (FR)", 'Chris 
Waller', 'Rosehill')
('Rosehill Gardens', '5/07/2014', '1', '7', 'Hidden Kisses', 'Chris 
Waller', 'Rosehill')
('Rosehill Gardens', '5/07/2014', '1', '8', 'Oakfield Commands', 'Gerald 
Ryan', 'Rosehill')
('Rosehill Gardens', '5/07/2014', '1', '9', 'Taxmeifyoucan', 'Gregory 
Hickman', 'Warwick Farm')
('Rosehill Gardens', '5/07/2014', '1', '10', 'The Peak', 'Bart & James 
Cummings', 'Randwick')
('Rosehill Gardens', '5/07/2014', '1', '11', 'Tougher Than Ever (NZ)', 
'Chris Waller', 'Rosehill')
('Rosehill Gardens', '5/07/2014', '1', '12', 'TROMSO', 'Chris Waller', 
'Rosehill')
('Rosehill Gardens', '5/07/2014', '2', '1', 'Big Bonanza', 'Don Robb', 
'Wyong')
('Rosehill Gardens', '5/07/2014', '2', '2', 'Casual Choice', 'Joseph 
Pride', 'Warwick Farm')

 >>> TM = TX.Table_Maker (headings = 
('Meeting','Date','Race','Number','Name','Trainer','Location'))
 >>> TM (race_table (your_csv_text)).write ()

  Meeting          | Date      | Race | Number | Name                   
| Trainer               | Location     |

  Rosehill Gardens | 5/07/2014 |    1 |      1 | Bennetta               
| Grahame Begg          | Randwick     |
  Rosehill Gardens | 5/07/2014 |    1 |      2 | Breakfast in Bed       
| David Vandyke         | Warwick Farm |
  Rosehill Gardens | 5/07/2014 |    1 |      3 | Capital Commander      
| Gerald Ryan           | Rosehill     |
  Rosehill Gardens | 5/07/2014 |    1 |      4 | Coup Ay Tee (NZ)       
| Chris Waller          | Rosehill     |
  Rosehill Gardens | 5/07/2014 |    1 |      5 | Generalife             
| John O'Shea           | Warwick Farm |
  Rosehill Gardens | 5/07/2014 |    1 |      6 | He's Your Man (FR)     
| Chris Waller          | Rosehill     |
  Rosehill Gardens | 5/07/2014 |    1 |      7 | Hidden Kisses          
| Chris Waller          | Rosehill     |
  Rosehill Gardens | 5/07/2014 |    1 |      8 | Oakfield Commands      
| Gerald Ryan           | Rosehill     |
  Rosehill Gardens | 5/07/2014 |    1 |      9 | Taxmeifyoucan          
| Gregory Hickman       | Warwick Farm |
  Rosehill Gardens | 5/07/2014 |    1 |     10 | The Peak               
| Bart & James Cummings | Randwick     |
  Rosehill Gardens | 5/07/2014 |    1 |     11 | Tougher Than Ever (NZ) 
| Chris Waller          | Rosehill     |
  Rosehill Gardens | 5/07/2014 |    1 |     12 | TROMSO                 
| Chris Waller          | Rosehill     |
  Rosehill Gardens | 5/07/2014 |    2 |      1 | Big Bonanza            
| Don Robb              | Wyong        |
  Rosehill Gardens | 5/07/2014 |    2 |      2 | Casual Choice          
| Joseph Pride          | Warwick Farm |





More information about the Python-list mailing list