language models can be used to interrogate different types of files and data structures. For example, CSV files, pandas data frames, JSON files, and SQL databases. Now, when working with language models, we can use a library called Langchain in order to make certain operations much easier. And what we're going to look at in this video is some of the toolkits that Langchain provides, and these toolkits, they're designed for specific tasks. We're going to look at three of them in this video. Firstly, we're going to look at the pandas agent. Secondly, the JSON agent. And thirdly, we're going to look at the SQL database agent. So, let's get started. I'm on the Langchain documentation for toolkits. I'll link this below the video, and we have a list of the different toolkits that Langchain provides out of the box. And you can see toolkits here like the Jira one, the JSON agent, which we're going to use, and also there are toolkits for Azure cognitive services and for Gmail and other tools as well. Now, we are

specifically going to look at the JSON agent. We're going to look at the pandas data frame agent, and we're also going to look at the SQL database agent as well. And you can see other agents at the bottom here for Vector stores, and if you're working with Spark clusters, for example, data frames and SQL agents on Spark, these are also available directly in the LangChain library. Now, to get started, I'm going to go to a Jupyter notebook, and I have installed in the notebook environment the LangChain and OpenAI Python libraries. And you can do that with this command here and put the exclamation mark at the start of the command if you're running it in a cell. That's going to then install those two libraries into your environment. Now, LangChain provides the toolkits, and we're going to call the OpenAI language model. So, we're going to install that library that allows us to call the API and get back responses from the language model. Now, once we've done that, what we're going to do is we're going to make sure

we have our DOT in fail defined and our project directory, and we can set a value there of openai API key, and you can set that equal to whatever your API key from open AIS. Once you've defined your dot end file, we're going to load from the Library dot in with a function called load.inf, and then just below that, we can call all that function, and that's going to say any environment variables that you've defined in the dot end file until your operating systems environment. We can then import the OS module from Python, and we can get the API Key by looking for OS dot Environ, and we can index in here at the open AI API key. Once we've executed that, that will load the key into this variable, and we can then use that in our calls to the openai API. So that's just some setup. We can now go to the documentation, and we're going to look at the pandas data frame agent to begin with, and this is an agent toolkit that you can use with langchin in order to interact and use Panda's.

data frames and get information from those data frames using your typical prompts. So let's copy these Imports into the environment. So let's go back to our code here, and I'm going to define a new cell and paste the Imports. So from langchain.agents, we're importing this function called create pandas data frame agent. And from the langchain.chat.models module, we're importing the chat open AI. And finally, below that, we are inputting this agent type construct from these agent types here. We're going to see what that means in a second. So let's go back to our documentation, and we have this file titanic.csv. I've used that in previous videos, but if we go to the directory structure, you can see I have copied that file to this local directory where the Jupyter notebook is running. And that means I can load this file into a pandas data frame directly from the current directory. And if you need this file of Titanic data, I will leave a link to this below the video. You can grab that and copy it to your file system. So

let's go back to the notebook, and we're going to read the data frame in on this cell here. So, importing pandas as PD. What we're going to do is call the PD dot read CSV function, and we can pass the name of the file to that, and then we store that in a variable called DF. We can then look at the head of that data frame, and we can see the data that we're getting back. So, the data frame head function will return, by default, five rows of data from the data frame, and you can see some of that data just below here. So, we have the data. Let's say we want to ask a question of that data. For example, we might want to ask, What's the average age of the passengers that were on the Titanic? What we can actually do here is go back to the documentation, and we can look at these two different calls that we can use to create pandas data frame agent. At the top, we have something called a zero-short react description, and that's the default agent type that

we get when we call the create pandas data frame agent function, and this is an agent that has no memory. It does not recall or know anything about previous calls to the OpenAI API, so it doesn't retain any memory. So, all of these are just One-Shot calls to the API with a particular prompt, and you get back your response, but there's no memory concept when you're using these. Now, below the zero-shot model, we also have the ability to use OpenAI functions. And with this one, the call to create Panda's data frame agent also takes a parameter called Agent type, and this time is set to agent type dot OpenAI functions. And this is an alternative to using the zero-shot model. If we go to Twitter for the moment, we can see the Line Chain account has tweeted this particular tweet. They have recently updated key agent toolkits to use OpenAI functions. So, previously, the SQL, CSV, and pandas agents, they all used the zero-shot react description agent that we've seen just in the documentation there, but this new one is

