Python: Let’s make it tidy ver. 2

Last Updated on June 3, 2022 by shibatau

I. Is the table tidy?

I’ve got an Excel file, 結果表1 総人口および指数(平成27(2015)年=100とした場合). Here are the first few rows:

I have load the data and created a tidy data table with Pandas like this:

You can learn what a tidy data table is from 12 Tidy data.

II. Scripts

You can see the scripts here though they are not completed yet.

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

III. How to make the data clean with Google Sheets

1.Add two columns

2.Fill the names of prefectures at blanks

a.Put =if(D2=””,C2,D2) at E2

b.Copy E2 pressing CTRL+c

d.Select E column clicking E

f.Paste the formula pressing CTRL+v

3.Translate Japanese names into English

a.Put =GOOGLETRANSLATE(E2,”ja”,”en”) at F2

b.Double-click on the autofill handle in the bottom-right corner of the cell

4.Filter city2_en

Check off Yokkaichi City and Mie Prefecture

5.Insert a line chart

IV. Wide to Long

You can handle the table data and create a useful chart easily as shown in the above. However, you need a data table of Long Format if you use Pandas and Plotly Express in Python for creating charts.

There can be some kinds of long-format tables. Let me show some and how to find a tidy one among them.

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. Required fields are marked *

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