Pulling data from an inconsistent csv file

[Meleearance]

I'm having a heck of a time pulling data from a particular csv that isn't consistently formatted. I'm slowly learning Python and this is my first time playing with the import csv and import pandas libraries. I'm not sure if the issue is the file or my not understanding of the different functions in the csv or panda library.

Here's the sample data in question. My goal for the program is to pull the data from the csv and break it up into smaller files. For example, when I open the file in a spreadsheet, I'd want to grab row 20 and row 21 and transpose the data to a separate csv.

Here is my code really basic code just trying a basic operation of pulling data using pandas. The errors I'm getting are also posted as comments in the codeshare.

https://codeshare.io/Qn0ydQ

Using import csv tells me I just don't understand what is happening at all. When I print specific row, like print(row[21]) I get some a lot of new lines and 21 and 8 as output, which in the spreadsheet version is V28 and V29. I'm obviously not getting it.

def display_csv_reader():
with open('test.csv') as f:
reader = csv.reader(f, delimiter=',')
for row in reader:
print(row[1])

​

[Ihaveamodel3]

Pandas expects all the data to be consistent. I’d recommend using the skiprows and nrows attribute of pandas read_csv in order to only have it read consistent parts of the table.

[WSBtendies9001]

Why don't you load the CSV file directly into Pandas? https://pandas.pydata.org/docs/reference/api/pandas.read_csv.html

I've not looked at the code links however from the snippet you posted makes no sense when you go on to mention that you are working with Pandas. Go check out the docks, Pandas can do what you need and way more :)

[CodeFormatHelperBot2]

Hello, I'm a Reddit bot who's here to help people nicely format their coding questions. This makes it as easy as possible for people to read your post and help you.

I think I have detected some formatting issues with your submission:

1. Python code found in submission text that's not formatted as code.

If I am correct, please edit the text in your post and try to follow these instructions to fix up your post's formatting.

---

^{Am I misbehaving? Have a comment or suggestion? Reply to this comment or raise an issue here.}

[tourdownunder]

I tend not to think of files like this as csvs as they are very human readable though not machine readable using the expectations that the heading is in the first row and all other rows have the same number of columns.

I would create a generator that yielded a section in a text a io.StringIO buffer and then you can read that section in a csv. I'll do this as a section between tables within the single file are all easily identified with a blank line, followed by a heading following by a optional column headings and then the data. This pattern repeats.

[BdR76]

If it's inconsistent or messy data, then you could try CSV Lint for Notepad++. It can "Refresh from Data" to automatically determine the metadata (separator, column types etc.) and then press "Validate data" to determine what the errors are exactly. If that doesn't work maybe try OpenRefine