How to read TSV file in pandas? TSV stands for Tab Separated File Use pandas which is a text file where each field is separated by tab (\t). In pandas, you can read the TSV file into DataFrame by using the read_table() function. In this pandas article, I will explain how to read a TSV file with or without a header, skip rows, skip columns, set columns to index, and many more with examples.
TSV files are plain text that is used to store 2-dimensional data in a simple human-readable format, this is the format mostly used in industry to exchange data files between organizations. In some cases, these files are also used to store metadata.
Related: pandas Write to CSV File
1. read_table() Syntax
Following is the syntax of the read_table() function
# Syntax of read_table()
pandas.read_table(filepath_or_buffer, sep=NoDefault.no_default, delimiter=None, header='infer', names=NoDefault.no_default, index_col=None, usecols=None, squeeze=None, prefix=NoDefault.no_default, mangle_dupe_cols=True, dtype=None, engine=None, converters=None, true_values=None, false_values=None, skipinitialspace=False, skiprows=None, skipfooter=0, nrows=None, na_values=None, keep_default_na=True, na_filter=True, verbose=False, skip_blank_lines=True, parse_dates=False, infer_datetime_format=False, keep_date_col=False, date_parser=None, dayfirst=False, cache_dates=True, iterator=False, chunksize=None, compression='infer', thousands=None, decimal='.', lineterminator=None, quotechar='"', quoting=0, doublequote=True, escapechar=None, comment=None, encoding=None, encoding_errors='strict', dialect=None, error_bad_lines=None, warn_bad_lines=None, on_bad_lines=None, delim_whitespace=False, low_memory=True, memory_map=False, float_precision=None, storage_options=None)
2. pandas Read TSV into DataFrame
To read a TSV file with tab (\t) delimiter use pandas read_table() function. This also supports optionally iterating or breaking the file into chunks. As you see in the above syntax, it takes several optional parameters to support reading TSV files with different options. When you are dealing with huge files, some of these params helps you in loading TSV file faster. In this article, I will explain the usage of some of these options with examples.
I will use the above data to read TSV file, you can find the data file at GitHub
# Import pandas
import pandas as pd
# Read TSV file into DataFrame
df = pd.read_table('courses.tsv')
print(df)
# Output:
# Courses Fee Duration Discount
# 0 Spark 25000 50 Days 2000
# 1 Pandas 20000 35 Days 1000
# 2 Java 15000 NaN 800
# 3 Python 15000 30 Days 500
# 4 PHP 18000 30 Days 800
By default, it reads first rows on TSV as column names (header) and it creates an incremental numerical number as index starting from zero.
Alternatively, you can also read_csv() but you need to use explicitly param sep or delimiter with '\t'
3. Set Column as Index
You can set a column as an index using index_col as a param. This param takes values {int, str, sequence of int / str, or False, optional, default None}.
# Set column as Index
df = pd.read_tsv('courses.tsv', index_col='Courses')
print(df)
# Output:
# Fee Duration Discount
# Courses
# Spark 25000 50 Days 2000
# Pandas 20000 35 Days 1000
# Java 15000 NaN 800
# Python 15000 30 Days 500
# PHP 18000 30 Days 800
Alternatively, you can also use index/position to specify the column name. When used a list of values, it creates a MultiIndex.
4. Skip Rows
Sometimes you may need to skip first-row or skip footer rows, use skiprows , and skipfooter param respectively.
# Skip first few rows
df = pd.read_table('courses.tsv', header=None, skiprows=2)
print(df)
# Output:
# 0 1 2 3
# 0 Pandas 20000 35 Days 1000
# 1 Java 15000 NaN 800
# 2 Python 15000 30 Days 500
# 3 PHP 18000 30 Days 800
skiprows param also takes a list of rows to skip.
4. Read CSV by Ignoring Column Names
By default, it considers the first row from excel as a header and used it as DataFrame column names. In case you wanted to consider the first row from excel as a data record use header=None param and use names param to specify the column names. Not specifying names result in column names with numerical numbers.
# Ignore header and assign new columns
columns = ['courses','course_fee','course_duration','course_discount']
df = pd.read_table('/courses.tsv', header=None,names=columns,skiprows=1)
print(df)
# Output:
# courses course_fee course_duration course_discount
# 0 Spark 25000 50 Days 2000
# 1 Pandas 20000 35 Days 1000
# 2 Java 15000 NaN 800
# 3 Python 15000 30 Days 500
# 4 PHP 18000 30 Days 800
5. Load only Selected Columns
Using usecols param you can select columns to load from the CSV file. This takes columns as a list of strings or a list of int.
# Load onlu selected columns
columns = ['courses','course_fee','course_duration','course_discount']
df = pd.read_table('courses.tsv', usecols =['Courses','Fee','Discount'])
print(df)
# Output:
# Courses Fee Discount
# 0 Spark 25000 2000
# 1 Pandas 20000 1000
# 2 Java 15000 800
# 3 Python 15000 500
# 4 PHP 18000 800
6. Set DataTypes to Columns
By default read_table() assigns the data type that best fits based on the data. For example Fee and Discount for DataFrame is given int64 and Courses and Duration are given string.
Let’s change the Fee columns to float type.
# Set column data types
df = pd.read_table('courses.tsv', dtype={'Courses':'string','Fee':'float'})
print(df.dtypes)
# Output:
# Courses string
# Fee float64
# Duration object
# Discount int64
# dtype: object
7. Other Params of pandas read_table()
nrows– Specify how many rows to read.true_value– What all values to consider as True.false_values– What all values to consider as False.mangle_dupe_cols– Duplicate columns will be specified as ‘X’, ‘X.1’, …’X.N’, rather than ‘X’…’X’.converters– Supply Dict of values you wanted to convert.skipinitialspace– Similar to right trim. Skips spaces after separator.na_values– Specify what all values to consider as NaN/NA.keep_default_na– Specify whether to load NaN values from the data.na_filter– Detect missing values. set this to False to improve performance.skip_blank_lines– skip empty lines with out data.parse_dates– Specify how you wanted to parse dates.thousands– Separator for thousdand.decimal– Character for decimal point.lineterminator– Line separator.quotechar– Use quote character when you wanted to consider delimiter within a value.
Besides these, there are many more optional params, refer to pandas documentation for details.
Conclusion
In this python article, you have learned what is TSV file, how to load it into pandas DataFrame. Also learned skipping rows, selecting columns, ignoring header, and many more examples.
Related Articles
- pandas ExcelWriter Usage with Examples
- pandas write CSV file
- Read Excel file into pandas DataFrame
- Pandas Read SQL Query or Table with Examples
- Pandas Read Text with Examples
- Pandas Read Multiple CSV Files into DataFrame
- Pandas Read JSON File with Examples
- Pandas read_csv() with Examples
- Pandas Read Excel with Examples
References
- https://pandas.pydata.org/docs/reference/api/pandas.read_table.html
- https://fileinfo.com/extension/tsv
