In this notebook, you're going to build the agent that's going to be used throughout the rest of this course. As you've seen, this is an example agent that uses a few different tools and a router to answer questions about a data file that's attached. So first thing you want to do is import a few different libraries that are going to be used by this agent. So you have an OpenAI library, Pandas, Json, and then a library called duckDB, which gets used for some of the SQL code that you're going to run, as well as pydantic, some markdown displaying, and then some helper methods that are used to gather the OpenAI key and a few other kind of things specific to your environment here. And then next thing you're going to do is you'll want to set up your OpenAI client that will be used throughout this agent. In this case, using GPT-4o-mini. And then you're ready to start defining the tools. So as mentioned there's three tools that the agent is going to have. The first is a database lookup tool that it will use. And so there's a local file in the notebook that is store sales price elasticity promotions data file. So, here's a path to that file that's going to be used. And this is a parquet file which is just a file that's going to be used to simulate the database that your agent can interact with. And this file contains a bunch of transaction data of different store sales. So you can imagine every time there's a sale in a particular store, one new entry gets added to this file with the product skew and with the any promotions attached to it, as well as the price and the cost of that sale. So now, there's a few steps to this first tool. First, the tool needs to generate SQL that can be run on this database that you have locally. And then it needs to actually run that SQL command and get the result back. So to generate the SQL you're going to use a prompt here. So you'll need a prompt to actually generate the SQL code. And so you can see that here generate a SQL query based on a prompt. Do not reply with anything besides the SQL query. And you can see the prompt is going to be passed in as a variable, as are the columns for the table and the table name, so that the LLM knows what available columns there are. What the table is, which is critical when it creates the SQL code that's going to run. So now you can set up a method to actually generate the SQL code itself, using that GPT-4o-mini model that you established. So here is the generated SQL method that you might use. So it takes in a prompt list of columns and then table name. And then it formats this SQL generation prompt that you have up here with those different variables. So you'll see the prompt, columns and table name passed in. And then that formatted prompt will be sent over to GPT-4o-mini as a user message. And then that response will get returned. And now you need to incorporate that SQL generation code into a tool that can be used by the agent to actually run the code after it's been generated. And so for that you'll find another method here. In this case, this is going to be called lookup sales data. And so what this method will do is it takes in a prompt. And then it will try to first create the database from that local parquet file. So you can see it creates a data frame here using pandas with that local file. And then it uses the DuckDB library here to say create this table if it doesn't exist already, which will create a SQL database table based on that data frame that you have. And then next, you'll use the generate SQL query method that you just created to create an SQL query here. And that can then be slightly modified here to remove any extra trending white spaces or any other extra characters here, and then run using duckDB to receive a result. This step right here can be useful because oftentimes you'll have the LLM respond with SQL at the top to kind of format the response there. It doesn't just respond with code. Sometimes it includes that extra SQL bit, which is why you're removing some of those characters here. DuckDB here is used to take the data frame that you have and turn it into, a corner database in memory that you can access. And that will make it easier to run any kinds of, queries across that database and will work fairly efficiently and very quickly. And it also allows you to use generated SQL code and query that database using SQL code in a very easy way. Now that you have your tool defined here, it's always good to make sure that the tool is actually working. So you can put in an example query here, print out the result, say example data and call your method with show me the sales data for store 1320 on November 1st, 2021, and you can run that. It might take a second to run and you should get a response back. Should get a response back that looks something like this. So you see data that's been retrieved from your SQL database. Great. That's one tool down. Two more to go. So next tool is a data analysis tool. And so this is a tool that can take in data and then makes an LLM call to conduct analysis and capture trends from that data. So, similar to before you're going to want to have a prompt for that particular tool. In this case, it's pretty straightforward. It's analyze the following data. And there's a variable for data. And then your job is to answer the following question. And there's a variable for the prompt. Now you'll have a method to actually make that call. So you can have an analyze sales data method here that takes in a prompt and some data. And then similar to before, you're going to format the prompt you just created with those parameters, make a call to your OpenAI client and get a response back as the analysis. And then you can always do some little bit of error checking here to see if the analysis doesn't come back for whatever reason, then you can still return within no analysis could be generated. This kind of thing is especially helpful in agents because if you have a response that breaks halfway through your agent, you want to make sure that that doesn't cascade and break everything else inside of your agent. So, good error checking is almost more important in agents than it can be in other tools. And now, with your analyze sales data method defined, you can test and make sure that that's working. So you can call analyze sales data the prompt: What trends do you see in this data? And you can pass in the example data that you retrieved with the previous tool. And you should get back something that looks like this, which is some markdown formatted response from your model. And moving on to the third and final tool that the agent will use here. This third tool is a data visualization tool. And if you think back to the previous lesson, you'll remember that this works using two different steps. So at first generates a chart config or what we're calling a chart config. And then it will use that chart config to generate some Python code to generate a visualization. So starting with the chart config here. You're going to again have a prompt that you want to use to generate that config. You'll see it says generate a chart config based on some data. Your goal is to show a visualization goal. And then here, you're going to use a tool called pydantic along with a feature of OpenAI called Structured Output. So that you can ensure that when you make a call to generate a chart config, it matches a very specific format that you're defining here. So first thing to do that is define the actual format that you want to use. So in this case this visualization config is a set of a dictionary with four different variables here. Chart type x axis, y axis, and title. And then you can see there's a description of what each of those different variables are. And you'll use that in just one sec here with your OpenAI call in order to make sure that the output matches that format exactly. So now you can define a method for extracting the chart config and creating that chart config. It'll take in some data as well as a visualization goal. And then again, it's going to format the chart config prompt that you set up above. And then there's going to be slight difference in this call that's made here. Because now you're using that structured output feature to tell OpenAI that it should respond in a format that matches that visualization config that you just created. So one important thing to note is there's a slightly different message string here to allow you to include that response format variable for visualization config. And again what this will do is it will make it so that when OpenAI responds, when GPT-4o-mini responds, it will be in the format that matches your visualization config. So you'll be able to access things like the content dot chart type or content dot x axis, or y axis, or title. And the broader goal of this approach is you're really scoping down the first thing you ask the model to do to say instead of just generating Python code, it really just has to pull out, okay, what's the chart type I should create? What's the x axis, y axis and title that I should give to my chart? And then you can pass that into an LLM in the next step of this tool. Do you have a more defined goal for that LLM to generate code for. So moving on to the next step within this third tool is to actually create the chart code itself. So similar to before, you're going to create a prompt to start off with. It says write Python code to generate a chart based on this config. And it's passing in this config here. And then you can define a method to use that particular prompt to generate Python code. So you'll see this create chart method here takes in a dictionary which is that config. So that's what it was generated by the last step there. And then it formats your create chart prompt here. With that config. Makes your standard OpenAI call gets its response back. And then similar to what you did with the SQL code, it's always a good idea to make sure that, any sort of prefix here that gets added by the model like this Python one you see here, is getting removed and replaced so that you're only left with a runnable code. And now the last thing to do with this tool is just compose those two different steps together into one method. So now this generate visualization method is going to take in some data and a visualization goal. It's first going to create a config with the extract chart config method. And then it will create some Python code using the create chart code. And finally, you can test and make sure that this is actually all working correctly. So you can say generate visualization with some example data and then a visualization goal. In this case, that's similar to the prompt the user might have. And we'll print out the response as well. And you should now see some Python code that's been printed out here that will generate a particular visualization for you. And you can actually take it one step further and run the code here using the command execute code. And you'll see a visualization. In this case it looks like the code was created correctly. But the visualization looks slightly off here. So, this is where evaluation is going to come into play later on because you'll be able to catch cases where maybe the visualization is slightly off. One important note is that you always have the option to include the ability for your agent to run code, but it's something you have to be a little bit careful with because if you just run code that's been created by an LLM, it could have errors. It could do things that you don't expect it to. So it's always good to run code within an enclosed environment if you're giving your agent that capability. In this example agent, you left that capability out just to keep things safe. But something good to know if you're incorporating the ability to run code into future agents. Now, with all of your tools defined, the next step is to put those tools into a format that can be understood by your router. In this case, you're using an OpenAI, GPT-4o-mini router with function calling. So there's a very specific format that OpenAI expects to be pass those tools in. You can add that in here. And what you'll see is that each object within this array of tools has a specific Json format that's been defined. So in this first one, this first tool here is your lookup sales data tool. So this is a method that you defined earlier. And you'll see there's a description in here of what that tool does. Or it can do lookup data from this particular file. And then you'll see that certain properties have been defined for things like the prompt parameter that needs to be passed into this tool. And so this is what tells the router how to choose this function. And if it chooses this function to call what parameters need to be passed into it. So these descriptions are really critical both for the description of the function as well as descriptions of the parameters. Because if you get these wrong, the router may choose not to call your function. It may not understand what that function does, or it may think the function can do things that it actually can't do. So this is often something that you end up changing a lot of times when building an agent is getting the description and the parameter descriptions exactly right. So there'll be an entry here for each of your different tools. As well as a mapping at the bottom here to say this name here matches this particular function, which I got used in a second. Awesome. You're now ready to define your router. To define your router you can use a few different approaches. Let's walk through the code here. So in this case, this run agent function that you have it's going to be what runs your router. Oftentimes, people will set up routers to either work recursively where you continually call a function that represents your router. Or in this case, you could go for something even simpler and just use a while loop here that you break out of when the agent completes its task. Looking through the code that you see here, you have the messages object that's passed into this function here. And then first we'll make sure that that messages object matches the right format that we expect. So in this case you want to have it as a user message. And this is a format that's expected by OpenAI in this case. So you want to check for cases where maybe you've passed in just a string as opposed to a dictionary of these messages and correct for that case. And then if the system prompt has not actually been added into this already, then you'll want to make sure that that's been added. We'll define that system prompt in just one moment. And then to actually run your agent, it's going to follow a similar loop here where you first make a call to OpenAI with your tools object that you just set up up there, as well as the messages, and then you'll receive a response back to look for if there's any tool calls inside that response. And then if there are, you're gonna want to handle those tool calls. So you'll need to define what that means in just a second. And then if there's no more tool calls then you can return the response back to the user. So there's a couple more pieces you need here for the router. First, you need this system prompt variable. So your router needs some sort of system prompt. So you can define that a very basic version of this is just you are a helpful assistant that can answer questions about this data set. And the last piece you'll need is a way to handle the tool calls that get made by your router. And so you can add in a method to handle those tool calls. So here this is just looping through each tool call that gets made. And then for that tool call looking up the corresponding function name parsing out any arguments that have been included in the tool call, and then calling that function with those arguments and appending the result back to the messages object. One other important thing to note here is that OpenAI specifically, as well as some other models, rely on a behavior where if they tell you to call a particular tool, if that's the response you get back from your router, then in the next call that you make to them, you need to include a response of that particular tool. So they'll give you a tool call ID, and if you don't include back a tool message here with that particular tool call ID, then they'll actually error and say, hey, you need to call this particular tool and you need to give me a response for that tool call that I asked you to make. And now with each of these you are ready to go and run your agent. Pull in an example here. You can ask it a question like "show me the code for a graph of sales by store. In November 2021 and tell me what trends you see." And you'll see it start to run, and you'll start to get some print statements from your router there where it's making calls to OpenAI, it receives some tool calls back, it's going to process those tool calls. Then go back to the router and you'll see that loop complete a few times as the code runs here. And once you see a message saying that there's no more tool calls to make and it's returned, the final response, you can print out your result. And you should see in this case some code to generate that graph. As well as some of the trends that were gathered by the data analysis tool. Congratulations. You now have a working agent that can answer questions based on this local database of sales data that you captured.