often faster and better. So, they have added this option. So, because of that, let's go back to the documentation, and we're going to copy this code here for the OpenAI function model. And let's go back to our Jupyter notebook, and what we're going to do is I'm actually going to do this in a new cell just below here. I'm going to paste that code in, and we can see that below. So, we're calling the create pandas data frame agent function, and we're passing in a chat OpenAI model to that. We set the temperature to zero in this case, and we're also setting the particular model that we want to call on the API. And the second parameter to the pandas agent is actually the data frame itself. And we can also pass some other options for both set to true, and we're also passing that OpenAI functions type. Let's execute this code, and what we can do on the cell below that is we can use that agent that we're getting back from calling that function, and that has a

function called run defined on it and then we can pass to the Run function our prompt. Now, the prompt that I just mentioned was this one here: What was the average age of the passengers? Let's execute that prompt, and you can see the line chain has been entered. And when we enter that chain, you can see that it's invoking what's called a tool in line chain, the Python REPL AST. And that is basically executing some Python code. And you can see the code that it's executing here. It's the data frame, we're indexing in on the age column, and we're getting the Min, which is exactly what you would write if you were defining the list yourself using code. And from that, you can see that the average age of the passengers is approximately 29.7 years. And if we copy that line of code and just quickly paste that below, you can see we get a result that is basically 29.7. So, this is quite similar in some ways to the Pandas AI videos that I did before, but this time we're using line chain, and we're

defining this agent here, the pandas data frame agent, and we can pass through that and open AI model and a data frame. And then when we get back the agent, we can provide the prompts to that agent using the dot run function. Now, be aware that when you're running this code, when you define the open AI model, you must have that open AI API key in your environment, or you can pass it as an argument here to that model. So, if you're getting any errors there, make sure you have the open AI key in your environment. Now, I'm going to go below here, and I'm going to paste another prompt. Let's get rid of this don't mean call. This time, we're going to ask where males or females are more likely to survive. Let's execute that again. We're entering the chain here, and we're getting back some more code that's been executed with this python tool. This time, the code that we're seeing here is the data frame dot group by function. So, we're grouping by the sex column and then we're indexing in at the survive

column, which is a Boolean column that tells us whether or not the passenger survived. And then we're getting the Min from those two groups of male and female, and that is returning this series of data below here, where the female and the male have a particular survey enable it. And below that, the output from the language model is that females were more likely to survive than males, and that the survival rate was approximately 2.16, and for males, it was 2.39. Now, if we go below here, I'm going to paste some code in to get the survival rate for males. So, what I'm going to do is pull out all of the entries in the data frame where the sex is equal to male. And then, what we're going to do is we're going to calculate all the number of meals that survived and divide that by the total number of males. And that gives us the rate of survival of 2.389, basically 2.39, as we got back from our model. So, again, what we're doing is

simply passing a prompt to the agent dot run method, and that allows us to interact with the OpenAI API and get back responses from the language model. And that also takes into account the data that's defined in our pandas data frame. And we can do all of this by using this create pandas data frame agent. So, that's very handy if you're working with data frames in your data, and you're getting back responses based on textual prompts to that data. Now, let's do one last prompt just below here. We're going to get the name and the age of the oldest person on board, and that gives us back this line here: The name of the oldest person was Algonon Henry Wilson, and his age was 80. And again, when line chain enters this chain here, you can see that it's invoking the Python tool, and it's executing this code to pull out the oldest person. So, we're looking at the age column, and we're getting the max age in that column, and then we're indexing

into the data frame to get back that single row for that person. So, that's very handy. We can interact with pandas data frames using this agent toolkit. Let's now move on to another one. If we go to the toolkits here, we're gonna go to the JSON agent now, and this is designed to interact with large JSON objects or dictionaries, and it's useful when you want to answer questions about JSON that's too large to fit in the context window of a language model. Now, the context window is basically the maximum number of characters that can be considered by the language model at a single time. What we're going to do in this example is we're going to get an open API specification, which defines the specification of an API, and we're gonna then take that specification, and we're going to ask questions of it using Python and line chain. Now, I'm going to link to this below the video, but we have a repository here, and this contains a JSON file defining the open API specification for an imaginary pet store.

