topic Re: Pandas Pivot Table with rounding in Python Questions

Pandas Pivot Table with rounding

ZacharyHart — Fri, 09 Nov 2018 16:21:21 GMT

I'm attempting to add in subtotals to a pivot table from a very basic array. The first example I provide I derived on my own, but this has no subtotals for each group.

The second example I borrowed and honestly I don't really get how it works just yet, and I cannot get a round to work.

pd.pivot_table(df.round({'ACRES':1}),values = 'ACRES', index = ['SUPER_TYPE','STRATA', 'OS_TYPE'], aggfunc=np.sum, margins = True)‍‍

Returns:

2nd example:

df.groupby(['SUPER_TYPE']).apply(lambda sub_df: sub_df.pivot_table(index=['STRATA', 'OS_TYPE'], values=['ACRES'], margins=True) )‍‍

I'm guessing there are a number of ways to go about this. Thanks in advance!

Re: Pandas Pivot Table with rounding

DanPatterson_Retired — Fri, 09 Nov 2018 23:57:12 GMT

pandas.DataFrame.round — pandas 0.23.4 documentation

pandas.Series.round — pandas 0.23.4 documentation

then there are examples on Stack Exchange

python - Rounding entries in a Pandas DafaFrame - Stack Overflow This one has a pivot table as an example

Numpy to the rescue!!

Re: Pandas Pivot Table with rounding

ZacharyHart — Sun, 12 Dec 2021 08:21:33 GMT

Dan Patterson, thanks for that...I didn't know it would be so simple. Here is what I came up with for my test code before I roll it up into a function. I've always got open ears for improvement! Edit: my pivot table fails to include a grand total. Any suggestions?

table = "\\\\fileServer\\MAP_PROJECTS\\LV_WEB\\SDE_CONNECTIONS\\LV_NEXUS.sde\\LV_NEXUS.DATAOWNER.NORTHEAST\\LV_NEXUS.DATAOWNER.NE_HARVEST_OPS"
HUID = "669-NMTC-139"
whereClause = """ "LV_HARVEST_UNIT_ID" = '{0}' """.format(HUID)
print(whereClause)
tableArray = arcpy.da.TableToNumPyArray(table, ['STAND_NUMB', 'SUPER_TYPE','STRATA', 'OS_TYPE', 'SILV_PRES', 'ACRES'], where_clause = whereClause)
df = pd.DataFrame(tableArray)
report = df.groupby(['SUPER_TYPE']).apply(lambda sub_df: sub_df.pivot_table(index=['STRATA', 'OS_TYPE', 'STAND_NUMB', 'SILV_PRES'], values=['ACRES'], margins=True) )
np.round(report,1)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Pandas Pivot Table with rounding

DanPatterson_Retired — Mon, 12 Nov 2018 14:46:41 GMT

Zachary

I have a few toolset post on the Code sharing site, I don't use pandas, preferring numpy instead, but feel free to examine coding.

Table Tools, has a crosstab/pivot table equivalent, amongst other things

/blogs/dan_patterson/2018/02/12/table-tools-a-set-of-tools-for-working-with-tabular-data

Re: Pandas Pivot Table with rounding

ZacharyHart — Tue, 13 Nov 2018 13:17:50 GMT

Dan Patterson‌ thanks! there's some real gold in there. I've bookmarked these and downloaded your table tools.

I ended up getting some help on SE and found an easy way to move the totals. Thanks again Dan!