Exploring and Manipulating Data with SAP HANA DataFrames

The Employee Churn dataset is freely available and can be uploaded from [1]. When it is loaded, it can be explored using SAP HANA DataFrames, which provide a high performance, columnar, in-memory data structure for efficient data manipulation and analysis within SAP HANA Cloud.

To begin, the dataset is read into a Pandas DataFrame using the read_csv() function, and the first five observations are displayed to get an initial overview:

12
df_data = pd.read_csv("./Emp_Churn_Train.csv", sep = ',')
df_data.head(5)

5 rows x 43 columns

‎

‎‎Additional materials related to the Employee Churn scenario can be found in the same folder [2].

To leverage SAP HANA’s in-memory processing capabilities, the dataset must be converted into a SAP HANA DataFrame. This can be done using the hana_ml.dataframe.create_dataframe_from_pandas() function [1], which creates a table in SAP HANA:

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

5 rows × 43 columns

‏‏‎ ‎Additional materials related SAP HANA DataFrame can be found in the reference [1].

Alternatively, you can explicitly load the Employee Churn data into a SAP HANA DataFrame. The conn.table() method allows you to access the SAP HANA table:

123
hdf_employeechurn = conn.table('EMPLOYEE_CHURN_DATA') 
display(hdf_employeechurn.select_statement)
display(hdf_employeechurn.head(6).collect())

6 rows × 43 columns

1
display(hdf_employeechurn.count())

Below, a SAP HANA DataFrame is returned with the group_by column along with the aggregates. This method supports all aggregation functions in the SAP HANA database instance, such as 'max', 'min', 'count', 'avg', 'sum', 'median', 'stddev', 'var'.

The name of the column in the returned DataFrame is the same as the original column, that is, FLIGHT_RISK. The FLIGHT_RISK column indicates whether an employee left the company in the last 12 months or not.

More information about the Employee Churn dataset can be explored in the folder Employee Chum dataset description [1] .

To count the number of employees based on 'FLIGHT_RISK', the following code can be used:

1
hdf_employeechurn.agg([('count', 'EMPLOYEE_ID', 'N')], group_by='FLIGHT_RISK').collect()

Next, the SQL query backing the returned SAP HANA DataFrame (with the group_by column) is displayed.

1
hdf_employeechurn.agg([('count', 'EMPLOYEE_ID', 'N')], group_by='FLIGHT_RISK').select_statement

'SELECT "FLIGHT_RISK", count("EMPLOYEE_ID") AS "N" FROM (SELECT * FROM "EMPLOYEE_CHURN_DATA") AS "DT_6" GROUP BY "FLIGHT_RISK"'

Data manipulation and analysis are vital in Data Science. The SAP class method DataFrame.describe() returns a DataFrame containing various statistics of data attributes/columns [1]. These descriptive statistics summarize central tendency, dispersion, and the shape of a dataset's distribution, helping organize and present data meaningfully.

For example, row number 3 (shown below) displays the statistics for the 'SALARY' column, which represents an employee's salary in € (Euros). This column has '19,115' observations, with no null values, and its mean value is '€62,003.15', ranging from a minimum of '€4,900.00' to a maximum of '€120,000.00'. The median is '€63,000.00' [2], which splits the dataset into its higher and lower halves.

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

Based on the dataset's statistical summary, the columns 'PREVCOUNTRYLAT' and 'PREVCOUNTRYLON' contain a significant number of null values—approximately 51% in each. Given this high proportion of missing data, these columns are dropped from the dataset for further analysis.