Using Python to get data from s4 hana cloud by odata and use python pandas to handle data

In some situation , we need to extract data from s4hc and do some data handle work like we do in SAP ECC or S4HAN OP system with abap . We can realize this with BTP sdk on BTP with JAVA. But today I want to try with python pandas .

refered blog :[Consuming OData service based on ODP extractor in Python | SAP Blogs](https://blogs.sap.com/2020/11/04/consuming-odata-service-based-on-odp-extractor-in-python/)

Prerequisite: communication arrangement for sales order and outbound delivery has been set in s4 hana cloud .

used dev tool: Pycharm

Install python library in cmd:

pip install requests

pip install pyodata

pip install pandas

pip install openpyxl

python code :

# This is a sample Python script.

import requests
import pyodata
import pandas as pd
import io
import json
from datetime import datetime

def handleData():
    # Use a breakpoint in the code line below to debug your script.
    SERVICE_URL = 'https://myxxxxx-api.saps4hanacloud.cn/sap/opu/odata/sap/API_OUTBOUND_DELIVERY_SRV;v=0002/A_OutbDeliveryItem/?$format=json'
    response = requests.get(SERVICE_URL,auth=('communication user', 'communication user password'), headers={"Prefer": "odata.maxpagesize=500", "Prefer": "odata.track-changes"})
    init_json = json.loads(response.content)
    length = len(init_json['d']['results'])
    print(length)
    # print(init_json['d']['results'])
    l_output = []
    l_record = []
    # l_record = ('DeliveryDocument','DeliveryDocumentItem','Plant','ReferenceSDDocumentCategory','ReferenceSDDocument','ReferenceSDDocumentItem','ActualDeliveredQtyInBaseUnit','BaseUnit')
    l_output.append(l_record)
    i=0
    while i < length:
        l_record = (init_json['d']['results'][i]['DeliveryDocument'],init_json['d']['results'][i]['DeliveryDocumentItem'],init_json['d']['results'][i]['Plant'],init_json['d']['results'][i]['ReferenceSDDocumentCategory'],init_json['d']['results'][i]['ReferenceSDDocument'],init_json['d']['results'][i]['ReferenceSDDocumentItem'],init_json['d']['results'][i]['ActualDeliveredQtyInBaseUnit'],init_json['d']['results'][i]['BaseUnit'])
        l_output.append(l_record)
        i=i+1
    # print(l_output)

    df_dn = pd.DataFrame(l_output,columns=['DeliveryDocument','DeliveryDocumentItem','Plant','ReferenceSDDocumentCategory','SalesOrder','SalesOrderItem','ActualDeliveredQtyInBaseUnit','BaseUnit'])
    # delete leading 0 from so item column
    df_dn['SalesOrderItem'] = df_dn['SalesOrderItem'].str.lstrip('0')
    # change data type from string to float for actual delivery quantity column
    df_dn[['ActualDeliveredQtyInBaseUnit']] = df_dn[['ActualDeliveredQtyInBaseUnit']].astype('float')
    #subtotal the columnn ActualDeliveredQtyInBaseUnit by SalesOrder,SalesOrderItem,BaseUnit
    df_dn = df_dn[['SalesOrder','SalesOrderItem','BaseUnit','ActualDeliveredQtyInBaseUnit']].groupby(['SalesOrder','SalesOrderItem','BaseUnit']).agg('sum')

    # get so item data from S4HC
    SERVICE_URL = 'https://myxxxxxx-api.saps4hanacloud.cn/sap/opu/odata/sap/API_SALES_ORDER_SRV/A_SalesOrderItem/?$format=json'
    response = requests.get(SERVICE_URL,auth=('communication user', 'communication user password'), headers={"Prefer": "odata.maxpagesize=500", "Prefer": "odata.track-changes"})
    init_json = json.loads(response.content)
    length = len(init_json['d']['results'])
    i=0
    l_output = []
    l_record = []
    while i<length:
        l_record = (
        init_json['d']['results'][i]['SalesOrder'], init_json['d']['results'][i]['SalesOrderItem'],
        init_json['d']['results'][i]['SalesOrderItemText'], init_json['d']['results'][i]['Material'],
        init_json['d']['results'][i]['OrderQuantityUnit'], init_json['d']['results'][i]['ConfdDelivQtyInOrderQtyUnit'],
        init_json['d']['results'][i]['TransactionCurrency'], init_json['d']['results'][i]['NetAmount'])
        l_output.append(l_record)
        i = i + 1

    df_so = pd.DataFrame(l_output, columns=[ 'SalesOrder','SalesOrderItem','SalesOrderItemText','Material', 'OrderQuantityUnit','ConfdDelivQtyInOrderQtyUnit','TransactionCurrency','NetAmount'])
    # change data type from string to float for ConfdDelivQtyInOrderQtyUnit column
    df_so[['ConfdDelivQtyInOrderQtyUnit']] = df_so[['ConfdDelivQtyInOrderQtyUnit']].astype('float')
    df_so[['NetAmount']] = df_so[['NetAmount']].astype('float')

    # join the so item table and dn item subtotal table
    df_so=pd.merge(df_so,df_dn,how='inner',on=['SalesOrder','SalesOrderItem'])
    print(df_so)
    file_name = 'so_' + str(datetime.now().strftime('%Y_%m_%d_%H_%M_%S')) + '.csv'
    # save the result in local file
    df_so.to_csv(file_name, index=True, header=True,encoding='utf-8')


# Press the green button in the gutter to run the script.
if __name__ == '__main__':
    handleData()

The result is easy to save as different format