Converting CSV to a SQLite Database
As a part of my data science course on EdX we have been working with a lot of csv files. I spoke SQL long before I spoke Pandas and I find that it is much easier to do initial exploration of the data using raw SQL queries compared to the Pandas DSL.
Kaggle is a great repository full of useful data sets that are ripe for exploration. While a lot of these data sets come in both csv and sql flavors, some of them are CSV only. Using SQLit we are able to easily import these csv files into a database and then run queries for further data exploration.
Im going to use the kickstarter data set for this tutorial, feel free to download the csv files from kaggle so that you can follow along.
Pre Requisites
Make sure that you have SQLite installed before getting started with this tutorial.
Steps to Convert CSV to SQLite
First, Download the data set from kaggle, this will come in the form of a zip file. Unzip this and open up a terminal in the directory where you have the new unzipped kickstarter-projects folder.
In your terminal open up a new sqlite session followed by the name of the file that you want to save your new database to.
sqlite3 ks.db
Inside of the sqlite shell, change the mode to csv.
.mode csv
Import the csv file, and add the name of the table that you want the data to be imported into.
.import kickstarter-projects/ks-projects-201801.csv ks
Verify that everything was imported correctly. Take a look at the schema, and first couple of rows. Your output should look something like this:
sqlite> .schema ks
CREATE TABLE ks(
"ID" TEXT,
"name" TEXT,
"category" TEXT,
"main_category" TEXT,
"currency" TEXT,
"deadline" TEXT,
"goal" TEXT,
"launched" TEXT,
"pledged" TEXT,
"state" TEXT,
"backers" TEXT,
"country" TEXT,
"usd pledged" TEXT,
"usd_pledged_real" TEXT,
"usd_goal_real" TEXT
);
sqlite> select * from ks limit 5;
1000002330|The Songs of Adelaide & Abullah|Poetry|Publishing|GBP|2015-10-09|1000.00|2015-08-11 12:12:28|0.00|failed|0|GB|0.00|0.00|1533.95
1000003930|Greeting From Earth: ZGAC Arts Capsule For ET|Narrative Film|Film & Video|USD|2017-11-01|30000.00|2017-09-02 04:43:57|2421.00|failed|15|US|100.00|2421.00|30000.00
1000004038|Where is Hank?|Narrative Film|Film & Video|USD|2013-02-26|45000.00|2013-01-12 00:20:50|220.00|failed|3|US|220.00|220.00|45000.00
1000007540|ToshiCapital Rekordz Needs Help to Complete Album|Music|Music|USD|2012-04-16|5000.00|2012-03-17 03:24:11|1.00|failed|1|US|1.00|1.00|5000.00
1000011046|Community Film Project: The Art of Neighborhood Filmmaking|Film & Video|Film & Video|USD|2015-08-29|19500.00|2015-07-04 08:35:03|1283.00|canceled|14|US|1283.00|1283.00|19500.00
Excellent! Now you can query this entire data set as your normally would. Happy data exploration!
Thank you for reading! Share your thoughts with me on mastodon or via email.
Check out some more stuff to read down below.
Most popular posts this month
- Dagger Feels Like Magic
- Setting up ANTLR4 on Windows
- SQLite DB Migrations with PRAGMA user_version
- 20 Years of Ubuntu
- видно по глазам - you can see it in the eyes
Recent Favorite Blog Posts
This is a collection of the last 8 posts that I bookmarked.
- Serendipity from Armin Ronacher's Thoughts and Writings
- Andrea Veri: GNOME Infrastructure migration to AWS from Planet GNOME
- A Whale of a Time from https://popagandhi.com/
- Pluralistic: You should be using an RSS reader (16 Oct 2024) from Pluralistic: Daily links from Cory Doctorow
- Sahil Dhiman: 25, A Quarter of a Century Later from Planet Debian
- Reflections on Palantir from Nabeel S. Qureshi
- Reading Old Posts from Kev Quirk
- Capture less than you create from David Heinemeier Hansson
Articles from blogs I follow around the net
Script Doctoring
I’ve been having a number of communications problems in my interactions with my doctors at Kaiser lately, and it’s becoming one of those things where the burden and onus entirely is placed upon me to sort out, and that’s exhausting for the actually autist…
via Bix Dot Blog October 22, 2024Blockchain company Forte acquires games studios, demands secrecy, shuts them down
Sometime in 2023, blockchain firm Forte acquired game studios Phoenix Labs and Rumble Games. However, it would be a year before this came to light, because according to a report from Game Developer, Forte demanded secrecy from employ…
via Web3 is Going Just Great October 22, 2024Initial explorations of Anthropic's new Computer Use capability
Two big announcements from Anthropic today: a new Claude 3.5 Sonnet model and a new API mode that they are calling computer use. (They also pre-announced Haiku 3.5, but that's not available yet so I'm ignoring it until I can try it out myself.) Comp…
via Simon Willison's Weblog: Entries October 22, 2024Generated by openring