Day 2

Work with CSV files & JSON in Python




Olly

Book

Contents:

1. Basic python

  • Statement Assignment
  • print()
  • list/list slice
  • for loop + list
  • str()
  • dict

2. Third-party modules

Check Modules Exist

You don't need to install csv and json, because they are in standard library.

Without Error Massage!!

import csv
import json

Let's Start!

CSV (Comma-separated values)

  • Don’t have types for their values—everything is a string
  • Don’t have settings for font size or color
  • Don’t have multiple worksheets
  • Can’t specify cell widths and heights
  • Can’t have merged cells
  • Can’t have images or charts embedded in them

Create csv file Manually

Save as .csv file or download csv file here

Reader Objects

csv.reader(open(csvfile))
In [3]:
import csv
exampleFile = open('Docs/D2_01.csv')
exampleReader = csv.reader(exampleFile)
exampleData = list(exampleReader)
exampleData
Out[3]:
[['DateTime', 'Name', 'Age', 'Sex'],
 ['2018/1/1', 'Olly', '13', 'F'],
 ['2018/1/2', 'Ollier', '14', 'F'],
 ['2018/1/3', 'Oil', '12', 'M'],
 ['2018/1/4', 'Oba', '16', 'F'],
 ['2018/1/5', 'Oma', '17', 'M'],
 ['2018/1/6', 'Ohya', '18', 'F']]

Access the value at a particular row and column with the expression:

exampleData[row][col]
In [5]:
exampleData[1][1]
Out[5]:
'Olly'
In [5]:
exampleData[2][1]
Out[5]:
'Ollier'
In [9]:
exampleData[6][2]
Out[9]:
'18'

Reading Data from Reader Objects in a for Loop

In [13]:
import csv
exampleFile = open('Docs/D2_01.csv')
exampleReader = csv.reader(exampleFile)
for row in exampleReader:
        print(row)

['DateTime', 'Name', 'Age', 'Sex']
['2018/1/1', 'Olly', '13', 'F']
['2018/1/2', 'Ollier', '14', 'F']
['2018/1/3', 'Oil', '12', 'M']
['2018/1/4', 'Oba', '16', 'F']
['2018/1/5', 'Oma', '17', 'M']
['2018/1/6', 'Ohya', '18', 'F']

Get the current row number expression：

exampleReader.line_num
In [11]:
import csv
exampleFile = open('Docs/D2_01.csv')
exampleReader = csv.reader(exampleFile)
for row in exampleReader:
        print('Row #' + str(exampleReader.line_num) + ' ' + str(row))

Row #1 ['DateTime', 'Name', 'Age', 'Sex']
Row #2 ['2018/1/1', 'Olly', '13', 'F']
Row #3 ['2018/1/2', 'Ollier', '14', 'F']
Row #4 ['2018/1/3', 'Oil', '12', 'M']
Row #5 ['2018/1/4', 'Oba', '16', 'F']
Row #6 ['2018/1/5', 'Oma', '17', 'M']
Row #7 ['2018/1/6', 'Ohya', '18', 'F']

Writer Objects

csv.writer(open(csvfile, 'w', newline='' ))
.writerow(list)
In [18]:
import csv
outputFile = open('Docs/D2_02.csv', 'w', newline='')
outputWriter = csv.writer(outputFile)
outputWriter.writerow(['dog', 'cat', 'bird'])
outputWriter.writerow(['Hi, dog!', 'cat', 'bird'])
outputWriter.writerow([1, 2, 3.14])

outputFile.close()
  • Automatically escapes the comma with csv module

Notice!

If you forget to set the newline argument on Windows system...

Your row wil be double-spaced

In [21]:
import csv
outputFile = open('Docs/D2_03.csv', 'w')
outputWriter = csv.writer(outputFile)
outputWriter.writerow(['dog', 'cat', 'bird'])
outputWriter.writerow(['Hi, dog!', 'cat', 'bird'])
outputWriter.writerow([1, 2, 3.14])

outputFile.close()

The delimiter Keyword Arguments

  • Changes the character between cells to a tab:
delimiter='\t'
In [36]:
import csv
outputFile = open('Docs/D2_04.tsv', 'w', newline='')
outputWriter = csv.writer(outputFile, delimiter='\t')
outputWriter.writerow(['dog', 'cat', 'bird'])
outputWriter.writerow(['Hi, dog!', 'cat', 'bird'])
outputWriter.writerow([1, 2, 3.14])

outputFile.close()

JSON (JavaScript Object Notation)

  • A popular way to format data as a single human-readable string
  • Many websites make their data available in JSON format, and offer APIs (Application Programming Interfaces ) for programs to use

Reading JSON

To translate a string containing JSON data into a Python value :

json.loads()
In [23]:
JasonString1 = '{"name": "Olly", "Female": true, "Height": 154, "Weight": null}'
type(JasonString1)
Out[23]:
str
In [24]:
import json
JasonAsPyValue =json.loads(JasonString1)
JasonAsPyValue
Out[24]:
{'Female': True, 'Height': 154, 'Weight': None, 'name': 'Olly'}
In [25]:
type(JasonAsPyValue)
Out[25]:
dict

Writing JSON

To translate a Python value into a string of JSON-formatted data :

json.dumps()
In [31]:
PyDict = {'Female': False, 'Height': None, 'Weight': 50, 'name': 'Ohbo'}
type(PyDict)
Out[31]:
dict
In [32]:
import json
PyDictAsJason = json.dumps(PyDict)
PyDictAsJason
Out[32]:
'{"Height": null, "Female": false, "name": "Ohbo", "Weight": 50}'
In [60]:
type(PyDictAsJason)
Out[60]:
str

