Work with 3D data in Pandas with code example

As of recent versions of Pandas (starting from version 0.25.0), Panel has been deprecated and eventually removed in version 1.0.0.

The Panel data structure was a 3D data container in Pandas, intended to manage data with three axes (e.g., time, items, and data dimensions).

However, there are alternative ways to work with 3D data in Pandas and similar libraries:

1. MultiIndex DataFrames: Using a Pandas DataFrame with a MultiIndex to simulate a Panel.
2. NumPy Arrays: Using NumPy arrays for handling 3D data directly.
3. xarray: The xarray library, which offers extensive support for N-dimensional data arrays and can handle 3D (and higher) datasets more naturally.

In this tutorial, we’ll explore these methods to manage 3D data in Python.

1. Using a MultiIndex DataFrame to Simulate a Panel

With a MultiIndex DataFrame, we can represent 3D data in a single DataFrame by setting multi-level indexes for the rows or columns.

Example 1: Creating a MultiIndex DataFrame

Let’s say we have data for three different items over two dates with two metrics per item. We can organize this as a 3D-like dataset using a MultiIndex.

import pandas as pd
import numpy as np

# Create sample data
data = {
    ("Item1", "Metric1"): [1.1, 1.2],
    ("Item1", "Metric2"): [1.3, 1.4],
    ("Item2", "Metric1"): [2.1, 2.2],
    ("Item2", "Metric2"): [2.3, 2.4],
    ("Item3", "Metric1"): [3.1, 3.2],
    ("Item3", "Metric2"): [3.3, 3.4],
}
index = pd.MultiIndex.from_product([["2022-01-01", "2022-01-02"]], names=["Date"])

# Create a DataFrame with a MultiIndex
df = pd.DataFrame(data, index=index)
print(df)

Output:

            Item1         Item2         Item3       
           Metric1 Metric2 Metric1 Metric2 Metric1 Metric2
Date                                                    
2022-01-01    1.1    1.3     2.1     2.3     3.1     3.3
2022-01-02    1.2    1.4     2.2     2.4     3.2     3.4

Explanation:

• Here, Item1, Item2, and Item3 act like a “3rd dimension” and are created as column headers with two metrics (Metric1, Metric2) per item.
• MultiIndex on the columns allows us to organize the data in a 3D-like structure, with the outer level representing items and the inner level representing metrics.

Example 2: Accessing Data in a MultiIndex DataFrame

You can access elements in this DataFrame by specifying the item and metric.

# Access data for "Item1" and "Metric1"
print(df["Item1"]["Metric1"])

# Access data for "Item2" on all metrics
print(df["Item2"])

Output:

Date
2022-01-01    1.1
2022-01-02    1.2
Name: Metric1, dtype: float64

            Metric1  Metric2
Date                        
2022-01-01      2.1      2.3
2022-01-02      2.2      2.4

Explanation:

• You can select data from different dimensions by specifying the column levels.

Example 3: Analyzing Data in a MultiIndex DataFrame

You can perform group-level operations, such as aggregations, by accessing the columns based on items or metrics.

# Calculate the mean for each metric across items
print(df.mean(level=1, axis=1))

Output:

            Metric1  Metric2
Date                        
2022-01-01      2.1      2.3
2022-01-02      2.2      2.4

• Here, we calculate the mean across each metric for all items.

2. Using NumPy Arrays for 3D Data

If you’re working with purely numerical data, you may want to use a NumPy array.

With a 3D array, you can handle three dimensions directly and use Pandas for converting specific slices back into DataFrames if needed.

Example 4: Creating and Accessing a 3D NumPy Array

import numpy as np

# Create a 3D NumPy array with shape (3 items, 2 dates, 2 metrics)
data = np.array([
    [[1.1, 1.2], [1.3, 1.4]],  # Item1
    [[2.1, 2.2], [2.3, 2.4]],  # Item2
    [[3.1, 3.2], [3.3, 3.4]]   # Item3
])

# Access data for Item2, first date, first metric
print("Item2, Date 1, Metric 1:", data[1, 0, 0])

