Trying to create Script Report using Pandas

[Azhar_Umar]

I am trying to create a script report that will show me a week-wise summary of payment requests per supplier, using Pandas.

I have written the following code to get get the output in Pandas DataFrame:

import frappe
import pandas as pd

from erpnext.accounts.utils import get_balance_on

def test():
    pass

def execute(filters=None):
    pr = frappe.db.get_list("Payment Request",filters={"docstatus":1,"status":"Initiated","party_type":"Supplier"},fields=["name", "transaction_date", "payment_request_type", "party", "supplier_name", "reference_name", "grand_total", "status"])
    df = pd.DataFrame.from_records(pr)
    df['transaction_date'] = pd.to_datetime(df['transaction_date'])
   
    df["week"] = df['transaction_date'].dt.year.astype(str) + df['transaction_date'].dt.week.map("{:02}".format).astype(str)
    df = df.groupby(["party", "week", "supplier_name"],as_index=False)["grand_total"].sum()
    df = df.sort_values(by=['week'],ascending=True)
    df = df.pivot(index=["party","supplier_name"], columns='week', values='grand_total').reset_index().rename_axis(None, axis=1).fillna(0)

    print(df.to_string())

    column_names = df.columns.values
    print(column_names)

    columns, data = [], []
    return columns, data

The data output is as follows:

The following are the things I want to achieve now.

1. I need a new column with Supplier Balance. How can I achieve this using get_balance_on(party_type, party) function? I tried the below code, but was of no help.

df["balance"] = get_balance_on(party_type="Supplier", party=df["name"])

2. How can I get this dataframe into columns, data = [], []. I have outputted column names using column_names = df.columns.values

Any guidance in this regard would be of great help.

[Azhar_Umar]

So I figured out the answer for my first question from a response to my question on stackoverflow. The below line of code will fetch me supplier balances in my pandas pivot table.

df["balance"] = df.apply(lambda x: get_balance_on(party_type="Supplier",party=x['party']), axis=1)

Now, how can I get this data frame into erpnext report

[brian_pond]

To render a Report, you need to return 2 pieces of information:

1. The column headers. These are a List of Dictionary. Example:

[
  {
	"label": _("Delivery Date"),
	"fieldname": "delivery_date",
	"fieldtype": "Date",
	"width": 120
  },
  {
	"label": _("Day Of Week"),
	"fieldname": "day_of_week_name",
	"fieldtype": "Data",
	"width": 100
  }
]

2. The data rows. Also a List of Dictionary objects. Just convert your dataframe into that, and you’re set.

[Azhar_Umar]

I understand how to define static columns. But how can I do it when i am fetching the column names like this:

column_names = df.columns.values

[Azhar_Umar]

I tried looking into this, but no hope.

Pandas install on ERPnext VM

Found the solution. The below lines of code is all that is required to make crosstab / pivot report. sl_entries = get_stock_ledger_entries(filters, items) # coming from frappe.db.sql in get_stock_ledger_entries() colnames = [key for key in sl_entries[0].keys()] # create list of columns used in creating dataframe df = pd.DataFrame.from_records(sl_entries, columns=colnames) # this is key to get the data from frappe.db.sql loaded correctly. pvt = pd.pivot_table( df, values='actual_qty', index=…

[brian_pond]

I’d probably do something like this:

# First, create your dataframe "df"

column_names = []

for each_column_name in df.columns.values:
    new_dict = { 
        "label": each_column_name,
        "field_name": each_column_name,
        "fieldtype": "Data",
        "width": 100
    }
    column_names.append(this_dict)

# variable "column_names" is now in the correct format for Report usage.

If you wanted to get fancier, you could teach it to map to DocFields, and fetch metadata, to get a more friendly “label”, and an accurate “fieldtype”.

[aakvatech]

I used

data = pvt.reset_index().values.tolist() # reset the index and create a list for use in report.

columns += pvt.columns.values.tolist() # create the list of dynamic columns added to the previously defined static columns

To get data and columns from the df

[Azhar_Umar]

Just these three lines worked:

	df = df.set_index('party') # to change the index to party, otherwise index will get displayed again.

	data = df.values.tolist()
	columns = df.columns.values.tolist()

@aakvatech Thanks for the input. I just modified your code to get the desired output.
@brian_pond Thanks for the input brother

[Azhar_Umar]

I have finished my report. But now I have stumbled upon a new problem. This might be a real silly question for developers, I have just started on my journey to learning python

All the values in the report is showing as float and without proper column dict, how can I assign it as currency?

Here is my code:

# Copyright (c) 2022, Zarnik Hotel Supplies Private Limited and contributors
# For license information, please see license.txt

import frappe
import pandas as pd

from erpnext.accounts.utils import get_balance_on

def test():
	pass

def execute(filters=None):

	pr = frappe.db.get_list("Payment Request",filters={"docstatus":1,"status":"Initiated","party_type":"Supplier"},fields=["name", "transaction_date", "payment_request_type", "party", "supplier_name", "reference_name", "grand_total", "status"])
	df = pd.DataFrame.from_records(pr)
	df['transaction_date'] = pd.to_datetime(df['transaction_date'])


	df["week"] = df['transaction_date'].dt.year.astype(str) + " - " + df['transaction_date'].dt.week.map("{:02}".format).astype(str)
	df = df.groupby(["party", "week", "supplier_name"],as_index=False)["grand_total"].sum()
	
	df = df.sort_values(by=['week'],ascending=True)
	df = df.pivot(index=["party","supplier_name"], columns='week', values='grand_total').reset_index().rename_axis(None, axis=1).fillna(0)

	df["Scheduled Total"] = round(df.iloc[:,1:].sum(axis=1),2)

	df["Ledger Balance"] = round(df.apply(lambda x: get_balance_on(party_type="Supplier",party=x['party']), axis=1),2)

	df["Unscheduled Amount"] = round(df["Ledger Balance"] + df["Scheduled Total"],2)
	df = df.set_index('party')
	df.rename(columns = {'party':'Supplier Code','supplier_name':'Supplier Name'}, inplace = True)

	data = df.values.tolist()
	columns = df.columns.values.tolist()

	return columns, data

[brian_pond]

I don’t believe this is possible, without passing the columns as a Dictionary with "fieldtype": "Currency"

[Azhar_Umar]

Exactly

Maybe, I’ll try to figure out a solution through how you suggested, by using a for loop.

[MartinHBramwell]

Hi Azhar!

I am studying what you did. I would like to ask, if I may …

Once you have the report doing what you want, do you run the Python code from the command line or is there a way to run it from ERPNext?

If the latter, could you point me towards some documentation pages or tutorials sites?

Thanks,

[brian_pond]

My impression was he was just writing a Script Report.

[MartinHBramwell]

¡¡¡ Kewl !!!

That’s all I needed!

Thanks Brian.

[Azhar_Umar]

Once the report was done. I get the report from ERPNext. Not the command line. As @brian_pond pointed, it was just a script report where i used Pandas.