[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Extracting data from a dictionary object to csv using Python 3.4

Posted on 2015-02-04
3
Medium Priority
?
329 Views
Last Modified: 2015-02-16
Hi, I'd like some help with the syntax to extract  data from a dictionary object into csv. Below is the code so far and the required csv output. I can use the script to print the data but just want to modify it now to export the data.
Thanks

 Required csv output

import urllib.request
import json
import sqlite3
import decimal
import csv

decimal.getcontext().prec = 2

HOST = "http://datapoint.metoffice.gov.uk/public/data"
KEY = '...................................'

# Data categories
VAL = "val"             # Location-specific data
TEXT = "txt"            # Textual data
IMAGE = "image"         # Stand-alone imagery
LAYER = "layer"         # Map overlay imagery

# Resource type: forecast or observation
FORECAST = "wxfcs"
OBSERVATIONS = "wxobs"

# Field
ALL = "all"             # Can also be used instead of a location ID

DATA_TYPE = "json"      # Easier to work with than the XML alternative

# Time steps
DAILY = "daily"
THREE_HOURLY = "3hourly"
HOURLY = "hourly"

#Location ID
LOCATION = "3002"

url = (HOST + '/' + VAL + '/' + OBSERVATIONS + '/' + ALL + '/' + DATA_TYPE + '/' + LOCATION + '?' + 'res=' + HOURLY + '&key=' + KEY)


print (url)

###  THIS IS THE CALL TO GET THE MET OFFICE FILE FROM THE INTERNET
response = urllib.request.urlopen(url)
FCData = response.read()
FCDataStr = FCData.decode('utf-8')
###   END OF THE CALL TO GET MET OFFICE FILE FROM THE INTERNET

#Converts JSON data to a dictionary object
FCData_Dic = json.loads(FCDataStr)

#The following are examples of extracting data from the dictionary object.
#The JSON data is heavily nested.
#Each [] goes one level down, usually defined with {} in the JSON data.
dataDate = (FCData_Dic['SiteRep']['DV']['dataDate'])
print('dataDate =',dataDate)

#Get the Location info
LocationID = (FCData_Dic['SiteRep']['DV']['Location'].get('i',''))
print('LocationID =',LocationID)

Lat = (FCData_Dic['SiteRep']['DV']['Location'].get('lat',''))
print('Lat =',Lat)

Lon = (FCData_Dic['SiteRep']['DV']['Location'].get('lon',''))
print('Lon =',Lon)

Name = (FCData_Dic['SiteRep']['DV']['Location'].get('name',''))
print('Name =',Name)

Country = (FCData_Dic['SiteRep']['DV']['Location'].get('country',''))
print('Country =',Country)

Continent = (FCData_Dic['SiteRep']['DV']['Location'].get('continent',''))
print('Continent =',Continent)

Elevation = (FCData_Dic['SiteRep']['DV']['Location'].get('elevation',''))
print('Elevation =',Elevation)

#There are also [] in the JSON data, which are referenced with integers, 
# starting from [0]
#Here, the [0] refers to the first day's block of data defined with [].
DateDay0 = (FCData_Dic['SiteRep']['DV']['Location']['Period'][0]['value'])
print('DateDay0 =',DateDay0)

#The second [0] picks out each of the first day's forecast data, in this case the time, referenced by '$'
TimeOfFC = (FCData_Dic['SiteRep']['DV']['Location']['Period'][0]['Rep'][0]['$'])
print('TimeOfFC =',TimeOfFC)

#Ditto for the temperature.    
Temperature = decimal.Decimal((FCData_Dic['SiteRep']['DV']['Location']['Period'][0]['Rep'][0].get('T','')))
print('Temperature =',Temperature)

#Ditto for the screen relative humidity.    
ScreenRelativeHumidity = decimal.Decimal((FCData_Dic['SiteRep']['DV']['Location']['Period'][0]['Rep'][0].get('H','')))
print('ScreenRelativeHumidity =',ScreenRelativeHumidity)

#Ditto for the weather Type (a code number).
WeatherType = decimal.Decimal((FCData_Dic['SiteRep']['DV']['Location']['Period'][0]['Rep'][0].get('W','')))
print('WeatherType =',WeatherType)

#Ditto for the wind gust.
WindGust = ((FCData_Dic['SiteRep']['DV']['Location']['Period'][0]['Rep'][0].get('G','')))
print('WindGust =',WindGust)

#Ditto for the pressure.    
Pressure = decimal.Decimal((FCData_Dic['SiteRep']['DV']['Location']['Period'][0]['Rep'][0].get('P','')))
print('Pressure =',Pressure)

#Ditto for the visibility.    
Visibility = decimal.Decimal((FCData_Dic['SiteRep']['DV']['Location']['Period'][0]['Rep'][0].get('V','')))
print('Visibility =',Visibility)

#Ditto for the wind direction.    
WindDirection = ((FCData_Dic['SiteRep']['DV']['Location']['Period'][0]['Rep'][0].get('D','')))
print('WindDirection =',WindDirection)

#Ditto for the wind speed.    
WindSpeed = decimal.Decimal((FCData_Dic['SiteRep']['DV']['Location']['Period'][0]['Rep'][0].get('S','')))
print('WindSpeed =',WindSpeed)

#Ditto for the pressure tendency.    
PressureTendency = ((FCData_Dic['SiteRep']['DV']['Location']['Period'][0]['Rep'][0].get('Pt','')))
print('PressureTendency =',PressureTendency)

#Ditto for the dew point.    
DewPoint = decimal.Decimal((FCData_Dic['SiteRep']['DV']['Location']['Period'][0]['Rep'][0].get('Dp','')))
print('DewPoint =',DewPoint)

# write the csv
OUTPUT_FILE = "C:\Temp\example_data.csv"
# Open the otput file for writing in binary mode.
with open(OUTPUT_FILE, 'w', newline='') as outcsv:
    # Wrap the file object by the writer.
    writer = csv.writer(outcsv)
    # Write the header line to the output if needed.
    writer.writerow(["dataDate"
                     ,"LocationID"
                     ,"Lat","Lon"
                     ,"Name","Country"
                     ,"Continent"
                     ,"Elevation"
                     ,"DateDay0"
                     ,"TimeOfFC"
                     ,"Temperature"
                     ,"ScreenRelatveHumidity"
                     ,"WeatherType"
                     ,"WindGust"
                     ,"Pressure"
                     ,"Visibility"
                     ,"WindDirection"
                     ,"WindSpeed"
                     ,"PressureTendency"
                     ,"DewPoint"])

   
    # Loop through the dictionary and get the tuples.
    # Need to sort it somehow. Not shown here.
    for t in LocationID:
        writer.writerow([LocationID])

Open in new window

0
Comment
Question by:crompnk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 29

Accepted Solution

by:
pepr earned 2000 total points
ID: 40590952
You need to form the row (list) of values between the lines 156 and 157 (or earlier, when collecting the data). The writer.writerow(row) expect the iterable structure (usually a list). You get the LocationID and put it as a single element of the row. This is wrong. You have to get the wanted elements for the LocationID, build the list of them in the order to be present in the CSV file -- basically the same way you do it for the CSV heading (before the loop).
0
 

Author Comment

by:crompnk
ID: 40592330
Hi, thanks, this works great, but why is the data exported 4 times i.e. 4 rows of the same data:

expected-csv-output.png
 # Loop through the dictionary and get the tuples.
    # Need to sort it somehow. Not shown here.
    for t in LocationID:
        writer.writerow([dataDate
                         ,LocationID
                         ,Lat
                         ,Lon
                         ,Name
                         ,Country
                         ,Continent
                         ,Elevation
                         ,DateDay0
                         ,TimeOfFC
                         ,Temperature
                         ,ScreenRelativeHumidity
                         ,WeatherType
                         ,WindGust
                         ,Pressure
                         ,Visibility
                         ,WindDirection
                         ,WindSpeed
                         ,PressureTendency
                         ,DewPoint])

Open in new window

0
 
LVL 29

Assisted Solution

by:pepr
pepr earned 2000 total points
ID: 40592462
This is wrong again. Apparently, you get four t's when iterating through the LocationID in the loop. However, you form the row from the variable values collected before the loop. Because of that you get four times the same rows.

You have to form the row based on the t value inside the final-loop body.
0

Featured Post

What’s Wrong with Your Cloud Strategy ?

Even as many CIOs are embracing a cloud-first strategy, the reality is that moving to the cloud is a lengthy process and the end-state is likely to be a blend of multiple clouds—public and private. Learn why multicloud solutions matter in this webinar by Nimble Storage.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Strings in Python are the set of characters that, once defined, cannot be changed by any other method like replace. Even if we use the replace method it still does not modify the original string that we use, but just copies the string and then modif…
The purpose of this article is to demonstrate how we can use conditional statements using Python.
Learn the basics of modules and packages in Python. Every Python file is a module, ending in the suffix: .py: Modules are a collection of functions and variables.: Packages are a collection of modules.: Module functions and variables are accessed us…
Learn the basics of while and for loops in Python.  while loops are used for testing while, or until, a condition is met: The structure of a while loop is as follows:     while <condition>:         do something         repeate: The break statement m…
Suggested Courses

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question