Pandas install on ERPnext VM

Oasis_Agano · February 26, 2018, 10:09am

Greetings,

I am trying to setup pandas on the ERPnextVM in order to use it in a basic data analysis script.

I tried installing it normally pip install pandas it raised conflicting errors, i tried adding it in a new app and running bench setup requirements but it doesnt seem to install it.

The error log is:

numpy.f2py.f2py2e: module references file
numpy.f2py.setup: module references file
numpy.lib.utils: module references file
numpy.lib.utils: module references path
numpy.lib.utils: module MAY be using inspect.getsource
numpy.lib.utils: module MAY be using inspect.getsourcefile
numpy.testing.nose_tools.noseclasses: module references file
numpy.testing.nose_tools.nosetester: module references file
numpy.testing.nose_tools.parameterized: module MAY be using inspect.stack
numpy.testing.nose_tools.utils: module references file
creating /usr/local/lib/python2.7/dist-packages/numpy-1.14.1-py2.7-linux-i686.egg
Extracting numpy-1.14.1-py2.7-linux-i686.egg to /usr/local/lib/python2.7/dist-packages
Adding numpy 1.14.1 to easy-install.pth file
Installing f2py script to /usr/local/bin

Installed /usr/local/lib/python2.7/dist-packages/numpy-1.14.1-py2.7-linux-i686.egg
Searching for pytz>=2011k
Reading Links for pytz
Downloading https://pypi.python.org/packages/bf/d1/d0c9babd1695441cb059be39f9eea38ad1b631cd386765d2cdc97359ccae/pytz-2018.3-py2.7.egg#md5=c5b4a9db14a291d3b38a4fa8d3192b5e
Best match: pytz 2018.3
Processing pytz-2018.3-py2.7.egg
Moving pytz-2018.3-py2.7.egg to /usr/local/lib/python2.7/dist-packages
Adding pytz 2018.3 to easy-install.pth file

Installed /usr/local/lib/python2.7/dist-packages/pytz-2018.3-py2.7.egg
Finished processing dependencies for pandas

Any help is welcome.

Kr,

Oasis

Oasis_Agano · February 26, 2018, 10:28am

The solution was

sudo easy_install pandas, it worked after many failures.

Thanks

aakvatech · May 24, 2019, 12:19am

Looking out for a pandas experienced person on ERPNext for stock pivots by warehouse, item group, brand, etc.

clarkej · May 24, 2019, 1:48pm

Yup pivot tables are the way to analyse data, plot and isolate trends, or troubleshoot missing data.

This ‘sales funnel’ tutorial has lots of q&a comments Pandas Pivot Table Explained - Practical Business Python

This OReilly one Pivot Tables | Python Data Science Handbook uses a Titanic passenger dataset for multivariable pivot table sex, age, class, fare, and second birthrate data example.

The first tutorial uses just the command line while the second one uses the Jupyter notebook code workspace. Both environments require some ‘terminal console’ skill.

The question is, to what degree anyone has mashed pandas into ERPNEXT or limited to manual command line intervention cases?

aakvatech · June 14, 2019, 10:51pm

I am trying to use pandas on a script report so the challenges I see are:

How to prepare data from frappe.db.sql to be able to present as raw_data into pandas.
How to convert data from pandas.crosstab into a format that can be used as data return back to the grid from the report.

issue 1. Currently the frappe.db.sql outputs dictionary of row values where as pandas expects in a dictionary of column values.
issue 2. Currently the pandas.crosstab outputs in a dataframe format native to pandas and visible on console, but the data to be returned from the script is a row list of column list.

Not sure if my analysis is correct, but that’s what it looks like.

If anybody has managed to make a script report with pandas, an example will be appreciated.

rmeyer · July 8, 2019, 10:34am

You can simply use pandas in ERPNext code as it’s already in the list of dependencies.

I used it to output a report as a custom CSV file:

import pandas as pd

data = frappe.db.sql("""SELECT * FROM `tabCustomer`""", as_dict=1)
df = pd.DataFrame.from_records(data)
# latin-1-encoded CSV file, separated by semicolons
csv_file = df.to_csv(sep=b';', encoding='latin_1')

