Python Forum
Reading csv with multiple "headers"
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Reading csv with multiple "headers"
#1
Greetings,

I have a csv file with an odd format. It consists of a number of 10 blocks.
The first line of the block has a "header" 'Grid-ref=X(int), Y(int)
Each of the nine rows, has 12 columns with spaces, not a ',' separator.

### Python used to read
for blocks in datareader:
print(blocks)
##PRINTS Nested List> seperator = '' not ','
#csv structure is:

['Grid-ref= 1', ' 148']
[' 3020 2820 3040 2880 1740 1360 980 990 1410 1770 2580 2630']
[' 3020 2820 3040 2880 1740 1360 980 990 1410 1770 2580 2630']
[' 3020 2820 3040 2880 1740 1360 980 990 1410 1770 2580 2630']
[' 3020 2820 3040 2880 1740 1360 980 990 1410 1770 2580 2630']
[' 3020 2820 3040 2880 1740 1360 980 990 1410 1770 2580 2630']
[' 3020 2820 3040 2880 1740 1360 980 990 1410 1770 2580 2630']
[' 3020 2820 3040 2880 1740 1360 980 990 1410 1770 2580 2630']
[' 3020 2820 3040 2880 1740 1360 980 990 1410 1770 2580 2630']
[' 3020 2820 3040 2880 1740 1360 980 990 1410 1770 2580 2630']
[' 3020 2820 3040 2880 1740 1360 980 990 1410 1770 2580 2630']
['Grid-ref= 1', ' 311']
[' 490 290 280 230 200 250 440 530 460 420 530 450']
[' 490 290 280 230 200 250 440 530 460 420 530 450']
[' 490 290 280 230 200 250 440 530 460 420 530 450']
[' 490 290 280 230 200 250 440 530 460 420 530 450']
[' 490 290 280 230 200 250 440 530 460 420 530 450']
[' 490 290 280 230 200 250 440 530 460 420 530 450']
[' 490 290 280 230 200 250 440 530 460 420 530 450']
[' 490 290 280 230 200 250 440 530 460 420 530 450']
[' 490 290 280 230 200 250 440 530 460 420 530 450']
[' 490 290 280 230 200 250 440 530 460 420 530 450']
['Grid-ref= 1', ' 312']
[' 460 280 260 220 190 240 430 520 450 400 520 410']
[' 460 280 260 220 190 240 430 520 450 400 520 410']
[' 460 280 260 220 190 240 430 520 450 400 520 410']
[' 460 280 260 220 190 240 430 520 450 400 520 410']
[' 460 280 260 220 190 240 430 520 450 400 520 410']
[' 460 280 260 220 190 240 430 520 450 400 520 410']
[' 460 280 260 220 190 240 430 520 450 400 520 410']
[' 460 280 260 220 190 240 430 520 450 400 520 410']
[' 460 280 260 220 190 240 430 520 450 400 520 410']
[' 460 280 260 220 190 240 430 520 450 400 520 410']


#### Python used to read
for blocks in datareader:
for line in blocks:
print(line)
#### PRINTS the lines >> seperator = '' not ','
# csv structure:

Grid-ref= 1
148
3020 2820 3040 2880 1740 1360 980 990 1410 1770 2580 2630
3020 2820 3040 2880 1740 1360 980 990 1410 1770 2580 2630
3020 2820 3040 2880 1740 1360 980 990 1410 1770 2580 2630
3020 2820 3040 2880 1740 1360 980 990 1410 1770 2580 2630
3020 2820 3040 2880 1740 1360 980 990 1410 1770 2580 2630
3020 2820 3040 2880 1740 1360 980 990 1410 1770 2580 2630
3020 2820 3040 2880 1740 1360 980 990 1410 1770 2580 2630
3020 2820 3040 2880 1740 1360 980 990 1410 1770 2580 2630
3020 2820 3040 2880 1740 1360 980 990 1410 1770 2580 2630
3020 2820 3040 2880 1740 1360 980 990 1410 1770 2580 2630
Grid-ref= 1
311
490 290 280 230 200 250 440 530 460 420 530 450
490 290 280 230 200 250 440 530 460 420 530 450
490 290 280 230 200 250 440 530 460 420 530 450
490 290 280 230 200 250 440 530 460 420 530 450
490 290 280 230 200 250 440 530 460 420 530 450
490 290 280 230 200 250 440 530 460 420 530 450
490 290 280 230 200 250 440 530 460 420 530 450
490 290 280 230 200 250 440 530 460 420 530 450
490 290 280 230 200 250 440 530 460 420 530 450
490 290 280 230 200 250 440 530 460 420 530 450
Grid-ref= 1
312
460 280 260 220 190 240 430 520 450 400 520 410
460 280 260 220 190 240 430 520 450 400 520 410
460 280 260 220 190 240 430 520 450 400 520 410
460 280 260 220 190 240 430 520 450 400 520 410
460 280 260 220 190 240 430 520 450 400 520 410
460 280 260 220 190 240 430 520 450 400 520 410
460 280 260 220 190 240 430 520 450 400 520 410
460 280 260 220 190 240 430 520 450 400 520 410
460 280 260 220 190 240 430 520 450 400 520 410
460 280 260 220 190 240 430 520 450 400 520 410

