Open an Excel file in Python and color the map of Japan

We will deal with these two here. It runs on Google Colaboratory.

Color the map of Japan

Installation of library japanmap

!pip install japanmap

Import required libraries

from japanmap import pref_names,pref_code,picture

Name of prefecture

print(pref_names)

['_','Hokkaido','Aomori Prefecture','Iwate Prefecture','Miyagi Prefecture','Akita Prefecture','Yamaguchi Prefecture','Fukui Prefecture','Ibaraki Prefecture','Tochigi Prefecture',' Gunma prefecture',' Saitama prefecture',' Chiba prefecture',' Tokyo Metropolitan area',' Kanagawa prefecture',' Niigata prefecture',' Tottori prefecture',' Ishikawa prefecture',' Fukui prefecture',' Yamanashi prefecture',' Nagano prefecture','Gifu prefecture','Shizuoka prefecture','Aichi prefecture','Mie prefecture','Shiga prefecture','Kyoto prefecture','Osaka prefecture','Hyogo prefecture','Nara prefecture',' Wakayama prefecture','Tottori prefecture','Shimane prefecture','Okayama prefecture','Hiroshima prefecture','Yamaguchi prefecture','Tokushima prefecture','Kagawa prefecture','Ehime prefecture','Kochi prefecture',' Fukuoka prefecture',' Saga prefecture',' Nagasaki prefecture',' Kumamoto prefecture',' Oita prefecture',' Miyazaki prefecture',' Kagoshima prefecture',' Okinawa prefecture']

Prefecture code

pref_code('Tokyo')
13

Blank map drawing

%matplotlib inline
import matplotlib.pyplot as plt
from pylab import rcParams
 
rcParams['figure.figsize'] = 6, 6
plt.imshow(picture())

output_4_1.png

Coloring of the specified prefecture

plt.imshow(picture({'Tottori':'red','Saga':(0,255,0)}))

output_5_1.png

Open excel file

As the subject, I used the Excel file of the statistical data by prefecture published on the Teikoku Shoin website.

download

urllib is a library for download, but if you use it on Google Colab, you will upload it on Google Colab. In Google Colab, the uploaded file disappears at the end of the session, so upload it again at that time.

#Download by urllib
import urllib.request 
url = "URL where the excel file is located"
urllib.request.urlretrieve(url, 'Teikoku-Shoin-Japan.xlsx') 

Open excel

As far as I know, there are two ways to open an Excel file in Python.

import pandas as pd
excel = pd.read_excel('Teikoku-Shoin-Japan.xlsx') #This method may not work
import pandas as pd
excel = pd.ExcelFile('Teikoku-Shoin-Japan.xlsx') #I feel that this one works better

Excel, unlike csv etc., consists of multiple sheets.

for s in enumerate(excel.sheet_names):
    print(s) #List sheet names and numbers

