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!

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Pandas Pivot Table with rounding

ZacharyHart

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<SPAN class="punctuation token">.</SPAN>pivot_table<SPAN class="punctuation token">(</SPAN>df<SPAN class="punctuation token">.</SPAN>round<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">{</SPAN><SPAN class="string token">'ACRES'</SPAN><SPAN class="punctuation token">:</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">}</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">,</SPAN>values <SPAN class="operator token">=</SPAN> <SPAN class="string token">'ACRES'</SPAN><SPAN class="punctuation token">,</SPAN> index <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">'SUPER_TYPE'</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">'STRATA'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'OS_TYPE'</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">,</SPAN> aggfunc<SPAN class="operator token">=</SPAN>np<SPAN class="punctuation token">.</SPAN>sum<SPAN class="punctuation token">,</SPAN> margins <SPAN class="operator token">=</SPAN> <SPAN class="token boolean">True</SPAN><SPAN class="punctuation token">)</SPAN>‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN>

Returns:

2nd example:

df<SPAN class="punctuation token">.</SPAN>groupby<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">[</SPAN><SPAN class="string token">'SUPER_TYPE'</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">.</SPAN>apply<SPAN class="punctuation token">(</SPAN><SPAN class="keyword token">lambda</SPAN> sub_df<SPAN class="punctuation token">:</SPAN> sub_df<SPAN class="punctuation token">.</SPAN>pivot_table<SPAN class="punctuation token">(</SPAN>index<SPAN class="operator token">=</SPAN><SPAN class="punctuation token">[</SPAN><SPAN class="string token">'STRATA'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'OS_TYPE'</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">,</SPAN> values<SPAN class="operator token">=</SPAN><SPAN class="punctuation token">[</SPAN><SPAN class="string token">'ACRES'</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">,</SPAN> margins<SPAN class="operator token">=</SPAN><SPAN class="token boolean">True</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="punctuation token">)</SPAN>‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN>

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

Vind meer berichten getagd met

Accepted answers

DanPatterson_Retired

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!!

All comments

ZacharyHart

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!

DanPatterson_Retired

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

ZacharyHart

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 <SPAN class="operator token">=</SPAN> <SPAN class="string token">"\\\\fileServer\\MAP_PROJECTS\\LV_WEB\\SDE_CONNECTIONS\\LV_NEXUS.sde\\LV_NEXUS.DATAOWNER.NORTHEAST\\LV_NEXUS.DATAOWNER.NE_HARVEST_OPS"</SPAN>
HUID <SPAN class="operator token">=</SPAN> <SPAN class="string token">"669-NMTC-139"</SPAN>
whereClause <SPAN class="operator token">=</SPAN> <SPAN class="string token">""" "LV_HARVEST_UNIT_ID" = '{0}' """</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>HUID<SPAN class="punctuation token">)</SPAN>
<SPAN class="keyword token">print</SPAN><SPAN class="punctuation token">(</SPAN>whereClause<SPAN class="punctuation token">)</SPAN>
tableArray <SPAN class="operator token">=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>TableToNumPyArray<SPAN class="punctuation token">(</SPAN>table<SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">'STAND_NUMB'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'SUPER_TYPE'</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">'STRATA'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'OS_TYPE'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'SILV_PRES'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'ACRES'</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">,</SPAN> where_clause <SPAN class="operator token">=</SPAN> whereClause<SPAN class="punctuation token">)</SPAN>
df <SPAN class="operator token">=</SPAN> pd<SPAN class="punctuation token">.</SPAN>DataFrame<SPAN class="punctuation token">(</SPAN>tableArray<SPAN class="punctuation token">)</SPAN>
report <SPAN class="operator token">=</SPAN> df<SPAN class="punctuation token">.</SPAN>groupby<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">[</SPAN><SPAN class="string token">'SUPER_TYPE'</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">.</SPAN>apply<SPAN class="punctuation token">(</SPAN><SPAN class="keyword token">lambda</SPAN> sub_df<SPAN class="punctuation token">:</SPAN> sub_df<SPAN class="punctuation token">.</SPAN>pivot_table<SPAN class="punctuation token">(</SPAN>index<SPAN class="operator token">=</SPAN><SPAN class="punctuation token">[</SPAN><SPAN class="string token">'STRATA'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'OS_TYPE'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'STAND_NUMB'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'SILV_PRES'</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">,</SPAN> values<SPAN class="operator token">=</SPAN><SPAN class="punctuation token">[</SPAN><SPAN class="string token">'ACRES'</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">,</SPAN> margins<SPAN class="operator token">=</SPAN><SPAN class="token boolean">True</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="punctuation token">)</SPAN>
np<SPAN class="punctuation token">.</SPAN>round<SPAN class="punctuation token">(</SPAN>report<SPAN class="punctuation token">,</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">)</SPAN>‍‍‍‍‍‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

DanPatterson_Retired

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!!

Doe mee aan het gesprek

Aangemelde leden kunnen berichten plaatsen, updates volgen en meer. Nieuw hier? Registreer een gratis account.

Snelle Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.