The data structure is:
Block 1
(n = 10 rows) (n = 12 columns)
Jan Feb Mar Apr May Apr May Jun Jul Aug Sept Oct Nov Dec
Grid-ref= Int, int
1991 int int int int int int int int int int int int
1992 int int int int int int int int int int int int
1993 int int int int int int int int int int int int
1994 int int int int int int int int int int int int
1995 int int int int int int int int int int int int
1996 int int int int int int int int int int int int
1997 int int int int int int int int int int int int
1998 int int int int int int int int int int int int
1999 int int int int int int int int int int int int
2000 int int int int int int int int int int int int


I want to insert each block into a table.

I want to loop through each block of 10 rows + 12 columns and insert into (Data format ):
From Jan-1991 to Dec-2000
Index X(Int), Y(int) Jan-1991 Feb-1991 Mar-1991 … Dec-1991 Jan-1991 Feb-1991 Mar-1991 … Dec-1992 … Dec-2000
1 int int int int int int int int int int (n)int
2 int int int int int int int int int int (n)int
n int int int int int int int int int int (n)int


I have read the csv file but cannot loop through each block, and insert into the table??
Any assistance is appreciated!!

import csv
import os, sys
from itertools import islice
import re
import pandas as  pd
## sys.setdefaultencoding('utf-8')

table = []

csvfile = open('cru-ts-2-10-1991-2000-cutdown.csv','r')
datareader = csv.reader(csvfile)     ###   datareader = csv.reader(csvfile, delimiter=',')
for blocks in datareader:
	#print(blocks)   #### PRINTS Nested List  >> seperator = '' not ','
	#for i in blocks[0]:
		#print(i)
	for line in blocks:
		print(line)   ####  PRINTS the lines

## Prints the index, not the 
##for line in range(10):
##	print(line)
	

df = pd.DataFrame(table)
##print(df)  #### Empty table
Reply
#2
Since the file is oddly formatted I suggest that you do not use csv.
Instead just read the file line by line in your expected pattern.
Copy each line into a new list and insert your new values in their correct places.
Then dump the list of lines into a new file or back into the original file as needed.
Reply
#3
Hi,

That is the question that I was asking..
Reply
#4
(Jun-04-2020, 02:33 PM)Clives Wrote: Hi,

That is the question that I was asking..

Did you find answer to your question? I have similar problem and I am breaking my head to find out how to do it? Can you help with that? Thanks in advance
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  How to store columns of a .csv in variables (when the .csv has no headers) ? hobbyist 6 1,152 Aug-18-2023, 02:06 PM
Last Post: deanhystad
  TreeView column headers TWB 2 1,975 Jan-29-2023, 02:13 PM
Last Post: TWB
  export sql table to csv using BCP with headers mg24 0 684 Jan-19-2023, 05:36 AM
Last Post: mg24
  Code changing rder of headers Led_Zeppelin 0 892 Jul-13-2022, 05:38 PM
Last Post: Led_Zeppelin
  Reading Multiple text Files in pyhton Fatim 1 1,886 Jun-25-2021, 01:37 PM
Last Post: deanhystad
  Request Headers (scheme) JohnnyCoffee 0 1,891 Mar-31-2021, 09:17 PM
Last Post: JohnnyCoffee
  Reading Multiple Lists Using SUM function dgrunwal 6 3,285 Jun-03-2020, 08:23 PM
Last Post: dgrunwal
  Reading multiple raw_inputs bindo 5 4,431 Apr-19-2017, 08:47 PM
Last Post: volcano63

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020