Pandas function to read table data in Datasphere Dataflow script editor

[t_ravichandra]

Hi Team,

Can someone please help me in getting the Pandas DataFrame function to read table data in Dataflow python transformation editor.

[XaviPolo · Accepted solution]

in a DF add the source (table or view,) then connect it to the python transform.

When you execute the DF it will pass the connected source to the function as a Pandas Dataframe.

the Python transform implements this function as

def transform(data):
    # data param is a pandas dataframe that has the data of the connected source
    data['NEW'] = data['ID'] + data['NAME']
    return data

So if you connect a table with 2 fields (ID and NAME), you will have into the ptyhon function a dataframe data with this two columns. You can modify the dataframe adding or removing columns like data['NEW'] = data['ID'] + data['NAME']

Then update the output of python transform in the "Columns section", adding or removing columns.

Regards,

[t_ravichandra · Accepted solution]

Thanks xavierpolo for the reply. But in my case, I need to have a DataFrame of Lookup table which is not linked in this flow. I need this to derive some fields. PFB screenshot

Could you please let me know how to achieve this.

[XaviPolo · Accepted solution]

sorry but the python transformation can only access data passed to it by connecting a source.

It is not allowed to access from the code to external data, nor the use of communication functions to for example read with pandas a CSV via a URL, etc.

This limitation also only allows a single input source to be sent as a dataframe (you can combine multiple sources in a view with join or union, but always prior to python transformation). In addition, the function will be called for each 10,000 rows of the source, i.e. it does not pass the entire set, it does it by blocks.

Regards,

[t_ravichandra · Accepted solution]

Hmm.... lot of restrictions 😞

Then what is the purpose of this python transformation, the existing logic can be derived using SQL View as well. Do we have any specific usecase, where we can go for Python transformation ?

And SAP has any plan to implement the DataFrame READ_TABLE function ?

[XaviPolo · Accepted solution]

well, you can do "advanced" transformations in SQL Scripting or in Pyhton, It is up to each person which one to choose depending on his or her knowledge,

In the product roadpam there is nothing about it, and I don't think it is an apriority for SAP to extend python capabilities. Besides that to run python you need resources and this has a direct impact on costs,

In SAC you can use R in two ways, a "limited" version if you use the one provided by SAP, or you can provide an RServe with all the possibilities since you host and manage it yourself.

I don't think there is anything similar to RServe in Python so ...

Regards,

[t_ravichandra · Accepted solution]

Got it, thanks so much xavierpolo.