将JSON元素写入csv文件python [英] Write JSON elements to csv file python

问题描述

我刚刚创建了一个python程序，它刮掉google webmaster API来检查target_site是否适合移动设备。然后基于响应，它提取某些Json元素。它也捕获本地文件夹的屏幕截图

脚本工作正常，但当我试图写这些json对象到csv文件，它不工作。 >

这是我的代码： -

  import requests，json，string，random ，时间
 import csv 
从base64导入解析
从随机导入randint 
 
 #links = open（r'D：\\Carlos\\ Links.txt'）
 links = ['https://www.googleapis.com/pagespeedonline/v3beta1/mobileReady?key=AIzaSyDkEX-f1JNLQLC164SZaobALqFv4PHV-kA&screenshot=true&snapshots=true&locale=en_US&url = https：//www.economicalinsurance.com/en/& strategy = mobile& filter_third_party_resources = false'，
'https://www.googleapis.com/pagespeedonline/v3beta1/mobileReady?key=AIzaSyDkEX-f1JNLQLC164SZaobALqFv4PHV -kA& screenshot = true& snapshots = true& locale = en_US& url = http：//www.volkswagen-me.com/en-vwme/service/protection/motor-insurance.html& strategy = mobile& filter_third_party_resources = false '] 
 
 def id_generator（size = 6，chars = string.ascii_uppercase + string.digits）：
 return''.join（random.choice（chars）for _ in range ））
 
i = 12 
 
 def get_data（each）：
 try：
r = requests.get（each）
 except：
 pass 
＃time.sleep（randint（1，3））
 try：
 json_data = json.loads（r.text）
 except：
 pass 
 try：
 score = json_data ['ruleGroups'] ['USABILITY'] ['score']; score = int（score）
 except：
 pass 
 try：
 Pass = json_data ['ruleGroups'] ['USABILITY'] ['pass']; Pass = str（Pass）
 except：
 pass 
 try：
 ConfigureViewport = json_data ['formattedResults'] ['ruleResults'] ['ConfigureViewport'] ['localizedRuleName']; ConfigureViewport = str（ConfigureViewport）
除了：
 pass 
 try： 
 UseLegibleFontSizes = json_data ['formattedResults'] ['ruleResults'] ['UseLegibleFontSizes'] ['localizedRuleName']; UseLegibleFontSizes = str（UseLegibleFontSizes）
 except：
 pass 
 try ：
 AvoidPlugins = json_data ['formattedResults'] ['ruleResults'] ['AvoidPlugins'] ['localizedRuleName']; AvoidPlugins = str（AvoidPlugins）
除了：
 pass 
 try：
 SizeContentToViewport = json_data ['formattedResults'] ['ruleResults'] ['SizeContentToViewport'] ['localizedRuleName']; SizeContentToViewport = str（SizeContentToViewport）
 except：
 pass 
 try：
 SizeTapTargetsAppropriately = json_data ['formattedResults'] ['ruleResults'] ['SizeTapTargetsAppropriately'] ['localizedRuleName']; SizeTapTargetsAppropriately = str（SizeTapTargetsAppropriately）
除了：
 pass 
 try：
 AvoidInterstitials = json_data ['formattedResults'] ['ruleResults'] ['AvoidInterstitials'] ['localizedRuleName']; AvoidInterstitials = str（AvoidInterstitials）
 except：
 pass 
 try：
 image_link = json_data ['screenshot'] ['data']; image_link = image_link.replace（_，/\").replace(\"-\",\"+）
除了：
 pass 
 #try：
 id_generator_name = .join（[random.choice（string.letters）for i in xrange（15）]）+'。jpeg'
 #except：
＃pass 
 #try：
 fh = open（id_generator_name，wb）
 #except：