# Convert a slice to DataFrame
df = pd.DataFrame(data[1], columns=["Metric1", "Metric2"], index=["2022-01-01", "2022-01-02"])
print(df)

Output:

Item2, Date 1, Metric 1: 2.1

            Metric1  Metric2
2022-01-01      2.1      2.2
2022-01-02      2.3      2.4

Explanation:

• The 3D array represents items, dates, and metrics.
• We access the element at Item2, Date 1, Metric 1 using the indices [1, 0, 0].
• You can convert specific slices back into DataFrames for further analysis.

3. Using xarray for N-Dimensional Data

The xarray library is built specifically for working with multi-dimensional arrays and is well-suited for datasets with labeled dimensions, such as those commonly found in climate data and physical sciences.

It works seamlessly with Pandas and NumPy.

Example 5: Creating a DataArray in xarray

If you have 3D data (e.g., time, items, and metrics), xarray can handle this data more intuitively than a DataFrame.

import xarray as xr

# Sample data for xarray DataArray
data = np.array([
    [[1.1, 1.2], [1.3, 1.4]],  # Item1
    [[2.1, 2.2], [2.3, 2.4]],  # Item2
    [[3.1, 3.2], [3.3, 3.4]]   # Item3
])

# Define dimensions and coordinates
items = ["Item1", "Item2", "Item3"]
dates = ["2022-01-01", "2022-01-02"]
metrics = ["Metric1", "Metric2"]

# Create an xarray DataArray
data_array = xr.DataArray(data, coords=[items, dates, metrics], dims=["Item", "Date", "Metric"])
print(data_array)

Output:

<xarray.DataArray (Item: 3, Date: 2, Metric: 2)>
array([[[1.1, 1.2],
        [1.3, 1.4]],

       [[2.1, 2.2],
        [2.3, 2.4]],

       [[3.1, 3.2],
        [3.3, 3.4]]])
Coordinates:
  * Item     (Item) <U5 'Item1' 'Item2' 'Item3'
  * Date     (Date) <U10 '2022-01-01' '2022-01-02'
  * Metric   (Metric) <U7 'Metric1' 'Metric2'

Example 6: Accessing Data in an xarray DataArray

xarray makes it easy to select data by dimension names, making code more readable and understandable.

# Access data for Item2 on 2022-01-01, Metric1
print(data_array.sel(Item="Item2", Date="2022-01-01", Metric="Metric1"))

# Access all metrics for Item1 on 2022-01-02
print(data_array.sel(Item="Item1", Date="2022-01-02"))

Output:

<xarray.DataArray ()>
array(2.1)

<xarray.DataArray (Metric: 2)>
array([1.3, 1.4])
Coordinates:
    Item     <U5 'Item1'
    Date     <U10 '2022-01-02'
  * Metric   (Metric) <U7 'Metric1' 'Metric2'

Explanation:

• Using sel() allows you to access elements by dimension names, making selection by Item, Date, and Metric more intuitive than with multi-indexing alone.

Example 7: Applying Operations in xarray

You can also apply operations across specific dimensions, similar to DataFrame operations.

# Calculate mean across the Date dimension
mean_data = data_array.mean(dim="Date")
print(mean_data)

Output:

<xarray.DataArray (Item: 3, Metric: 2)>
array([[1.2, 1.3],
       [2.2, 2.3],
       [3.2, 3.3]])
Coordinates:
  * Item     (Item) <U5 'Item1' 'Item2' 'Item3'
  * Metric   (Metric) <U7 'Metric1' 'Metric2'

Explanation:

• This calculates the mean of each metric for each item across both dates.

Summary of 3D Data Options

Conclusion

While Pandas Panel has been deprecated, there are effective alternatives for handling 3D data in Python:

1. MultiIndex DataFrames simulate 3D data with multi-level indexing.
2. NumPy arrays provide fast, efficient computation for numerical data.
3. xarray is ideal for N-dimensional labeled data and provides a high-level interface, making it well-suited for complex scientific datasets.