API. You might have seen this if you've watched programming examples before, and if you're unfamiliar with these specifications, they describe your API in a particular way. For example, if we scroll down, you can see all of the endpoints that are available on the API. That's with this paths key, and each object within that refers to a specific endpoint and contains data such as the HTTP method that you can use with that endpoint. You can also get the parameters that you can send to the API endpoint. For example, in this case, we have tags and a limit parameter that you can send in the URL in order to alter the response that you're getting back. Below that, we can also get the response codes. For example, 200 if all goes well. There's an entry in this paths dictionary for every endpoint that's defined on your API. So, if we scroll down here, we can see another endpoint. We have the pits slash Dynamic ID endpoint is defined, and that will return an entity based on its ID. So, what we're going to do in this section of the

video is we're going to use the Json agent in line chain. So again, the link to this data file is below the video. You can copy that into your directory. I've already got it here, and it's called pet stores simple.json. If you go back to our documentation for this, I'm going to bring in a couple of imports here. So first of all, let's grab this Json toolkit from launching, and I'm going to paste that here just below. So this is defined in the agent toolkits module, and it's called Json toolkit. And we're also going to grab the Json specification, which is a particular tool that we can use. So let's copy that into the notebook just below this import. And before we go on, let's go back to the documentation, and we have these toolkits, but we also are importing Json spec from the tool module. If we go to the tools documentation, you can see that these are interfaces that agents can use to interact with the world. So they are functions that agents can use, and these tools can be genetic utilities for

example search. They can be other chains that are defined, and you can even have other agents as tools as well. The tool that we are looking at here is the Json spec tool. So, what I'm going to do is go to line chains source code on GitHub, and you can see some of the tools that are defined for Azure cognitive Services, Bing search, and so on. There's quite a lot of tools here. You can explore this if you want. If you're interested in a video on any of these tools, let me know. What we can do is load up the Json tool, and we're going to go to the source code for this, and we're going to have a look at this code or some of the tools that they find in this file when we're executing the code that we're about to use here. So, let's start by loading in our pet store file, the API definition. We're going to load that into the notebook. So, we're going to open a file, and it's that file there, and we're

going to open it as if and what we're also going to need here is the JSON module from the Python standard library. So, let's import that, and we're going to set data equal to the json.load function. We can pass the file descriptor into that function, and then we will get back the data for that API, as you can see below. So, now we have the data. Let's go back to the documentation for the JSON agent, and what we're going to do is copy these three lines of code, and we're going to bring them into the notebook. So, let's remove the data, and we're copying that in. So, we're setting up a JSON spec object, and we're passing in the data that we've loaded in as the dict argument here. So, this creates the JSON specification, and then we instantiate the JSON toolkit and pass that specification in as an argument. Below that, we're calling a function called create JSON agent, and we need to import that. So, let's go back to the documentation, and we're going to import

that from the linechain.agents module. We can do that at the top of the file here. So, we're calling the create Json agent function, and we're passing our model to that as the llm argument, and also a toolkit. Now, rather than using the open EI model, I'm actually going to scroll up here, and I'm going to change that to the same model that we used before. So, let's copy that line of code, and that saves us having to import the open AI model as well. So, let's remove this line of code, and we're going to paste in chat open EI model. So, let's try executing this now, and that gives us back this Json agent executor. And just like the pandas data frame agent, we can use the dot run function on the Json agent executor in order to run a prompt against the own Json data. Now, I'm going to use the prompt to ask what parameters are required or optional for this slash pits endpoint here. So, let's go back to the notebook, and I'm going to paste that.

