About the Dataset<\/h3>\n
The dataset in hand is a time series dataset obtained from the U.S Bureau Labor of Statistics<\/a>. The Dataset can be downloaded here<\/a>. Download the text files from the ‘Average Price Data’ section in the website (highlighted below).<\/p>\n The text files highlighted in the image below have been used for the analysis –<\/p>\n It’s important to understand the size of the dataset so that we can plan out the process to load the data into GridDB. This is important so as to not put a huge burden on the system where the program is running (client system). The datasets have more than 40 years of data which gives us good scope to do some analysis on price trends.<\/p>\n Note that these text files are fixed width files. Hence, the fixed width files need to be downloaded using the read_fwf function of pandas.<\/p>\n Let’s now look at the memory utilized by the dataframes.<\/p>\n As seen above, the size of the ‘household_prices’, ‘food_prices’ and ‘gasoline_prices’ datasets when loaded into memory are 22.7MB, 28.7 MB and 18.5 MB respectively. This brings us to a total of 69.9 MB. Hence, an efficient load strategy would be needed while loading the data into GridDB.<\/p>\n The above gives us a reference to the number of records that will be processed\/loaded into GridDB.<\/p>\n An efficient load strategy would be to chunk the data into 1000 rows each and load them into GridDB one chunk at a time. This will result in multiple invocations of the GridDB API to load the data. But this will allow us greater control on the data being loaded. ALternatively, the entire data can be loaded in one shot too but that may utilize a lot of memory and bandwidth (ensure that any timeouts on the HTTP connection are configured leniently in this case).<\/p>\n Let’s perform some data cleaning before loading the data into GridDB. Let’s now define a function that will remove unneeded columns, extract the month number from the period field, perform data cleaning in the value field to ensure that all data is numeric and finally, order the columns in the dataframe. Refer to GridDB WebAPI<\/a> to know more about creating containers in GridDB.<\/p>\n We will now proceed with creating a container in GridDB to hold the data.<\/p>\n The container that will store all the data has now been created in GridDB. Note that I set rowkey to False as the series_id field will not be unique as we have the same item occurring multiple times in the dataset (for each month and year).<\/p>\n The below function converts a dataframe into a JSON object and then chunks it into lots of 1000 rows.<\/p>\n We will now load the 3 datasets that we have using the function that chunks the data and loads it into GridDB<\/p>\n Now that we’ve loaded data, we are good to proceed with the analysis. For the purpose of this analysis, we use GRIDDB’s query language called TQL. To know more on TQL, check out this resource <\/a>. There are also some Youtube video tutorials to grasp TQL quickly. Click here <\/a> for one such video tutorial. Follow the GridDB WebAPI guide <\/a> (Refer to the section ‘Fetching Data’) to learn the basic structure of a request for a TQL statement.<\/p>\n The below TQL query does a quick check to see if the number of records inserted into GridDB is the same as the sum of the records in the 3 dataframes used for the data load.<\/p>\n ** Below are the mapping files to be downloaded.<\/p>\n ** Note that all files are tab separated files. These mapping files can be used to lookup code descriptions if needed. These are going to remain in Python and not going to be loaded into GridDB.**<\/p>\n ** Writing a query to gather data for series_id APU0000701311 (Rice, white, long grain, precooked); APU0000701312 (Rice, white, long grain, uncooked). Note that each is of 1 pound weight. They are not seasonally adjusted. **<\/p>\n We will process the response received from the GridDB WebAPI invocation and construct a dataframe with the data from the response. Based on the trend in Fig.1, the following insights are observed –<\/p>\n ** Poultry includes all items that include chicken and eggs. Writing a query to gather data for series_id APU0200FF1101 (chicken breast with bone); APU0300FF1101 (chicken breast boneless); APU0300706111 (Chicken whole); APU0300706211 (Chicken breast); APU0300706212 (Chicken legs); APU0300708111 (Eggs). Note that as per the data, these items are non-seasonally adjusted. The prices are per pound (453.6 gm).**<\/p>\n We will process the response received from the GridDB WebAPI invocation and construct a dataframe with the data from the response. We have a very interesting trend here. Based on the trend in Fig.2, the following insights are observed –<\/p>\n ** The following series_ids are considered – APU0000709111 – Milk, fresh, whole, fortified; APU0000709211 – Milk, fresh, skim; APU0000709212 – Milk, fresh, low fat; APU0200709111 – Milk, fresh, whole, fortified; APU0200709211 – Milk, fresh, skim . Note that only items that are 1.5 gallons and that are not seasonally adjusted have been considered. **<\/p>\n We will process the response received from the GridDB WebAPI invocation and construct a dataframe with the data from the response.![\"\"](\"https:\/\/griddb.net\/wp-content\/uploads\/2022\/08\/download-2.jpeg\")
<\/a><\/p>\n![\"\"](\"https:\/\/griddb.net\/wp-content\/uploads\/2022\/08\/download-3.jpeg\")
<\/a><\/p>\nPlanning the load process to GridDB\/Understanding the Size of the Dataset<\/h3>\n
import os\nimport numpy as np\nimport pandas as pd\nimport nums_from_string as ns\nfrom IPython.core.display import Image, display\nimport seaborn as sns\nfrom matplotlib import pyplot as plt\n<\/code><\/pre>\n<\/div>\nhousehold_prices = pd.read_fwf('ap.data.1.HouseholdFuels.txt')\ngasoline_prices = pd.read_fwf('ap.data.2.Gasoline.txt')\nfood_prices = pd.read_fwf('ap.data.3.Food.txt')<\/code><\/pre>\n<\/div>\nprint('Size of household_prices', household_prices.info(memory_usage='deep'))\nprint('Size of food_prices', food_prices.info(memory_usage='deep'))\nprint('Size of gasoline_prices', gasoline_prices.info(memory_usage='deep'))<\/code><\/pre>\n<\/div>\n<class 'pandas.core.frame.DataFrame'>\nRangeIndex: 103139 entries, 0 to 103138\nData columns (total 5 columns):\n # Column Non-Null Count Dtype \n--- ------ -------------- ----- \n 0 series_id 103139 non-null object \n 1 year 103139 non-null int64 \n 2 period 103139 non-null object \n 3 value 103139 non-null object \n 4 footnote_codes 0 non-null float64\ndtypes: float64(1), int64(1), object(3)\nmemory usage: 20.4 MB\nSize of household_prices None\n<class 'pandas.core.frame.DataFrame'>\nRangeIndex: 139986 entries, 0 to 139985\nData columns (total 5 columns):\n # Column Non-Null Count Dtype \n--- ------ -------------- ----- \n 0 series_id 139986 non-null object \n 1 year 139986 non-null int64 \n 2 period 139986 non-null object \n 3 value 139986 non-null object \n 4 footnote_codes 0 non-null float64\ndtypes: float64(1), int64(1), object(3)\nmemory usage: 27.8 MB\nSize of food_prices None\n<class 'pandas.core.frame.DataFrame'>\nRangeIndex: 88754 entries, 0 to 88753\nData columns (total 5 columns):\n # Column Non-Null Count Dtype \n--- ------ -------------- ----- \n 0 series_id 88754 non-null object \n 1 year 88754 non-null int64 \n 2 period 88754 non-null object \n 3 value 88754 non-null float64\n 4 footnote_codes 0 non-null float64\ndtypes: float64(2), int64(1), object(2)\nmemory usage: 13.0 MB\nSize of gasoline_prices None\n<\/code><\/pre>\nprint(len(household_prices))\nprint(len(gasoline_prices))\nprint(len(food_prices))<\/code><\/pre>\n<\/div>\n103139\n88754\n139986\n<\/code><\/pre>\nData Cleaning<\/h3>\n
\nFirst, let’s add a “Category” field that will store the type of data. This way we are able to identify the source of a record in case that is needed in the future.<\/p>\nhousehold_prices['Category'] = 'Household Commodities'\ngasoline_prices['Category'] = 'Gasoline'\nfood_prices['Category'] = 'Food'<\/code><\/pre>\n<\/div>\n
\nOnce done, we will invoke this function against the 3 datasets (dataframes) that were loaded.<\/p>\ndef data_cleaning (df):\n df.drop('footnote_codes', axis=1, inplace=True) # Delete the column 'footnote_codes'\n df['month'] = df['period'].str[-2:] #extract the month\n # DataType conversions\n df[\"month\"] = pd.to_numeric(df[\"month\"]) #convert to number\n df[\"value\"] = df[\"value\"].replace(['-'],0) #Replace values that have a '-' to a 0\n df[\"value\"] = pd.to_numeric(df[\"value\"]) #convert to number\n cols = list(df.columns.values)\n df = df[['series_id', 'year', 'period', 'value', 'month', 'Category']] #order the columns in the dataframe\n return df<\/code><\/pre>\n<\/div>\n#Invoke the cleaning function on the 3 dataframes\nhousehold_prices = data_cleaning(household_prices)\ngasoline_prices = data_cleaning(gasoline_prices)\nfood_prices = data_cleaning(food_prices)<\/code><\/pre>\n<\/div>\nCreating a Container structure in GridDB<\/h3>\n
import requests # to make http calls\nimport http\nhttp.client.HTTPConnection.debuglevel = 1 #Tip - to enable detailed logging of http calls; this is not needed in case you don't detailed logging<\/code><\/pre>\n<\/div>\n#Construct an object to hold the request headers (ensure that you replace the XXX placeholder with the correct value that matches the credentials for your GridDB instance)\nheader_obj = {\"Authorization\":\"XXX\",\"Content-Type\":\"application\/json; charset=UTF-8\",\"User-Agent\":\"PostmanRuntime\/7.29.0\"}\n\n#Construct the base URL based on your GRIDDB cluster you'd like to connect to (ensure that you replace the placeholders in the URL below with the correct values that correspond to your GridDB instance)\nbase_url = 'https:\/\/[host]:[port]\/griddb\/v2\/[clustername]\/dbs\/[database_name]'<\/code><\/pre>\n<\/div>\n#Construct an object to hold the request body (i.e., the container that needs to be created)\ndata_obj = {\n \"container_name\": \"Inflation_CPI_Analysis\",\n \"container_type\": \"COLLECTION\",\n \"rowkey\": False,\n \"columns\": [\n {\n \"name\": \"series_id\",\n \"type\": \"STRING\"\n },\n {\n \"name\": \"year\",\n \"type\": \"INTEGER\"\n },\n {\n \"name\": \"period\",\n \"type\": \"STRING\"\n },\n {\n \"name\": \"value\",\n \"type\": \"FLOAT\"\n },\n {\n \"name\": \"month\",\n \"type\": \"INTEGER\"\n },\n {\n \"name\": \"category\",\n \"type\": \"STRING\"\n } \n ]\n}\n\n#Set up the GridDB WebAPI URL\nurl = base_url + '\/containers'\n\n#Invoke the GridDB WebAPI with the headers and the request body\nx = requests.post(url, json = data_obj, headers = header_obj)<\/code><\/pre>\n<\/div>\nAdding data to the container in GridDB\/Registering rows<\/h2>\n
Loading Strategy: Creating Data Chunks<\/h3>\n
#Setup the URL to be used to invoke the GridDB WebAPI to register rows in the container created previously\nurl = base_url + '\/containers\/Inflation_CPI_Analysis\/rows'\n\ndef create_chunks_to_load_to_GRIDDB(df):\n list_of_dataframes = np.array_split(df, len(df)\/1000) #Returns a list of dataframes; here,# the chunk size is 1000\n for i in list_of_dataframes:\n df_json = i.to_json(orient='values')\n request_body = df_json\n x = requests.put(url, data=request_body, headers=header_obj) #Invoke the GridDB WebAPI using the request constructed\n print(x.text)<\/code><\/pre>\n<\/div>\ncreate_chunks_to_load_to_GRIDDB(household_prices)<\/code><\/pre>\n<\/div>\ncreate_chunks_to_load_to_GRIDDB(food_prices)<\/code><\/pre>\n<\/div>\ncreate_chunks_to_load_to_GRIDDB(household_prices)<\/code><\/pre>\n<\/div>\nQuick Validation & Introduction to TQL<\/h2>\n
#Setup the URL to be used to invoke the GridDB WebAPI to retrieve data from the container\nurl = base_url + '\/tql'\n\n#Construct the request body which has the TQL that is to be used to retrieve the data\n#Use the count function to get the number of records in the container \nrequest_body = '[{\"name\":\"Inflation_CPI_Analysis\", \"stmt\":\"SELECT count(*) \", \"columns\":[]}]' \n\n\n#Invoke the GridDB WebAPI\nx = requests.post(url, data=request_body, headers=header_obj)<\/code><\/pre>\n<\/div>\nprint(x.text)<\/code><\/pre>\n<\/div>\n[{\"columns\":[{\"name\":\"aggregationResult\",\"type\":\"DOUBLE\"}],\"results\":[[331879.0]]}]\n<\/code><\/pre>\nprint(len(household_prices) + len(food_prices) + len(gasoline_prices))<\/code><\/pre>\n<\/div>\n331879\n<\/code><\/pre>\nLoading the mapping tables as dataframes<\/h1>\n
<\/a><\/p>\nitem_mapping = pd.read_csv('ap.item_mapping.txt',sep='t')\nperiod_mapping = pd.read_csv('ap.period_mapping.txt',sep='t')\nseasonal_mapping = pd.read_csv('ap.seasonal_mapping.txt',sep='t')\narea_mapping = pd.read_csv('ap.area_mapping.txt',sep='t')\nseries_mapping = pd.read_csv('ap.series_mapping.txt',sep='t')<\/code><\/pre>\n<\/div>\nTime Series Trends Analysis<\/h1>\n
Scenario 1: How has inflation impacted the price of Rice?<\/h2>\n
#Setup the URL to be used to invoke the GridDB WebAPI to retrieve data from the container\nurl = base_url + '\/tql'\n\n#Construct the request body which has the TQL that is to be used to retrieve the data\n# Getting data for series_id APU0000701311 (Rice, white, long grain, precooked); APU0000701312 (Rice, white, long grain, uncooked)\nrequest_body = '[{\"name\":\"Inflation_CPI_Analysis\", \"stmt\":\"SELECT * WHERE series_id = 'APU0000701311' OR series_id = 'APU0000701312'\", \"columns\":[]}]' \n\n#Invoke the GridDB WebAPI\ndata_req1 = requests.post(url, data=request_body, headers=header_obj)\ndata_req1<\/code><\/pre>\n<\/div>\n
\nOnce done, we will group the data by the year field so that we have the mean values for each year. This can then be plotted to see the trend in a visual manner.<\/p>\n#Process the response received and construct a Pandas dataframe with the data from the response\nmyJson = data_req1.json()\nRice_trends = pd.DataFrame(myJson[0][\"results\"], columns=[myJson[0][\"columns\"][0][\"name\"], myJson[0][\"columns\"][1][\"name\"], myJson[0][\"columns\"][2][\"name\"], myJson[0][\"columns\"][3][\"name\"],myJson[0][\"columns\"][4][\"name\"],myJson[0][\"columns\"][5][\"name\"]])<\/code><\/pre>\n<\/div>\n#Group the data by year and get the mean for each year\nAvg_value_rice = pd.DataFrame(Rice_trends.groupby(['year'])['value'].mean())<\/code><\/pre>\n<\/div>\n#Construct a line plot with the data\nplt.rcParams[\"figure.figsize\"] = [12,12]\nplt.rcParams[\"figure.autolayout\"] = True\nax = sns.lineplot(x=\"year\", y=\"value\", data=Avg_value_rice)\nax.tick_params(rotation=45)\nax.set_title('Fig.1 Scenario 1 - Time Series Trends of Rice Prices', fontsize = 18)\nplt.xlabel('Year', fontsize=14)\nplt.ylabel('Average value ($ per pound)', fontsize=14)\nplt.show()<\/code><\/pre>\n<\/div>\n![\"\"](\"https:\/\/griddb.net\/wp-content\/uploads\/2022\/08\/download.png\")
<\/a><\/p>\nInsights for Scenario 1: Effect of Inflation on Rice<\/h2>\n
\n
Scenario 2: How has inflation impacted the price of poultry?<\/h2>\n
#Setup the URL to be used to invoke the GridDB WebAPI to retrieve data from the container\nurl = base_url + '\/tql'\n\n#Construct the request body which has the TQL that is to be used to retrieve the data\n# Getting data for series_id APU0200FF1101 (chicken breast with bone); APU0300FF1101 (chicken breast boneless); APU0300706111 (Chicken whole); APU0300706211 (Chicken breast); APU0300706212 (Chicken legs); APU0300708111 (Eggs)\nrequest_body = '[{\"name\":\"Inflation_CPI_Analysis\", \"stmt\":\"SELECT * WHERE series_id = 'APU0200FF1101' OR series_id = 'APU0300FF1101' OR series_id = 'APU0300706111' OR series_id = 'APU0300706211' OR series_id = 'APU0300706212' OR series_id = 'APU0300708111' \", \"columns\":[]}]' \n\n#Invoke the GridDB WebAPI\ndata_req2 = requests.post(url, data=request_body, headers=header_obj)\ndata_req2<\/code><\/pre>\n<\/div>\n
\nOnce done, we will group the data by the year field so that we have the mean values for each year. This can then be plotted to see the trend in a visual manner.<\/p>\n#Process the response received and construct a Pandas dataframe with the data from the response\nmyJson = data_req2.json()\nPoultry_trends = pd.DataFrame(myJson[0][\"results\"], columns=[myJson[0][\"columns\"][0][\"name\"], myJson[0][\"columns\"][1][\"name\"], myJson[0][\"columns\"][2][\"name\"], myJson[0][\"columns\"][3][\"name\"],myJson[0][\"columns\"][4][\"name\"],myJson[0][\"columns\"][5][\"name\"]])<\/code><\/pre>\n<\/div>\n#Group the data on a yearly basis and get the mean value for each year\nAvg_value_poultry = pd.DataFrame(Poultry_trends.groupby(['year'])['value'].mean())<\/code><\/pre>\n<\/div>\n#Construct a line plot with the data\nplt.rcParams[\"figure.figsize\"] = [12,12]\nplt.rcParams[\"figure.autolayout\"] = True\nax = sns.lineplot(x=\"year\", y=\"value\", data=Avg_value_poultry)\nax.tick_params(rotation=45)\nax.set_title('Fig.2 Scenario 2 - Time Series Trends of Poultry Prices',fontsize=18)\nplt.xlabel('Year', fontsize=14)\nplt.ylabel('Average value ($ per pound)', fontsize=14)\nplt.show()<\/code><\/pre>\n<\/div>\n![\"\"](\"https:\/\/griddb.net\/wp-content\/uploads\/2022\/08\/download-1.png\")
<\/a><\/p>\nInsights for Scenario 2: Effect of Inflation on Poultry<\/h2>\n
\n
Scenario 3: How has inflation impacted milk prices?<\/h2>\n
#Setup the URL to be used to invoke the GridDB WebAPI to retrieve data from the container\nurl = base_url + '\/tql'\n\n#Construct the request body which has the TQL that is to be used to retrieve the data\n# Getting data for series_id APU0000709111 - Milk, fresh, whole, fortified; APU0000709211 - Milk, fresh, skim; APU0000709212 - Milk, fresh, low fat; APU0200709111 - Milk, fresh, whole, fortified; APU0200709211 - Milk, fresh, skim\nrequest_body = '[{\"name\":\"Inflation_CPI_Analysis\", \"stmt\":\"SELECT * WHERE series_id = 'APU0000709111' OR series_id = 'APU0000709211' OR series_id = 'APU0000709212' OR series_id = 'APU0200709111' OR series_id = 'APU0200709211' \", \"columns\":[]}]' \n\n#Invoke the GridDB WebAPI\ndata_req3 = requests.post(url, data=request_body, headers=header_obj)\ndata_req3<\/code><\/pre>\n<\/div>\n
\nOnce done, we will group the data by the year field so that we have the mean values for each year. This can then be plotted to see the trend in a visual manner.<\/p>\n#Process the response received and construct a Pandas dataframe with the data from the response\nmyJson = data_req3.json()\nMilk_trends = pd.DataFrame(myJson[0][\"results\"], columns=[myJson[0][\"columns\"][0][\"name\"], myJson[0][\"columns\"][1][\"name\"], myJson[0][\"columns\"][2][\"name\"], myJson[0][\"columns\"][3][\"name\"],myJson[0][\"columns\"][4][\"name\"],myJson[0][\"columns\"][5][\"name\"]])<\/code><\/pre>\n<\/div>\n#Group the data by rear and get the mean value for each year\nAvg_value_milk = Milk_trends.groupby('year', as_index=False)['value'].mean()<\/code><\/pre>\n<\/div>\n#Construct a line plot with the data; ensure that the ticks on the x axis are integer values\nfrom matplotlib.ticker import MaxNLocator\n\nplt.rcParams[\"figure.figsize\"] = [12,12]\nplt.rcParams[\"figure.autolayout\"] = True\n#year = (Milk_trends['year'])\nax = sns.lineplot(x=\"year\", y=\"value\", data=Avg_value_milk)\nax.tick_params(rotation=45)\nax.set_title('Fig.3 Scenario 3 - Time Series Trends of Milk Prices',fontsize=18)\nax.xaxis.set_major_locator(MaxNLocator(integer=True))\nplt.xlabel('Year', fontsize=14)\nplt.ylabel('Average value ($ per pound)', fontsize=14)\nplt.show()<\/code><\/pre>\n<\/div>\n
![\"\"](\"https:\/\/griddb.net\/wp-content\/uploads\/2022\/08\/download-2.png\")
<\/a><\/p>\n![\"\"](\"https:\/\/griddb.net\/wp-content\/uploads\/2022\/08\/download-3.png\")
<\/a><\/p>\n![\"\"](\"https:\/\/griddb.net\/wp-content\/uploads\/2022\/08\/download-4.png\")
<\/a><\/p>\n