We think everyone should learn to program (at least a bit), and the secret to starting is finding an achievable project that is useful to you. For this mini-tutorial, let's turn to business users who increasingly have to deal with large data files (some examples here).
If you have python installed then tag-along (if you don't, quickly install it and also tag along). Type "cmd" in your Windows Start Menu to initiate a terminal, navigate to a directory with large files (in our case we downloaded a large CSV in the download folder) and follow the steps:
Step 1: open a file
Python provides a native library to interact with your operating system and its files. The following three commands list all files in a directory, and then open a specific file:
Congrats, although nothing visual happened, you've successfully loaded the file into the variable 'file_for_analysis'. Did you note it was instantaneous? Now let's find out what this file contains.
Step 2: first explorations
OK, so what's going on here. First we initialized a counter variable 'ctr'. Using the 'readlines()' command, we loop through the file line-by-line and increment the counter for each line. We print the first 10 lines and print the final ctr value after going through all lines in the file. In our case its 5,985,365 lines and executing the above lines takes about 7 seconds.
If you think you can do the same with Excel, this is what we got after 45 seconds of waiting for Excel to load the file:
This is not meant as Excel-bashing: we love Excel and use it a lot. However its not suited for big data files, and 5 lines of code have enabled us identify the size of the file and see some sample information. Let's keep going.
Step 3: more details
Let's say we want to understand how many different years there are in the data. Once we've split a line by the commas into a list of different sub-lines, we can check the text at the 3rd position. In most/all programming languages, the index of a list starts at 0, so the third item is at the index 2. This is something you quickly get used to so don't worry about it.
We create an empty set to store unique values. We store the year from each line into the set, and then print out its contents. If you want to be fancy and store the years in order, you can transform the set into a list and then sort it as so:
Step 4: let's get out of here
OK, we want to analyze the 2021 data in a tool we know better. But how to extract the data for 2021 years only? We can tweak our code to copy the lines with the 2021 date to a new file as so:
Yay, only 274,329 lines in the new csv file, which Excel will be able to open without a problem.
Putting it all togther
As the data arounds us continues to grow in size and quantity, we think everyone needs to find a way to explore large data-sets. We're skimming the surface here, things become a lot more exciting once you start tapping into data processing and visualization libraries, which are not that hard to use. We intend this as a fun little project for you to get to know Python better, and help if you have a large file you cannot open.
Finally, several years ago when some of us were consultants, we put together various functions into an executable and shared it within the team (split a file, random sample of lines, filter, etc.). It still works, so anyone who needs a quick solution is welcome to use this .exe file. If you don't trust the .exe file, you can use the source code here (note that it was programmed in Python 2.6, Python is now on the 3.x version, which provides better simpler faster functions).
If you have python installed then tag-along (if you don't, quickly install it and also tag along). Type "cmd" in your Windows Start Menu to initiate a terminal, navigate to a directory with large files (in our case we downloaded a large CSV in the download folder) and follow the steps:
Step 1: open a file
Python provides a native library to interact with your operating system and its files. The following three commands list all files in a directory, and then open a specific file:
import os
os.listdir(".")
file_for_analysis = open("Data7602DescendingYearOrder.csv")
Congrats, although nothing visual happened, you've successfully loaded the file into the variable 'file_for_analysis'. Did you note it was instantaneous? Now let's find out what this file contains.
Step 2: first explorations
ctr = 0
for l in file_for_analysis.readlines():
if ctr < 10: print( l )
ctr = ctr + 1
print("Total amount of lines:", ctr)
OK, so what's going on here. First we initialized a counter variable 'ctr'. Using the 'readlines()' command, we loop through the file line-by-line and increment the counter for each line. We print the first 10 lines and print the final ctr value after going through all lines in the file. In our case its 5,985,365 lines and executing the above lines takes about 7 seconds.
If you think you can do the same with Excel, this is what we got after 45 seconds of waiting for Excel to load the file:
This is not meant as Excel-bashing: we love Excel and use it a lot. However its not suited for big data files, and 5 lines of code have enabled us identify the size of the file and see some sample information. Let's keep going.
Step 3: more details
Let's say we want to understand how many different years there are in the data. Once we've split a line by the commas into a list of different sub-lines, we can check the text at the 3rd position. In most/all programming languages, the index of a list starts at 0, so the third item is at the index 2. This is something you quickly get used to so don't worry about it.
file_for_analysis.seek(0,0) # optional line to re-initialize the lines for reading
years_set = set([])
for l in file_for_analysis.readlines():
line_parts = l.split(",")
year_in_row = line_parts[2]
years_set.add( year_in_row )
print("Different years available:", years_set )
We create an empty set to store unique values. We store the year from each line into the set, and then print out its contents. If you want to be fancy and store the years in order, you can transform the set into a list and then sort it as so:
years_list = list( years_set )
years_list.sort()
print(years_list)
Step 4: let's get out of here
OK, we want to analyze the 2021 data in a tool we know better. But how to extract the data for 2021 years only? We can tweak our code to copy the lines with the 2021 date to a new file as so:
file_for_analysis.seek(0,0) # optional line to re-initialize the lines for reading
new_file = open("2021_data.csv", "w")
for l in file_for_analysis.readlines():
line_parts = l.split(",")
year_in_row = line_parts[2]
if year_in_row == "2021" or year_in_row == "year":
new_file.write( l )
new_file.close()
Yay, only 274,329 lines in the new csv file, which Excel will be able to open without a problem.
Putting it all togther
As the data arounds us continues to grow in size and quantity, we think everyone needs to find a way to explore large data-sets. We're skimming the surface here, things become a lot more exciting once you start tapping into data processing and visualization libraries, which are not that hard to use. We intend this as a fun little project for you to get to know Python better, and help if you have a large file you cannot open.
Finally, several years ago when some of us were consultants, we put together various functions into an executable and shared it within the team (split a file, random sample of lines, filter, etc.). It still works, so anyone who needs a quick solution is welcome to use this .exe file. If you don't trust the .exe file, you can use the source code here (note that it was programmed in Python 2.6, Python is now on the 3.x version, which provides better simpler faster functions).