PandasDataFrameOutputParser

Author: Jiwon Kim
Peer Review : Junseong Kim, Jaehun Choi
Proofread : Two-Jay
This is a part of LangChain Open Tutorial

Overview

This tutorial handles how LLM output could be controlled as pd.DataFrame format.

Pandas is a package, useful in a format of table (i.e. tabular data), widely used by data scientist. It will help you explore, clean, and process the data.

To learn more about pd.DataFrame and its capabilities, visit one of Pandas official tutorial,10 minutes to pandas.

PandasDataFrameOutputParser parses an output using Pandas DataFrame format, according to the official API doc

This can be utilized both as forming of structured output to LLM query in a string and as a forming of structured input for LLM query
- As an output, you can further look at the usage of pydantic in 01-PydanticOutputParser and elsewhere in this learning guide.
- As an input, pd.DataFrame dataset can be utilized, to make LLM interact with the data

Objective of this tutorial

Know where PandasDataFrameOutputParser is used to interact with pd.DataFrame

Data Acqusition

Access to figshare.com where you can discover the outputs of the academic research from csv to pdfs.

References

Internal Reference

01-PydanticOutputParser

Environment Setup

Set up the environment. You may refer to Environment Setup for more details.

[Note]

langchain-opentutorial is a package that provides a set of easy-to-use environment setup, useful functions and utilities for tutorials.
You can checkout the langchain-opentutorial for more details.

%%capture --no-stderr
%pip install langchain-opentutorial

%pip install pprintpp
%pip install typing

Collecting pprintpp
      Using cached pprintpp-0.4.0-py2.py3-none-any.whl.metadata (7.9 kB)
    Using cached pprintpp-0.4.0-py2.py3-none-any.whl (16 kB)
    Installing collected packages: pprintpp
    Successfully installed pprintpp-0.4.0
    Requirement already satisfied: typing in /Users/jiwonkim/Library/Caches/pypoetry/virtualenvs/langchain-opentutorial-BdlRz0c_-py3.11/lib/python3.11/site-packages (3.7.4.3)

# Install required packages
from langchain_opentutorial import package

package.install(
    [
        "pprint",  # Need installation
        "typing",  # Need installation
        "os",
        "pandas",
        "langchain",
        "langchain_core",
        "langchain_community",
        "langchain_openai",
    ],
    verbose=False,
    upgrade=False,
)

ERROR: Could not find a version that satisfies the requirement pprint (from versions: none)
    ERROR: No matching distribution found for pprint

# Set environment variables
from langchain_opentutorial import set_env

set_env(
    {
        "OPENAI_API_KEY": "",
        "LANGCHAIN_API_KEY": "",
        "LANGCHAIN_TRACING_V2": "true",
        "LANGCHAIN_ENDPOINT": "https://api.smith.langchain.com",
        "LANGCHAIN_PROJECT": "PandasDataFrameOutputParser",
    }
)

Environment variables have been set successfully.

You can alternatively set API keys such as OPENAI_API_KEY in a .env file and load them.

[Note] This is not necessary if you've already set the required API keys in previous steps.

# Load API keys from .env file
from dotenv import load_dotenv

load_dotenv(
    override=True, dotenv_path="../.env"
)  # override to my own in one-upper mother path

True

import pprint
from typing import Any, Dict
import os
import pandas as pd
from langchain.output_parsers import PandasDataFrameOutputParser
from langchain_core.prompts import PromptTemplate
from langchain_openai import ChatOpenAI

print(
    f'{os.environ["LANGCHAIN_PROJECT"]} project has been tracked at langsmith endpoint'
)

LANGCHAIN_PROJECT project has been tracked at langsmith endpoint

# Initiate Langchain OpenAI model (recommend using gpt-4o-mini)
model = ChatOpenAI(temperature=0, model_name="gpt-4o-mini")

Without model use

The format_parser_output function is used to convert parser output to dictionary format and format the output.
When input is pd.DataFrame or file.

# Purpose: Only to print. It is a helper function.
def format_parser_output(parser_output: Dict[str, Any]) -> None:
    length_val = len(list(parser_output.values()))
    if length_val == 1 and isinstance(parser_output.values, (int, float)):
        return {"result": parser_output}
        # Iterate around the "keys" of parser output (dict)
    else:
        for key in parser_output.keys():
            parser_output[key] = parser_output[key].to_dict()
            # Pretty Print
            return pprint.PrettyPrinter(width=4, compact=True).pprint(parser_output)