＃pass 
 try：
 fh.write（str（image_link）.decode（'base64'）） 
 time.sleep（1）
 except：
 pass 
 try：
 fh.close（）
 except：
 pass 
 try：
 error_code = json_data ['error'] ['message']; error_code = str（error_code）
 except：
 pass 
 try：
 print each ，score，Pass，ConfigureViewport，UseLegibleFontSizes，AvoidPlugins，SizeContentToViewport，SizeTapTargetsAppropriately，AvoidInterstitials，error_code 
 except：
 pass 
 try：
 writer.writerow（{'each' 'Size'：SizeContentToViewport'：SizeTapTargetsAppropriately'：SizeTapTargetsAppropriately'SizeTapTargetsAppropriately'：SizeTapTargetsAppropriately'SizeTapTargetsAppropriately'：SizeTapTargetsFileSizes，'AvoidPlugins'：AvoidPlugins，
' 
'AvoidInterstitials'：AvoidInterstitials，'error_code'：error_code，'imagename'：id_generator_name}）
 except：
 pass 
 
 #path到csv文件
 with open（D：\Carlos\Data_file\output.csv，ab）as export：
 fieldnames = ['each'，'score'，'Pass'，'ConfigureViewport' ，'UseLegibleFontSizes'，'AvoidPlugins'，'SizeContentToViewport'，
'SizeTapTargetsAppropriately'，'AvoidInterstitials'，'error_code'，'imagename'] 
 writer = csv.DictWriter（export，fieldnames = fieldnames）
 writer.writeheader（）
每个链接：
 #try：
 get_data（每个）
 #except：
＃pass 
  

请咨询如何写csv？

解决方案

我喜欢使用Pandas数据框架，但是如果你不会使用熊猫。 Pandas数据帧也非常适合分析和比较。

您可以将JSON放入数据框架，然后将dataframe输出到CSV文件。

  import pandas as pd 
 
 df = pd.read_json（'path / to / json / file '）
 df.to_csv（'filename.csv'）
  

注意，只有当你的JSON有一个级别，也可能是一个csv。否则，您需要将JSON读入一个dict，导航到相应的级别，然后读入数据帧。

http://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_json.html
http：// pandas。 pydata.org/pandas-docs/stable/generated/pandas.DataFrame.to_csv.html

http://pandas.pydata.org/

I just created a python program which scrapes google webmaster API to check if the target_site is mobile friendly or not & then based on response it extracts certain Json elements. Also it captures the screenshot on local folder

the script is working fine,BUT When i am trying to write those json objects to csv file ,its not working .

Here is my code :-

import requests, json, string, random, time
import csv
from base64 import decodestring
from random import randint

#links = open(r'D:\\Carlos\\Links.txt')
links = ['https://www.googleapis.com/pagespeedonline/v3beta1/mobileReady?key=AIzaSyDkEX-f1JNLQLC164SZaobALqFv4PHV-kA&screenshot=true&snapshots=true&locale=en_US&url=https://www.economicalinsurance.com/en/&strategy=mobile&filter_third_party_resources=false',
         'https://www.googleapis.com/pagespeedonline/v3beta1/mobileReady?key=AIzaSyDkEX-f1JNLQLC164SZaobALqFv4PHV-kA&screenshot=true&snapshots=true&locale=en_US&url=http://www.volkswagen-me.com/en-vwme/service/protection/motor-insurance.html&strategy=mobile&filter_third_party_resources=false']

def id_generator(size=6, chars=string.ascii_uppercase + string.digits):
    return ''.join(random.choice(chars) for _ in range(size))

i = 12