Update March 2023: since version-14 pandas is no longer bundled with erpnext. You’ll have to include it into your own apps dependencies.

aakvatech · July 10, 2019, 4:20am

Achieved:

Using pandas to create pivot table using bench console with below code.

Code:

import datetime
import pandas as pd
import numpy as py

df = pd.DataFrame(sl_entries)
frappe.msgprint("dataframe is: " + str(df.count))
pvt = pd.pivot_table(df,index=[“posting_date”,“Particulars”],values=[“actual_qty”],columns=[“item_code”],aggfunc=[np.sum], fill_value=0)
pvtri = pvt.reset_index(level=[0,1])
columns += pvt.columns.levels[2].values.tolist()
data = pvt.reset_index().values.tolist()

But when I put the same code into the .py for report it gives error like below:

Error:

File “/home/frappe/frappe-bench/apps/csf_tz/csf_tz/csf_tz/report/itemwise_stock_movement/itemwise_stock_movement.py”, line 25, in execute
pvt = pd.pivot_table(df,index=[“posting_date”,“Particulars”],values=[“actual_qty”],columns=[“item_code”],aggfunc=[np.sum], fill_value=0)
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pandas/core/reshape/pivot.py”, line 41, in pivot_table
margins=margins, margins_name=margins_name)
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pandas/core/reshape/pivot.py”, line 61, in pivot_table
raise KeyError(i)
KeyError: u’actual_qty’

Scenario:

This code works well and prepares the data ready to display by the report

but when I run it in the report and load using front end it gives KeyError.

If we can overcome this error, we can have a lot of ease in making report using pandas

List of DIctionaries is as below.