Since "typical" titanic.csv data that had been brought up multiple times in such tutorials, it is boring
Let's get from figshare.com
- Data Link
  - Permanent: Air-Traffic-Passenger-Statistics-csv.csv
  - Website: Air-Traffic-Passenger-Statistics-csv.csv

# Define dataframe
df = pd.read_csv("./data/Air-Traffic-Passenger-Statistics-csv.csv")
df.head()

Activity Period

Activity Period Start Date

Operating Airline

Operating Airline IATA Code

Published Airline

Published Airline IATA Code

GEO Summary

GEO Region

Activity Type Code

Price Category Code

Terminal

Boarding Area

Passenger Count

data_as_of

data_loaded_at

199907

1999/07/01

ATA Airlines

Domestic

Deplaned

Low Fare

Terminal 1

31432

2023/11/20 07:01:34 AM

2023/11/20 07:02:25 AM

199907

1999/07/01

ATA Airlines

Domestic

Enplaned

Low Fare

Terminal 1

31353

2023/11/20 07:01:34 AM

2023/11/20 07:02:25 AM

199907

1999/07/01

ATA Airlines

Domestic

Thru / Transit

Low Fare

Terminal 1

2518

2023/11/20 07:01:34 AM

2023/11/20 07:02:25 AM

199907

1999/07/01

Aeroflot Russian International Airlines

NaN

Aeroflot Russian International Airlines

NaN

International

Europe

Deplaned

Other

Terminal 2

1324

2023/11/20 07:01:34 AM

2023/11/20 07:02:25 AM

199907

1999/07/01

Aeroflot Russian International Airlines

NaN

Aeroflot Russian International Airlines

NaN

International

Europe

Enplaned

Other

Terminal 2

1198

2023/11/20 07:01:34 AM

2023/11/20 07:02:25 AM

df.columns

Index(['Activity Period', 'Activity Period Start Date', 'Operating Airline',
           'Operating Airline IATA Code', 'Published Airline',
           'Published Airline IATA Code', 'GEO Summary', 'GEO Region',
           'Activity Type Code', 'Price Category Code', 'Terminal',
           'Boarding Area', 'Passenger Count', 'data_as_of', 'data_loaded_at'],
          dtype='object')

df.columns = df.columns.str.strip().str.replace('"', "").str.replace("'", "")

# Set up a parser and inject instructions into the prompt template.
parser = PandasDataFrameOutputParser(dataframe=df)

# Print the parser's instructions.
print(parser.get_format_instructions())

The output should be formatted as a string as the operation, followed by a colon, followed by the column or row to be queried on, followed by optional array parameters.
    1. The column names are limited to the possible columns below.
    2. Arrays must either be a comma-separated list of numbers formatted as [1,3,5], or it must be in range of numbers formatted as [0..4].
    3. Remember that arrays are optional and not necessarily required.
    4. If the column is not in the possible columns or the operation is not a valid Pandas DataFrame operation, return why it is invalid as a sentence starting with either "Invalid column" or "Invalid operation".
    
    As an example, for the formats:
    1. String "column:num_legs" is a well-formatted instance which gets the column num_legs, where num_legs is a possible column.
    2. String "row:1" is a well-formatted instance which gets row 1.
    3. String "column:num_legs[1,2]" is a well-formatted instance which gets the column num_legs for rows 1 and 2, where num_legs is a possible column.
    4. String "row:1[num_legs]" is a well-formatted instance which gets row 1, but for just column num_legs, where num_legs is a possible column.
    5. String "mean:num_legs[1..3]" is a well-formatted instance which takes the mean of num_legs from rows 1 to 3, where num_legs is a possible column and mean is a valid Pandas DataFrame operation.
    6. String "do_something:num_legs" is a badly-formatted instance, where do_something is not a valid Pandas DataFrame operation.
    7. String "mean:invalid_col" is a badly-formatted instance, where invalid_col is not a possible column.
    
    Here are the possible columns:
    ```
    Activity Period, Activity Period Start Date, Operating Airline, Operating Airline IATA Code, Published Airline, Published Airline IATA Code, GEO Summary, GEO Region, Activity Type Code, Price Category Code, Terminal, Boarding Area, Passenger Count, data_as_of, data_loaded_at
    ```