prompt in here what parameters can we provide for the slash pits endpoint, and let's now execute the function again. We're entering our chain here, and you can see a specific action. In this case, it's the Json spec list case, so that lists out the keys at the top level of the Json file, and you can see the thoughts that are occurring below that. So, for example, it sees that there's a key called paths, so it then goes into that sub-object for paths, and it lists out the keys that are available there. And this is iterative; it keeps doing this, and eventually, it gets down to this action here where we're actually getting the value from a particular sub-object and the Json file. So, it's smart enough to go into each nested level of digestion until it finds something that matches what it's looking for. So, you can see two different types of actions here: we have the Json spec list keys and also the Json spec get value. And that's the whole idea of these toolkits; they chain together different operations that you

might want to do on a particular source of data, and the agent is smart enough to make decisions at each step about what to do based on the observations it's getting back. Now, if we go to the bottom here, you can see the final answer that's outputted by this particular operation, and the answer states that the parameters that can be provided for the slash pits endpoint are tags and limits. So, if we go back to the open API specification for this endpoint, we can go to the list of parameters that's defined here, and you can see that actually matches exactly what's defined to you. So, we have a parameter called Tags and also a parameter called limit. So, we were able to use our prompt to actually get information about this API specification, and we can use LINE chains Json agent toolkit in order to do that. Now, if you go back to GitHub, I loaded up the source code earlier on, and we have these Json specifications in this source code, but we also have these tools just below here. So, we have the Json list Keys.

tool, and that's a tool for listing out the keys in a JSON specification. And that's what was called, as you can see here in our code. So, we were calling the JSON spec list keys tool at each step in this process in order to try and find the right data. And if we go down even further, you can see the JSON get value tool. And that is what was called to actually extract a value from a particular key in the data structure. So, the line chain agent toolkits, such as the JSON token, are comprised of often multiple tools, and it chains together operations and makes smart decisions at each step based on what you're trying to achieve in your prompt. And we could then run further queries here, for example, as the limit parameter for the slash pits endpoint required. So, let's execute that, and we'll see what we get back once that's finished running. We can scroll down, we can see the output here, and it's returned just false to us. So, basically, it's telling us that the limit parameter is not required. If we go back to the

specification. We can see the limit parameter here, and the required field for that is set to false, so that is returning the correct result. So, that's the Json toolkit that you can use with langching. What we're going to do now is move on to the last example in this video, and that is the SQL database agent. This agent will build off of the SQL database chain that's defined in line chain, and it's designed to answer general questions about a database, as well as to recover from errors. So, what we're going to do is we're going to download a SQL Lite database file just for simplicity in this video, and I'm going to link this page below the video. The particular database that we're going to use is this one here, and that's just because it's very small. It doesn't contain a lot of data. It's only 0.1 megabytes. So, let's download that Florida file here, and this is for inmates on death row in Florida. So, I now have that file in my local directory. You can see

it here. It's the .SQL light file. So now that we have that, let's go back to the documentation, and we're going to import these three at the top here. So it's from langchain.agents. Let's import the create SQL agent. We're going to import from the agent toolkits module the SQL database toolkit. And finally, from langchain.sql database, we will import the SQL database object. Now let's go back to the notebook and paste the top three imports in there. And I'm going to create some new cells just below here. Now again, I'm going to create an llm object. So let's call that llm, and we're going to invoke or instantiate our chat OpenAI object. And again, that's going to call the GPT 3.5 turbo model. Just below that, I'm going to set a reference to our file, which is the SQL light file that we're going to load and then analyze using prompt. If we go back to the documentation here, what we're going to do is actually load the database using

the SQL database. Dot from URI function. And then what we can do is pass that database that we've loaded to the SQL database toolkit. So let's copy these lines of code and go back to our notebook. I'm going to paste those lines of code just below here, and we need to change the file that's being referenced here, right, other than that one. So I'm going to set our Python F-string here, and we're going to reference the file that we have got here in the cell above. That's the floor of the death row dot SQL light file. So we're going to load that file into a variable called DB, and then we pass that DB as a parameter to the SQL database toolkit and the LLM. I'm going to change this. I'm going to pass in the LLM that we have defined in the cell above here. So let's execute these cells, and just below that, what we're going to do if we go back to the documentation is we're going to grab this agent here. We're going to use the