[{u’Particulars’: u’Mohamed Shamshu’,
u’actual_qty’: -800.0,
u’item_code’: u’Halotel 2000’,
u’posting_date’: datetime.date(2019, 1, 2)},
{u’Particulars’: u’Mohamed Shamshu’,
u’actual_qty’: -10000.0,
u’item_code’: u’Halotel 500’,
u’posting_date’: datetime.date(2019, 1, 2)},
{u’Particulars’: u’Raphael’,
u’actual_qty’: -40.0,
u’item_code’: u’Halotel 2000’,
u’posting_date’: datetime.date(2019, 1, 2)},
{u’Particulars’: u’Raphael’,
u’actual_qty’: -500.0,
u’item_code’: u’Halotel 500’,
u’posting_date’: datetime.date(2019, 1, 2)},
{u’Particulars’: u’Stock Entry’,
u’actual_qty’: 9500.0,
u’item_code’: u’Halotel 1000’,
u’posting_date’: datetime.date(2019, 1, 2)},
{u’Particulars’: u’Stock Entry’,
u’actual_qty’: 200.0,
u’item_code’: u’Halotel 10000’,
u’posting_date’: datetime.date(2019, 1, 2)},
{u’Particulars’: u’Stock Entry’,
u’actual_qty’: 2000.0,
u’item_code’: u’Halotel 2000’,
u’posting_date’: datetime.date(2019, 1, 2)},
{u’Particulars’: u’Stock Entry’,
u’actual_qty’: 19250.0,
u’item_code’: u’Halotel 500’,
u’posting_date’: datetime.date(2019, 1, 2)},
{u’Particulars’: u’Stock Entry’,
u’actual_qty’: 980.0,
u’item_code’: u’Halotel 5000’,
u’posting_date’: datetime.date(2019, 1, 2)},
{u’Particulars’: u’Athar Msasani’,
u’actual_qty’: -500.0,
u’item_code’: u’Halotel 1000’,
u’posting_date’: datetime.date(2019, 1, 3)},
{u’Particulars’: u’Athar Msasani’,
u’actual_qty’: -20.0,
u’item_code’: u’Halotel 10000’,
u’posting_date’: datetime.date(2019, 1, 3)},
{u’Particulars’: u’Athar Msasani’,
u’actual_qty’: -500.0,
u’item_code’: u’Halotel 500’,
u’posting_date’: datetime.date(2019, 1, 3)},
{u’Particulars’: u’Cheni’,
u’actual_qty’: -250.0,
u’item_code’: u’Halotel 1000’,
u’posting_date’: datetime.date(2019, 1, 3)},
{u’Particulars’: u’Cheni’,
u’actual_qty’: -1500.0,
u’item_code’: u’Halotel 500’,
u’posting_date’: datetime.date(2019, 1, 3)},
{u’Particulars’: u’Counter 4 Adam’,
u’actual_qty’: -2000.0,
u’item_code’: u’Halotel 1000’,
u’posting_date’: datetime.date(2019, 1, 3)},
{u’Particulars’: u’Counter 4 Adam’,
u’actual_qty’: -150.0,
u’item_code’: u’Halotel 10000’,
u’posting_date’: datetime.date(2019, 1, 3)},
{u’Particulars’: u’Counter 4 Adam’,
u’actual_qty’: -220.0,
u’item_code’: u’Halotel 2000’,
u’posting_date’: datetime.date(2019, 1, 3)},
{u’Particulars’: u’Counter 4 Adam’,
u’actual_qty’: -3800.0,
u’item_code’: u’Halotel 500’,
u’posting_date’: datetime.date(2019, 1, 3)},
{u’Particulars’: u’Counter 4 Adam’,
u’actual_qty’: -240.0,
u’item_code’: u’Halotel 5000’,
u’posting_date’: datetime.date(2019, 1, 3)},
{u’Particulars’: u’Devota Dsm’,
u’actual_qty’: -1000.0,
u’item_code’: u’Halotel 1000’,
u’posting_date’: datetime.date(2019, 1, 3)},
{u’Particulars’: u’Devota Dsm’,
u’actual_qty’: -60.0,
u’item_code’: u’Halotel 2000’,
u’posting_date’: datetime.date(2019, 1, 3)},
{u’Particulars’: u’Devota Dsm’,
u’actual_qty’: -1500.0,
u’item_code’: u’Halotel 500’,
u’posting_date’: datetime.date(2019, 1, 3)},
{u’Particulars’: u’Faustine’,
u’actual_qty’: -750.0,
u’item_code’: u’Halotel 1000’,
u’posting_date’: datetime.date(2019, 1, 3)},
{u’Particulars’: u’Faustine’,
u’actual_qty’: -2000.0,
u’item_code’: u’Halotel 500’,
u’posting_date’: datetime.date(2019, 1, 3)},
{u’Particulars’: u’Kalinga’,
u’actual_qty’: -1200.0,
u’item_code’: u’Halotel 1000’,
u’posting_date’: datetime.date(2019, 1, 3)},
{u’Particulars’: u’Kalinga’,
u’actual_qty’: -10.0,
u’item_code’: u’Halotel 10000’,
u’posting_date’: datetime.date(2019, 1, 3)},
{u’Particulars’: u’Kalinga’,
u’actual_qty’: -300.0,
u’item_code’: u’Halotel 500’,
u’posting_date’: datetime.date(2019, 1, 3)},
{u’Particulars’: u’Kmj (Najma)‘,
u’actual_qty’: -1000.0,
u’item_code’: u’Halotel 1000’,
u’posting_date’: datetime.date(2019, 1, 3)},
{u’Particulars’: u’Kmj (Najma)‘,
u’actual_qty’: -20.0,
u’item_code’: u’Halotel 10000’,
u’posting_date’: datetime.date(2019, 1, 3)},
{u’Particulars’: u’Kmj (Najma)‘,
u’actual_qty’: -40.0,
u’item_code’: u’Halotel 2000’,
u’posting_date’: datetime.date(2019, 1, 3)},
{u’Particulars’: u’Kmj (Najma)‘,
u’actual_qty’: -1000.0,
u’item_code’: u’Halotel 500’,
u’posting_date’: datetime.date(2019, 1, 3)},
{u’Particulars’: u’Kmj (Najma)‘,
u’actual_qty’: -460.0,
u’item_code’: u’Halotel 5000’,
u’posting_date’: datetime.date(2019, 1, 3)},
{u’Particulars’: u’Maiko Mbute’,
u’actual_qty’: -500.0,
u’item_code’: u’Halotel 1000’,
u’posting_date’: datetime.date(2019, 1, 3)},
{u’Particulars’: u’Maiko Mbute’,
u’actual_qty’: -6000.0,
u’item_code’: u’Halotel 500’,
u’posting_date’: datetime.date(2019, 1, 3)},
{u’Particulars’: u’Mohamed Honga’,
u’actual_qty’: -1000.0,
u’item_code’: u’Halotel 1000’,
u’posting_date’: datetime.date(2019, 1, 3)},
{u’Particulars’: u’Mohamed Honga’,
u’actual_qty’: -60.0,
u’item_code’: u’Halotel 2000’,
u’posting_date’: datetime.date(2019, 1, 3)},
{u’Particulars’: u’Mohamed Honga’,
u’actual_qty’: -60.0,
u’item_code’: u’Halotel 5000’,
u’posting_date’: datetime.date(2019, 1, 3)},
{u’Particulars’: u’Mohamed Shamshu’,
u’actual_qty’: -6000.0,
u’item_code’: u’Halotel 1000’,
u’posting_date’: datetime.date(2019, 1, 3)},
{u’Particulars’: u’Mohamed Shamshu’,
u’actual_qty’: -300.0,
u’item_code’: u’Halotel 2000’,
u’posting_date’: datetime.date(2019, 1, 3)},
{u’Particulars’: u’Mohamed Shamshu’,
u’actual_qty’: -15000.0,
u’item_code’: u’Halotel 500’,
u’posting_date’: datetime.date(2019, 1, 3)},
{u’Particulars’: u’Mohamed Shamshu’,
u’actual_qty’: -200.0,
u’item_code’: u’Halotel 5000’,
u’posting_date’: datetime.date(2019, 1, 3)},
{u’Particulars’: u’Purchase Receipt’,
u’actual_qty’: 10000.0,
u’item_code’: u’Halotel 1000’,
u’posting_date’: datetime.date(2019, 1, 3)},
{u’Particulars’: u’Purchase Receipt’,
u’actual_qty’: 100.0,
u’item_code’: u’Halotel 10000’,
u’posting_date’: datetime.date(2019, 1, 3)},
{u’Particulars’: u’Purchase Receipt’,
u’actual_qty’: 1000.0,
u’item_code’: u’Halotel 2000’,
u’posting_date’: datetime.date(2019, 1, 3)},
{u’Particulars’: u’Purchase Receipt’,
u’actual_qty’: 40000.0,
u’item_code’: u’Halotel 500’,
u’posting_date’: datetime.date(2019, 1, 3)},
{u’Particulars’: u’Purchase Receipt’,
u’actual_qty’: 500.0,
u’item_code’: u’Halotel 5000’,
u’posting_date’: datetime.date(2019, 1, 3)},
{u’Particulars’: u’Raphael’,
u’actual_qty’: -2100.0,
u’item_code’: u’Halotel 1000’,
u’posting_date’: datetime.date(2019, 1, 3)},
{u’Particulars’: u’Raphael’,
u’actual_qty’: -4900.0,
u’item_code’: u’Halotel 500’,
u’posting_date’: datetime.date(2019, 1, 3)},
{u’Particulars’: u’Raphael’,
u’actual_qty’: -40.0,
u’item_code’: u’Halotel 5000’,
u’posting_date’: datetime.date(2019, 1, 3)},
{u’Particulars’: u’Solar Kiosk’,
u’actual_qty’: -2500.0,
u’item_code’: u’Halotel 1000’,
u’posting_date’: datetime.date(2019, 1, 3)}]

The code works from bench console, but running it from report .py file it gives error

aakvatech · July 10, 2019, 5:54pm

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=['posting_date', 'Particulars'],
	columns='item_code',
	fill_value=0
)

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

Example output from script report:

rmeyer · July 14, 2020, 2:14pm

FYI, ERPNext version-13 comes with pandas 1.0.5 instead of pandas 0.24 in ERPNext version-12 .

https://github.com/frappe/erpnext/issues/22621

Azhar_Umar · December 6, 2022, 8:15pm

@aakvatech Can you help me define columns and data for my little project here?

MartinHBramwell · February 17, 2023, 2:28pm

You are my hero!!!

Saved me a ton of aggravation!
Thanx!

Prasanth_Kumar_J · December 29, 2023, 6:28am

@aakvatech any solution you found?