read_csv file using Pandas skipping rows based on content
Tags: python, programming, datascience
Published: 2023-03-13 15:13:38

---
title: read_csv file using Pandas skipping rows based on content
published: true
description: Sometimes you need to extrac CSV data from a file that has some mixed content.
tags: python, programming, datascience
# cover_image: https://direct_url_to_image.jpg
# Use a ratio of 100:42 for best results.
# published_at: 2023-03-13 15:10 +0000
canonical_url: https://code-maven.com/read-csv-skip-rows-by-content
series: python
---

Ideally each CSV file you work with will contain homogeneous data: only one row of header and in ever row similar type of data.
Unfortunately real world sometimes gives us files where several tables are mixed. The question arises how can we read the content
of such file using the [read_csv](https://pandas.pydata.org/docs/reference/api/pandas.read_csv.html?highlight=read_csv)
method of Pandas?

One could use the `skiprows` and the `skipfooter` parameters, but what if I don't the number of the rows we need to skip?


## The file

This a very short version of a file I just created for the demo. The real file is much, much bigger.

```
code,from,to,departure,length,price,tickets
CM 2201,BUD,LTN,2016-05-29 06:00,2:35,80,20
CM 2203,BUD,LTN,2016-05-29 09:00,2:35,80,17

CM 2202,LTN,BUD,2016-06-10 08:10,2:25,120,5


Planet name,Distance (AU),Mass
Mercury,0.4,0.055
Venus,0.7,0.815

City 1,City 2,distance
Budapest,Bukarest,1200
Tel Aviv,Beirut,500
```

This file has 3 different tables in it.


## Read everything


```python
import pandas as pd

filename = "examples/data/mixed.csv"
df = pd.read_csv(filename)
print(df)
```

Reading everything will not result in anything useful. The header of the 2nd and the 3rd table are seen as data in the first table.
Along with the content of those tables.


```
          code           from        to         departure length  price  tickets
0      CM 2201            BUD       LTN  2016-05-29 06:00   2:35   80.0     20.0
1      CM 2203            BUD       LTN  2016-05-29 09:00   2:35   80.0     17.0
2      CM 2202            LTN       BUD  2016-06-10 08:10   2:25  120.0      5.0
3  Planet name  Distance (AU)      Mass               NaN    NaN    NaN      NaN
4      Mercury            0.4     0.055               NaN    NaN    NaN      NaN
5        Venus            0.7     0.815               NaN    NaN    NaN      NaN
6       City 1         City 2  distance               NaN    NaN    NaN      NaN
7     Budapest       Bukarest      1200               NaN    NaN    NaN      NaN
8     Tel Aviv         Beirut       500               NaN    NaN    NaN      NaN
```

We could try to fiddle with the dataframe to locate the rows that are relevant to our data, but I thought a much filtering the original data would be
simpler.


## Set the skips manually

Before trying to go to the dynamic solution I wanted to see if the `skiprows` and `skipfooter` parameters work as I expect.
So we have a version where we supply these two parameters specific to the sample csv file:

```python
import pandas as pd

filename = "examples/data/mixed.csv"
df = pd.read_csv(filename, skiprows=7, skipfooter=4, engine="python")
print(df)
```

The result is this:

```
  Planet name  Distance (AU)   Mass
0     Mercury            0.4  0.055
1       Venus            0.7  0.815
```

I also had to supply the `engine="python"` to avoid a warning:
`ParserWarning: Falling back to the 'python' engine because the 'c' engine does not support skipfooter; you can avoid this warning by specifying engine='python'.`

This also made me worried that maybe this will cause the whole loading to work a lot slower. We'll have to see it on real data.


## Calculate the skips

Finally we have the code to calculate the number of rows automatically. I did not want to do it with Pandas as this solution, where we only care about strings, seems to be much faster.
(But I have not measured it.)

```python
import pandas as pd

def get_row(rows, text):
    matches = list(filter(lambda row: row[1].rstrip("\n") == text, enumerate(rows)))
    # print(matches)

    if len(matches) == 0:
        raise Exception(f"header {text} could not be found")
    if len(matches) > 1:
        raise Exception(f"duplicate header {text} was found")

    return matches[0][0]


filename = "examples/data/mixed.csv"
with open(filename) as fh:
    rows = fh.readlines()

starting_row = get_row(rows, "Planet name,Distance (AU),Mass")
# print(starting_row) # 7

end_row = len(rows) - get_row(rows, "City 1,City 2,distance")
# print(end_row) # 4

df = pd.read_csv(filename, skiprows=starting_row, skipfooter=end_row, engine="python")
print(df)
```

First we read the content of the csv file into memory as a list of rows. Then we use the `get_row` function to find the row-number.

`enumerate()` returns a list of tuples of the form <b>(row_number, row)<b>.

We use the `filter` on the enumerated list. The filter function takes element 1 of the tuple (which is the current row in the file) and checks if it is equal to the string we supplied.

`filter` returns a filter object, we use `list` to flatten it into a list.

Then we make sure we found exactly one such row. I know theoretically the file should be correct and have exactly this format. But I know in reality....
So I prefer to report an error or an ambiguity on the input data then to give false results.

That's it.

I hope this will be useful to someone.