Pandas read csv with repeating header rows

Question

I have a csv file where the data is as follows:

    Col1    Col2    Col3
v1  5       9       5
v2  6       10      6
    Col1    Col2    Col3
x1  2       4       6
x2  1       2       10
x3  10      2       1
    Col1    Col2    Col3
y1  9       2       7

i.e. there are 3 different tables with same headers laid on top of each other. I am trying to pythonically get rid of repeating header rows and get the following result:

    Col1    Col2    Col3
v1  5       9       5
v2  6       10      6
x1  2       4       6
x2  1       2       10
x3  10      2       1
y1  9       2       7

I am not sure how to proceed.

Quang Hoang · Accepted Answer · 2020-07-07 13:26:10Z

4

You can read the data and remove the rows that are identical to the columns:

df = pd.read_csv('file.csv')

df = df[df.ne(df.columns).any(1)]

Output:

   Col1 Col2 Col3
v1    5    9    5
v2    6   10    6
x1    2    4    6
x2    1    2   10
x3   10    2    1
y1    9    2    7

edited Jul 7, 2020 at 13:26

answered Jul 7, 2020 at 13:24

Quang Hoang

151k11 gold badges64 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Zanam Over a year ago

What is any(1) refer to?

Quang Hoang Over a year ago

that checks along the row for a True value.

Andrew O'Brien · Accepted Answer · 2021-12-02 06:55:54Z

An alternative solution is to detect the repeated header rows first, and then use the skiprows=... argument in read_csv().

This has the downside of reading the data twice, but has the advantage that it allows read_csv() to automatically parse the correct datatypes, and you won't have to cast them afterwards using astype().

This example uses hard-coded column name for the first column, but a more advanced version could determine the header from the first row, and then detect repeats of that.

# read the file once to detect the repeated header rows
header_rows = []
header_start = "Col1"
with open('file.csv') as f:
    for i, line in enumerate(f):
        if line.startswith(header_start):
            header_rows.append(i)

# the first (real) row should always be detected
assert header_rows[0] == 0

# skip all header rows except for the first one (the real one)
df = pd.read_csv('file.csv', skiprows=header_rows[1:])

Output:

   Col1 Col2 Col3
v1    5    9    5
v2    6   10    6
x1    2    4    6
x2    1    2   10
x3   10    2    1
y1    9    2    7

Collectives™ on Stack Overflow

Pandas read csv with repeating header rows

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related