Example of looking up a value for a column.

print(df["GEO Region"].head())

0        US
    1        US
    2        US
    3    Europe
    4    Europe
    Name: GEO Region, dtype: object

df.columns[7]

'GEO Region'

# Example of working with columns.

df_query = "column:GEO Region[0..4]"  # Replace with your intended query
output = parser.parse(df_query)
print(output)
print("----")
print("----")
format_parser_output(output)

{'GEO Region': 0        US
    1        US
    2        US
    3    Europe
    4    Europe
    Name: GEO Region, dtype: object}
    ----
    ----
    {'GEO Region': {0: 'US',
                    1: 'US',
                    2: 'US',
                    3: 'Europe',
                    4: 'Europe'}}

Example of a row lookup.

# Example of retrieving first row
df_query = "row:1"

# Row lookup
output = parser.parse(df_query)
format_parser_output(output)

{'1': {'Activity Period': 199907,
           'Activity Period Start Date': '1999/07/01',
           'Activity Type Code': 'Enplaned',
           'Boarding Area': 'B',
           'GEO Region': 'US',
           'GEO Summary': 'Domestic',
           'Operating Airline': 'ATA '
                                'Airlines',
           'Operating Airline IATA Code': 'TZ',
           'Passenger Count': 31353,
           'Price Category Code': 'Low '
                                  'Fare',
           'Published Airline': 'ATA '
                                'Airlines',
           'Published Airline IATA Code': 'TZ',
           'Terminal': 'Terminal '
                       '1',
           'data_as_of': '2023/11/20 '
                         '07:01:34 '
                         'AM',
           'data_loaded_at': '2023/11/20 '
                             '07:02:25 '
                             'AM'}}

Further Operations on Column

df["Passenger Count"].head(5)

0    31432
    1    31353
    2     2518
    3     1324
    4     1198
    Name: Passenger Count, dtype: int64

# Average passenger counts from 5 first rows
df["Passenger Count"].head(5).mean()

13565.0

# Querying the same operation as above
df_query = "mean:Passenger Count[0..4]"

output = parser.parse(df_query)
print(output)

{'mean': 13565.0}

Next Example is using value_counts()

df.head()

Activity Period

Activity Period Start Date

Operating Airline

Operating Airline IATA Code

Published Airline

Published Airline IATA Code

GEO Summary

GEO Region

Activity Type Code

Price Category Code

Terminal

Boarding Area

Passenger Count

data_as_of

data_loaded_at

199907

1999/07/01

ATA Airlines

Domestic

Deplaned

Low Fare

Terminal 1

31432

2023/11/20 07:01:34 AM

2023/11/20 07:02:25 AM

199907

1999/07/01

ATA Airlines

Domestic

Enplaned

Low Fare

Terminal 1

31353

2023/11/20 07:01:34 AM

2023/11/20 07:02:25 AM

199907

1999/07/01

ATA Airlines

Domestic

Thru / Transit

Low Fare

Terminal 1

2518

2023/11/20 07:01:34 AM

2023/11/20 07:02:25 AM

199907

1999/07/01

Aeroflot Russian International Airlines

NaN

Aeroflot Russian International Airlines

NaN

International

Europe

Deplaned

Other

Terminal 2

1324

2023/11/20 07:01:34 AM

2023/11/20 07:02:25 AM

199907

1999/07/01

Aeroflot Russian International Airlines

NaN

Aeroflot Russian International Airlines

NaN

International

Europe

Enplaned

Other

Terminal 2

1198

2023/11/20 07:01:34 AM

2023/11/20 07:02:25 AM

# Wrongly formatted query
df_query = "Calculate value_counts for `GEO` Region."

# Parse out the query
output = parser.parse(df_query)

# Print out result
print(output)

---------------------------------------------------------------------------

OutputParserException                     Traceback (most recent call last)

Cell In[22], line 5
      2 df_query = "Calculate value_counts for `GEO` Region."
      4 # Parse out the query
----> 5 output = parser.parse(df_query)
      7 # Print out result
      8 print(output)


