Python - 将csv文件转换为JSON

Question

Python - 将csv文件转换为JSON

8

我需要将一个csv文件转换成层次化的JSON对象（最好使用Python）。我认为我拥有的脚本（如下）可以正确地将其转换为JSON，但是我提供给JSON数据的JavaScript库（D3.js）无法使用它。

csv文件长这样：

subject,branch,book,chapter,Encode ID,Level 1,Level 2,Level 3,Level 4
MAT,TRI,CK-12 Trigonometry - Second Edition,Right Triangles and an Introduction to Trigonometry,MAT.TRI.000,Right Triangles and an Introduction to Trigonometry,,,
MAT,TRI,CK-12 Trigonometry - Second Edition,Right Triangles and an Introduction to Trigonometry,MAT.TRI.004,,The Pythagorean Theorem,,
MAT,TRI,CK-12 Trigonometry - Second Edition,Right Triangles and an Introduction to Trigonometry,MAT.TRI.005,,,The Pythagorean Theorem,
MAT,TRI,CK-12 Trigonometry - Second Edition,Right Triangles and an Introduction to Trigonometry,MAT.TRI.006,,,Pythagorean Triples,
MAT,TRI,CK-12 Trigonometry - Second Edition,Right Triangles and an Introduction to Trigonometry,MAT.TRI.007,,,Converse of the Pythagorean Theorem,
MAT,TRI,CK-12 Trigonometry - Second Edition,Right Triangles and an Introduction to Trigonometry,MAT.TRI.008,,,The Distance Formula,
MAT,TRI,CK-12 Trigonometry - Second Edition,Right Triangles and an Introduction to Trigonometry,MAT.TRI.009,,Special Right Triangles,,

现在，我有以下递归构建层次数组的代码：

import csv
import json
import random

random.seed()

# open up the csv file
f = open('/path/to/file','rU')
c = csv.DictReader(f)

# lists for keeping track of what subjects and branches I've already iterated over
subject_list = []
branch_list = []
lev1_list = []
lev2_list = []
lev3_list = []
lev4_list = []

# iterate through file
i = 0
for row in c:
    if i == 0:
        subject = row['subject']
        branch = row['branch']
        if len(row['Level 1']) > 0:
            lev1 = row['Level 1']
        else:
            lev2 = None
        if len(row['Level 2']) > 0:
            lev2 = row['Level 2']
        else:
            lev2 = None
        if len(row['Level 3']) > 0:
            lev3 = row['Level 3']
        else:
            lev3 = None

    else:
        if row['subject'] != subject:

            # add all branches to this subject
            subject_list.append({'name':subject,'type':'subject','children':branch_list})


            # set current subject
            subject = row['subject']

        if row['branch'] != branch:
            # add all concepts to this branch
            branch_list.append({'name':branch,'type':'branch','children':lev1_list})

            # empty lev1_list
            lev1_list = []

            # set new branch
            branch = row['branch']

        if len(row['Level 1']) > 0 and row['Level 1'] != lev1:
            # print lev1
            # add all level 2 concepts to this level 1
            lev1_list.append({'name':lev1,'type':'concept','level':1,'children':lev2_list})
            #print lev1
            #empty lev2_list
            lev2_list = []

            #lev1_list.append(row['Level 1'])
            lev1 = row['Level 1']

        if len(row['Level 2']) > 0 and row['Level 2'] != lev3:
            #print lev2
            #print lev3_list
            # add all level 3 concepts to this level 2
            if lev2 is not None:
                lev2_list.append({'name':lev2,'type':'concept','level':2,'children':lev3_list})

            # empty lev3_list
            lev3_list = []

            # lev2_list.append(row['Level 2'])
            lev2 = row['Level 2']

        if len(row['Level 3']) > 0 and row['Level 3'] != lev3:
            # print lev3
            # add all level 4 concepts to this level 4
            # lev3_list.append({'name':lev3,'type':'concept','level':3})

            # empty level 4 concepts
            # lev4_list = []

            # add new level 3
            if lev3 is not None:
                lev3_list.append({'name':lev3,'type':'concept','level':3,'size':random.randint(1,100)})
            lev3 = row['Level 3']


        #if row['Level 4'] is not None and row['Level 4'] is not lev4:
        #   lev4_list.append({'name':lev4,'type':'concept','level':4})
        #   lev4 = row['Level 4']

    i += 1

f.close()


branch_list.append({'name':branch,'type':'branch','children':lev1_list})

