Most data science students only learn to import CSV, but at work, you have to deal with multiple data formats, and things can get complicated if you are doing it for the first time. In this guide, we will be focusing on importing CSV, Excel, SQL, HTML, and JSON datasets. 

SQL

# !kaggle datasets download -d anth7310/mental-health-in-the-tech-industry # !unzip mental-health-in-the-tech-industry

import pandas as pd import sqlite3 # Prepare a connection object # Pass the Database name as a parameter conn = sqlite3.connect("mental_health.sqlite") # Use read_sql_query method # Pass SELECT query and connection object as parameter pdSql = pd.read_sql_query("SELECT * FROM Question", conn)

pdSql.head()

questiontextobject

questionidint64

What is your age?

What is your gender?

What country do you live in?

If you live in the United States, which state or territory do you live in?

Are you self-employed?

HTML

df_html = pd.read_html( "https://www.pharmaceutical-technology.com/covid-19-vaccination-tracker/" )[0] df_html.head()

Countryobject

Doses Administeredint64

World

10436627754

China

3052435000

India

1736068837

United States

547948656

Brazil

379776348

# !pip install kaggle # !kaggle datasets download -d yasserh/bike-sharing-dataset # !unzip bike-sharing-dataset

CSV

data_csv = pd.read_csv("day.csv") data_csv.head()

instantint64

dtedayobject

01-01-2018

02-01-2018

03-01-2018

04-01-2018

05-01-2018

EXCEL

# !kaggle datasets download -d yamqwe/u-s-presidents-and-debte # !unzip u-s-presidents-and-debte

data_excel = pd.read_excel("US_Presidents.xlsx",sheet_name = 1, index_col = "index") data_excel.head()

yearint64

presidentobject

1949

Harry S. Truman

1950

Harry S. Truman

1951

Harry S. Truman

1952

Harry S. Truman

1953

Dwight D. Eisenhower

JSON

# !pip install kaggle # !kaggle datasets download -d bricevergnou/spotify-recommendation # !unzip spotify-recommendation

df_json = pd.read_json("good.json",orient='records') df_json

audio_featuresobject

{'danceability': 0.749, 'energy': 0.839, 'key': 6, 'loudness': -4.8469999999999995, 'mode': 1, 'speechiness': 0.297, 'acousticness': 0.0867, 'instrumentalness': 0, 'liveness': 0.20400000000000001, 'valence': 0.804, 'tempo': 172.068, 'type': 'audio_features', 'id': '55mcupbf7cIsuCEVAuTJVk', 'uri': 'spotify:track:55mcupbf7cIsuCEVAuTJVk', 'track_href': 'https://api.spotify.com/v1/tracks/55mcupbf7cIsuCEVAuTJVk', 'analysis_url': 'https://api.spotify.com/v1/audio-analysis/55mcupbf7cIsuCEVAuTJVk', 'duration_ms': 111000, 'time_signature': 4}1%

{'danceability': 0.5730000000000001, 'energy': 0.581, 'key': 10, 'loudness': -9.026, 'mode': 0, 'speechiness': 0.339, 'acousticness': 0.753, 'instrumentalness': 1.39e-06, 'liveness': 0.13, 'valence': 0.35100000000000003, 'tempo': 76.506, 'type': 'audio_features', 'id': '57RtLWT7IpugV0yi5bsxJk', 'uri': 'spotify:track:57RtLWT7IpugV0yi5bsxJk', 'track_href': 'https://api.spotify.com/v1/tracks/57RtLWT7IpugV0yi5bsxJk', 'analysis_url': 'https://api.spotify.com/v1/audio-analysis/57RtLWT7IpugV0yi5bsxJk', 'duration_ms': 169347, 'time_signature': 4}1%

98 others98%

{'danceability': 0.749, 'energy': 0.839, 'key': 6, 'loudness': -4.8469999999999995, 'mode': 1, 'spe…

{'danceability': 0.5730000000000001, 'energy': 0.581, 'key': 10, 'loudness': -9.026, 'mode': 0, 'sp…

{'danceability': 0.8, 'energy': 0.719, 'key': 7, 'loudness': -6.2620000000000005, 'mode': 1, 'speec…

{'danceability': 0.778, 'energy': 0.632, 'key': 8, 'loudness': -6.415, 'mode': 1, 'speechiness': 0.…

{'danceability': 0.797, 'energy': 0.852, 'key': 8, 'loudness': -5.202, 'mode': 1, 'speechiness': 0.…

{'danceability': 0.743, 'energy': 0.663, 'key': 2, 'loudness': -8.47, 'mode': 1, 'speechiness': 0.0…

{'danceability': 0.915, 'energy': 0.551, 'key': 8, 'loudness': -10.49, 'mode': 1, 'speechiness': 0.…

{'danceability': 0.798, 'energy': 0.835, 'key': 9, 'loudness': -3.832, 'mode': 1, 'speechiness': 0.…

{'danceability': 0.8270000000000001, 'energy': 0.804, 'key': 9, 'loudness': -5.846, 'mode': 1, 'spe…

{'danceability': 0.8300000000000001, 'energy': 0.707, 'key': 2, 'loudness': -5.777, 'mode': 1, 'spe…

import json with open('good.json') as data_file: data = json.load(data_file) df = pd.json_normalize(data["audio_features"]) df.head()

danceabilityfloat64

energyfloat64

0.749

0.839

0.573

0.581

0.8

0.719

0.778

0.632

0.797

0.852