Python: json对csv的响应？

Question

我正在调用一个API (https://min-api.cryptocompare.com/data/histoday?fsym=BTC&tsym=USD&limit=10)，它提供以下响应：

{“响应”：“成功”，“类型”：100，“聚合”：假，“数据”：{“时间”：1561852800，“关闭”：10769.05，“高”：12200.02，“低”：10677.83，“打开”：11884.1，“容量”：80893.36，“容量”：917158052.37}，{“时间”：1561939200，“关闭”：10591.87，“高”：11207，“低”：10006.43，“打开”：10769.05，“容量”：115739.97，“容量”：1225129699.57}，{“时间”：1562025600，“关闭”：10844.13，“高”：10927.6，“低”：9678.1，“打开”：10591.87，“容量”：120994.95，“容量”：1239524970.43}，{“时间”：1562112000，“关闭”：11981.61，“高”：12009.59，“低”：10841.91，“打开”：10844.13，“容量”：115565.16，“容量”：1313585829.89}，{“时间”：1562198400，“关闭”：11156.52，“高”：12055.11，“低”：11067.68，“打开”：11981.61，“容量”：71141.03，“容量”：831236841.56}，{“时间”：1562284800，“关闭”：10993.25，“高”：11435.38，“低”：10787.94，“打开”：11156.52，“容量”：66066.75，“容量”：734424868.07}，{“时间”：1562371200，“关闭”：11248.94，“高”：11709.27，“低”：10985.4，“打开”：10993.25，“容量”：48172.2，“容量”：549769169.13}，{“时间”：1562457600，“关闭”：11474.28，“高”：11605.43，“低”：11109.42，“打开”：11248.94，“容量”：36847.21，“容量”：418161890.29}，{“时间”：1562544000，“关闭”：12296.16，“高”：12386.28，“低”：11339.02，“打开”：11474.28，“容量”：63847.27，“容量”：762033323.29}，{“时间”：1562630400，“关闭”：12537.38，“高”：12808.06，“低”：12117.31，“打开”：12296.16，“容量”：79366.56，“容量”：990863142.59}，{“时间”：1562716800，“关闭”：12855.54，“高”：12855.54，“低”：12537.38，“打开”：12537.38，“容量”：0，"volumeto":0}，"TimeTo":1562716800，"TimeFrom":1561852800，"FirstValueInArray":true，“ConversionType”：{“type”{“type”：“conversionSymbol”：“}”，"RateLimit":{}，"HasWarning":false}

我需要将其转换为包含time、close、high、low、open、volumefrom和volumeto列的csv。应如何做到这一点？我已经尝试过一些现有的将json转换为csv的解决方案，但在这种情况下它们不起作用。

Answer 1

对于熊猫来说，这很容易做到：

import pandas as pd
import json

# Parse the json string to a python dictionary
data = json.loads(json_data)

# The desired data is in the `Data` field, use pandas to construct the data frame
df = pd.DataFrame(data["Data"])

# Save to a csv file
df.to_csv("result.csv")

Answer 2

由于您没有提到文件的使用，所以我只是在没有csv库的情况下生成了逗号分隔的值。但是，这也可以用它来完成。

import requests
import json

r = requests.get("https://min-api.cryptocompare.com/data/histoday?fsym=BTC&tsym=USD&limit=10")

# this is assuming the response has no errors and is status code 200
data = json.loads(r.text)

# in csv the headers are conventionally the first row
csv_data = "time, close, high, low, open"

for row in data["Data"]:

    # since the order is specific, select the values from the row in that order
    row_data = [row["time"], row["close"], row["high"], row["low"], row["open"]]

    # here I am using list comprehension to convert all the data from the row to a string
    # then I use the .join method to concatonate all these values as one comma seperated list
    csv_row = ", ".join([str(j) for j in row_data])

    csv_data += "\n" + csv_row

# here is your data
print(csv_data)

文档链接

• 请求 --一个常用的、友好的python请求库包装器。
• 列表理解 --一种在python中生成列表的更紧凑的方法，而且效率也略高一些。

Answer 3

这就是你要的!这应该对你有用！请随意问任何额外的问题，我已经试着保持每件事的评论和合理整洁。c：

#!/usr/bin/env python3
import json, csv

# Here is our input string/response. You can replace this!
responseString = '{"Response":"Success","Type":100,"Aggregated":false,"Data":[{"time":1561852800,"close":10769.05,"high":12200.02,"low":10677.83,"open":11884.1,"volumefrom":80893.36,"volumeto":917158052.37},{"time":1561939200,"close":10591.87,"high":11207,"low":10006.43,"open":10769.05,"volumefrom":115739.97,"volumeto":1225129699.57},{"time":1562025600,"close":10844.13,"high":10927.6,"low":9678.1,"open":10591.87,"volumefrom":120994.95,"volumeto":1239524970.43},{"time":1562112000,"close":11981.61,"high":12009.59,"low":10841.91,"open":10844.13,"volumefrom":115565.16,"volumeto":1313585829.89},{"time":1562198400,"close":11156.52,"high":12055.11,"low":11067.68,"open":11981.61,"volumefrom":71141.03,"volumeto":831236841.56},{"time":1562284800,"close":10993.25,"high":11435.38,"low":10787.94,"open":11156.52,"volumefrom":66066.75,"volumeto":734424868.07},{"time":1562371200,"close":11248.94,"high":11709.27,"low":10985.4,"open":10993.25,"volumefrom":48172.2,"volumeto":549769169.13},{"time":1562457600,"close":11474.28,"high":11605.43,"low":11109.42,"open":11248.94,"volumefrom":36847.21,"volumeto":418161890.29},{"time":1562544000,"close":12296.16,"high":12386.28,"low":11339.02,"open":11474.28,"volumefrom":63847.27,"volumeto":762033323.29},{"time":1562630400,"close":12537.38,"high":12808.06,"low":12117.31,"open":12296.16,"volumefrom":79366.56,"volumeto":990863142.59},{"time":1562716800,"close":12855.54,"high":12855.54,"low":12537.38,"open":12537.38,"volumefrom":0,"volumeto":0}],"TimeTo":1562716800,"TimeFrom":1561852800,"FirstValueInArray":true,"ConversionType":{"type":"direct","conversionSymbol":""},"RateLimit":{},"HasWarning":false}'

# Turn the response into JSON and only grab the 'Data' list.
responseString = json.loads(responseString)['Data']

# Open us a new file!
with open('output.csv', 'w') as output_csv_file:
    # Create us a new csv_object using the keys of the data as fieldnames.
    csv_object = csv.DictWriter(output_csv_file, fieldnames=responseString[0].keys())

    # For each row of data in the JSON, print it and write it to the CSV.
    for row in responseString:
        print(row)
        csv_object.writerow(row)

# Automatically close CSV file/object and print "Done!"
print("Done!")