Automate Excel With Python – John Wengler

If you lean toward greater efficiency and more tightly packed code, that’s perfectly fine. Listing 7-7 demonstrates the ‘max’ and ‘min’ arguments for aggfunc, which correspond to Excel’s 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.

>>> 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’s values can generate different cross- tabulations.

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—COUNTIF for frequency counts and SUMIF for totals—as shown in Figure 7-3.

Each column requires its own formula to generate the complete summary shown in Figure 7-4.

AUTOMATE EXCEL WITH PYTHON. Copyright © 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.

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®, Inc. 245 8th Street, San Francisco, CA 94103 phone: +1.415.863.9900 www.nostarch.com; [email protected] Publisher: William Pollock Managing Editor: Jill Franklin Production Manager: Sabrina Plomitallo-González Production Editor: Jennifer Kepler Developmental Editor: Rachel Monaghan Cover Illustrator: Rob Fiore Interior Design: Octopod Studios Technical Reviewer: Daniel Zingaro Copyeditor: Ryan E.

Holman Proofreader: Michael Fedison Indexer: BIM Creatives, LLC Library of Congress Control Number: 2026001077 For customer service inquiries, please contact [email protected]. For information on distribution, bulk sales, corporate sales, or translations: [email protected]. For permission to translate this work: [email protected]. To report counterfeit copies or piracy: [email protected]. The authorized representative in the EU for product safety and compliance is EU Compliance Partner, Pärnu mnt. 139b-14, 11317 Tallinn, Estonia, [email protected], +3375690241. No Starch Press and the No Starch Press iron logo are registered trademarks of No Starch Press, Inc.

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 “As Is” basis, without warranty. While every precaution has been taken in the preparation of this work, neither the author nor No Starch Press, Inc.

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–like career spanning journalism, community planning, risk management, and compliance.

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.

This is a short excerpt from the opening of “” by Unknown, quoted for review and introduction purposes. All rights belong to the copyright holders.

Book Information

• Unique ID: f7556cb0b285ebf7
• File Extension: .pdf
• File Size: 12,880,422 bytes (12.284 MB)
• Title: –
• Author: Unknown
• ISBN: 9781718504646, 9781718504653
• Pages: 511
• Language: English (en)

Reading & Word Statistics

• Estimated Reading Time: 484.82 minutes
• Total Words: 96,965
• Total Characters: 638,984
• Average Words per Page: 189.76
• Average Characters per Page: 1250.46

Most Frequent Words

dataframe (831), print (784), type (728), excel (699), column (694), dog (688), name (640), python (555), dfpets (550), method (546), list (535), index (505), values (459), listing (408), data (407), file (406), columns (405), dataframes (390), new (383), cat (374), pandas (359), object (339), value (329), use (316), using (309), first (294), datetime (294), import (288), bounce (280), function (276), ronnie (259), objects (248), holly (241), date (233), like (226), obj (226), one (222), example (222), string (217), chapter (214), folder (197), labels (196), workbook (188), pathname (188), sheet (185), default (183), dict (181), row (179), parameter (177), code (176), book (172), nan (172), you’ll (165), weight (164), source (161), merge (159), single (155), set (155), also (151), age (151), formatting (149), rows (148), variable (148), shown (147), str (147), table (143), left (141), module (139), city (138), cell (137), email (137), right (136), figure (136), two (135), xlsx (134), creating (131), strings (128), different (122), returns (120), create (119), series (118), read (117), today (117), script (116), count (115), time (114), label (113), want (111), chicago (110), true (109), pets (109), working (108), lists (107), display (106), crosstab (106), types (105), you’re (105), enter (105), attribute (104), files (103).

PDF Download