How to Skip Rows while Reading CSV File using Pandas

Pandas is a powerful Python library that provides easy-to-use data manipulation tools for working with tabular data. It is built on top of the NumPy package and provides a high-level interface for data analysis. One of the most common tasks in data analysis is to read data from a CSV file. This article will explore how to skip rows while reading a CSV file using Pandas.

What is a CSV file?

CSV stands for Comma Separated Values. It is a file format used to store tabular data in plain text. Each row of the CSV file represents a record, and each column represents a field in the record. The values in each column are separated by commas. CSV files are easy to create and can be read by many applications, making them popular for storing data.

Reading a CSV file with Pandas

Pandas provide the read_csv() function to read data from a CSV file. This function returns a Data Frame, a two-dimensional data table with Labeled axes. The first row of the CSV file is assumed to be the header row, and the column names are inferred from this row.

If the CSV file does not have a header row, we can specify the column names using the names parameter of the read_csv() function.

By default, the read_csv() function reads all the rows of the CSV file. However, in some cases, we may want to skip some rows. For example, if the CSV file has some header rows or comments, we may want to skip these rows.

Skipping Rows while Reading a CSV file

To skip rows while reading a CSV file, we can use the skip rows parameter of the read_csv() function. The skip rows parameter takes a list of integers that represent the rows to skip. For example, to skip the first row of the CSV file, we can set skip rows=[0]. Similarly, we can set skiprows=[0, 1] to skip the first two rows.

Let's look at an example. Suppose we have a CSV file named data.csv that looks like this:

# This is a comment
# Date: 2021-02-20
Name, Age, City
John, 25, New York
Mary, 30, Los Angeles

This CSV file has two header rows that start with the # symbol, and we want to skip these rows while reading the file. We can use the skip rows parameter to skip these rows:

import pandas as pd
df = pd.read_csv('data.csv', skiprows=[0, 1])
print(df)

Output:

   Name   Age          City
0  John    25      New York
1  Mary    30  Los Angeles

As we can see, the first two rows of the CSV file have been skipped, and the resulting Data Frame contains only the data rows.

Skipping Rows Based on a Condition

Sometimes, we may want to skip rows based on a condition. For example, we may want to skip rows with missing values or rows that do not meet a certain criterion. We can combine the skip rows parameter with other parameters to achieve this.

Let's consider an example. Suppose we have a CSV file named data.csv that contains information about students, including their names, ages, and grades:

Name, Age, Grade
John, 25, A
Mary, 30, B
Peter, A
Lisa, 20,

This CSV file has missing values in some rows. We want to skip rows that have missing values while reading the file. We can use the na_values parameter of the read_csv() function to specify the values that should be considered missing values and then use the skip parameter to skip the rows with missing values. For example:

import pandas as pd
df = pd.read_csv('data.csv', na_values=[''])
df = df.dropna()
print(df)

Output:

   Name   Age Grade
0  John  25.0     A
1  Mary  30.0     B

Skipping Rows while Reading a Large CSV file

When working with large CSV files, reading all the rows can take a long time and consume a lot of memory. In such cases, reading the file in chunks may be more efficient than processing each chunk separately. We can use the chunk_size parameter of the read_csv() function to read the file in chunks of a specified size.

Let's consider an example. Suppose we have a large CSV file named data.csv that contains millions of rows of data:

Name, Age, City
John, 25, New York
Mary, 30, Los Angeles
...

We want to skip the first 100,000 rows of the file while reading it. We can use the chunk_size parameter to read the file in chunks of 100,000 rows each and then concatenate the resulting Data Frames. For example:

import pandas as pd
chunks = pd.read_csv('data.csv', skiprows=range(1, 100001), chunksize=100000)
df = pd.concat(chunks)
print(df)

This will output the complete Data Frame with all the rows except the first 100,000.

Pandas is a powerful tool for data analysis in Python, and it provides many other features for data manipulation, such as filtering, sorting, grouping, and aggregating data. Learning Pandas can be valuable for anyone working with data in Python, from data analysts to data scientists and machine learning engineers. With its intuitive syntax and powerful tools, Pandas makes it easy to work with tabular data and perform complex data analysis tasks.

