{"id":46781,"date":"2023-10-20T00:00:00","date_gmt":"2023-10-20T07:00:00","guid":{"rendered":"https:\/\/griddb-linux-hte8hndjf8cka8ht.westus-01.azurewebsites.net\/blog\/analysis-of-global-trade-statistics-using-griddb\/"},"modified":"2025-11-13T12:56:47","modified_gmt":"2025-11-13T20:56:47","slug":"analysis-of-global-trade-statistics-using-griddb","status":"publish","type":"post","link":"https:\/\/griddb-linux-hte8hndjf8cka8ht.westus-01.azurewebsites.net\/en\/blog\/analysis-of-global-trade-statistics-using-griddb\/","title":{"rendered":"Analysis of Global Trade Statistics Using GridDB"},"content":{"rendered":"
\n
\n
\n
\n<\/div>\n
\n
In\u00a0[29]:<\/div>\n
\n
\n
<\/span>import<\/span> <\/span>os<\/span>\nimport<\/span> <\/span>pandas<\/span> <\/span>as<\/span> <\/span>pd<\/span>\nimport<\/span> <\/span>numpy<\/span> <\/span>as<\/span> <\/span>np<\/span>\nfrom<\/span> <\/span>IPython.display<\/span> <\/span>import<\/span> Image<\/span>\nimport<\/span> <\/span>jaydebeapi<\/span>\nimport<\/span> <\/span>urllib.parse<\/span>\nimport<\/span> <\/span>sys<\/span>\nimport<\/span> <\/span>matplotlib.pyplot<\/span> <\/span>as<\/span> <\/span>plt<\/span>\nimport<\/span> <\/span>seaborn<\/span> <\/span>as<\/span> <\/span>sns<\/span>\nimport<\/span> <\/span>time<\/span>\nfrom<\/span> <\/span>tabulate<\/span> <\/span>import<\/span> tabulate<\/span>\n<\/pre><\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
\n
\n
\n<\/div>\n
\n<\/div>
\n
About the Dataset\u00b6<\/a><\/h2>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
\n
\n
\n<\/div>\n
\n<\/div>
\n
The  Global Commodity Trade Statistics <\/b> can be downloaded from https:\/\/www.kaggle.com\/datasets\/unitednations\/global-commodity-trade-statistics<\/a>. To download the dataset, click on the 'Data Card' tab and scroll all the way below to access the '.csv' file. Use the download icon (highlighted below) to download the data file.<\/p>\n<\/div>\n<\/div>\n<\/div>\n<\/div>
\n
\n
\n<\/div>\n
\n
In\u00a0[6]:<\/div>\n
\n
\n
<\/span>##Specify the path to your image file<\/span>\nimage_path<\/span> =<\/span> 'Dataset.png'<\/span>\n\nwidth<\/span> =<\/span> 500<\/span>\n\n## Display the image<\/span>\nImage<\/span>(<\/span>filename<\/span>=<\/span>image_path<\/span>,<\/span> width<\/span>=<\/span>width<\/span>)<\/span>\n<\/pre><\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
\n
\n<\/div>\n
\n
\n
Out[6]:<\/div>\n
\n\"No\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
\n
\n
\n<\/div>\n
\n<\/div>
\n
The resulting zip file has the name commodity_trade_statistics_data.csv (as shown in the image below).<\/p>\n<\/div>\n<\/div>\n<\/div>\n<\/div>
\n
\n
\n<\/div>\n
\n
In\u00a0[8]:<\/div>\n
\n
\n
<\/span>##Specify the path to your image file<\/span>\nimage_path<\/span> =<\/span> 'file_save.png'<\/span>\n\nwidth<\/span> =<\/span> 500<\/span>\n\n## Display the image<\/span>\nImage<\/span>(<\/span>filename<\/span>=<\/span>image_path<\/span>,<\/span> width<\/span>=<\/span>width<\/span>)<\/span>\n<\/pre><\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
\n
\n<\/div>\n
\n
\n
Out[8]:<\/div>\n
\n\"No\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
\n
\n
\n<\/div>\n
\n<\/div>
\n
Right-click this file and click 'Extract All' to get the csv file which has the data.<\/p>\n<\/div>\n<\/div>\n<\/div>\n<\/div>
\n
\n
\n<\/div>\n
\n
In\u00a0[11]:<\/div>\n
\n
\n
<\/span>##Specify the path to your image file<\/span>\nimage_path<\/span> =<\/span> 'file_extract_all.png'<\/span>\n\nwidth<\/span> =<\/span> 400<\/span>\n\n## Display the image<\/span>\nImage<\/span>(<\/span>filename<\/span>=<\/span>image_path<\/span>,<\/span> width<\/span>=<\/span>width<\/span>)<\/span>\n<\/pre><\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
\n
\n<\/div>\n
\n
\n
Out[11]:<\/div>\n
\n\"No\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
\n
\n
\n<\/div>\n
\n<\/div>
\n
Loading the data into a dataframe & Data Cleaning\u00b6<\/a><\/h2>\n<\/div>\n<\/div>\n<\/div>\n<\/div>
\n
\n
\n<\/div>\n
\n
In\u00a0[210]:<\/div>\n
\n
\n
<\/span>##Loading the data into a dataframe in Python<\/span>\ncommodity_data_df<\/span> =<\/span> pd<\/span>.<\/span>read_csv<\/span>(<\/span>'C:<\/span>\\\\<\/span>Users<\/span>\\\\<\/span>mg_su<\/span>\\\\<\/span>Desktop<\/span>\\\\<\/span>Work<\/span>\\\\<\/span>Work_Upwork<\/span>\\\\<\/span>Israel Imru_GridDB<\/span>\\\\<\/span>Python_GridDB_POC11<\/span>\\\\<\/span>commodity_trade_statistics_data.csv\\commodity_trade_statistics_data.csv'<\/span>,<\/span>sep<\/span>=<\/span>','<\/span>)<\/span> #Load the dataframe <\/span>\n<\/pre><\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
\n
\n<\/div>\n
\n
\n
<\/div>\n
\n
C:\\Users\\mg_su\\AppData\\Local\\Temp\/ipykernel_23308\/908296312.py:2: DtypeWarning: Columns (2) have mixed types. Specify dtype option on import or set low_memory=False.\n  commodity_data_df = pd.read_csv('C:\\\\Users\\\\mg_su\\\\Desktop\\\\Work\\\\Work_Upwork\\\\Israel Imru_GridDB\\\\Python_GridDB_POC11\\\\commodity_trade_statistics_data.csv\\commodity_trade_statistics_data.csv',sep=',') #Load the dataframe\n<\/pre>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
\n
\n
\n<\/div>\n
\n<\/div>
\n
In order to do some insightful mining, we subset the dataframe to specific categories like dairy products and sugar. We also consider only the years 2014 to 2016 for this analysis.<\/p>\n<\/div>\n<\/div>\n<\/div>\n<\/div>
\n
\n
\n<\/div>\n
\n
In\u00a0[211]:<\/div>\n
\n
\n
<\/span># Assuming 'commodity_data_df' is your DataFrame<\/span>\ncategories_to_keep<\/span> =<\/span> [<\/span>\n    '04_dairy_products_eggs_honey_edible_animal_product_nes'<\/span>,<\/span>\n    '17_sugars_and_sugar_confectionery'<\/span>\n]<\/span>\n\n# Use boolean indexing to subset the DataFrame<\/span>\ncategory_subset_df<\/span> =<\/span> commodity_data_df<\/span>[<\/span>commodity_data_df<\/span>[<\/span>'category'<\/span>]<\/span>.<\/span>isin<\/span>(<\/span>categories_to_keep<\/span>)]<\/span>\n<\/pre><\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>
\n
\n
\n<\/div>\n
\n
In\u00a0[212]:<\/div>\n
\n
\n
<\/span>Final_subset_df<\/span> =<\/span> (<\/span>category_subset_df<\/span>[<\/span>category_subset_df<\/span>[<\/span>'year'<\/span>]<\/span>>=<\/span>2014<\/span>])<\/span>\n<\/pre><\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>
\n
\n
\n<\/div>\n
\n
In\u00a0[213]:<\/div>\n
\n
\n
<\/span># Create a list of keywords to match<\/span>\ncommodity_keywords<\/span> =<\/span> [<\/span>'Milk'<\/span>,<\/span> 'Cheese'<\/span>,<\/span> 'Butter'<\/span>,<\/span>'Yogurt'<\/span> ,<\/span>'Buttermilk'<\/span>,<\/span>'Sugar'<\/span>]<\/span>\n\n# Create a regular expression pattern to match any of the keywords<\/span>\npattern<\/span> =<\/span> '|'<\/span>.<\/span>join<\/span>(<\/span>commodity_keywords<\/span>)<\/span>\n\n# Subset the DataFrame using str.contains() with the pattern<\/span>\nFinal_subset_df<\/span> =<\/span> Final_subset_df<\/span>[<\/span>Final_subset_df<\/span>[<\/span>'commodity'<\/span>]<\/span>.<\/span>str<\/span>.<\/span>contains<\/span>(<\/span>pattern<\/span>,<\/span> case<\/span>=<\/span>False<\/span>)]<\/span>\n<\/pre><\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>
\n
\n
\n<\/div>\n
\n
In\u00a0[214]:<\/div>\n
\n
\n
<\/span>Final_subset_df_copy<\/span> =<\/span> Final_subset_df<\/span>.<\/span>copy<\/span>()<\/span>\n\n# Replace NaN and inf with a specific value, e.g., 0<\/span>\nFinal_subset_df_copy<\/span>.<\/span>loc<\/span>[:,<\/span> 'weight_kg'<\/span>]<\/span> =<\/span> Final_subset_df_copy<\/span>[<\/span>'weight_kg'<\/span>]<\/span>.<\/span>fillna<\/span>(<\/span>0<\/span>)<\/span>.<\/span>astype<\/span>(<\/span>float<\/span>)<\/span>\nFinal_subset_df_copy<\/span>.<\/span>loc<\/span>[:,<\/span> 'quantity'<\/span>]<\/span> =<\/span> Final_subset_df_copy<\/span>[<\/span>'quantity'<\/span>]<\/span>.<\/span>fillna<\/span>(<\/span>0<\/span>)<\/span>.<\/span>astype<\/span>(<\/span>float<\/span>)<\/span>\nFinal_subset_df_copy<\/span>.<\/span>loc<\/span>[:,<\/span> 'year'<\/span>]<\/span> =<\/span> Final_subset_df_copy<\/span>[<\/span>'year'<\/span>]<\/span>.<\/span>fillna<\/span>(<\/span>0<\/span>)<\/span>.<\/span>astype<\/span>(<\/span>str<\/span>)<\/span>\nFinal_subset_df_copy<\/span>.<\/span>loc<\/span>[:,<\/span> 'comm_code'<\/span>]<\/span> =<\/span> Final_subset_df_copy<\/span>[<\/span>'comm_code'<\/span>]<\/span>.<\/span>fillna<\/span>(<\/span>0<\/span>)<\/span>.<\/span>astype<\/span>(<\/span>str<\/span>)<\/span>\n<\/pre><\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>
\n
\n
\n<\/div>\n
\n
In\u00a0[215]:<\/div>\n
\n
\n
<\/span>'The final subset has '<\/span>+<\/span> str<\/span>(<\/span>len<\/span>(<\/span>Final_subset_df_copy<\/span>))<\/span>+<\/span> ' rows and '<\/span> +<\/span> str<\/span>(<\/span>len<\/span>(<\/span>Final_subset_df_copy<\/span>.<\/span>columns<\/span>))<\/span> +<\/span> ' columns.'<\/span> \n<\/pre><\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
\n
\n<\/div>\n
\n
\n
Out[215]:<\/div>\n
\n
'The final subset has 15484 rows and 10 columns.'<\/pre>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
\n
\n
\n<\/div>\n
\n<\/div>
\n
Containers in GridDB\u00b6<\/a><\/h2>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
\n
\n
\n<\/div>\n
\n<\/div>
\n
GridDB supports two types of containers namely Collections and TimeSeries containers. In the context of NoSQL databases, a collection is a container for storing related data items, and each item in the collection is represented as a row with attributes (columns). GridDB collections are akin to SQL tables, with rows representing records and columns representing attributes, much like the structure found in traditional SQL databases.<\/p>\n
Despite the similarities, there are some key differences between collections in GridDB and tables in relational databases.<\/p>\n
Additionally, NoSQL databases typically offer horizontal scalability and can handle large volumes of data with high availability and performance, making them well-suited for certain use cases, such as time-series data, IoT applications, and big data analytics.<\/p>\n
In summary, while collections in GridDB share some similarities with SQL tables in traditional relational databases, they also have distinct characteristics that make them suitable for specific data storage and processing needs in modern application scenarios.<\/p>\n
Collection containers store data as key-value pairs. You can use Collection containers when you don't need to organize data based on time and when real-time data processing is not a primary concern.<\/p>\n
TimeSeries containers, on the other hand, are optimized for storing data where each entry in the container has a timestamp associated with it. TimeSeries containers are suitable for scenarios where data is continuously generated with a timestamp, such as sensor data, IoT devices, log files, etc. The data in TimeSeries containers is organized based on time, and it provides efficient query capabilities for time-based data retrieval and analysis. TimeSeries containers can break down data in terms of hours, minutes, seconds, etc., depending on the level of granularity needed. If you are working with non-time series data, collections are the ideal choice. In this case, we will be creating a Collection.<\/p>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
\n
\n
\n<\/div>\n
\n<\/div>
\n
A short note on the JayDeBeAPI\u00b6<\/a><\/h2>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
\n
\n
\n<\/div>\n
\n<\/div>
\n
The JayDeBeApi is a Python module that enables connecting to databases that support the Java Database Connectivity (JDBC) standard. It acts as a bridge between Python and the database using JDBC, allowing users to interact with standalone and cloud databases. In this article, we use the GridDB Cloud environment which is currently only available in Japan but it will be available globally in the near-future. Before getting started, make sure that you whitelist your IP Address from within the GridDB portal. Refer to the GridDB cloud documentation <\/a> for more information.<\/p>\n
The JayDeBeApi library requires the JDBC driver specific to the database you want to connect to. In the case of GridDB, you need to have the GridDB JDBC driver (gridstore-jdbc.jar) installed.<\/p>\n
Here are the steps to do so -<\/p>\n
Download the GridDB JDBC drivers from the GridDB Cloud portal by accessing the 'Supports' page on the Cloud portal and scrolling down to the \"GridDB Cloud Library and Plugin download\" section.\nNote that the file downloaded is a zip archive. On extracting it, the JDBC driver files can be found within the JDBC folder.<\/p>\n