(0,'Area \ u3000 [2017]') (1,'Population \ u3000 [2018]') (2,'Population density \ u3000 [2018]') (3,'Population growth rate \ u3000 [2010-2015]') (4,'Old age (65 years old and over) population rate \ u3000 [2018]') (5,'Population ratio by industry (2015)') (6,'Average life expectancy (female) \ u3000 [2015]') (7,'Average life expectancy (male) \ u3000 [2015]') (8,'Cultivated land area [2017]') (9,'Cultivated land ratio \ u3000 [2017]') (10,'Forest rate / planted forest rate [2017]') (11,'Wheat production \ u3000 [2018]') (12,'Rice production \ u3000 [2017]') (13,'Sweet potato production \ u3000 [2017]') (14,'Potato production \ u3000 [2017]') (15,'Soybean production \ u3000 [2017]') (16,'Peanut production \ u3000 [2017]') (17,'Cabbage production \ u3000 [2017]') (18,'Cucumber production \ u3000 [2017]') (19,'Watermelon production \ u3000 [2017]') (20,'Daikon production \ u3000 [2017]') (21,'Production of rapeseed [2017]') (22,'Carrot production \ u3000 [2017]') (23,'Leek production \ u3000 [2017]') (24,'Chinese cabbage production \ u3000 [2017]') (25,'Pepper production \ u3000 [2017]') (26,'Spinach production \ u3000 [2017]') (27,'Production of tea (rough tea) \ u3000 [2017]') (28,'Strawberry production \ u3000 [2017]') (29,'Plum production \ u3000 [2017]') (30,'Production of melon \ u3000 [2017]') (31,'Persimmon production \ u3000 [2017]') (32,'Cherry production \ u3000 [2017]') (33,'Mandarin orange production \ u3000 [2017]') (34,'Momo production \ u3000 [2017]') (35,'Apple production \ u3000 [2017]') (36,'Chrysanthemum shipments [2017]') (37,'Pansy shipments [2017]') (38,'Shipping volume of orchids (cut flowers) \ u3000 [2017]') (39,'Number of broiler birds \ u3000 [2018]') (40,'Number of pigs raised \ u3000 [2018]') (41,'Number of beef cattle raised \ u3000 [2018]') (42,'Number of dairy cows \ u3000 [2018]') (43,'Production of eyebrows [2017]') (44,'Agricultural output \ u3000 [2017]') (45,'Rice output \ u3000 [2017]') (46,'Wheat output \ u3000 [2017]') (47,'Bean output \ u3000 [2017]') (48,'Potato output \ u3000 [2017]') (49,'Vegetable output \ u3000 [2017]') (50,'Fruit output \ u3000 [2017]') (51,'Flower output \ u3000 [2017]') (52,'Craft crop output \ u3000 [2017]') (53,'Livestock output \ u3000 [2017]') (54,'Fishery production (fishery / aquaculture) [2016]') (55,'Squid catch \ u3000 [2016]') (56,'Katsuo catch \ u3000 [2016]') (57,'Pacific saury catch \ u3000 [2016]') (58,'Sardine catch \ u3000 [2016]') (59,'Tuna catch \ u3000 [2016]') (60,'Oyster farming \ u3000 [2016]') (61,'Scallop farming \ u3000 [2016]') (62,'Wakame seaweed farming \ u3000 [2016]') (63,'Production of material (raw wood) \ u3000 [2016]') (64,'Industrial shipment value \ u3000 [2016]') (65,'Production of fruit wine [2016]') (66,'Production of Yakichu [2016]') (67,'Production of sake (including muddy sake) \ u3000 [2016]') (68,'Beer production \ u3000 [2016]') (69,'Production of petroleum and coal products [2016]') (70,'Semiconductor production \ u3000 [2016]') (71,'Production of industrial robots \ u3000 [2016]') (72,'Production of automobiles (including motorcycles) \ u3000 [2016]') (73,'Fiber production \ u3000 [2016]') (74,'Steel production \ u3000 [2016]') (75,'Production of integrated circuits \ u3000 [2016]') (76,'Prefectural income per capita \ u3000 [2015]') (77,'Urban park area per person \ u3000 [end of March 2017]') (78,'Electricity demand per person \ u3000 [2015]') (79,'Garbage emissions per person per day \ u3000 [2016]') (80,'Sewerage penetration rate \ u3000 [2018]') (81,'Number of passenger cars per household \ u3000 [2018]') (82,'Retail annual sales \ u3000 2015') (83,'Number of pollution complaints \ u3000 [2017]')

I will select a suitable sheet

sheet_index = 2
print(excel.sheet_names[sheet_index])
sheet = excel.parse(excel.sheet_names[sheet_index], header=None) #Make the contents of the second sheet into pandas format

Population density [2018]

sheet.head() #Check the shape of the data
0 1 2
0 Source: 2018 Basic Resident Register Population / Household Number Table, 2017 Area by Prefecture, City, Town, and Village NaN NaN
1 Prefecture code Prefecture name (people / km2)
2 1 Hokkaido 64
3 2 Aomori 136
4 3 Iwate 83

Data processing

sheet.iloc[2:49, [0, 2]] #Extract the necessary parts
0 2
2 1 64
3 2 136
4 3 83
5 4 317
... ... ...
46 45 144
47 46 180
48 47 645
sheet.iloc[2:49, [0, 2]].values #Convert pandas format to numpy format
array([[1, 64],
       [2, 136],
       [3, 83],
       [4, 317],
       [5, 87],
       ...,
       [46, 180],
       [47, 645]], dtype=object)

Color the Japanese map with Excel data

Data processing

values = sheet.iloc[2:49, [0, 2]].values

Specifying how to paint the color

The code below

Outputs the color code of.

import numpy as np
def color_scale(value, max_value):
    try:
        v = value / max_value
        if v >= 1.0:
            return (255, 0, 0)
        elif v <= 0:
            return (0, 0, 255)
        elif v > 0.5:
            red = 1
            green = int(255 * (2 - 2 * v))
            blue = 0
            return (red, green, blue)
        else:
            red = int(255 * 2 * v)
            green = int(255 * 2 * v)
            blue = int(255 * (1 - 2 * v))
            return (red, green, blue)
    except:
        return (255, 255, 255)