#subject_list.append({'name':subject,'type':'subject','children':branch_list})
subject_dict = {'name':subject,'type':'subject','children':branch_list}

#json_list= json.dumps(subject_list)
json_list = json.dumps(subject_dict)

f = open('/Users/thaymore/Sites/d3/data/trig_map.json','wb')
f.write(json_list)
f.close()

目前我得到的是类似于这样的内容：

{"type": "subject", "name": "MAT", "children": [{"type": "branch", "name": "TRI", "children": [{"children": [{"children": [{"size": 40, "type": "concept", "name": "The Pythagorean Theorem", "level": 3}, {"size": 19, "type": "concept", "name": "Pythagorean Triples", "level": 3}, {"size": 68, "type": "concept", "name": "Converse of the Pythagorean Theorem", "level": 3}], "type": "concept", "name": "The Pythagorean Theorem", "level": 2}, {"children": [{"size": 28, "type": "concept", "name": "The Distance Formula", "level": 3}, {"size": 49, "type": "concept", "name": "Special Right Triangle #1: Isosceles Right Triangle", "level": 3}, {"size": 33, "type": "concept", "name": "Special Right Triangle #2: 30-60-90 Triangle", "level": 3}], "type": "concept", "name": "Special Right Triangles", "level": 2}, {"children": [{"size": 18, "type": "concept", "name": "Using Special Right Triangle Ratios", "level": 3}, {"size": 49, "type": "concept", "name": "The Sine, Cosine, and Tangent Functions", "level": 3}], "type": "concept", "name": "Basic Trigonometric Functions", "level": 2}, {"children": [{"size": 100, "type": "concept", "name": "Secant, Cosecant, and Cotangent Functions", "level": 3}, {"size": 73, "type": "concept", "name": "Solving Right Triangles", "level": 3}, {"size": 93, "type": "concept", "name": "Inverse Trigonometric Functions", "level": 3}, {"size": 88, "type": "concept", "name": "Finding the Area of a Triangle", "level": 3}, {"size": 6, "type": "concept", "name": "Angles of Elevation and Depression", "level": 3}, {"size": 3, "type": "concept", "name": "Right Triangles and Bearings", "level": 3}], "type": "concept", "name": "Solving Right Triangles", "level": 2}, {"children": [{"size": 68, "type": "concept", "name": "Other Applications of Right Triangles", "level": 3}, {"size": 92, "type": "concept", "name": "Angles of Rotation in Standard Position", "level": 3}], "type": "concept", "name": "Measuring Rotation", "level": 2}, {"children": [{"size": 14, "type": "concept", "name": "Coterminal Angles", "level": 3}, {"size": 68, "type": "concept", "name": "Trigonometric Functions of Angles in Standard Position", "level": 3}], "type": "concept", "name": "Applying Trig Functions to Angles of Rotation", "level": 2}, {"children": [{"size": 61, "type": "concept", "name": "The Unit Circle", "level": 3}, {"size": 95, "type": "concept", "name": "Reference Angles and Angles in the Unit Circle", "level": 3}, {"size": 11, "type": "concept", "name": "Trigonometric Functions of Negative Angles", "level": 3}, {"size": 45, "type": "concept", "name": "Trigonometric Functions of Angles Greater than 360 Degrees", "level": 3}], "type": "concept", "name": "Trigonometric Functions of Any Angle", "level": 2}], "type": "concept", "name": "Right Triangles and an Introduction to Trigonometry", "level": 1}, {"children": [{"children": [{"size": 20, "type": "concept", "name": "Using a Calculator to Find Values", "level": 3}, {"size": 25, "type": "concept", "name": "Reciprocal identities", "level": 3}, {"size": 40, "type": "concept", "name": "Domain, Range, and Signs of Trig Functions", "level": 3}, {"size": 97, "type": "concept", "name": "Quotient Identities", "level": 3}, {"size": 18, "type": "concept", "name": "Cofunction Identities and Reflection", "level": 3}], "type": "concept", "name": "Relating Trigonometric Functions", "level": 2}, {"children": [{"size": 35, "type": "concept", "name": "Pythagorean Identities", "level": 3}, {"size": 95, "type": "concept", "name": "Understanding Radian Measure", "level": 3}, {"size": 30, "type": "concept", "name": "Critial Angles in Radians", "level": 3}, {"size": 16, "type": "concept", "name": "Converting Any Degree to Radians", "level": 3}, {"size": 25, "type": "concept", "name": "The Six Trig Functions and Radians", "level": 3}], "type": "concept", "name": "Radian Measure", "level": 2}, {"children": [{"size": 19, "type": "concept", "name": "Check the Mode", "level": 3}, {"size": 63, "type": "concept", "name": "Rotations", "level": 3}, {"size": 33, "type": "concept", "name": "Length of Arc", "level": 3}, {"size": 54, "type": "concept", "name": "Area of a Sector", "level": 3}, {"size": 6, "type": "concept", "name": "Length of a Chord", "level": 3}], "type": "concept", "name": "Applications of Radian Measure", "level": 2}, {"children": [{"size": 71, "type": "concept", "name": "Angular Velocity", "level": 3}, {"size": 16, "type": "concept", "name": "The Sine Graph", "level": 3}, {"size": 65, "type": "concept", "name": "The Cosine Graph", "level": 3}, {"size": 32, "type": "concept", "name": "The Tangent Graph", "level": 3}, {"size": 93, "type": "concept", "name": "The Three Reciprocal Functions", "level": 3}, {"size": 30, "type": "concept", "name": "Cotangent", "level": 3}, {"size": 4, "type": "concept", "name": "Cosecant", "level": 3}], "type": "concept", "name": "Circular Functions of Real Numbers", "level": 2}, {"children": [{"size": 100, "type": "concept", "name": "Secant", "level": 3}, {"size": 40, "type": "concept", "name": "Vertical Translations", "level": 3}], "type": "concept", "name": "Translating Sine and Cosine Functions", "level": 2}, {"children": [{"size": 58, "type": "concept", "name": "Horizontal Translations or Phase Shifts", "level": 3}, {"size": 76, "type": "concept", "name": "Amplitude", "level": 3}, {"size": 91, "type": "concept", "name": "Period and Frequency", "level": 3}], "type": "concept", "name": "Amplitude, Period and Frequency", "level": 2}, {"children": [{"size": 78, "type": "concept", "name": "Combining Amplitude and Period", "level": 3}, {"size": 12, "type": "concept", "name": "The Generalized Equations", "level": 3}, {"size": 22, "type": "concept", "name": "Drawing Sketches/Identifying Transformations from the Equation", "level": 3}], "type": "concept", "name": "General Sinusoidal Graphs", "level": 2}], "type": "concept", "name": "Graphing Trigonometric Functions - 2nd edition", "level": 1}, {"children": [{"children": [{"size": 81, "type": "concept", "name": "Writing the Equation from a Sketch", "level": 3}, {"size": 60, "type": "concept", "name": "Tangent and Cotangent", "level": 3}, {"size": 27, "type": "concept", "name": "Secant and Cosecant", "level": 3}], "type": "concept", "name": "Graphing Tangent, Cotangent, Secant, and Cosecant", "level": 2}, {"children": [{"size": 62, "type": "concept", "name": "Graphing Calculator Note", "level": 3}, {"size": 20, "type": "concept", "name": "Quotient Identity", "level": 3}, {"size": 15, "type": "concept", "name": "Reciprocal Identities", "level": 3}, {"size": 28, "type": "concept", "name": "Pythagorean Identity", "level": 3}, {"size": 28, "type": "concept", "name": "Even and Odd Identities", "level": 3}], "type": "concept", "name": "Fundamental Identities", "level": 2}, {"children": [{"size": 24, "type": "concept", "name": "Cofunction Identities", "level": 3}, {"size": 91, "type": "concept", "name": "Working with Trigonometric Identities", "level": 3}], "type": "concept", "name": "Proving Identities", "level": 2}, {"children": [{"size": 59, "type": "concept", "name": "Technology Note", "level": 3}, {"size": 26, "type": "concept", "name": "Simplifying Trigonometric Expressions", "level": 3}, {"size": 94, "type": "concept", "name": "Solving Trigonometric Equations", "level": 3}, {"size": 49, "type": "concept", "name": "Solving Trigonometric Equations Using Factoring", "level": 3}], "type": "concept", "name": "Solving Trigonometric Equations", "level": 2}, {"children": [{"size": 25, "type": "concept", "name": "Solving Trigonometric Equations Using the Quadratic Formula", "level": 3}, {"size": 11, "type": "concept", "name": "Sum and Difference Formulas: cosine", "level": 3}, {"size": 30, "type": "concept", "name": "Using the Sum and Difference Identities of cosine", "level": 3}, {"size": 75, "type": "concept", "name": "Sum and Difference Identities: sine", "level": 3}, {"size": 94, "type": "concept", "name": "Sum and Difference Identities: Tangent", "level": 3}, {"size": 22, "type": "concept", "name": "Using the Sum and Difference Identities to Verify Other Identities", "level": 3}], "type": "concept", "name": "Sum and Difference Identities", "level": 2}, {"children": [{"size": 15, "type": "concept", "name": "Solving Equations with the Sum and Difference Formulas", "level": 3}, {"size": 88, "type": "concept", "name": "Deriving the Double Angle Identities", "level": 3}, {"size": 42, "type": "concept", "name": "Applying the Double Angle Identities", "level": 3}], "type": "concept", "name": "Double Angle Identities", "level": 2}, {"children": [{"size": 13, "type": "concept", "name": "Solving Equations with Double Angle Identities", "level": 3}, {"size": 36, "type": "concept", "name": "Deriving the Half Angle Formulas", "level": 3}], "type": "concept", "name": "Half-Angle Identities", "level": 2}], "type": "concept", "name": "Trigonometric Identities and Equations - 2nd edition", "level": 1}, {"children": [{"children": [{"size": 100, "type": "concept", "name": "Solving Trigonometric Equations Using Half Angle Formulas", "level": 3}, {"size": 93, "type": "concept", "name": "Sum to Product Formulas for Sine and Cosine", "level": 3}, {"size": 71, "type": "concept", "name": "Product to Sum Formulas for Sine and Cosine", "level": 3}, {"size": 53, "type": "concept", "name": "Solving Equations with Product and Sum Formulas", "level": 3}, {"size": 45, "type": "concept", "name": "Triple-Angle Formulas and Beyond", "level": 3}, {"size": 18, "type": "concept", "name": "Linear Combinations", "level": 3}], "type": "concept", "name": "Products, Sums, Linear Combinations, and Applications", "level": 2}, {"children": [{"size": 73, "type": "concept", "name": "Applications & Technology", "level": 3}, {"size": 54, "type": "concept", "name": "Defining the Inverse of the Trigonometric Ratios", "level": 3}, {"size": 15, "type": "concept", "name": "Exact Values for Inverse Sine, Cosine, and Tangent", "level": 3}], "type": "concept", "name": "Basic Inverse Trigonometric Functions", "level": 2}, {"children": [{"size": 1, "type": "concept", "name": "Finding Inverses Algebraically", "level": 3}, {"size": 93, "type": "concept", "name": "Finding the Inverse by Mapping", "level": 3}], "type": "concept", "name": "Graphing Inverse Trigonometric Functions", "level": 2}, {"children": [{"size": 79, "type": "concept", "name": "Finding the Inverse of the Trigonometric Functions", "level": 3}, {"size": 29, "type": "concept", "name": "Composing Trig Functions and their Inverses", "level": 3}, {"size": 19, "type": "concept", "name": "Composing Trigonometric Functions", "level": 3}, {"size": 53, "type": "concept", "name": "Inverse Reciprocal Functions", "level": 3}, {"size": 28, "type": "concept", "name": "Composing Inverse Reciprocal Trig Functions", "level": 3}], "type": "concept", "name": "Inverse Trigonometric Properties", "level": 2}], "type": "concept", "name": "Inverse Trigonometric Functions - 2nd edition", "level": 1}, {"children": [{"children": [], "type": "concept", "name": "Applications & Models", "level": 2}, {"children": [{"size": 42, "type": "concept", "name": "Trigonometry in Terms of Algebra", "level": 3}, {"size": 38, "type": "concept", "name": "Derive the Law of Cosines", "level": 3}, {"size": 82, "type": "concept", "name": "Case #1:  Finding the Side of an Oblique Triangle", "level": 3}, {"size": 68, "type": "concept", "name": "Case #2:  Finding any Angle of a Triangle", "level": 3}], "type": "concept", "name": "The Law of Cosines", "level": 2}, {"children": [{"size": 20, "type": "concept", "name": "Identify Accurate Drawings of General Triangles", "level": 3}, {"size": 90, "type": "concept", "name": "Find the Area Using Three Sides:  Heron\u2019s Formula", "level": 3}, {"size": 7, "type": "concept", "name": "Heron\u2019s Formula:", "level": 3}], "type": "concept", "name": "Area of a Triangle", "level": 2}, {"children": [{"size": 21, "type": "concept", "name": "Finding a Part of the Triangle, Given the Area", "level": 3}, {"size": 58, "type": "concept", "name": "Deriving the Law of Sines", "level": 3}, {"size": 15, "type": "concept", "name": "AAS (Angle-Angle-Side)", "level": 3}, {"size": 41, "type": "concept", "name": "ASA (Angle-Side-Angle)", "level": 3}], "type": "concept", "name": "The Law of Sines", "level": 2}, {"children": [{"size": 87, "type": "concept", "name": "Solving Triangles", "level": 3}, {"size": 31, "type": "concept", "name": "Possible Triangles with SSA", "level": 3}, {"size": 45, "type": "concept", "name": "Using the Law of Sines", "level": 3}], "type": "concept", "name": "The Ambiguous Case", "level": 2}, {"children": [{"size": 40, "type": "concept", "name": "Using the Law of Cosines", "level": 3}, {"size": 2, "type": "concept", "name": "Summary of Triangle Techniques", "level": 3}, {"size": 18, "type": "concept", "name": "Using the Law of Cosines", "level": 3}], "type": "concept", "name": "General Solutions of Triangles", "level": 2}, {"children": [{"size": 42, "type": "concept", "name": "Using the Law of Sines", "level": 3}, {"size": 6, "type": "concept", "name": "Directed Line Segments, Equal Vectors, and Absolute Value", "level": 3}, {"size": 60, "type": "concept", "name": "Vector Addition", "level": 3}, {"size": 76, "type": "concept", "name": "Vector Subtraction", "level": 3}], "type": "concept", "name": "Vectors", "level": 2}], "type": "concept", "name": "Triangles and Vectors", "level": 1}]}]}