In addition to skipping rows while reading a CSV file, Pandas provides many other data pre-processing, cleaning, and transformation functions. For example, we can use functions like drop duplicates(), drop(), and fillna() to remove duplicates, drop columns or rows, and fill missing values in a DataFrame.

Let's consider some examples. Suppose we have a CSV file named data.csv that contains data about students, including their names, ages, grades, and genders:

Name, Age, Grade, Gender
John, 25, A, Male
Mary, 30, B, Female
John, A, Male
, 20, B, Male

We want to skip the rows with missing data, remove duplicates, and drop the Gender column. We can do this as follows:

import pandas as pd
df = pd.read_csv('data.csv', na_values=[''])
df = df.dropna()
df = df.drop_duplicates()
df = df.drop('Gender', axis=1)
print(df)

Output:

   Name   Age Grade
0  John  25.0     A
1  Mary  30.0     B

As we can see, the DataFrame now contains only the rows with complete data, no duplicates, and without the Gender column.

Another useful function for data pre-processing is applied (). This function allows us to apply a function to each element of a DataFrame or column element. For example, we can use apply() to convert the Age column to integers:

import pandas as pd
df = pd.read_csv('data.csv', na_values=[''])
df = df.dropna()
df = df.drop_duplicates()
df = df.drop('Gender', axis=1)
df['Age'] = df['Age'].apply(int)
print(df)

Output:

   Name  Age Grade
0  John   25     A
1  Mary   30     B

As we can see, the Age column has been converted to integers.

Another thing to note is that Pandas also provides several options for reading CSV files, including specifying the column names and data types and setting options for handling missing values, delimiters, and quoting characters. These options can be specified using the parameters of the read_csv() function.

For example, if the CSV file has no header row, we can specify the column names using the names parameter:

import pandas as pd
df = pd.read_csv('data. csv', names=['Name,' 'Age,' 'Grade,' 'Gender'], na_values=[''])
df = df.dropna()
df = df.drop_duplicates()
df = df.drop('Gender', axis=1)
df['Age'] = df['Age'].apply(int)
print(df)

Output: This output is in same DataFrame as before:

   Name  Age Grade
0  John   25     A
1  Mary   30     B

As we can see, the DataFrame now has column names specified by the name's parameter.

We can also specify the data types of the columns using the dtype parameter:

import pandas as pd
df = pd.read_csv('data. csv', names=['Name,' 'Age,' 'Grade,' 'Gender'], dtype={'Age': int}, na_values=[''])
df = df.dropna()
df = df.drop_duplicates()
df = df.drop('Gender', axis=1)
print(df)

Output:

This will give output in the same DataFrame as before, but with the Age column as integers:

   Name  Age Grade
0  John   25     A
1  Mary   30     B

As we can see, the dtype parameter allows us to specify the data types of the columns, which can be useful for avoiding data type errors and optimizing memory usage.

Pandas also provide several options for handling missing values, such as specifying the values to be treated as missing using the na_values parameter or how to handle missing values using the fillna() function.

Conclusion

In this article, we have explored how to skip rows while reading a CSV file using Pandas. We have seen that we can use the skip rows parameter of the read_csv() function to skip rows based on their index or use other parameters, such as na_values and skip to skip rows based on a condition. We have also seen how to read a large CSV file in chunks and concatenate the resulting DataFrames.

Skipping rows while reading a CSV file can be useful in many data analysis tasks, such as skipping header rows or comments, skipping rows with missing data, or skipping a certain number of rows at the beginning of the file. By using Pandas, we can easily skip rows while reading a CSV file and manipulate the resulting DataFrames as needed.

Data pre-processing and cleaning are essential steps in data analysis and machine learning, as they can affect the quality and accuracy of the results. We can easily pre-process and clean data using Pandas and its functions and transform it into a format suitable for analysis or modeling. Pandas is a powerful tool for data analysis in Python, and it provides many other features for data manipulation, such as filtering, sorting, grouping, and aggregating data. Learning Pandas can be valuable for anyone working with data in Python, from data analysts to data scientists and machine learning engineers. With its intuitive syntax and powerful tools, Pandas makes it easy to work with tabular data and perform complex data analysis tasks.

Next TopicHow to split a Python List or Iterable into Chunks

← prev next →