Python — Spreadsheet Data Query

You know what, I can’t run away from SPREADSHEET. It’s following me doesn’t matter where I am or how well I structurally store the data at the database level; at the end of the day the same data will hit my table in the spreadsheet file format 😂 I met Pandas back in 2013 and this Python library is an awesome weapon — with active community. We can write query, chop and combine the data to get the expected outcome with Pandas. Check this simple use case (countries & population query) as your future reference and let’s fight spreadsheet in more heroic way. So.., who needs SQL? 😛

Query — Single Column

Objective : Select country in ASIA.

Query — Multiple Column

Objective : Select country in ASIA with population greater than 30M.

Full Script

import pandas as pdimport numpy as npfile_path = “/Users/johndoe/Desktop/Data Science/Countries of the world.xls”pd.set_option(‘display.float_format’, ‘{:.0f}’.format)df = pd.read_excel(file_path, skiprows=3)display(df)byregion = df[df[‘Region’].str.contains(“ASIA”, na=False)]display(byregion)df[‘Population’] = df[‘Population’].fillna(0)df[‘Population’] = df[‘Population’].astype(int)byregion = df[df[‘Region’].str.contains(“ASIA”, na=False) & df[‘Population’].ge(30000000)]display(byregion)




Software engineering and film. All are work-in-progress.

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Processing of industrial IoT data streams

Industrial pump photo by Christian Egeberg

Five Rules for Designing Tables

Introductory image showing two tables. Before and after

Deepwater Data

Big Changes and Big Data

The Future of Computing for Data Science

Formal Introduction to Statistical Inference

Natural Language Processing: Sentiment Analysis and the TextBlob Library

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store


Software engineering and film. All are work-in-progress.

More from Medium

Getting Started With Pandas

Getting started with pandas

Python Before SQL? Sure!

Useful Pandas Commands

Learn about Pandas package in Python