我认为这个符合JSON规范，但是我试图将JSON数据提供给的库（D3.js）似乎无法处理它。这是因为元素的顺序错了吗（即，“children”元素在“name”元素之前出现）？还是我做的更基本的问题？

（要匹配结构的文件位于这里。）

- tchaymore

检查是否为顺序，通过将所有的 {} / dict 替换为 OrderedDict 并报告回来。 - agf

“无法处理它”是什么意思？是指无法解析它（例如，也许你示例末尾的额外逗号只是导致了解析错误），还是有更具体的问题？ - Zach Snow

@agf 正在运行 Python 2.6 -- 在该版本中是否有 OrderedDict 可用？ - tchaymore

@ZachSnow 额外的逗号是因为我截断了数据。 D3.js 库应该能够将 JSON 对象转换为分层节点布局，并且对于我上面链接的默认 JSON 文件，这个功能完全正常。然而，对于我的 JSON 数据，它什么也没做 - 好像无法处理数据或返回一个“未定义”的节点。 - tchaymore

为了帮助我们验证您的返回输出是否正确，能否请提供完整的JSON输出？在"等等"之前很难确定您停下来的位置。 - jathanism

显示剩余2条评论

4个回答

0

元素的顺序对于json来说并不重要。

但是，你生成的json是错误的。你试图匹配的示例在顶层没有“subject”键。它只显示了“name” => “flare”和一个子列表。你的脚本生成了带有“subject”键的json。