Project : Fetching Current Weather Data

Step 0 : Sign up to acces specific Key for WeatherAPI

Step 1: Download the JSON Data

Step 2: Load JSON Data and Print Weather

Step 3 : Keep Weather Informantion as csv file

Step 0 Sign up to acces specific Key for WeatherAPI

Enter OpenWeatherMap hit Sign Up

Create personal account

Find your personal API key after login

There is the document for Howt to Start

Fill the location you want

Fill your API Key into APPID

location = 'Taipei'
APPID = '848b6cd4XXXXXXXXXXXXXXX'

Step 1: Download the JSON Data

In [164]:
import json, requests
url = 'http://api.openweathermap.org/data/2.5/weather?q=%s&APPID=%s' %(location, APPID)
response = requests.get(url)
response.raise_for_status()

Step 2 : Load JSON Data and Print Weather

In [165]:
weatherData = json.loads(response.text)
In [166]:
weatherData.keys()
Out[166]:
dict_keys(['cod', 'id', 'visibility', 'base', 'weather', 'clouds', 'wind', 'dt', 'coord', 'sys', 'name', 'main'])
In [167]:
weatherData
Out[167]:
{'cod': 200, 'id': 1668341, 'visibility': 10000, 'base': 'stations', 'weather': [{'main': 'Clouds', 'icon': '04n', 'description': 'broken clouds', 'id': 803}], 'clouds': {'all': 75}, 'wind': {'speed': 3.1, 'deg': 80}, 'dt': 1509365100, 'coord': {'lon': 121.53, 'lat': 25.05}, 'sys': {'message': 0.0085, 'type': 1, 'country': 'TW', 'id': 7479, 'sunrise': 1509314416, 'sunset': 1509354862}, 'name': 'Taipei', 'main': {'temp_min': 292.15, 'pressure': 1023, 'humidity': 68, 'temp': 292.6, 'temp_max': 293.15}}

Get information of 'City', 'Datetime' and 'Weather'

  • name : City name
  • dt : Time of data calculation, unix, UTC
  • weather.main : Group of weather parameters (Rain, Snow, Extreme etc.)
  • weather.description : Weather condition within the group
In [168]:
city = weatherData['name']
dt = weatherData['dt']
weather = weatherData['weather'][0]
In [169]:
city
Out[169]:
'Taipei'
In [170]:
import datetime
dtime = datetime.datetime.fromtimestamp(dt).strftime('%Y-%m-%d %H:%M:%S')
dtime
Out[170]:
'2017-10-30 20:05:00'
In [171]:
w_main = weather['main']
w_main
Out[171]:
'Clouds'
In [172]:
w_desc = weather['description']
w_desc
Out[172]:
'broken clouds'
In [173]:
print('~~ Current Wheather ~~')
print('★ Location :\t', city)
print('★ Date Time :\t', dtime)
print('★ Weather :\t', w_main, '-', w_desc)

~~ Current Wheather ~~
★ Location :	 Taipei
★ Date Time :	 2017-10-30 20:05:00
★ Weather :	 Clouds - broken clouds

Step 3 : Keep Weather Informantion as csv file

In [174]:
import csv, json

outputFile = open('Docs/D2_05.csv', 'w', newline='')
outputWriter = csv.writer(outputFile)
outputWriter.writerow([city, dtime, w_main, w_desc])

outputFile.close()

Exercise

Work with CSV files & JSON in Python

  1. Import csv & json & requests
  2. Get 5/3hrs days weahter forecast result for Taipei from OpenWeatherMap
    • API call:
      http://api.openweathermap.org/data/2.5/forecast?q={city}&units=metric&APPID={APPID}
    • Parameters:
      • APPID : API Key
      • q : city name
      • units=metric : Temperature (Celsius)
  3. Write data as csv file

Answer

In [199]:
import csv, json, requests, datetime
def Forecast30hrs(location, csvfile, APPID):
    # Use openweathermap API get json sting
    url = 'http://api.openweathermap.org/data/2.5/forecast?q=%s&units=metric&APPID=%s' %(location, APPID)
    response = requests.get(url)
    response.raise_for_status()
    # load json as dict()
    weatherData = json.loads(response.text)
    # Get weather information by the key of dict() 
    w = weatherData['list']
    # Open csv file
    path = 'Docs/'+ csvfile
    outputFile = open(path, 'w', newline='')
    outputWriter = csv.writer(outputFile)
    # Write column names
    outputWriter.writerow(['Forecast datetime', 'Temp. Range (Celsius)', \
                           'Group of weather parameters', 'Weather condition within the group'])
    for i in range(10):
    # Get Forecast datetime, min/max daily temperature, main, description
        dtime = datetime.datetime.fromtimestamp(w[i]['dt']).strftime('%Y-%m-%d %H:%M:%S')
        temp_min = w[i]['main']['temp_min']
        temp_max = w[i]['main']['temp_max']
        main = w[i]['weather'][0]['main']
        desc = w[i]['weather'][0]['description']
        # Write rows into csv file
        outputWriter.writerow([dtime, str(temp_min)+ ' - ' +str(temp_max), main, desc])
    outputFile.close()

Fill your API Key into APPID

APPID = '848b6cd4XXXXXXXXXXXXXXX'
In [201]:
Forecast30hrs('Taipei', 'D2_Exercise.csv', APPID)

def Day2End() :

     return 'Thank U ❤'