<jupyter_start><jupyter_text>Market Basket Analysis with HANA ML  Introduction In this notebook we will walk through the process of analyzing a retail dataset with **HANA ML** with the goal of finding associations between popular items. The so called **market basket analysis** consists, in fact, in discovering groups of items that are frequently bought together. This information can be used by retailers that want to maximize their sales, for instance by building customized recommendations or proposing discounts to new customers visiting their online store, based on the items they have saved in the virtual cart. In this particular example, we will make use of a dataset containing the purchase orders history of a fictitious children book shop. Mr. Cricket, the book shop manager, is a pretty clever guy and choosed SAP to run his business 😎. We will show how the sales orders data stored in his **S/4 HANA CLOUD** environment can be processed with **HANA ML Predictive Analysis Library (PAL)** for an insightful market basket analysis.SAP HANA PAL provides functions for predictive analysis and machine learning. It supports an extremely conprehensive sets of algorithms for classification, regression, cluster analysis, time series analysis, association analysis, social network analysis, recommender systems, data pre-processing, and statistics. PAL functions can be implemented in several ways: they can be called from within SAP HANA SQLScript procedures, but python and R APIs are also available. Blue Fairy S.p.A. is the SAP golden partner taking care of Mr. Cricket needs. They happen to have a wonderful team of data scientists that love python coding. So let's show them how to use PAL from a Jupyter notebook client. 👩‍💻 👩‍💻 👩‍💻  Install libraries First of all, we need to make sure all the python packages we need for the analysis are available. We will need two very common python libraries, pandas and numpy, and of course hana_ml, the package providing python APIs for HANA ML. Let's use the **pip install** command to download them, in case they are not already there.<jupyter_code>! pip install hana_ml --upgrade
! pip install pandas
! pip install numpy
! pip install shapely<jupyter_output><empty_output><jupyter_text>Access input data in SAP Hana The great advantage of HANA ML is the fact that its algorithms are built to run embedded in SAP HANA. Data stay in memory and don't need to be copied to the client. This way the analysis runs more effectively than it would if we used opensource libraries for the same task (think about sklearn algorithms running on data stored in pandas dataframe). The benefits are particularly evident in case of complex algorithms and huge datasets.To proceed with our analysis, we need to create a **HANA dataframe** containing input data. These are different from a pandas dataframe, that reside and consume resources in the client domain (our jupyter notebook), a HANA dataframe exsists only in memory in HANA. Therefore, we can access it as long as a connection with HANA is enstablished. In the cell below, we create a connection to the HANA layer of the SAP Data Warehouse Cloud Tenant where the bookshop sales orders are stored. Please replace the connection Id with the one related to your account.<jupyter_code>import hana_ml.dataframe as dataframe
from notebook_hana_connector.notebook_hana_connector import NotebookConnectionContext
conn = NotebookConnectionContext(connectionId = 'DWC_D2VUXXXX' )<jupyter_output><empty_output><jupyter_text>Now that the connection is set, we can access the sales order table in the form of a hana dataframe, and start to prepare the data for the association analysis. Please edit the schema input variable below according to your own DWC space.<jupyter_code>df_hana = (conn.table('All_BL_Sales_Order_Data', schema='D2VUXXXX'))