与其使用

subject_dict = {'name':subject,'type':'subject','children':branch_list}

尝试

subject_dict = {'name':subject,'children':branch_list}

我不了解D3.js，所以无法确定它是否需要遵循一个精确结构的对象。

提示：检查json对象结构的一个好工具是http://jsoneditor.appspot.com。只需将您的字符串粘贴到其中，然后单击“转到树视图”。它将帮助您轻松找到您拥有的内容与您想要的内容之间的差异。

- lullis

0

D3期望JSON中的值也用双引号括起来，例如"level": 3应该是"level":"3"。当您在D3中使用此数据进行计算时，它需要再次成为一个数字，因此在使用它时，您只需在d3代码中使用+data.xxx.yyy.level而不是data.xxx.yyy.level即可将其转换为数字。

- Tom Jorissen

-1

import csv
import json
file1 = csv.DictReader(open('filename.csv', 'r'))
output =[]
for each in complent:
    row = {}
    row['Id'] = each['Id']
    row['Name'] = each['Name']
    row['Address'] = each['Address']
    row['Mobile'] = each['Mobile']
    row['LandLine'] = each['LandLine']
    row['Email'] = each['Email']
    output.append(row)

json.dump(output,open('new_file.json','w'),indent=4,sort_keys=False)

- Mohan

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- g19fanatic · Accepted Answer

这个不是可以用吗？

import json

f = open('path/to/file','r')

arr=[]
headers = []

for header in f.readline().split(','):
  headers.append(header)

for line in f.readlines():
  lineItems = {}
  for i,item in enumerate(line.split(',')):  
    lineItems[headers[i]] = item
  arr.append(lineItems)

f.close()

jsonText = json.dumps(arr)

print jsonText