Python Pandas Read CSV with custom delimiter

Convert-Pandas

In this post, we are going to understand Python Pandas Read CSV with custom delimiter code examples. We will use a delimiter that includes hyphen(_), semicolon(;), colon(:), tab, and space, and multiple delimiters using regular expression.

Pandas read_csv() method

Pandas library has a built-in read_csv() method to read a CSV file to Dataframe.

Syntax

pandas.read_csv(filepath_or_buffer, sep='', delimiter=None, header='infer', names=<no_default>, index_col=None)

It read the file at the given path and read its contents in the dataframe. It uses a comma as a defualt separator or delimiter or even a custom delimiter or regular expression can be used.

Steps to read a CSV to Dataframe

  • First import the pandas libaray using import pandas as pd
  • Call the read_csv() with filePath and delimiter.

1. Regular Exp to Read_csv() with mutiple delimters

This is a sample file we are using in the below program example.it is present in the current directory.

Name,Subjs;Marks
Alex:Phy|100
Ben;Chem_100
Jack,Math|100

In this example, we are reading a CSV to dataframe with multiple delimiters(:;|_) by using regular expression. We can remove multiple delimiters from CSV using Regular expression

Program example

import pandas as pd
 
# Read a csv file to a dataframe using mutiple delimiters
student_csv =  pd.read_csv('students.csv', sep='[:,;|_]', engine='python')

print(student_csv)

Output

   Name Subjs  Marks
0  Alex   Phy    100
1   Ben  Chem    100
2  Jack  Math    100

2.Pandas Read_CSV with comma delimiter

This is the sample file we are using in this example that is present in the current working directory.

Name,Subjs,Marks
Alex,Phy,100
Ben,Chem,100
Jack,Math,100

In this example, we are reading a CSV file to dataframe by using the default delimiter comma(,).

Program example

import pandas as pd
 
# Read a csv file to a dataframe using comma(,) delimiter
student_csv =  pd.read_csv('students.csv', sep=','  , engine='python')

print(student_csv)

Output

   Name    Subjs  Marks
0  Alex      Phy    100
1   Ben     Chem    100
2  Jack     Math    100
3   Tom  Science    100
4  Tony     Math    100

3.Read_CSV with delimiter colon (:)

Sample file

Name:Subjs:Marks
Alex:Phy:100
Ben:Chem:100
Jack:Math:100

In this example, we are reading a CSV file to a dataframe by using a custom delimiter colon(:).

import pandas as pd
 
# Read a csv file to a dataframe using colon delimiters
student_csv =  pd.read_csv('students.csv', sep=':', engine='python')

print(student_csv)

Output

   Name Subjs  Marks
0  Alex   Phy    100
1   Ben  Chem    100
2  Jack  Math    100

4. Read_CSV With delimiter hypen ‘_’

This is a sample file we are using the below program example.It is present in the current directory.

Name_Subjs_Marks
Alex_Phy_100
Ben_Chem_100
Jack_Math_100

In this example, we are reading a CSV file to a dataframe by using a custom delimiter hyphen ( _ ).

Program example

import pandas as pd
 
# Read a csv file to a dataframe using comma(_) delimiter

student_csv =  pd.read_csv('students.csv', sep='_'  , engine='python')

print(student_csv)

Output

 Name    Subjs  Marks
0  Alex      Phy    100
1   Ben     Chem    100
2  Jack     Math    100

5. Read_CSV to Dataframe delimiter vertcial tab|

This is a sample file we are using the below program example.It is present in the current directory.

Name|Subjs|Marks
Alex|Phy|100
Ben|Chem|100
Jack|Math|100

In this example, we are reading a CSV file to dataframe by using a custom delimiter vertical tab.

Program Example

import pandas as pd
 
#reading a csv to dataframe using custome delimiter vertcial tab.

student_csv =  pd.read_csv('students.csv', sep='|'  , engine='python')

print(student_csv)

Output

   Name Subjs  Marks
0  Alex   Phy    100
1   Ben  Chem    100
2  Jack  Math    100

6. Read_CSV delimter semicolon

This is a sample file we are using in the below program example. It is present in the current directory.

Name;Subjs;Marks
Alex;Phy;100
Ben;Chem;100
Jack;Math;100

In this example, we are reading a CSV file to dataframe by using a custom delimiter semicolon(;).

import pandas as pd
 
# csv dataframe using custome delimiter semicolon(;)

student_csv =  pd.read_csv('students.csv', sep=';'  , engine='python')

print(student_csv)

The output will be CSV to dataframe after removing delimiter ;

   Name Subjs  Marks
0  Alex   Phy    100
1   Ben  Chem    100
2  Jack  Math    100

7. Read_CSV to with delimiter space and tab

This is the sample file we are using for this example. It is present in the current directory.

Name Subjs Marks
Alex Phy 100
Ben	Chem	100
Jack	Math	100

In this example, we are reading a CSV file to dataframe by using custom delimiter space or tab(\t ).

import pandas as pd
 
# Read a csv file to a dataframe using comma(,) delimiter
student_csv =  pd.read_csv('students.csv', sep='\s+'  , engine='python')

print(student_csv)

The output will be CSV to dataframe tab and space delimiters

   Name Subjs  Marks
0  Alex   Phy    100
1   Ben  Chem    100
2  Jack  Math    100

Summary

In Pandas read CSV to dataframe with multiple delimiters, We have explored different ways with code examples. We have used delimiters hyphen(_), semicolon(;), colon(:), tab and space, and multiple delimiters using regular expression. We can change delimiters in the code example as per our requirment.

Related Posts