Exploring and Manipulating Data with SAP HANA Dataframes

The overnight stays dataset is freely available and can be uploaded from [1] as shown below. Using the following code, we display the first five observations of the newly created Pandas DataFrame.

More material related to the overnight stays scenario can be found in the very same folder [2].

123
df_data= pd.read_csv('./OVERNIGHTSTAYS.csv', sep = ',')
df_data.MONTH = pd.to_datetime(df_data.MONTH, format='%d/%m/%Y')
df_data.head(5)

The SAP function hana_ml.dataframe.create_dataframe_from_pandas() [3] creates an SAP HANA DataFrame from a Pandas DataFrame and creates a table in SAP HANA, that is, table_name = 'OVERNIGHTSTAYS'.

Then, we display the first five observations of the SAP HANA DataFrame, and then DataFrame.collect() copies the current DataFrame to a new Python Pandas DataFrame [4].

1234567

df_remote = dataframe.create_dataframe_from_pandas(connection_context = conn,
                                                   pandas_df = df_data,
                                                   table_name = 'OVERNIGHTSTAYS',
                                                   force = True,
                                                   replace = False)
display(df_remote.head(5).collect())

We then show the structure of the table that has been created as follows:

_Output:

We can also explicitly load the overnight stays data into the SAP HANA DataFrame as shown below.

Then, we display the SQL query backing the DataFrame.

Finally, we display the first six observations of the DataFrame.

123
hdf_overnightstays = conn.table('OVERNIGHTSTAYS')
display(hdf_overnightstays.select_statement)
display(hdf_overnightstays.head(6).collect())

Next, DataFrame.count()[5] computes the number of rows in the SAP HANA DataFrame and displays it.

1
display(hdf_overnightstays.count())

29029

Data manipulation and analysis are vital in the field of Data Science. The SAP class method DataFrame.describe() returns a DataFrame that contains various statistics of data attributes/columns [6].

Such descriptive statistics include those that summarize the central tendency, dispersion, and shape of a dataset's distribution. This helps to organize and present data in a meaningful manner.

For example,in the following figure, the row labeled 3 displays the statistics for the COUNTRYOFRESIDENCE column. This column represents the visitor's country of residence. We notice that it has 29029 observations as the rest of the dataset's columns. It has no null values, and we may remember it is a character-based data type; however, a numerical attribute is the first column, that is the OVERNIGHTSTAYS column, for which several statistics are computed, namely minimum, maximum, mean, median, and so on. We may remember the median is the value splitting the higher half from the lower half of the dataset [7].

1
hdf_overnightstays.describe().head(10).collect()

4 rows × 15 columns

We can explore the data by looking at a graphical report as shown below:

1
UnifiedReport(hdf_overnightstays).build().display()

The following image shows the Variable Type Distribution graph.

The following image shows the High Cardinality Variables chart.

The following image shows the Highly Skewed Variables chart.

In order to perform further data analysis, we need to aggregate the overnight stays by month as shown below:

123
hdf_overnightstays_agg = hdf_overnightstays.agg([('sum', 'OVERNIGHTSTAYS', 'OVERNIGHTSTAYS_SUM')], group_by='MONTH')
hdf_overnightstays_agg = hdf_overnightstays_agg.sort('MONTH')
hdf_overnightstays_agg.head(5).collect()

We proceed to plot the monthly data as follows:

12345
df_data = hdf_overnightstays_agg.collect()

df_data.plot(x='MONTH')
plt.xticks( rotation='vertical')
plt.show()

[1]Overnight Stays Dataset

[2]Overnight Stays Scenario

[3]SAP function hana_ml.dataframe.create_dataframe_from_pandas()

[4]SAP hana_ml.dataframe-DataFrame.collect()

[5]SAP hana_ml.dataframe-DataFrame.count()

[6]SAP hana.ml.dataframe-DataFrame.describe()

[7]Statistics: Median