14.2 Uploading Dataset

Uploading Excel or csv Files from Your Computer

If your data file is in Excel spreadsheet or csv (comma separated value) format stored in your local computer, you can run the code-cell below to upload the file in to Google Colab.

from google.colab import files
uploaded = files.upload()

After running the code, click the Choose Files button and browse your computer's folder to locate your Excel or csv file. Open the file. Colab will upload it with the same file name, for example file_name.xlsx. Next, you will need to run the code-cell below to convert it to pandas dataframe.

import io
import pandas as pd

excel_file = io.BytesIO(uploaded['file_name.xlsx'])
x_df = pd.read_excel(excel_file)

Make sure the file names match. Now your dataframe, x_df, is ready for analysis and plotting.

For a csv file run the code-cell below

import io
import pandas as pd

csv_file = io.StringIO(uploaded['MyData.csv'].decode('utf-8'))
x_df = pd.read_csv(csv_file)

Uploading csv Dataset From URL

Watch demo video

If you want to upload a csv file from the internet with url address, use the code below. Replace the web_link_address with your url address first.

import pandas as pd
url = `www_link_address`

x_df = pd.read_csv(url)

Example: Write a program to read the dataset (URL below) and display the shape (size) of the dataset. In addition, display the first and last five rows of the dataset.

my_csv_file.csv

import pandas as pd

url = 'my_csv_file.csv'

x_df = pd.read_csv(url)

x_df.shape   # displays the number of rows and columns

Show expected output

(180, 8)

Interpretation: There are 180 rows and 8 columns in x_df.

x_df   # displays the frist 5 and the last 5 rows

	id	gender	section	time	level	test1_score	test2_score	test3_score
0	1	female	C	17.3	low	65.4	84.3	51.9
1	2	female	D	5.1	low	72.4	86.1	52.0
2	3	female	A	2.4	low	73.8	84.2	53.1
3	4	female	A	8.8	low	70.2	87.9	52.0
4	5	male	B	15.0	low	60.3	81.4	55.5
...	...	...	...	...	...	...	...	...
175	176	male	B	13.2	high	78.8	89.3	52.5
176	177	female	B	12.6	high	76.5	83.4	50.0
177	178	male	D	8.4	high	78.1	86.3	51.0
178	179	male	C	9.5	high	76.6	86.6	52.4
179	180	male	C	16.2	high	83.1	89.4	54.1

Cleaning Data

Common cleaning data techniques include removing duplicate columns or observations (rows), combining or summarizing columns and replacing or deleting missing values.

To drop a column use

x_df.drop(['column_name'],axis=1,inplace=True)

To drop duplcate rows by keeping only the first observation, use

x_df.drop_duplicates(subset='column_name', keep="first",inplace=True)

To combine columns and make a new one, for example average use

df['average']=(x_df['test1']+x_df['test2]+x_df['test3])/3

To drop the rows where at least one element is missing, use

x_df.dropna

To drop the columns where at least one element is missing, use

x_df.dropna(axis='columns')

x_df.isnull.sum returns the number of missing values for each column
To replace missing (NaN) values with the mean, use

x_df['column_name'].fillna(value=df['column_name'].mean, inplace=True)

Downloading Data as csv file to your computer

If you make changes to your x_df in Google colab and want to save or download your updated x_df to your local computer you can follow the steps below. For more on updating your dataframe, see the Cleaning Data section above.

first run the code-cell below

from google.colab import drive

then on a new code-cell run

drive.mount('/drive')

You will receive a message that says go to the url link to get authorization from your Google account. Click the link, then choose your Google Account (if you have more than one), then scroll down and click allow. Next you will need to copy the authorization code. Then go back to your Colab, paste the code where it says Enter Authorization Code and press enter.

You will see a new message: Mounted at /drive

Now you will need to choose a folder in your Google Drive where the file is to be downloaded or saved. For example:

x_df.to_csv('/drive/My Drive/Yiakl/my_csv_file.csv')

your dataframe name is x_df and your file will be downloaded to your Google Drive under the Yiakl folder. The name of the downloaded file is chosen here to be my_csv_file.csv.

Adapted from Python for Introductory Statistics, by Simon Aman (Truman College, City Colleges of Chicago), licensed under CC BY 4.0. Changes were made: reformatted as an accessible XYZ web edition with live in-browser code cells. License: CC-BY-4.0.