def get_data(each):
    try:
        r = requests.get(each)
    except:
        pass
    #time.sleep(randint(1, 3))
    try:    
        json_data = json.loads(r.text)
    except:
        pass
    try:
        score = json_data['ruleGroups']['USABILITY']['score'];score=int(score)
    except:
        pass
    try:
        Pass = json_data['ruleGroups']['USABILITY']['pass'];Pass=str(Pass)
    except:
        pass
    try:
        ConfigureViewport = json_data['formattedResults']['ruleResults']['ConfigureViewport']['localizedRuleName'];ConfigureViewport=str(ConfigureViewport)
    except:
        pass
    try:
        UseLegibleFontSizes = json_data['formattedResults']['ruleResults']['UseLegibleFontSizes']['localizedRuleName'];UseLegibleFontSizes=str(UseLegibleFontSizes)
    except:
        pass
    try:
        AvoidPlugins = json_data['formattedResults']['ruleResults']['AvoidPlugins']['localizedRuleName'];AvoidPlugins=str(AvoidPlugins)
    except:
        pass
    try:
        SizeContentToViewport = json_data['formattedResults']['ruleResults']['SizeContentToViewport']['localizedRuleName'];SizeContentToViewport=str(SizeContentToViewport)
    except:
        pass
    try:
        SizeTapTargetsAppropriately = json_data['formattedResults']['ruleResults']['SizeTapTargetsAppropriately']['localizedRuleName'];SizeTapTargetsAppropriately=str(SizeTapTargetsAppropriately)
    except:
        pass
    try:
        AvoidInterstitials = json_data['formattedResults']['ruleResults']['AvoidInterstitials']['localizedRuleName'];AvoidInterstitials=str(AvoidInterstitials)
    except:
        pass
    try:
        image_link = json_data['screenshot']['data']; image_link = image_link.replace("_", "/").replace("-","+")
    except:
        pass
    #try:
    id_generator_name = "".join( [random.choice(string.letters) for i in xrange(15)] )+'.jpeg'
    #except:
       # pass
    #try:
    fh = open(id_generator_name, "wb")
    #except:
    #    pass
    try:
        fh.write(str(image_link).decode('base64'))
        time.sleep(1)
    except:
        pass
    try:
        fh.close()
    except:
        pass
    try:
        error_code = json_data['error']['message'];error_code=str(error_code)
    except:
        pass
    try:
        print each, score, Pass, ConfigureViewport, UseLegibleFontSizes, AvoidPlugins, SizeContentToViewport, SizeTapTargetsAppropriately, AvoidInterstitials, error_code
    except:
        pass
    try:
        writer.writerow({'each':each, 'score':score, 'Pass':Pass, 'ConfigureViewport':ConfigureViewport,
                         'UseLegibleFontSizes':UseLegibleFontSizes, 'AvoidPlugins':AvoidPlugins,
                         'SizeContentToViewport':SizeContentToViewport,'SizeTapTargetsAppropriately':SizeTapTargetsAppropriately,
                         'AvoidInterstitials':AvoidInterstitials, 'error_code':error_code,'imagename':id_generator_name})
    except:
        pass

#path to the csv file
with open("D:\Carlos\Data_file\output.csv", "ab")as export:
    fieldnames = ['each', 'score', 'Pass', 'ConfigureViewport', 'UseLegibleFontSizes', 'AvoidPlugins', 'SizeContentToViewport',
                  'SizeTapTargetsAppropriately', 'AvoidInterstitials', 'error_code','imagename']
    writer = csv.DictWriter(export, fieldnames=fieldnames)
    writer.writeheader()
    for each in links:
    #try:
        get_data(each)
    #except:
    #  pass

Please advice on how to write to csv ? Or where things are wrong in the code ?

解决方案

I like to use Pandas dataframes for this, but it may be overkill if you wouldn't use Pandas otherwise. Pandas dataframes are also great for analysis and comparison.

You would put the JSON into a dataframe, and then output the dataframe to a CSV file.

import pandas as pd

df = pd.read_json('path/to/json/file')
df.to_csv('filename.csv')

Note that it's this simple only when your JSON has one level and might as well be a csv. Otherwise, you would need to read the JSON into a dict, navigate to the appropriate level and then read that into a dataframe.

http://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_json.html http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.to_csv.html

http://pandas.pydata.org/

这篇关于将JSON元素写入csv文件python的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！