Complete Guide: Working with CSV/Excel Files and EDA in Python

This hands-on tutorial will walk you through the entire process of working with CSV/Excel files and conducting exploratory data analysis (EDA) in Python. We’ll use a realistic e-commerce sales dataset that includes transactions, customer information, inventory data, and more.

Introduction

Data analysis is an essential skill in today’s data-driven world. In this tutorial, we’ll learn how to:

• Import data from Excel files
• Clean and preprocess data
• Explore and analyze data through statistics and visualization
• Draw meaningful insights from business data

We’ll be using several key Python libraries:

• pandas: For data manipulation and analysis
• numpy: For numerical operations
• matplotlib and seaborn: For data visualization

Setting Up Your Environment

First, let’s install the necessary libraries:

• openpyxl and xlrd are backends that pandas uses to read Excel files
• Import the libraries in your Python script:

Understanding Our Dataset

Our sample dataset represents an e-commerce company’s sales data. It contains five sheets:

1. Sales_Data: Main transactional data with 1,000 orders
2. Customer_Data: Customer demographic information
3. Inventory: Product inventory details
4. Monthly_Summary: Pre-aggregated monthly sales data
5. Data_Issues: A sample of data with intentional quality problems for practice

You can download the dataset here

Reading Excel Files

Now that we have our dataset, let’s start by reading the Excel file:

You should see output showing the available sheets and their dimensions.

Reading Specific Rows or Columns

Sometimes you might only want to read specific parts of a large Excel file:

Basic Data Exploration

Let’s explore our sales data to understand its structure and contents:

Let’s look at the distribution of orders across different categories and regions:

Data Cleaning and Preparation

Let’s practice data cleaning using the “Data_Issues” sheet, which was specifically created with common data problems:

Now let’s clean the data:

Let’s also clean our main sales data:

Merging and Joining Data

Now let’s combine data from different sheets to gain richer insights:

Let’s also join inventory data to analyze product-level metrics:

Exploratory Data Analysis

Now let’s perform some meaningful exploratory data analysis to understand our business:

Sales Performance Analysis

Customer Segment Analysis

Payment Method Analysis

Return Rate Analysis

Cross-Tabulation Analysis

Correlation Analysis

Data Visualization

Now let’s create visualizations to better understand our data:

Basic Visualizations

Advanced Visualizations with Seaborn

Complex Visualizations

Conclusion

In this tutorial, we explored the full workflow of handling CSV and Excel files in Python, from importing and cleaning raw data to conducting insightful exploratory data analysis (EDA). Using a realistic e-commerce dataset, we learned how to merge and join datasets, handle common data quality issues, and extract key business insights through statistical analysis and visualization. We also covered essential Python libraries like pandas, NumPy, matplotlib, and seaborn. By the end, you should be equipped with practical EDA skills to transform raw data into actionable insights for real-world applications.

The post Complete Guide: Working with CSV/Excel Files and EDA in Python appeared first on MarkTechPost.