Data Analysis Using LLM | ChatGPT Data analysis | Retail data analytics using langchain

hey guys in this video we are going to do some interesting stuff we are going to take a retail transaction data upload it into a database and ask some questions to generate some report from that database okay so we are going to use database to store the data chat GPT to ask the questions and Lang chain to Club all these things together so let's start the process let's understand how to do this using lanch so this is how we will go guys first we will see introduction to SQL light as I told you we will use a database so SQL light will be our database then I will work you through what data we are going to use and I'm going to upload that in SQL light okay then I'm going to integrate various pieces of our code in Lang chain okay so the purpose is to do data analysis using Lang chin or using chat GPT okay and then analyzing response so we will see how the response is coming and then I'm going to walk you through a very interesting concept known as prom templates okay so let's start first of all with the introduction to SQL light now what is SQL light basically so SQL light stands for uh simply SQL which is lightweight you can say the meaning of lightweight is it can run in your computer without any problem okay and you can do stuff on this and uh if you want to let's say do some prototype or if you want to create a small solution you can do things here and then replicate that in a larger database so I'm just opening the page here SQL light is a C library that provides a lightweight disk based database okay so it will be in your computer no need of a server okay so it's easy to work with hence uh I thought of covering this as part of this video okay and allows accessing database using a non-standard variant of SQL language I will show you now some application of SQL light are internal data storage as I told you it is possible to prototype an application using SQL light and then Port the code to a larger database okay so to build some prototype basically now uh how to work with basic SQL uh light so let me take you to python so in Python you can import SQL light package like this and you can simply say connect me to a database called tutorial. DB okay so tutorial. DB database it will connect do2 okay that is a default DB there so if I run this right it will connect here and then uh I'm going to create a table here if you see create table movie title year and score I'm just running this as you can see it says table movie already exists so what I have to do I have to go here and delete the table okay so I'm deleting the table now I will go here one more time and I will try to create the table and then I'm going to insert the record in the table it's very simple see is basically your cursor which will which is helping you to execute those commands Okay so I'm inserting data in the movies so this is my title this is my year and this is my score okay so what is happening now uh this has run already okay and if I say commit then it will get committed and here I'm selecting title from movie if you see which means I'm running a query and if I run a query I run this two times so two times it is giving me okay so two times it is giving me I run this Square two times if I run one more time right it should give me six times see 2 + 2 + 2 so with this simple uh piece of code is I'm trying codes I'm trying to show you how SQL light is working okay so it is a dis based TV light wa used to do some prototyping okay you can play around with this whatever I showed you now is already in the homepage of SQL light here okay so nothing uh you know I have written it is already on SQL light homepage you can just see that now let me walk you through our data the data which we are going to use okay so in kagle there is a retail sales data guys uh if you want I can give you link in the description but this is basically a very simple data uh let me show you the Excel of this so it has various columns for example transaction ID date customer ID gender age product category quantity price per unit and total am so basically you can think of a retail transaction data from this retail transaction data I want to do certain analysis for example I want to ask what is the average sales per month okay so here data is there for all the month of 2023 right so November January July June I want to ask give me the average sales or average price per unit by every month so that is one report I want to generate okay if I have to do it in SQL I can write a group by query and get it done but I want to do this using language model so what I'm going to do I'm going to take this data upload it in the database first and then ask questions using language models let me do that here let me do that here in my code so SQL light you already got some idea what that is so what I'm going to do here is I'm going to Simply import the data that I showed you right now in Excel okay and before that what I'm doing is I'm just importing my open AI key which I have kept in a separate file some of the common packages are uh imported here and then I'm initializing llm Lang Chen uh chat open AI module okay so very basic things happening here I'm just reading that Excel file and I'm just uploading in SQL light Connection in a database called Amon customer okay okay and table name will be sales so let me run this once I run this what would have happened in background is there is a DB am customer. DB created in SQL light in that DB there is a table called sales table uh that I created just now and what is the content of sales table the content of sales table is nothing but retailor DF that is your Excel file okay and this is basically connection to the chat open AI now we have a database and we have data let's go ahead and try to do some query so what I'm going to do is I'm going to import some of the packages from langen for example from langen Agents I'm going to take SQL agent from asent toolkits I'm going to take database toolkit agent type and SQL database okay and here what I'm going to do is I'm going to Simply connect to a database called Aman customer. DB okay this is the same database that I created above now in this database I already have a table called sales if you remember in that sales table I have my transaction data okay second thing I had already created a llm variable here if you see chat open a llm variable so these two things are critical for me to create SQL agent okay so what I'm going to do here I'm going to create an SQL agent executor with specific parameters okay and in SQL agent I'm saying llm is equal to llm and SQL database toolkit DV is equal to my DB which is nothing but am customer DB and llm is equal to llm once I do this right I have a LC agent executor which means Lang chain agent executor so my database is uploaded my connection is created now I will ask the questions so let's go here and try to type some question so for example the first thing I'm saying is what is average sales across different product category now I'm not going to run this because I have run just before this video and sometimes it takes like 3 4 5 minutes to get response depending on the speed okay so I'll just walk you through what is the response coming okay so for example if I say what is the average sales across different product categories then what should happen here in this there are different product categories right so how you will write a SQL query is you will simply go and say group Buy on this product category and give me the average sales now how the system is reaching there let's try to understand it says I need to calculate average sales of each product category in the database okay select product category average sales from database sales table Group by this okay Group by this and then it says select product category average of sales so it is running same query that we discussed just now okay now the sales underscore table is not there so it is it is taking sales sales table because that is the nearby table so it is saying I should use SQL DB schema to create the correct table for the sales table so sales initially by by uh by uh you know default it took sales uncore table but our T table name is sales table okay so it will immediately change its query to sales table once it changes right you see here it is creating a table like this and what it is doing it is running a query like this see select product category average of total amount from sales so here what is happening is Group by product category so this is your output so if I go and filter in the beauty category the average should be 47.4 7 so let's see that in Beauty category in Beauty category average right right 4 67.4 7 this is the average right and if I change the category then the average will be 4 44 3.24 okay so this this number right is coming by this SQL query and it is giving by writing a simple line of text here so I can write a different uh thing and generate a different report here and how the flow is happening that we need to understand so first it write in the sales table it is not there so it looked for the alternate table and then it went into that table okay going back to the next next query right what age group is buying most now this one is a little difficult one because age group is not a direct column okay so what the query is going to do is it is going to create let's see how the query is created okay I should check the sales data to see which group is making the most purchase so initially first it will go and it will create it this for from sales data again it will not find the sales data and it will go back to sales table but still in the sales table also the query it will run is Select age count star from Total purchase Group by and then it will take the top one so I'm asking for age groups but it is giving me age so final final answer it is giving me 64 maybe 64 AG right 64 a is the maximum here in the in our data okay so that is why it is giving me 64 but what I'm asking I'm asking the age group so system is not able to create the age group that is where something known as prompt templates comes very very handy okay this is the most important part of this video prompt template comes very very handy in prompt template what you say is you give some background info with your prompt okay and in in the background info I'm saying my role is to analyze the transaction pattern of customer the feature Engineering in table sales is crucial for statistical exploration for example column age can be grouped into bins of this and so on understanding the data in this colum help to gain Insight enabling us to offer personalized Services Etc once I run this with some background info right then you will see that I need to examine the sales table because I'm clearly saying here go to the sales table okay in the table sales so it is not going to the alternate table now as in previous examples it is going directly to the sales table and examine the sales table see the data in each column action create table sales this thing and you know table is created and then it will simply go and create the buckets is like this they like this and this is your final response age group 106 2125 age group 104 is 2630 over 40 is 597 so which age group is buying most it should be over 40 so final answer is over 40 okay just try to understand how prompt templates is setting the context and helping us to get the right response okay and you can play around with prompt templates with more variants of what you want to extract from the data fine let me show you some more interesting stuff from prompting strategies Lang chain page so whatever I explained you now some of these come from this page some of these come from my experimentation but you can go to this page so this page you can simply search for prompting in Lang chain okay and you can simply write some SQL queries for example here find the total duration of all tracks query is this okay so what is happening it is different ways of prompting so what I used is prompt template okay you can do few short prompt templates some other prompt templates zero sort Etc right and you can play around with this so all in all what we have done as part of this video is we have taken transaction data loaded in database through the use of Lang chain we have done the query using plain simple English and we are getting the reports back you can create more fancy report you can create more uh better prompt by using different prompting strategies okay so let me know guys how did you like this video I'll see you all in the next video wherever you are stay safe and take care

Channel: Unfold Data Science

Views: 2,877

Keywords: Data Analysis Using LLM, ChatGPT Data analysis, Retail data analytics using langchain, lanchain openai tutorial, lanchain openai, lanchain openai API, lanchain openai chroma, lanchain openai assistant, lanchain openai functions, lanchain openai function calling, lanchain openai integration, lanchain openai SQL, langchain SQL, Langchain streamlit, Sqllite, sqllite in python, llm data anlysis, chatgpt data analytics, unfold data science genai, genai data analysis

Id: ziuXicfYnHo

Length: 14min 28sec (868 seconds)

Published: Thu Feb 22 2024