Scrapy: Item returning JSON with values as a list, trying to output each element of the list as its own line

Viewed 266

I'm trying to scrape the Jeopardy archive, using scrapy items. Here is my current spider:

import scrapy
from jeopardy.items import JeopardyItem
from scrapy.loader import ItemLoader

class JeopardySpider(scrapy.Spider):
    name = 'clues'
    start_urls = ['http://www.j-archive.com/showgame.php?game_id=1']

    def parse(self, response):
            loader = ItemLoader(item = JeopardyItem(), response=response)
            loader.add_xpath('game_id', '//div[@id = "game_title"]//text()')
            loader.add_css('value', 'td.clue_value::text,td.clue_value_daily_double::text')
            loader.add_value('value', 'FJ')
            order_number = response.css('td.clue_order_number').css('a::text').extract()
            loader.add_value('order_number', order_number)
            loader.add_value('order_number', "61")
            loader.add_css('clue_id', 'td.clue_text::attr(id)')
            clue = response.css('td.clue').css('td.clue_text').xpath('string()').extract() 
            loader.add_value('clue', clue)
            jep_item = loader.load_item()
            answers_url = response.xpath('//*[@id="final_jeopardy_round"]/h4/a[1]//@href').get()
            yield response.follow(answers_url, self.parse_answers, meta = {'jep_item': jep_item})
        #for a in response.xpath('//*[@id="contestants_table"]//a[@href]'):
            #yield response.follow(a, self.parse)

    def parse_answers(self, response):
        jep_item = response.meta['jep_item']
        loader = ItemLoader(item=jep_item, response=response)
        answers = response.xpath('//em[@class = "correct_response"]').xpath('string()').extract()
        loader.add_value('correct_response', answers)
        yield loader.load_item()

Which outputs the following (abridged) JSON:

[
{"game_id": ["Show #4596 - Monday, September 6, 2004"], "value": ["$200", "$200", "$200", ...], "order_number": ["1", "4", "26", ...], "clue_id": ["clue_J_1_1", "clue_J_2_1", "clue_J_3_1"...], "clue": ["Let's all flock to read Psalm 95, in which humans are compared to these animals", "The rap on him is he's sometimes \"Puffy\"", "In the 1980s this city passed Chicago as the USA's second most populous", ...], "correct_response": ["sheep", "Sean Combs", "Los Angeles" ...]}
]

I'm trying to format the data in the following way:

{"game_id": "Show #4596 - Monday, September 6, 2004", "clue_id": "clue_J_1_1", "order_number": "1", "value": "$200", "clue": "Let's all flock to read Psalm 95, in which humans are compared to these animals"},
{"game_id": "Show #4596 - Monday, September 6, 2004", "clue_id": "clue_J_2_1", "order_number": "4", "value": "$200", "clue": "The rap on him is he's sometimes \"Puffy\""}
...

This JSON was scraped using a different spider, but doesn't use items and instead uses regular python dictionaries, and doesn't output the correct response.

import scrapy

class JeopardySpider(scrapy.Spider):
    name = 'test'
    start_urls = [
        'http://www.j-archive.com/showgame.php?game_id=1'
    ]
    #allowed_domains = ['http://www.j-archive.com']
    def parse(self, response):
        for post in response.css('td.clue'):
            yield {
                'game_id' : response.xpath('//div[@id = "game_title"]//text()').get(),
                'clue_id' : post.css('td.clue_text::attr(id)').get(),
                'order_number' :  post.css('td.clue_order_number').css('a::text').get(),
                'value' : post.css('td.clue_value::text,td.clue_value_daily_double::text').get(),
                'clue' :  post.css('td.clue_text').xpath('string()').get()
            }
            answers_url = response.xpath('//*[@id="final_jeopardy_round"]/h4/a[1]//@href').get()
            yield response.follow(answers_url, self.parse_answers)
        #for a in response.xpath('//*[@id="contestants_table"]//a[@href]'):
            #yield response.follow(a, self.parse)
    def parse_answers(self, response):
        yield {
            'correct_response' : response.xpath( '//em[@class= "correct_response"]//text()').get(),
        }

I'm looking to do one of three things:

  1. Format the first spider so it outputs a JSON such that each line is a clue, rather than each line a game
  2. Format the second spider so it adds the correct response
  3. Somehow take the first JSON and make it readable in pandas

Also the game ID in the first JSON might complicate things since that list has one element and not 61 like the others, but I'm willing to comment that out if necessary.

I've been tearing my hair out trying to figure this out, so any help/advice would be greatly appreciated.

0 Answers
Related