Different Ways to Connect Google Drive to a Google Colab Notebook! (Part 2)

Continuing with the Different Ways to Connect Google Drive to a Google Colab Notebook this time I wanted to share ways of calling google sheet files just by their doc name and making it a Pandas Data frame. Also, I want to share the 2 different ways I have used to send data frames to a new google sheet file or update an existing one.

Google Colaboratory Code Snippets

There is something great about Google Colaboratory and is their code snippets that can help you with lots of things. From visualizations using the Altair library to database connections using python, and even a code snippet for using a webcam to capture images for processing on the runtime (Colab Kernel).

To access this Google Colaboratory Snippets:

1. Go to your Google Colab Notebook.
2. Click on the code symbol in the left-down part of the notebook.

3. It will prompt as a new tab automatically on the Notebook after clicking showing a list of code snippets.

4. You can use the Filter section to search the code snippet that you need.

This could help you if you forget the code for connecting to Google Drive or saving data to a Google Sheet as I will show you in this article.

Connecting to a specific google sheet and making it a Dataframe

In this first part, you will learn how to connect and bring google sheets data to a google colab notebook just by using the doc name.

1. You will authenticate
2. Then will bring google credentials for connection
3. Authorize the connection
4. Connect to the google sheet using its name (enter the name replacing the {} are inside of ”). And specify the sheet (tab) of the document.
5. Export all the data values.
6. Use pandas to convert it to a Pandas Dataframe

7. Done! Start your analysis.

   

Update an existing Google Sheet

This next code will help you bring the google sheet that already exists and update it with new data that you have in your Google Colab.

Since in the last point we learned to open a Google Sheet using its name, let’s use the Google Sheet key (series of numbers and letters in the URL that gives its unique key identifier).

1. Let us bring data from the vega_datasets, datasets that can be found in google collaboratory.

2. Now that we have the dataset, let’s make sure that it does not contain any Nan values because this will not be recognized through the update and will give us errors.
   • We can use the following code to see any columns that have null values and how many null values are presented:

cars.isnull().sum()

Since the null values presented in the data are numerical values we can fill those values with 0. Since this is only an exercise and we are not doing a deep analysis of the data, I will fill those with 0, but in the real world if we want to have good data, you should handle better those null values.

• Couple of ideas to handle null values that I have used in the past:
  1. Calculate the % of nulls in your column, if it is significant, you should consider dropping the column, it will not give you any insight if basically, the entire column is Nan.
  2. If the % is not at all significant and you see it can be handled, see if you can use mean value or use 0.
  3. Use a data Imputer (See my article Showing Buenos Aires properties in a Map with Plotly where I show how to use an imputer to fill null values in longitude and latitude data columns).

3. Filling Null values with 0

cars.fillna(0, inplace=True)

4. After filling those Null values with 0, let’s connect to the google sheet using the key. Here you will need to change {key} to the google sheet key that can be found in the URL

   

5. Now we need to transform our data into lists so the data can be recognized during the update, this is for showing google sheets where the data should be (cells) and which is the column name for each value. Before we make the update, we should be sure that all our data is in strings or int/float data types to be assigned to a list for the update.

   

Creating a new Spreadsheet using Google Colab and Adding Data

Finally, we are creating a new spreadsheet using Google Colab Notebooks and adding new data. This could help you in cases that you are using the google colab to make an analysis on some data that you brought and want to analyze it using google sheets, but don’t want to download it and then uploaded to a google sheet, or don’t want to update the spreadsheet that you already have and will like to use a new one for your output. There are different occasions that you can use this.

Is kind of the same as the code we have used before, but this time is only for creating a new spreadsheet from scratch.

With this, I finalize some of the Different Ways to Connect Google Drive to a Google Colab Notebook. These are some of the things I have used in the past in a collaboratory environment that have helped me a lot, most of all because is not needed for you to send your code or Jupyter Notebook for someone to run, install libraries, and create an environment for them to run it, also this helps on not sending files, which will take time, just share between co-workers or team members.

I hope this is very useful for yall!

And as always, any feedback you have I will be glad to know! If you have any other new topic that has to be with this and wants to know how to do it, just let me know, and I will write about it!

PD: don’t forget that all of these code snippets can be found in the google colab notebook under the code snippet feature!