Read Messy Excel Files Like a Pro

Read Messy Excel Files Like a Pro

Using pandas to read less than ideal excel formats. Experience it because it is very useful for you.

Using pandas to read less than ideal excel formats

As data scientists, we often interface with non-technical colleagues. When they provide data in the form of a spreadsheet, they probably don’t think about how pd.read_excel() will perform on our end, it’s just not their job to format their data to favor our tech stack.

Luckily, there are ways we can remedy this using pandas. In this article, I will walk you through some code I wrote to handle such a spreadsheet. One thing to note is the pd.read_excel() file will usually return something, so debugging issues with spreadsheet structure rests solely on manually inspecting the resulting dataframe.

The Spreadsheet

Let’s start by taking a look at the spreadsheet. This was generated by software used internally, so it’s something I encounter regularly. You’ll see that the spreadsheet has the following hurdles:

  1. The header row is not the first row
  2. the last row of the data is not the last row in the spreadsheet
  3. the first column of the data is not the first column of the spreadsheet
  4. There are cell group labels (Customer Status and Region) which only exist in one row, so most of the rows will return NaN

data-science excel pandas data python

Bootstrap 5 Complete Course with Examples

Bootstrap 5 Tutorial - Bootstrap 5 Crash Course for Beginners

Nest.JS Tutorial for Beginners

Hello Vue 3: A First Look at Vue 3 and the Composition API

Building a simple Applications with Vue 3

Deno Crash Course: Explore Deno and Create a full REST API with Deno

How to Build a Real-time Chat App with Deno and WebSockets

Convert HTML to Markdown Online

HTML entity encoder decoder Online

Data Science With Python Training | Python Data Science Course | Intellipaat

🔵 Intellipaat Data Science with Python course: https://intellipaat.com/python-for-data-science-training/In this Data Science With Python Training video, you...

How To Build A Data Science Career In 2021

In Conversation With Dr Suman Sanyal, NIIT University,he shares his insights on how universities can contribute to this highly promising sector and what aspirants can do to build a successful data science career.

Data Science with Python Certification Training in Chennai

Enroll in our Data Science with Python training in Chennai. Best Data Science with Python Training courses in Chennai for 100% Job Placements Support.

Python for Data Science | Data Science With Python | Python Data Science Tutorial

🔥Intellipaat Python for Data Science Course: https://intellipaat.com/python-for-data-science-training/In this python for data science video you will learn e...

Applied Data Science with Python Certification Training Course -IgmGuru

Master Applied Data Science with Python and get noticed by the top Hiring Companies with IgmGuru's Data Science with Python Certification Program. Enroll Now