CODEWITHSUNDEEP
pythonjson
venkat
venkat

Reputation: 1263

How to Convert the text into Json Format using Python

I have extracted the data using python Selenium from the site below.

https://portfoliomanager.energystar.gov/pm/targetFinder;jsessionid=F6FC40FBDE075BDA3834643F9BD65E37?execution=e1s2

Please have a look at the table "Metrics Comparison for Your Design and/or Target".

I have extracted the table as a text format.

Here is the sample output of the text below

Metric Design Project Design Target Median Property*
ENERGY STAR score (1-100) Not Available 75 50
Source EUI (kBtu/ft²) 3.1 Not Available 127.9
Site EUI (kBtu/ft²) 1.0 Not Available 40.7
Source Energy Use (kBtu) 314.0 Not Available 12,793.0
Site Energy Use (kBtu) 100.0 Not Available 4,074.2
Energy Cost ($) 2,000.00 Not Available 81,484.00
Total GHG Emissions (Metric Tons CO2e) 0.0 Not Available 0.5

I tried to convert the text into json,

import csv
import json

with open('file.txt', 'rb') as csvfile:
    filereader = csv.reader(csvfile, delimiter=' ')
    i = 0
    header = []
    out_data = []
    for row in filereader:
        row = [elem for elem in row if elem]
        if i == 0:
            i += 1
            header = row
        else:
            row[0:4] = [row[0]+" "+row[1]+" "+row[2]+" "+row[3]]
            _dict = {}
            for elem, header_elem in zip(row, header):
                _dict[header_elem] = elem
            out_data.append(_dict)

print json.dumps(out_data)

The JSON format output which i got was like 

[{"Project": "75", "Metric": "ENERGY STAR score (1-100)", "Design": "50"}]

The JSON format output should be in the form of 

[{"Design Project": "Not Available", "Design Target": "75", "Metric": "ENERGY STAR score (1-100)", "Median Property*": "50"}]

Upvotes: 1

Views: 1529

Answers (1)

akonieczny
akonieczny

Reputation: 76

You forgotten create data and header for other json keys (like Design Project, Design Target etc)

This is correct version:

import csv
import json

with open('test.txt', 'r') as csvfile: # Opens file
    filereader = csv.reader(csvfile, delimiter=' ')
    i = 0
    header = []
    out_data = []
    for row in filereader:
        row = [elem for elem in row if elem]
        if i == 0:
            i += 2
            row[1:3] = [row[1]+" "+row[2]]  # Design Project key
            row[2:4] = [row[2]+" "+row[3]]  # Design Target key
            row[3:5] = [row[3]+" "+row[4]]  # Median Property*
            header = row
        else:
            row[0:4] = [row[0]+" "+row[1]+" "+row[2]+" "+row[3]]  # Metric value
            if len(row) == 5:  # check conditions for better parse
                row[1:3] = [row[1]+" "+row[2]]  # Design Project value
            _dict = {}
            for elem, header_elem in zip(row, header):
                _dict[header_elem] = elem
            out_data.append(_dict)

    print json.dumps(out_data)

It work only if structure of your data is constant, and key/value consists of the same number of words.

You can add additional conditions (like me in line 21):

if len(row) == 5:  # check conditions for better parse
    row[1:3] = [row[1]+" "+row[2]]  # Design Project value

Upvotes: 1

Related Questions