Supercharge Your Tabular Data with Pandas and Generative AI

What if you could add the power of Generative AI, Amazon Bedrock, and Amazon Nova foundation models to your data workflows?

Laith Al-Saadoon

Published Dec 16, 2024

Last modified Dec 17, 2024

Pandas is the go-to Python library for data analysis, enabling you to slice, dice, and transform your data at scale. But what if you could go a step further and add the power of Generative AI to your data workflows?

A few people asked me how I sling tabular data, Pandas, and generative AI. So I thought I'd write a post about it.

In this post, I'll walk through how to combine Pandas with a generative AI model served through Amazon Bedrock and orchestrated by LangChain AWS . You will start by installing necessary packages, then I'll show you how to create a sample dataset, do some feature engineering, and finally apply generative AI transformations to your DataFrame. By the end, you’ll be able to supercharge your data analysis with machine-generated insights, summaries, and even structured output to extract new columns from rich text columns.

Prerequisites

Python 3.7+
AWS Credentials with proper permissions to access Amazon Bedrock (if you’re running locally)
Jupyter Notebook (optional but recommended for interactivity)
Familiarity with Pandas (if you're not, I recommend reading the Pandas documentation )
Enabled access to the foundation model (Amazon Nova Lite) in your AWS account. See Amazon Bedrock for more information.

Step 1: Install the necessary packages:

%pip install langchain-aws pandas langchain-core boto3 pydantic

Step 2: Setup a synthetic dataset

We're going to create a synthetic dataset with 10 rows and 8 columns. We'll use the numpy library to generate random data for each column.

Don't worry to much about the code for generating the dataset. We'll use the data later to apply generative AI transformations to the dataset. At this step, you would otherwise use pd.read_csv() or another Pandas method to read your structured data.

In brief, we're creating a dataset with the following columns:

bedrooms: Randomly generated number of bedrooms between 1 and 5
bathrooms: Randomly generated number of bathrooms between 1 and 3
sqft: Randomly generated square footage between 500 and 3500
location: Randomly generated location from a list of cities
view: Randomly generated view from a list of views
style: Randomly generated style from a list of styles
year_built: Randomly generated year built between 1900 and 2024
condition: Randomly generated condition from a list of conditions
parking: Randomly generated parking from a list of parking options
pool: Randomly generated pool from a list of pool options
price: Loosely dependent on the other features of the house

Your dataset will look different if you change the random seed.

The price of each property is calculated using the following formula:

f(price) = base_price × location_multiplier × view_multiplier × condition_multiplier

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
import pandas as pd
import numpy as np
from datetime import datetime

np.random.seed(42)

source_df = pd.DataFrame({
    "bedrooms": np.random.randint(1, 6, 10),
    "bathrooms": np.random.randint(1, 4, 10),
    "sqft": np.random.randint(500, 3500, 10),
    "location": np.random.choice(
        ["New York", "Los Angeles", "Chicago", "Houston", "Miami"], 10
    ),
    "view": np.random.choice(["park", "city", "ocean", "mountain", "lake"], 10),
    "style": np.random.choice(
        ["modern", "traditional", "art deco", "mid-century", "modern farmhouse"], 10
    ),
    "year_built": np.random.randint(datetime.now().year - 100, datetime.now().year, 10),
    "condition": np.random.choice(["excellent", "good", "fair", "poor"], 10),
    "parking": np.random.choice(["garage", "parking lot", "street", "driveway"], 10),
    "pool": np.random.choice(["yes", "no"], 10),
})

# Let's say price is somewhat dependent on the other features

# Create location multipliers
location_multipliers = {
    "New York": 2.0,
    "Los Angeles": 1.8,
    "Chicago": 1.2,
    "Houston": 1.0,
    "Miami": 1.5,
}

# Create view multipliers
view_multipliers = {
    "ocean": 1.4,
    "mountain": 1.2,
    "city": 1.1,
    "park": 1.05,
    "lake": 1.15,
}

# Create condition multipliers
condition_multipliers = {"excellent": 1.3, "good": 1.1, "fair": 0.9, "poor": 0.7}

source_df["price"] = (
    source_df["bedrooms"] * 100_000
    + source_df["bathrooms"] * 20_000
    + source_df["sqft"] * 300 / 10
) * (
    source_df["location"].map(location_multipliers)  # Location factor
    * source_df["view"].map(view_multipliers)  # View factor
    * source_df["condition"].map(condition_multipliers)  # Condition factor
    * (1 + (2024 - source_df["year_built"]) * -0.002)  # Age depreciation
).round()

source_df["price"] = source_df["price"].astype(int)

source_df.head()

Step 3: Feature engineering

Let's add a derived feature to the dataset. We'll add a new column called price_category that categorizes the house price into three categories: Low, Medium, and High automatically.

1
2
3
4
5
6
7
source_df["price_category"] = pd.cut(
    source_df["price"],
    bins=3,
    labels=["Low", "Medium", "High"],
)
# show High, Medium, Low examples
source_df.head()

Step 4: Initialize the LLM model

We'll use ChatBedrockConverse from LangChain AWS to call Amazon Bedrock and use Amazon Nova Lite as the LLM.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
import boto3
from langchain_aws.chat_models import ChatBedrockConverse

bedrock_client = boto3.client("bedrock-runtime")

# For more model information, see [Amazon Bedrock foundation model information](https://docs.aws.amazon.com/bedrock/latest/userguide/models-supported.html)
# The `us.` prefix enables Cross Region Inference for the model. For more information, see [Cross Region Inference](https://docs.aws.amazon.com/bedrock/latest/userguide/cross-region-inference.html)
model_id = "us.amazon.nova-lite-v1:0"

# Initialize the LLM model
llm = ChatBedrockConverse(
    model=model_id,
    client=bedrock_client,
    max_tokens=1000,
    temperature=1,
)

# Test to make sure you can invoke the model
response = llm.invoke("Hello, how are you?")
print(response.content)

Step 5: Generate a property description for each row

Let’s say we want to use the model to generate human-readable property descriptions based on the features. We can define a function that takes a single DataFrame row, crafts a prompt, sends it to the LLM, and returns a generated description. We'll also use our global statistics to provide context to the LLM about overall trends in the dataset.

We'll use pandas apply to apply the function to each row in the DataFrame.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
def generate_property_description(row: pd.Series) -> str:
    # unpack the columns into variables so we can use them in the prompt neatly
    location, view, style, year_built, condition = row[
        ["location", "view", "style", "year_built", "condition"]
    ]
    parking, pool, bedrooms, bathrooms, sqft, price = row[
        ["parking", "pool", "bedrooms", "bathrooms", "sqft", "price"]
    ]
    prompt = f"""You are a helpful real estate agent. You are given the following property attributes:"
        <property_attributes>
        - {location=}\n"
        - {view=}\n"
        - {style=}\n"
        - {year_built=}\n"
        - {condition=}\n"
        - {parking=}\n"
        - {pool=}\n"
        - {bedrooms=}\n"
        - {bathrooms=}\n"
        - {sqft=}\n"
        - {price=}\n\n"
        </property_attributes>

        Using the content from <property_attributes></property_attributes>,
        please write a short, appealing, and exciting real estate listing description of no more than 3 sentences."""

    # Use the chat_model to get a completion
    response = llm.invoke(prompt)
    return response.content

# apply the function and store the result in a new column in a new dataframe
df_with_descriptions = source_df.copy(deep=True)
df_with_descriptions["description"] = df_with_descriptions.apply(
    generate_property_description, axis=1
)

Let's check out the result!

1
2
3
from IPython.display import HTML

display(HTML(df_with_descriptions.head().to_html()))

Step 6: Use `groupby` to generate insights for groups of data

What if you want to generate insights for groups of data? For example, you might want to generate a summary of the dataset by location. You can use the groupby method to group the data by location and then apply to use a generative AI transformation for each group.

We’ll compute some global statistics about the dataset to provide context to the LLM. This will help our LLM produce richer summaries that consider how each group compares to the entire dataset.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# Numeric statistics DataFrame
numeric_stats = source_df.describe(include=[float, int])

# remove count row from numeric_stats
numeric_stats = numeric_stats.drop(index="count")

# Categorical column frequency DataFrame
cat_cols = source_df.select_dtypes(include=["object"]).columns
cat_freq = pd.concat(
    [source_df[col].value_counts() for col in cat_cols],
    keys=cat_cols,
    names=["column", "value"],
).to_frame("frequency")

display(numeric_stats)
display(cat_freq)

Let's create a new function that takes a group of data and generates a summary of the group. We'll use the groupby method to group the data by location and then apply to use a generative AI transformation for each group.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
def generate_group_summary(
    grouped_by: str, group_name: str, group: pd.DataFrame
) -> str:
    prompt = f"""You are a helpful real estate analyst. You are given the following real estate data:"

        <global_stats>
        - {numeric_stats.to_json()}
        - {cat_freq.to_json()}
        </global_stats>

        <group_data>
        - {grouped_by}: {group_name}
        - {group.to_json()}
        </group_data>

        Use the content of <global_stats></global_stats> and <group_data></group_data>
        to write a concise summary of the group and how it relates to the global statistics."""

    response = llm.invoke(prompt)
    return response.content

# apply the function and store the result in a new column in a new dataframe
group_summaries = source_df.groupby(
    "location",
).apply(lambda x: generate_group_summary("location", x.name, x), include_groups=False)

Drum roll...

1
2
3
from IPython.display import Markdown

display(Markdown(group_summaries.iloc[0]))

Let's try another example. What if we want to generate a summary of the dataset by price category?

1
2
3
4
5
6
7
8
price_category_summaries = source_df.groupby(
    "price_category",
    observed=True,
).apply(
    lambda x: generate_group_summary("price_category", x.name, x), include_groups=False
)

display(Markdown(price_category_summaries.iloc[0]))

Bonus! Extract structured data from a long-form text column into new columns

Let's say we have a column with a long-form text description of a property and we want to extract structured data from it. We can use a generative AI model to do this. Conceptually, we will go the opposite direction of the previous examples. Instead of generating a summary or description, we will generate structured data from a text column.

Let's start by reviewing our description column and pretend that we are starting from only the description column.

1
2
3
4
descriptions_only = df_with_descriptions["description"].to_frame()

with pd.option_context("display.max_colwidth", None):
    display(descriptions_only)

We can use a generative AI model to extract structured data from the text column. We'll use the ChatBedrockConverse and Pydantic to do this.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
from pydantic import BaseModel, Field

class PropertyData(BaseModel):
    """
    Extract the property data from the provided description.
    """

    bedrooms: str = Field(description="The number of bedrooms in the property")
    bathrooms: str = Field(description="The number of bathrooms in the property")
    sqft: str = Field(description="The square footage of the property")
    price: str = Field(description="The price of the property")

llm.temperature = 0
structured_output_llm = llm.with_structured_output(PropertyData)
regenerated_df = pd.concat(
    [
        descriptions_only,
        pd.json_normalize(
            descriptions_only["description"].apply(
                lambda x: structured_output_llm.invoke(x).model_dump()
            )
        ),
    ],
    axis=1,
)

And check out the results!

1
2
with pd.option_context("display.max_colwidth", None):
    display(regenerated_df.sort_values(by="price", ascending=False))

Conclusion

In this post, you've seen how to use Pandas and Generative AI with Amazon Bedrock to supercharge your data analysis. You've seen how to generate summaries, descriptions, and structured data from a dataset. You've also seen how to use groupby to generate insights for groups of data.

I hope you found this post useful for your own projects. If you have any questions, please feel free to reach out to me on LinkedIn .

# generative-ai # pandas # data # python # amazon-bedrock

Any opinions in this article are those of the individual author and may not reflect the opinions of AWS.

Enjoyed reading this content? Let the author know!

Your likes, comments, shares, and saves help creators reach more builders.

Loading recommendations

Loading article

Builder Center

Supercharge Your Tabular Data with Pandas and Generative AI

Laith Al-Saadoon

Prerequisites

Step 1: Install the necessary packages:

Step 2: Setup a synthetic dataset

Step 3: Feature engineering

Step 4: Initialize the LLM model

Step 5: Generate a property description for each row

Step 6: Use groupby to generate insights for groups of data

Bonus! Extract structured data from a long-form text column into new columns

Conclusion

Step 6: Use `groupby` to generate insights for groups of data