zero shot react description agent. So, let's copy the agent code into our notebook, and again, we're going to pass the LLM directly rather than reference that OpenAI model. So, we have a call to the create SQL agent that's going to create that SQL database agent. And you can see, as well as the LLM, we're also passing in a toolkit here, and that's the SQL database toolkit we've defined in the sale above. And this time, we're setting the agent type to zero shot react description. So, let's execute that code and create this Agent X executor object. Now, what I'm going to do is we're going to have a look at the database and see what kind of data is stored in that database. So, we have a file here called Florida death row dot SQL Lite, and I have a tool called DB browser for SQL Lite installed, and we have the database open in that tool. We can see that there's one table in the database, and it's a table called inmates. We can right-click and browse this table. We can see

the data that's stored in this table, and we have, for example, a column for the inmate name, for the race and gender, for the dates of the offense and the sentence, and also the county where the offense occurred. So, that's the data. Now, we know what that looks like. Let's go back to our notebook, and we're going to refer to the SQL agent here, and we're going to call the dot run function on that agent, and let's pass the prompt to that. How many rows are in the inmates table? So, that enters a new chain, and it's going through some observations and some actions here. For example, the SQL DB list tables action, and because we put that particular name of the table in the query, it can detect that that's one of the tables, and then it's able to then dive into that table and analyze the schema and get back some results. So, you can see also below there's an action called SQL database query, and that takes as an input a particular SQL query. For example, select count from inmates, and

that's exactly the query we want to get back the number of rows in the table, and the output of that query is 492. What we can also do, if we scroll down, is execute another query, and we're going to check which county has the most inmates. So, let's execute that. We'll see if that gets a valid result, and once that's finished executing, you can see we get back the county Duval. Now, we can see above the SQL DB query action, we're going to copy this SQL statement, and I'm going to paste that into the SQLite browser. So, let's go back to the browser, and that has a tab called execute SQL. So, let's paste that in, and we can then execute that particular code, and that gives us back the Davao County, which had 72 inmates apparently. Now, in order to get all of them, we can remove the limit one clause that's been used in that SQL, and we can then re-execute this, and we get back all of the counties with the number of inmates that are in that.

county, and you can see that Deval is the number one County in the database. It's got the most inmates, and it's followed by Miami-Dade. And we're getting this output by using the SQL Group by statement, and then we're ordering by the column number of inmates, which is the Alias we're giving to the count all. So, LangChain's SQL token is smart enough to build up those SQL commands and execute them against the underlying database to return a particular result to you. Now, I will emphasize that these tools are not perfect. They can be very brittle, and you need to format your queries sometimes in a way that makes sense and will make it more obvious to the tools what they are actually trying to achieve and how to do that. And sometimes the output you get back from queries is not in a format that can be output immediately, and that can cause some issues with these types of operations as well. Now, if you look at LangChain's documentation, we have under the model IO section on output parcels, and that allows us to get back responses.

from a language model and then change the format of those responses or parse that response in a particular way. For example, you might want to output numbers instead of strings, or you might want to extract data from a textual output into a Python dictionary or something like that. Output parcels can help with that, but we're not going to cover that in this video. But it might be useful if you can use these with the agent toolkits and pass some of your output responses into a particular format. There's lots of options with line chains, a very versatile tool, and it's developing rapidly at the moment. So if you're interested in more videos on that, please let me know. Thank you for watching. If you've enjoyed this video, please like and subscribe to the channel, and we'll see you in the next video.

Langchain Agent Toolkits - Pandas DataFrame, JSON and SQL Database Agents

On this channel, we create tutorials and videos primarily about web development, data analytics and data science. We'll focus on implementations using primarily Python and JavaScript, occasionally diving into other languages. As the channel progresses, we will also touch on concepts such as DevOps, containerization, cloud computing (AWS) and machine learning/AI.

If you can spare any change, please consider buying me a coffee to help keep me motivated to write tutorials, make videos and write blog posts! It would be greatly appreciated: https://ko-fi.com/bugbytes

Blog: https://www.bugbytes.io/posts/
Twitter: https://twitter.com/bugbytesio
TikTok: https://www.tiktok.com/@bugbytes.io
Github: https://github.com/bugbytes-io