The usage example looks like this.

for v in [0.0, 0.2, 0.5, 0.7, 1.0, "string"]:
    print(v, color_scale(v, 1))
0.0 (0, 0, 255)
0.2 (102, 102, 153)
0.5 (255, 255, 0)
0.7 (1, 153, 0)
1.0 (255, 0, 0)
string (255, 255, 255)

Find the maximum value

max_value = np.max([a for a in values[:, 1] if not isinstance(a, str)])

Decide the color for each prefecture

data ={}
for code, value in zip(values[:, 0], values[:, 1]):
    data[code] = color_scale(value, max_value)

Color the map of Japan

%matplotlib inline
import matplotlib.pyplot as plt
from pylab import rcParams
 
print(excel.sheet_names[sheet_index])
plt.imshow(picture(data))

Population density [2018]

output_21_2.png

Summary

It looks like this when painted with different data. Where there is no data, it is completely white.

sheet_index = 60
print(excel.sheet_names[sheet_index])
sheet = excel.parse(excel.sheet_names[sheet_index], header=None)
values = sheet.iloc[2:49, [0, 2]].values
max_value = np.max([a for a in values[:, 1] if not isinstance(a, str)])

data ={}
for code, value in zip(values[:, 0], values[:, 1]):
    data[code] = color_scale(value, max_value)

plt.imshow(picture(data))

Oyster farming [2016]

output_22_2.png

Recommended Posts

Open an Excel file in Python and color the map of Japan
Get the formula in an excel file as a string in Python
Comparing the basic grammar of Python and Go in an easy-to-understand manner
Change the saturation and brightness of color specifications like # ff000 in python 2.5
Compare the speed of Python append and map
I want to color a part of an Excel string in Python
I tried to open the latest data of the Excel file managed by date in the folder with Python
The simplest Python memo in Japan (classes and objects)
The story of FileNotFound in Python open () mode ='w'
[Golang] Specify an array in the value of map
Let's use the open data of "Mamebus" in Python
Download the file in Python
The result of making a map album of Italy honeymoon in Python and sharing it
[Python] Change the text color and background color of a specific keyword in print output
Output the specified table of Oracle database in Python to Excel for each file
[Python] Open the csv file in the folder specified by pandas
Get the MIME type in Python and determine the file format
Manipulate the clipboard in Python and paste the table into Excel
[Note] Import of a file in the parent directory in Python
Read the VTK file and display the color map with jupyter.
[Tips] Problems and solutions in the development of python + kivy
Google search for the last line of the file in Python
The story of Python and the story of NaN
Save the binary file in Python
Create an Excel file with Python3
[Python] Adjusted the color map standard
The story of the "hole" in the file
Count the number of Thai and Arabic characters well in Python
How to know the internal structure of an object in Python
I got an AttributeError when mocking the open method in python
File open function in Python3 (difference between open and codecs.open and speed comparison)
Get the title and delivery date of Yahoo! News in Python
Trial of writing the configuration file in Python instead of .ini etc.
Find the white Christmas rate by prefecture with Python and map it to a map of Japan
Specify the color in the matplotlib 2D map
Check the behavior of destructor in Python
The story of an error in PyOCR
The story of making Python an exe
Check the existence of the file with python
Quickly create an excel file with Python #python
Write O_SYNC file in C and Python
The result of installing python in Anaconda
Read the file line by line in Python
Read the file line by line in Python
The basics of running NoxPlayer in Python
In search of the fastest FizzBuzz in Python
[Python] Get the character code of the file
[Python] Read the specified line in the file
Project Euler # 1 "Multiples of 3 and 5" in Python
Python open and io.open are the same
[Python3] Understand the basics of file operations
Test & Debug Tips: Create a file of the specified size in Python
The file name was bad in Python and I was addicted to import
I made a program to check the size of a file in Python
An example of the answer to the reference question of the study session. In python.
Various ways to read the last line of a csv file in Python
Create a Python image in Django without a dummy image file and test the image upload
Output the number of CPU cores in Python
[Python] Sort the list of pathlib.Path in natural sort
You will be an engineer in 100 days --Day 29 --Python --Basics of the Python language 5
File DL, byte value and delete in Python3