Pandas Tutorial: Importing CSV, and TSV Files Into Data Frames
Do you know what’s the first step in data analysis? It’s importing files.
You might be wondering what’s hard in that. We will get a CSV file, and using pandas ‘read_csv()’ we can import the CSV file and convert it to a data frame.
But, this is not the same in all the cases. Every time you won’t get a CSV file. Sometimes you might be asked to import files using API or extract valuable data from the database.
Table of Contents:
∘ 1. CSV Files
∘ Q) What is the role of read_csv in Pandas?
∘ 2. TSV Files
∘ Q) Why the data from a TSV file is loaded into a single column instead of being properly separated into multiple columns in a DataFrame?
∘ Q) How to convert a TSV file to a perfect DataFrame?
∘ Q) How to name the columns in the data frame?
∘ That’s It for today!
∘ Link for Code:
Let’s explore!
1. CSV Files
CSV files are like tables in Excel but simpler. They store data separated by commas. Let’s see how we can bring them into Pandas.
But before that, you should first know that we can load .csv
file in 2 different ways:
1.1 Opening a local CSV file
Here we will be using the Titanic dataset from Kaggle.
# Import Pandas
import pandas as pd
# Read CSV file into a DataFrame
df = pd.read_csv('titanic.csv')
df
1.2 Opening a CSV file using a URL
import requests
from io import StringIO
url = "https://raw.githubusercontent.com/Data-Science-Delight/titanic.csv/main/train%20(1).csv"
response = requests.get(url)
data = StringIO(response.text)
pd.read_csv(data)
Q) What is the role of read_csv in Pandas?
read_csv
function in Pandas reads CSV files and converts them into a DataFrame making it easy to read and interpret data in a tabular form.
2. TSV Files
A TSV (Tab-Separated Values) file is a text-based file format used to store structured data in a tabular format.
Unlike CSV (Comma-Separated Values) files, TSV files use tabs as the delimiter to separate individual data fields within each row.
2.1 Loading TSV File to a DataFrame
Here we have used the movie_titles_metadata dataset from Kaggle.
All the files (csv, tsv) are present in our github repo. You can also download the file from there.
df2 = pd.read_csv('movie_titles_metadata.tsv')
df2.head()
Output:
Q) Why the data from a TSV file is loaded into a single column instead of being properly separated into multiple columns in a DataFrame?
This is because read_csv
reads comma-separated values. And here there is no comma-separated text, so everything appears in the same line or we can say in a single column.
2.2 Delimiter
Q) How to convert a TSV file to a perfect DataFrame?
Using “Sep Parameter”:
movies_df = pd.read_csv('movie_titles_metadata.tsv', sep = '\t')
movies_df.head()
Output:
NOTE:
You can see that in the columns section, we are getting values from the first row, instead of the columns name. So how to fix that? The answer is by using a header.
2.3 Header
movies_df = pd.read_csv('movie_titles_metadata.tsv', sep = '\t', header = None)
movies_df.head()
Output:
2.4 Names
Q) How to name the columns in the data frame?
movies_df = pd.read_csv('movie_titles_metadata.tsv', sep = '\t', header = None, names = ['Sl.No', 'Movie Title','Year', 'Ratings', 'Votes','Genre'])
movies_df.head()
Output:
Now, It looks like a perfect DataFrame.
That’s It for today!
Stay tuned for Part 2. Please like, share, and follow Data Science Delight for more!