File ~/Library/Caches/pypoetry/virtualenvs/langchain-opentutorial-BdlRz0c_-py3.11/lib/python3.11/site-packages/langchain/output_parsers/pandas_dataframe.py:85, in PandasDataFrameOutputParser.parse(self, request)
     83 splitted_request = request.strip().split(":")
     84 if len(splitted_request) != 2:
---> 85     raise OutputParserException(
     86         f"Request '{request}' is not correctly formatted. \
     87             Please refer to the format instructions."
     88     )
     89 result = {}
     90 try:


OutputParserException: Request 'Calculate value_counts for `GEO` Region.' is not correctly formatted.                     Please refer to the format instructions.
For troubleshooting, visit: https://python.langchain.com/docs/troubleshooting/errors/OUTPUT_PARSING_FAILURE

# Well formatted query
df_query = "value_counts:GEO Region"

# Parse out the query
output = parser.parse(df_query)

# Print out result
print(output)

{'value_counts': GEO Region
    US                     12475
    Asia                    7499
    Europe                  5758
    Canada                  3216
    Mexico                  2651
    Australia / Oceania     1712
    Central America          809
    Middle East              667
    South America             91
    Name: count, dtype: int64}

# Confirmed with the results
df["Operating Airline"].value_counts()

Operating Airline
    United Airlines - Pre 07/01/2013    3670
    United Airlines                     3368
    SkyWest Airlines                    2028
    Alaska Airlines                     1790
    Delta Air Lines                      772
                                        ... 
    Pacific Aviation                       2
    Casino Express                         1
    Boeing Company                         1
    Samsic Airport America, LLC            1
    Harmony Airways                        1
    Name: count, Length: 134, dtype: int64

Reflections
- Did not use the model

With model

# Query the column; Passenger count
df_query = "Query {Passenger count} column"

# Setting up the PromptTemplate
prompt = PromptTemplate(
    template="Answer the user query.\n{format_instructions}\n{query}\n",
    input_variables=["query"],  # Set up input variable. Look above {query}
    partial_variables={
        "format_instructions": parser.get_format_instructions()
    },  # Set up partial variable (how it would like to be displayed)
)

# Set up the chain
chain = prompt | model

# Execute the chain based on the query
chain_output = chain.invoke({"query": df_query})

# Use parser here
parser_output = parser.parse(chain_output.content[1:-1])

# Print out
format_parser_output(parser_output)

chain_output

AIMessage(content='"Invalid operation"', additional_kwargs={'refusal': None}, response_metadata={'token_usage': {'completion_tokens': 5, 'prompt_tokens': 479, 'total_tokens': 484, 'completion_tokens_details': {'accepted_prediction_tokens': 0, 'audio_tokens': 0, 'reasoning_tokens': 0, 'rejected_prediction_tokens': 0}, 'prompt_tokens_details': {'audio_tokens': 0, 'cached_tokens': 0}}, 'model_name': 'gpt-4o-mini-2024-07-18', 'system_fingerprint': 'fp_d02d531b47', 'finish_reason': 'stop', 'logprobs': None}, id='run-9dc3d6c3-edea-49ea-91f3-f26095e60ddc-0', usage_metadata={'input_tokens': 479, 'output_tokens': 5, 'total_tokens': 484, 'input_token_details': {'audio': 0, 'cache_read': 0}, 'output_token_details': {'audio': 0, 'reasoning': 0}})

df_query = "Calculate value counts for Operating Airline"
# Execute the chain based on the query
chain_output = chain.invoke({"query": df_query})

# Use parser here
parser_output = parser.parse(chain_output.content[1:-1])
print(parser_output)
# Print out
# format_parser_output(parser_output) unncessary

{'count': 34878}

# Average passenger counts from 5 first rows
df["Passenger Count"].head(5).mean()

df_query = "Calculate average passenger counts from 5 first rows"
# Execute the chain based on the query
chain_output = chain.invoke({"query": df_query})

# Use parser here
parser_output = parser.parse(chain_output.content[1:-1])
print(parser_output)
# Print out
# format_parser_output(parser_output) unncessary

{'mean': 13565.0}

PreviousJsonOutputParser NextDatetimeOutputParser

Last updated 7 months ago

Overview

Table of Contents

References

Environment Setup

Without model use

With model