Lecture: Using pivot tables in Excel and Pandas, ver. 2

Last Updated on September 19, 2021 by shibatau

I.What do you learn?

You will learn how to create and use pivot tables in Excel and Python.

At first look, pivot tables look complicating and difficult to understand but in this post you will learn the syntax of “pivot_table” is very simple and how they work in Pandas.

When you create a pivot table, it is important to distinguish the data types of the columns, especially strings or numbers. It isn’t worth a while to create a pivot table if there are few columns of strings.

II.Data

The sample data I used is here:

https://pastebin.com/cSZ8pYWh

III.Using pivot tables in Excel online

1.The data types of the columns

The three column are strings.

2.Creating pivot tables

The columns of strings are automatically dragged to ROWS . You should add fields and select functions for each of the fields if you change the default sum function. 

3.Adding calculated field

Calculated field in Excel Online/Excel for Web

III.Using pivot_table in Pandas

All the scripts are here:

https://colab.research.google.com/drive/1YtP2Gxr99FbiTwBtp8wr_SOpTp4vKY75?usp=sharing

You can define a different aggfunc for each column like Excel.

You can add a column based on another column in a pivot table but the column added is not in the pivot table as in Excel.

To be continued.

About shibatau

I was born and grown up in Kyoto. I studied western philosophy at the University and specialized in analytic philosophy, especially Ludwig Wittgenstein at the postgraduate school. I'm interested in new technology, especially machine learning and have been learning R language for two years and began to learn Python last summer. Listening toParamore, Sia, Amazarashi and MIyuki Nakajima. Favorite movies I've recently seen: "FREEHELD". Favorite actors and actresses: Anthony Hopkins, Denzel Washington, Ellen Page, Meryl Streep, Mia Wasikowska and Robert DeNiro. Favorite books: Fyodor Mikhailovich Dostoyevsky, "The Karamazov Brothers", Shinran, "Lamentations of Divergences". Favorite phrase: Salvation by Faith. Twitter: @shibatau

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.