df_hana.head(20).collect()<jupyter_output><empty_output><jupyter_text>To run the book association analysis, we will need just the order id, book id and title, so we have filtered these two columns using the **select** method.<jupyter_code>df_hana=df_hana.select('Order_ID','Book_ID','Book_Title')
df_hana.head(5).collect()
import numpy as np
print('Number of purchased books: ', df_hana.count())
n_transactions=len(np.unique(df_hana.collect()['Order_ID']))
print( 'Number of purchase orders: ',n_transactions)<jupyter_output><empty_output><jupyter_text>As we can see above, the book sales records contain the list of books sold by <NAME> in the last few years. *THE BGF* by <NAME> had a pretty good success. The table is in long (transactional) format, meaning that each row contains only one book, and books that were sold in the same transaction are recorded in multiple rows having the same order ID.The sales history contains **162480 transactions**, for a total of 287906 books.    Build the association model For this analysis, we will use the **Apriori association algorithm**. We will not enter here in the details of how the algorithm works, but you can check out the following resources to learn more:- [HANA ML Python APIs.- Association Analysis Algorithms](https://blogs.sap.com/2019/09/03/association-algorithms-hana-ml-apis/)- [Association Rules and the Apriori Algorithm: A Tutorial](https://www.kdnuggets.com/2016/04/association-rules-apriori-algorithm-tutorial.html)- [Association Discovery — the Apriori Algorithm](https://pub.towardsai.net/association-discovery-the-apriori-algorithm-28c1e71e0f04)- [SAP HANA PAL documentation](https://help.sap.com/viewer/2cfbc5cf2bc14f028cfbe2a2bba60a50/2.0.04/en-US/7a073d66173a4c1589ef5fbe5bb3120f.html)In the cell below, we import the Apriori algorithm from HANA ML and we fit it to our sales dataset. The algorithm will crunch historical sales records in search of good book associations rules. Notice that we set a few parameters while calling the algorithm. We will come back to these in a minute.<jupyter_code>from hana_ml.algorithms.pal.association import Apriori

min_support=0.0005
min_confidence=0.05

ap = Apriori(min_support=min_support,
             min_confidence=min_confidence,
             max_len = 2,
             )

ap.fit(data=df_hana)<jupyter_output><empty_output><jupyter_text>Let's have a look at the results:<jupyter_code>rules_df = ap.result_.collect().sort_values('LIFT',ascending=False)
rules_df<jupyter_output><empty_output><jupyter_text>The Apriori algorithm found 1720 book associations.  The first lines don't look very surprising: they seem to indicate that volumes belonging to the same series are often purchased together. We have *The Barney Mysteries* by <NAME> at the top, followed by a few chapters of the *Nancy Drew Mysteries Stories*. How can we interpret these results in more detail? The result table shows a list of **antecedent-consequent** pairs: customers that bought the *antecedent* book (***A*** in short) have often bought the corrensponding *consequent* (***C***) in the same purchase. The antecedent and consequent columns contain always just one book each because we set the maximum lenght of the sequence (**max_len** parameter) to 2. This has been done just for sake of simplicity. Otherwise, more complex sequences made of combinations of multiple books would be also possible. For each association rule, some statistics are also available: * **SUPPORT**  - The support indicates how frequent the book association is. This is why we set the  **minimum_support** parameter to a value of 0.05%, meaning that we are taking into account only books combinations that  took place at least in 0.05% of the transactions, that is to say in a few tens of occasions. As a matter of fact, it doesn't make sense to consider associations that happened less frequently than that: very rare books are not likely to bring any statistically significant information and they won't have much impact on Mr. Cricket revenues anyway. * **CONFIDENCE**  - The confidence is the probability of purchasing book C when book A is purchased. In general, the higher the confidence, the more robust the association is.  * **LIFT** - When both A and C are popular books, however, the confidence measure can be misleading. An association can be frequent just because both books involved are purchased frequently. Consider for instance the last association proposed. *The BGF* has been bought with *39 clues* quite frequently, but there is no meaningful association between the two. The thing is that these books are both very popular. The lift  measure helps precisely to distinguish these situatons. It is defined as the probability of purchasing *THE BGF* when *39 clues* is purchased, scaled by the overall probability of purchasing *THE BGF* anyways. Only combinations with lift > 1 are actually meaningful.  It's interesting to explore the combinations with intermediate lift values, as shown below. A few valuable associations of books not belonging to the same series or author were discovered. Notice for instance *I wish that I had duck feet* and *Click, clack, moo : cows that type*. This association makes definitely sense, but it is not obvious and it would not be easy to spot it without a statistical analysis.<jupyter_code>rules_df[(rules_df['LIFT']<30) & (rules_df['LIFT']>5)]<jupyter_output><empty_output><jupyter_text>Now that the Blue Fairy data scientists have understood the data using the apriori analysis, they can play with the notebook and adjust the parameters of the Apriori algorithm until they find a configuration they are happy with. For instance it might be a good idea to add a lift lower bound with the **min_lift** input parameter.   Save results Last thing left to do is to save the list of associations. Notice that **ap.result_** is also a HANA dataframe, so it exists only in memory and it will be gone forever if the connection with HANA is dropped. If you want to persists the data in our HANA DB, you can use the **save** method as follows:<jupyter_code>ap.result_.save(where='APRIORI_BOOK_ASSOCIATION',force=True)<jupyter_output><empty_output><jupyter_text>After running the line above, you should be able to see that a new table named APRIORI_BOOK_ASSOCIATION has been created in your schema. Now it's all done you can happily close the connection to HANA.<jupyter_code>conn.close()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.05755746007012076, 'fraction_numerical': 0.010323334631865992, 'mean_word_length': 5.193606755126659, 'pattern_counts': {'":': 0, '<': 41, '<?xml version=': 0, '>': 42, 'https://': 4, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '9', 'dir': 'jupyter-structured-clean-dedup', 'id': '443373', 'license': 'Apache-2.0', 'n_tokens_mistral': 2735, 'n_tokens_neox': 2575, 'n_words': 1420, 'path': '02-data-modeling&processing/exercises/DV210_Exercise01_Book_Recommendation.ipynb', 'repo_name': 'SAP-samples/btp-data-to-value-workshop', 'size': '10282'}