Pandas Query API

A lightweight Python REST API built with FastAPI, Pandas, and Pydantic.

The project loads a customer dataset from CSV, uses Pandas to filter customer records based on query parameters, and returns a validated JSON response through a FastAPI endpoint.

This project was built as a Python/Pandas practice project to refresh Python skills and practice working with Pandas, FastAPI, Pydantic, and automated testing.

Features

• Load customer data from a CSV file using Pandas.
• Filter customers by country, status, plan, and signup date.
• Combine multiple filters in a single request.
• Return the number of matching customers.
• Return matching customer records as JSON.
• Validate API responses with Pydantic.
• Test Pandas filtering logic with pytest.
• Test FastAPI endpoints with TestClient.

Tech Stack

• Python
• FastAPI — API framework
• Pandas — data loading and filtering
• Pydantic — response validation
• pytest — automated testing
• Uvicorn — development server

Project Structure

pandas-query-api/
├── app/
│   ├── __init__.py
│   ├── main.py
│   ├── models.py
│   └── services.py
├── data/
│   └── customer.csv
├── tests/
│   ├── test_api.py
│   └── test_services.py
├── venv/
├── README.md
└── requirements.txt

Setup

1. Clone the repository

git clone <your-repository-url>
cd pandas-query-api

2. Create a virtual environment

Windows:

python -m venv venv
venv\Scripts\activate

macOS/Linux:

python3 -m venv venv
source venv/bin/activate

3. Install dependencies

pip install -r requirements.txt

Running the API

From the project root:

uvicorn app.main:app --reload

The API will be available at:

http://127.0.0.1:8000

API documentation

Swagger UI:

http://127.0.0.1:8000/docs

ReDoc:

http://127.0.0.1:8000/redoc

API Endpoints

GET /

Returns a simple message confirming that the API is running.

Example response:

{
  "message": "Hello, pandas query api"
}

GET /customers

Returns all customers when no query parameters are supplied.

Example:

GET /customers

Response structure:

{
  "count": 100,
  "results": [
    {
      "id": 1,
      "name": "John Smith",
      "email": "john@example.com",
      "country": "Nigeria",
      "age": 30,
      "status": "active",
      "signup_date": "2024-01-15",
      "plan": "pro"
    }
  ]
}

The results array contains the customer records matching the request.

Query Parameters

The /customers endpoint supports these optional parameters:

Parameter	Type	Description
country	string	Filter customers by country
status	string	Filter customers by status
plan	string	Filter customers by subscription plan
signup_before	date (YYYY-MM-DD)	Customers who signed up before the specified date
signup_after	date (YYYY-MM-DD)	Customers who signed up after the specified date

Multiple query parameters can be combined. All supplied filters are applied together.

Examples

Filter by country:

GET /customers?country=Nigeria

Filter by status:

GET /customers?status=active

Filter by plan:

GET /customers?plan=pro

Customers who signed up before January 1, 2024:

GET /customers?signup_before=2024-01-01

Customers who signed up after January 1, 2024:

GET /customers?signup_after=2024-01-01

Combine filters:

GET /customers?country=Nigeria&signup_after=2024-01-01

Empty Results

If no customers match the supplied filters, the API returns:

{
  "count": 0,
  "results": []
}

Response Model

The API uses Pydantic models to validate the response.

CustomerResponse
├── count: int
└── results: list[Customer]

Each customer contains:

Customer
├── id: int
├── name: str
├── email: str
├── country: str
├── age: int
├── status: str
├── signup_date: str
└── plan: str

Testing

The project contains unit tests for the filtering service and integration tests for the API.

Run the complete test suite with:

pytest

Service tests

tests/test_services.py covers:

• No filters
• Country filtering
• Status filtering
• Plan filtering
• Combined filters
• No matching customers

API tests

tests/test_api.py covers:

• Fetching all customers
• Country filtering
• Status filtering
• Plan filtering
• Combined filters
• Non-existing countries
• Signup date filtering
• Combining date filters with other filters

Data Processing

The customer CSV is loaded when the FastAPI application starts.

The signup_date column is converted to Pandas datetime values:

data["signup_date"] = pd.to_datetime(
    data["signup_date"],
    errors="coerce"
)

This allows date comparisons such as:

signup_date < signup_before
signup_date > signup_after

The filtering logic is kept in services.py rather than directly inside the API route.

The application therefore has a simple separation of responsibilities:

main.py
    ↓
Handles HTTP requests and responses

services.py
    ↓
Handles Pandas filtering

models.py
    ↓
Defines and validates response structures

tests/
    ↓
Verifies service and API behavior

Example Requests with curl

Get all customers:

curl "http://127.0.0.1:8000/customers"

Filter by country:

curl "http://127.0.0.1:8000/customers?country=Nigeria"

Filter by status:

curl "http://127.0.0.1:8000/customers?status=active"

Filter by plan:

curl "http://127.0.0.1:8000/customers?plan=pro"

Filter by signup date:

curl "http://127.0.0.1:8000/customers?signup_after=2024-01-01"

Combine filters:

curl "http://127.0.0.1:8000/customers?country=Nigeria&plan=pro"

What I Practiced

This project was designed as a practical Python refresher and covered:

• Python type hints
• Virtual environments
• Project structure
• Pandas DataFrames
• CSV data loading
• DataFrame filtering with boolean conditions
• Pandas datetime conversion
• FastAPI routing
• Query parameters
• Pydantic models
• API response validation
• Separation of application and service logic
• Automated testing with pytest
• FastAPI integration testing with TestClient

Possible Future Improvements

The current project intentionally stays lightweight. Possible future extensions include:

• Pagination
• Sorting
• More advanced filtering
• Better validation/error handling
• Centralized data loading
• Database-backed customer data
• Authentication
• API versioning
• Dockerization
• Deployment

These are outside the current scope of this practice project.

License

This project is intended as a personal learning/practice project.