{"id":252706,"date":"2026-07-13T02:17:56","date_gmt":"2026-07-12T23:17:56","guid":{"rendered":"https:\/\/1kitap1.com\/en\/automate-excel-with-python-john-wengler\/"},"modified":"2026-07-13T02:17:56","modified_gmt":"2026-07-12T23:17:56","slug":"automate-excel-with-python-john-wengler","status":"publish","type":"post","link":"https:\/\/1kitap1.com\/en\/automate-excel-with-python-john-wengler\/","title":{"rendered":"Automate Excel With Python – John Wengler"},"content":{"rendered":"
\"<\/figure>\n

If you lean toward greater efficiency and more tightly packed code, that\u2019s perfectly fine. Listing 7-7 demonstrates the ‘max’ and ‘min’ arguments for aggfunc, which correspond to Excel\u2019s MAX and MIN functions. >>> dfMax = pd.crosstab( … index = dfPets[‘Type’], … columns = dfPets[‘City’], … aggfunc = ‘max’, … values = dfPets[‘Weight’]) >>> print(dfMax) City Boston Chicago Type Cat 11.0 NaN Dog 10.0 8.0 Frog NaN 6.0 >>> dfMin = pd.crosstab( … index = dfPets[‘Type’], … columns = dfPets[‘City’], … aggfunc = ‘min’, … values = dfPets[‘Weight’]) >>> print(dfMin) City Boston Chicago Type Cat 9.0 NaN Dog 10.0 7.0 Frog NaN 6.0 Listing 7-7: Finding maximum and minimum values with the aggfunc arguments ‘max’ and ‘min’ Likewise, the ‘mean’ and ‘median’ arguments replicate the Excel formulas AVERAGE and MEDIAN, as Listing 7-8 demonstrates.<\/p>\n

>>> dfMean = pd.crosstab( … index = dfPets[‘Type’], … columns = dfPets[‘City’], … aggfunc = ‘mean’, … values = dfPets[‘Weight’]) >>> print(dfMean) City Boston Chicago Type Cat 10.0 NaN Dog 10.0 7.5 Frog NaN 6.0 >>> dfMedian = pd.crosstab( … index = dfPets[‘Type’], … columns = dfPets[‘City’], … aggfunc = ‘median’, … values = dfPets[‘Weight’]) >>> print(dfMedian) City Boston Chicago Type Cat 10.0 NaN Dog 10.0 7.5 Frog NaN 6.0 Listing 7-8: Calculating statistics with the ‘mean’ and ‘median’ aggfunc arguments Listings 7-6 through 7-8 are just a few common examples of how changing the aggfunc parameter\u2019s values can generate different cross- tabulations.<\/p>\n

I hope they inspire you to search other resources for more applications. Using Multiple Aggregate Functions Spreadsheets simplify tasks such as creating a multimetric table like the one shown in Figure 7-3, where Table B summarizes a count and a summation side by side from the source data in Table A. Figure 7-3: A simple Excel example that converts source data into a multimetric table In Excel, you need separate formulas for each metric\u2014COUNTIF for frequency counts and SUMIF for totals\u2014as shown in Figure 7-3.<\/p>\n

Each column requires its own formula to generate the complete summary shown in Figure 7-4.<\/p>\n

\n

AUTOMATE EXCEL WITH PYTHON. Copyright \u00a9 2026 by John Wengler. All rights reserved. No part of this work may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording, or by any information storage or retrieval system, without the prior written permission of the copyright owner and the publisher.<\/p>\n

First printing 30 29 28 27 26 1 2 3 4 5 ISBN-13: 978-1-7185-0464-6 (print) ISBN-13: 978-1-7185-0465-3 (ebook) Published by No Starch Press\u00ae, Inc. 245 8th Street, San Francisco, CA 94103 phone: +1.415.863.9900 www.nostarch.com; info@nostarch.com Publisher: William Pollock Managing Editor: Jill Franklin Production Manager: Sabrina Plomitallo-Gonz\u00e1lez Production Editor: Jennifer Kepler Developmental Editor: Rachel Monaghan Cover Illustrator: Rob Fiore Interior Design: Octopod Studios Technical Reviewer: Daniel Zingaro Copyeditor: Ryan E.<\/p>\n

Holman Proofreader: Michael Fedison Indexer: BIM Creatives, LLC Library of Congress Control Number: 2026001077 For customer service inquiries, please contact info@nostarch.com. For information on distribution, bulk sales, corporate sales, or translations: sales@nostarch.com. For permission to translate this work: rights@nostarch.com. To report counterfeit copies or piracy: counterfeit@nostarch.com. The authorized representative in the EU for product safety and compliance is EU Compliance Partner, P\u00e4rnu mnt. 139b-14, 11317 Tallinn, Estonia, hello@eucompliancepartner.com, +3375690241. No Starch Press and the No Starch Press iron logo are registered trademarks of No Starch Press, Inc.<\/p>\n

Other product and company names mentioned herein may be the trademarks of their respective owners. Rather than use a trademark symbol with every occurrence of a trademarked name, we are using the names only in an editorial fashion and to the benefit of the trademark owner, with no intention of infringement of the trademark. The information in this book is distributed on an \u201cAs Is\u201d basis, without warranty. While every precaution has been taken in the preparation of this work, neither the author nor No Starch Press, Inc.<\/p>\n

shall have any liability to any person or entity with respect to any loss or damage caused or alleged to be caused directly or indirectly by the information contained in it. 1kitap1.com\/en For Dragana, naturally. 1kitap1.com\/en About the Author John Wengler enjoys a Mark Twain\u2013like career spanning journalism, community planning, risk management, and compliance.<\/p>\n

He taught himself Python to upgrade a spreadsheet process to help an employer save millions on a third-party system. John wrote the 2001 book Managing Energy Risk, as well as dozens of industry articles on corporate governance. A distinguished public speaker, he has taught courses at the Illinois Institute of Technology and Tulane University.<\/p>\n<\/blockquote>\n

This is a short excerpt from the opening of “” by Unknown, quoted for review and introduction purposes. All rights belong to the copyright holders.<\/em><\/p>\n

\n
\n

Table of Contents<\/p>\nToggle<\/span><\/path><\/svg><\/svg><\/span><\/span><\/span><\/a><\/span><\/div>\n