C..S..V..
This stands for “Comma Separated Values” and is a popular format for storing data.
Most of the time people use databases for large amounts of data, and spreadsheets for
small amounts.
But CSVs still have their place.
They are simple and convenient.
No drivers or special APIs are needed to use them.
And Python makes them even simpler with the CSV module.
Get ready to look at values that have been separated by commas...
- A CSV is a text file that contains data.
- Oftentimes the first row of the file is a header, letting you know what the values
represent.
- The remaining lines contain the data.
Think of each row as a record in a database.
- In each row, the pieces of data are separated by commas.
- Note: because this is a text file, there are no data types.
- While you may mentally interpret the data as strings, dates and numbers, everything
is represented as a string.
- When you read a CSV, it will be your responsibility to convert the data into the appropriate data
type.
- Also, whenever you see two commas in a row, that means a piece of data is simply missing.
- Today, we will learn how to read and write CSVs.
- We will show you how to use the Python CSV module by reading a file containing the first
10 years of stock data for Google.
- Next, we will analyze the data.
- We will then write the results of our analysis to a new CSV.
- By the way, there is a link to a spreadsheet containing this data in the description below.
You can export this spreadsheet as a CSV so you can practice the techniques you will learn
today.
- Let’s begin…
- Here is the path to the Google stock data CSV on my system.
- You can use the “open” function to display the contents of this file.
- There is indeed a lot of data there.
- To be useful, however, we need to store the data.
- We will show you two ways to do this: with and without the CSV module.
- Let’s first parse the data without using the CSV module.
- We can quickly read the contents of the file by using a list comprehension.
- Let’s look at the first two lines to see what we have.
- Notice that each line is treated as a single string.
- Furthermore, there is a newline character included at the end.
- We can use the “strip” method to remove any leading or trailing whitespace.
- And we can use the “split” function to divide the string into smaller pieces.
Just pass in the string or character to partition on.
- This is a much better way to read each line.
- So let’s read the data once more, but this time we will take care to remove unnecessary
whitespace, and split the string into separate values.
- If you now look at the first two lines, you can see that each row is now a list of
data.
- Unfortunately, the data are all still strings.
- There is also another problem.
- What if your CSV contained data on books or movies.
Many titles contain commas, so splitting on commas would split those titles.
Handling problems like this is one of many reasons why you should use the CSV module.
- First import the CSV module.
- When using a module for the first time, it’s always a good idea to print the directory
to see what functions and classes the module contains.
- Run…
- You can see the module contains functions for Excel spreadsheets.
- But today, we will focus on the “reader” and “writer” functions.
- Let’s now read the Google stock data once more, but this time we will use the CSV module.
- First, open the file.
Notice that we specified a “newline” keyword argument and passed in an empty string.
This is because depending on your system, strings may end with a newline, carriage return,
or both.
This technique will ensure the CSV module will work correctly across all platforms.
- We will now create a “reader” function to parse the CSV data from the file.
- Since the first line is a header and does not contain any data, use the “next” function
to extract the first line.
- And now we can read the data.
- To confirm that this works, let’s print both the header and the first row of data.
- While the code may not be shorter than our earlier approach, it is much more robust.
- Python will parse the CSV and handle most exceptions that can arise.
- But there is still a problem.
The data are still treated as strings.
- We need to improve our code by converting the data to the appropriate types.
- This CSV contains 7 pieces of data per row: Date, Open, High, Low, Close, Volume and Adjusted
Close.
- The Date column is a datetime, open / high / low / close and “adjusted close” are
all floats.
- And “Volume” is an integer.
- Let’s reload the data once more, but this time convert each string to the appropriate
data type.
- In order to parse the date, we need to import the datetime class from the datetime module.
- Personally I would have chosen a different name for the class, but I’m only a singleton.
- Let’s create a list called “data” which will hold all the correctly parsed rows.
- Next, loop over the rows.
- To improve readability, let’s include a comment describing what data is expected
in each row.
- To parse the datetime, we will call a method named “S-T-R-P-Time”.
This is short for “string parse time”.
- The first argument is the string, and the second argument is the expected format.
- Working with datetimes in Python is a delicate affair, so we will cover this in more detail
in a separate video.
- Next, convert the open.. high.. low.. and close.. to floats.
- We use “open price” as the variable name since “open” is the name of a builtin
Python function.
- These numbers represent the stock price when the market opened, its highest and lowest
price during the day, and its price when the market closed.
- Volume is the number of shares of stock traded that day.
- And “Adjusted Close” is an alternative closing price that takes into account that
prices can undergo quantum leaps due to stock splits and dividend payments.
- Finally, append a list containing these values to our “data” list.
- Now if you print the first row you can see the data are all of the appropriate type.
- They are no longer strings, but dates, floats and integers.
- Let’s now compute the daily stock returns and write them to a separate CSV.
- In finance, a stock return is just the percent change in price.
- A daily return is the percent change from one day to the next.
- People also look at weekly, monthly, quarterly and yearly returns.
- Today, we will only consider daily returns.
- We will store the data we compute in a file called “google_returns.csv”
- First, open this file in write mode.
- Let’s create a “CSV writer” object for storing our computed results.
- To write a row, call the “writerow” method with a list of values.
Let’s first write the header.
- Our data is luckily already sorted chronologically, so we can simply loop through it.
- To compute the daily change in price as a percent, we need the adjusted price for
two consecutive days.
- So instead of looping over the data list which would give us only one row at a time,
let’s create a “for loop” with an index instead.
- Notice that we will stop at the second-to-last row.
This is because for the first day, there is not a previous day’s price to compare to.
- Next, use the index “i” to get today’s data.
- The date is the first element in the “today’s data” list.
- The adjusted price is the last element in the list, which you can get with the index
-1.
- Because the dates are in decreasing order, we can get yesterday’s data using the index
“i+1”.
- Similarly, we can get yesterday’s price from the data array using index -1.
- We can now compute the daily return.
- The only data we will write to our “Google Returns” CSV is the date and the daily return.
- Run…
- If you look at the “Google returns” file, you will see the data is there.
- However, the date is not formatted in a user friendly way.
- Let’s fix this.
- We will use the “S-T-R-F time” method on today’s date and pass in the desired
format.
- This method name is short for “String Format Time”.
- We will use the same format used in the original CSV file.
- Now change the call to “write row” to use the formatted date and run…
- If you look at the “Google returns” CSV file, you will now see a more friendly-looking
set of data.
With Python, data and its commas are soon parted.
The CSV module gives you everything you need to responsibly read and write data.
And don’t forget - with big data, comes big responsibility.
So parse wisely, my friends.
Now go and subscribe to Socratica, because you never know when there’s something you
should know, but don’t know, but could know if you had only subscribed to Socratica...