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.

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.