Construct DataFrame from scraped data using Scrapy

user_7418254 0
Viewed 6778

I have a problem with constructing csv type data file from scraped data. I have managed to scrape the data from the table but when it comes to writing it I can't do that for days. I am using items and trying to write it to pandas data frame. I am using items list.

import scrapy
from wiki.items import WikiItem
import pandas as pd

class Spider(scrapy.Spider):

name = "wiki"
start_urls = ['https://datatables.net/']

def parse(self, response):

    items = {'Name':[], 'Position':[], 'Office':[], 'Age':[],
        'Start_Date':[],'Salary':[]}

    trs = response.xpath('//table[@id="example"]//tr')
    name = WikiItem()
    pos = WikiItem()
    office = WikiItem()
    age = WikiItem()
    start_data = WikiItem()
    salary = WikiItem()

    name['name'] = trs.xpath('//td[1]//text()').extract()
    pos['position'] = trs.xpath('//td[2]//text()').extract()
    office['office'] = trs.xpath('//td[3]//text()').extract()
    age['age'] = trs.xpath('//td[4]//text()').extract()
    start_data['start_data'] = trs.xpath('//td[5]//text()').extract()
    salary['salary'] = trs.xpath('td[6]//text()').extract()

    items['Name'].append(name)
    items['Position'].append(pos)
    items['Office'].append(office)
    items['Age'].append(age)
    items['Start_Date'].append(start_data)
    items['Salary'].append(salary)

    x = pd.DataFrame(items, columns=['Name','Position','Office','Age',
        'Start_Date','Salary'])

    yield x.to_csv("r",sep=",")

From this code what I get is like this ;

,Name,Position,Office,Age,Start_Date,Salary
0,"{'name': [u'Tiger Nixon',
      u'Garrett Winters',
      u'Ashton Cox',
      u'Cedric Kelly',
      u'Airi Satou',
      u'Brielle Williamson',
      u'Herrod Chandler',

I am getting the names column but I get it 59 times.For instance I have the first row, 'Tiger Nixon' 59 times. I get 59 times position column also and so on. And the scraped data is not in good shape also. I am new to scrapy and open to any help or suggestions. Thanks in advance!

EDIT : My items.py is like this;

import scrapy


class WikiItem(scrapy.Item):


name = scrapy.Field()
position = scrapy.Field()
office = scrapy.Field()
age = scrapy.Field()
start_data = scrapy.Field()
salary = scrapy.Field()
scrapy web-scraping python
2 Answers

I know this is not exactly pertaining to the use-case as posed by the question, but I feel it's relevant to the question's title: how to return pd DataFrame object in scrapy Spider?

Context:

If you are trying to export a pd.DataFrame object in a scrapy spider, if you directly state yield df, for example:

import scrapy 
import json
import pandas as pd 

class Spider(scrapy.Spider): 
   start_urls = ['mywebsite.com'] 

   def parse(self, response):
       #Let us assume mywebsite.com contains a script tag with JSON data rendered serverside
       script = response.xpath('//script[@id="windowData"]/text()').extract_first()
      
       data = json.loads(script)

       #Construct dataframe from dictionary
       df = pd.DataFrame.from_dict(data['anInterestingPieceOfData'])
 
       yield df

This will return an error that the thing you are trying to yield is not a Request, BaseItem, dict or None, instead it is a DataFrame.

Solution

So the question becomes, how can I convert the DataFrame object into a dict of some sort? This doesn't concern your feed export format (if the spider is outputting a CSV or JSON or whatever).

You could use anything from pandas to_csv, to_json, but I find that a flexible option is to use to_dict, such as using the yield from syntax:

yield from self.df.to_dict(orient="records")
user_129822050
Related