SFTW: Scraping data with Google Refine

For the first Something For The Weekend of 2012 I want to tackle a common problem when you’re trying to scrape a collection of webpage: they have some sort of structure in their URL like this, where part of the URL refers to the name or code of an entity:     http://www.ltscotland.org.uk/scottishschoolsonline/schools/freemealentitlement.asp?iSchoolID=5237521

  tp://www.ltscotland.org.uk/scottishschoolsonline/schools/freemealentitlement.asp?iSchoolID=5237629

    ttp://www.ltscotland.org.uk/scottishschoolsonline/schools/freemealentitlement.asp?iSchoolID=5237823

In this instance, you can see that the URL is identical apart from a 7 digit code at the end: the ID of the school the data refers to.

There are a number of ways you could scrape this data. You could use Google Docs and the =importXML formula, but Google Docs will only let you use this 50 times on any one spreadsheet (you could copy the results and select Edit > Paste Special > Values Only and then use the formula a further 50 times if it’s not too many – here’s one I prepared earlier).

And you could use Scraperwiki to write a powerful scraper – but you need to understand enough coding to do so quickly (here’s a demo I prepared earlier).

A middle option is to use Google Refine, and here’s how you do it.

Assembling the ingredients

With the basic URL structure identified, we already have half of our ingredients. What we need  next is a list of the ID codes that we’re going to use to complete each URL.

An advanced search for “list seed number scottish schools filetype:xls” brings up a link to this spreadsheet (XLS) which gives us just that.

The spreadsheet will need editing: remove any rows you don’t need. This will reduce the time that the scraper will take in going through them. For example, if you’re only interested in one local authority, or one type of school, sort your spreadsheet so that you can delete those above or below them.

Now to combine  the ID codes with the base URL.

Bringing your data into Google Refine

Open Google Refine and create a new project with the edited spreadsheet containing the school IDs.

At the top of the school ID column click on the drop-down menu and select Edit column > Add column based on this column…

In the New column name box at the top call this ‘URL’.

In the Expression box type the following piece of GREL (Google Refine Expression Language):

“http://www.ltscotland.org.uk/scottishschoolsonline/schools/freemealentitlement.asp?iSchoolID=”+value

(Type in the quotation marks yourself – if you’re copying them from a webpage you may have problems)

The ‘value’ bit means the value of each cell in the column you just selected. The plus sign adds it to the end of the URL in quotes.

In the Preview window you should see the results – you can even copy one of the resulting URLs and paste it into a browser to check it works. (On one occasion Google Refine added .0 to the end of the ID number, ruining the URL. You can solve this by changing ‘value’ to value.substring(0,7) – this extracts the first 7 characters of the ID number, omitting the ‘.0′) UPDATE: in the comment Thad suggests “perhaps, upon import of your spreadsheet of IDs, you forgot to uncheck the importer option to Parse as numbers?”

Click OK if you’re happy, and you should have a new column with a URL for each school ID.

Grabbing the HTML for each page

Now click on the top of this new URL column and select Edit column > Add column by fetching URLs…

In the New column name box at the top call this ‘HTML’.

All you need in the Expression window is ‘value’, so leave that as it is.

Click OK.

Google Refine will now go to each of those URLs and fetch the HTML contents. As we have a couple thousand rows here, this will take a long time – hours, depending on the speed of your computer and internet connection (it may not work at all if either isn’t very fast). So leave it running and come back to it later.

Extracting data from the raw HTML with parseHTML

When it’s finished you’ll have another column where each cell is a bunch of HTML. You’ll need to create a new column to extract what you need from that, and you’ll also need some GREL expressions explained here.

First you need to identify what data you want, and where it is in the HTML. To find it, right-click on one of the webpages containing the data, and search for a key phrase or figure that you want to extract. Around that data you want to find a HTML tag like <table class=”destinations”> or <div id=”statistics”>. Keep that open in another window while you tweak the expression we come onto below…

Back in Google Refine, at the top of the HTML column click on the drop-down menu and select Edit column > Add column based on this column…

In the New column name box at the top give it a name describing the data you’re going to pull out.

In the Expression box type the following piece of GREL (Google Refine Expression Language):

value.parseHtml().select(“table.destinations”)[0].select(“tr”).toString()

(Again, type the quotation marks yourself rather than copying them from here or you may have problems)

I’ll break down what this is doing:

value.parseHtml()

parse the HTML in each cell (value)

.select(“table.destinations”)

find a table with a class (.) of “destinations” (in the source HTML this reads <table class=”destinations”>. If it was <div id=”statistics”> then you would write .select(“div#statistics”) – the hash sign representing an ‘id’ and the full stop representing a ‘class’.

[0]

This zero in square brackets tells Refine to only grab the first table – a number 1 would indicate the second, and so on. This is because numbering (“indexing”) generally begins with zero in programming.

.select(“tr”)

Now, within that table, find anything within the tag <tr>

.toString()

And convert the results into a string of text.

The results of that expression in the Preview window should look something like this:

<tr> <th></th> <th>Abbotswell School</th> <th>Aberdeen City</th> <th>Scotland</th> </tr> <tr> <th>Percentage of pupils</th> <td>25.5%</td> <td>16.3%</td> <td>22.6%</td> </tr>

This is still HTML, but a much smaller and manageable chunk. You could, if you chose, now export it as a spreadsheet file and use various techniques to get rid of the tags (Find and Replace, for example) and split the data into separate columns (the =SPLIT formula, for example).

Or you could further tweak your GREL code in Refine to drill further into your data, like so:

value.parseHtml().select(“table.destinations”)[0].select(“td”)[0].toString()

Which would give you this:

<td>25.5%</td>

Or you can add the .substring function to strip out the HTML like so (assuming that the data you want is always 5 characters long):

value.parseHtml().select(“table.destinations”)[0].select(“td”)[0].toString().substring(5,10)

When you’re happy, click OK and you should have a new column for that data. You can repeat this for every piece of data you want to extract into a new column.

Then click Export in the upper right corner and save as a CSV or Excel file.

Source: http://onlinejournalismblog.com/2012/01/13/sftw-scraping-data-with-google-refine/

SEO Content Writing - Boost Your Sales and Services

In the world of today, access to knowledge of any kind is just one click away. This has become possible with broad use of internet and more precisely with the existence of search engines like Google, Yahoo etc.

The term SEO stands for "Search Engine Optimization." This refers to a special field of article writing which surrounds already provided keywords. These keywords are relevant to the specific website to be promoted. This methodology is adopted by webmasters to promote their web by getting higher SEO ranking through content writing. This type of content writingprovides two folds advantages. At one side it's an ordinary person surfing on the search engine for a specific knowledge and getting it at its best, on the other side it's the webmaster that enables the ordinary person visit his website thus increasing his website's SEO ranking.

Requirement of SEO writing services by webmasters provide earning opportunity for common people who have good writing skills. These people are given some amount of money for working from different countries. Some individuals who are experienced in this field make a team of writers to share their work. Webmasters outsource content writing to these SEO services providers.

Webmasters hire content writing service providers to do their work. They pay these providers a handsome amount in acknowledgment to their skilled work. SEO article writing is not a much difficult job. It is only a matter of common sense. The term SEO is actually not very common among laymen therefore many good writers consider it to be something out of their range. It is only required that a person should be able to write articles using keywords accurately.

Websites which use SEO content writing methods to give some information, get their ranking higher if more people visit their site. This technique is very beneficial to the companies. It all works in such a way that the content writing providing company makes partners who are usually individuals or teams. This combined working is called affiliate marketing. This is a kind of work cycle which is beneficial to both the employers and providers. Employers ask people to provide services for SEO writing in their budget. If work satisfies the company or individuals, there are chances that they maintain long term relationship with the content writing providing company or individuals and they give them assignments of SEO writing services on and off.

Source: http://ezinearticles.com/?SEO-Content-Writing---Boost-Your-Sales-and-Services&id=5613923

How a Content Writing Service Can Help You

The day has arrived when a person does not have to worry about the access of knowledge, may it be about shopping, academics, business, entertainment or whatever. The world of internet has made this day possible in our lives. To access information about a certain area a person just goes to a search engine like Google, Yahoo, etc. and finds his relevant material with just one click.

Search Engine Optimization, shortly termed as SEO is an idea of promoting web business through search engines. SEO technique helps a webmaster achieve his website in top SEO rankings with the help of SEO writings.

If a webmaster wants to promote his website through SEO writings and he himself is not specialist in writing that content, he may outsource content writing by hiring a content writing company. Main thing in SEO article writing is to be careful that the written article relates with the website a webmaster wants to promote. Web solution services provider are expert in such SEO article writing and they ensure that the article written by them exactly and precisely relates with the website to be promoted. Which in simpler words mean, if you want to promote a website which contain material such as selling or designs of undergarments, your written article must contain words related to undergarments like "stretchable undergarments", "silk undergarments", "importance of undergarments", "undergarments industry", "how to choose undergarments" and so on. Though it seems difficult to write such an article but it only requires sense of appropriate words selection, sentence construction and logical thinking. A rich article will profit you more whereas a poorly framed article may jeopardize all your efforts.

These articles are then submitted to different article directories on the web. Somewhere in the article, webmasters include the link of their website in a logical manner. Thus a person who wants to know something about already given example "undergarments" will enter his query or keyword in search engine which will lead him to the SEO article submitted by the webmaster. There onward, if driven, the person will click on the link and concerned website will be opened. In this way the desired website will be ranked higher in SEO ranking. Another website may also include or select your article on their website which enhances the chances of your own website promotion through more clicks. This method of affiliate marketing further improves the ranking of one's website. Besides this, the main secret behind favorable utility of these articles is its unique webpage publishing.

Services for SEO writing are pooled up from various resources like some dedicated company, some organization or even from freelancer individuals who are specialists in content writing. SEO writing services help a lot to promote a blog thereby resulting into huge volumes of profits for the webmasters.

Content writing project companies and people with article writing techniques work side by side for the good of webmasters. What is the right approach to hire content writing services? Here is the answer.....one may search such expertise from the internet and find out various skillful and talented individuals as well as some companies who are willing to contribute or who are expert in providing their services for SEO writing at the right time with perfect quality.

Source: http://ezinearticles.com/?How-a-Content-Writing-Service-Can-Help-You&id=5613919

The Art of Quality Content Writing

Content writing is a form of writing which is used in order to provide the web businesses with creatively written articles and information about them and their products. These articles can be written on any niche or topic depending on the owner of the website. Another term that might be easier to describe them with is web content.

Content can be written in various ways and for various topics. Blogs, newsletters, SEO articles and even press releases are all part of content writing. It is the cornerstone for any website. The key element to follow while writing content for any entity is quality. Articles that are written with good quality content rate higher in the reader's eye. Research plays a significant role as well. The uniqueness of the content makes it more attractive in the eyes of the curious reader.

Along with quality comes presentation. Depending on the target audience the content should be presented in an articulate manner. An easy to read layout is both pleasing and understandable. For writing SEO articles the correct use of keywords is vital for its search engine ranking. Though, extreme use is not advisable as it will not only spoil the texture of the article but would also harm its ranking.

Besides quality content writing and intriguing presentation, emphasis on diversification of content should also be laid on. This can be done by content management on a regular basis. Adding new articles every now and then can help in retaining reader's interest. The articles should be plagiarism free and original. Since its not viable for everyone to up-to date their website on a regular basis, the use of professional writing services or freelance writers comes in handy.

They are dedicated and creative individuals who can provide content writing services for your website or blogpost at affordable rates. Outsourcing content writing to other writers helps in getting not only variety in the articles, but also creativity.

Source: http://ezinearticles.com/?The-Art-of-Quality-Content-Writing&id=4864878

Why Focus on Content Writing?

The marketing of content means conveying the messages about services and products sold on the website. The strategy comes in when someone creates content by keeping in mind what exactly the purpose of article writing is. Randomly created articles and blog posts might look having too many words and density and the website may get traffic but then if you see the conversion, there are not satisfactory results. This is because, when someone does not know what the purpose is, the content writing and affiliate marketing confuses the visitor and this ends in vain. No matter how the company approaches content writing but then content written without any strategy is something whistling in the dark.

Making the website top on search engine result pages is not an overnight or over the week's task. One has to have creative and SEO friendly articles as content on the website along with many other SEO stuff to make the website visible to hundreds of thousands of people every time they search the niche keywords.

There are many companies that offer content writing, article writing, affiliate marketing, SEO friendly articles and other web supporting services. If we primarily talk about content writing services as offered by professionals, the content they deliver keeps website's regular communication focused along with virtual traffic and good conversion ratio. Precisely written content may help website building an attentive community and gain good business. Also, regular posting of articles turn the website or blog into an information resource that again bring genuine and targeted traffic.

Masters in providing SEO friendly articles can improve the search engine rankings of website. Hence, the owner of the website markedly notes the conversion rate. The firm hired for content writing services and affiliate marketing can also get you quality incoming and inbound web links that makes the webmaster an authority figure, the figure which is more trustworthy. It is also seen that professional article writing firms give a reason to prime visitors to stay longer on the site or blog.

When you decide to outsource content writing and approach professional article writing company, you may expect the following things:-

1. Precisely written SEO friendly article would strengthen the brand.

2. Keyword rich article can create new communication channels.

3. Wisely written articles with key-phrase in proper density target particular audience, which quickly responds to the content and become customers.

4. Experienced content writing team at such companies can get you articles that results in immediate queries and feedback from the visitors.

To take your website to skyrocketing growth, you must constantly need communicating with clients using precisely written articles and content. Keyword rich articles or the content on the website can deliver the thoughts to visitors and attract new comers. Website marketing and networking is only possible with professional content writing at the helm.

You can write content with your own or can hire the professional content writing firm or article writing company. No matter which corner of the world you are sitting in, you can surely contact genuine content writing service providers. All you need is just to make them understand the requirement of writing content. Once they are convinced, they can render you with best quality plagiarism free content and articles.

Source: http://ezinearticles.com/?Why-Focus-on-Content-Writing?&id=5539198

Content Writing India and Its Importance

Just browse through any job portal or jobs classifieds, you'll find one thing in openings in Content Writing. Yes, content writing has become the hottest job not only in India but worldwide. And you'll be amazed to know that majority of the content writing work is outsourced to India. But, what is content writing? This is the most common question asked by many. Literally, content writing is nothing but writing for the web. Ironically, it sounds easy but to be honest it is not. It is not only lucrative but also a time and brain consuming job. Ask any content writer, he or she will show you the correct picture. However, the crux is that if you want to be a content writer, you should be strong in research. Research is the base of any content writing and it is said that content writing involves, 10% actual writing time and 90% research.

It is obvious to know that for whom you're writing and what the motive of your written piece is. This is in return lessening the burden and makes the task easier. Even though you are racing against the time or deadline, the complete process of content writing should be done with calm and relaxed mind. As mentioned above, prior beginning with content writing, a content writer should ask three main questions to himself. That is:

1- Who is my target reader?

2- Why should they read it?

3- What can this written piece generate?

If a content writer is able to answer these three questions, then it won't be wrong to say that half the battle is won. Content writing in India is in full bloom and people from all over the world are outsourcing content writing to India. Mainly because of the strong word power and great command over vocabulary. It is said that content writing should be done by keeping the target audience and SEO in mind. This is the reason why SEO content writing is catching up these days. Anyone who is into online business wants to capture majority market and for that they have started taking content writing and SEO seriously.

Content writing and SEO goes together and none of them is complete if anyone of them is missing. Earlier any online trader or entrepreneur used to concentrate more on their website design and layout but now the trend is changing. As nowadays they emphasize more on content and SEO. According to them, if their website isn't that attractive, but still appearing on major search engines, they are ought to get more business.

Thus, we can say that content writing should be done in keeping SEO in mind and also it won't be wrong to say that India is the best and hottest place to outsource any content writing and SEO work.

The author is budding web content writer and also experienced in writing quality content for various websites. You can view his blog for more information.

Source: http://ezinearticles.com/?Content-Writing-India-and-Its-Importance&id=282974

Outsourced Content - 3 Mistakes to Avoid When Setting Up Your Project

The merits of soliciting outsourced content writing are plenty and include such extravagances as streamlined business operations and more free time. In order to get the most bang for your buck, however, there's a few no-no's to consider when it comes to outsourcing content. Here are 3 of the most common mistakes:

Mistake #1: Poor keyword research. Keywords are the phrases by which your readers find you. It doesn't make any sense to pay for outsourced content if you haven't done your research on best converting keywords and their associated terminology beforehand. After all, your writer is paid to write, not to be a marketing guru, and if you don't select the right words that grab your target demographic from the get-go, your organic traffic campaign has failed before it's even left the gate.

Mistake #2: Poor communication about what you want. As in all things, good communication between team members is critical to success. If you simply hand over a list of words to an outsourcer and tell them to give you some content, you can't be upset when you don't get what you want.

Before you hire a content writer, take some time to plot out what you want. Do you have a formatting or writing style preference? Is there a specific target audience your writer will be speaking to? Are there specific places you'd like them to use for research? Is there a specific slant you'd like them to give your topic, or a product name you'd like them to avoid? Communicating with your writer about these preferences before they even start writing is the best method of ensuring that you are satisfied with your end product.

Mistake #3: Paying for anything less than high quality, unique and timely material. It should go without saying that there is no point in paying for content that is poorly written, unedited, outdated or ripped off from somewhere else. Your content is representing your business, and the quality of that content speaks volumes to your potential customer about the overall quality and value of your company. Invest your money ONLY in content that portrays your intentions and business accurately.

When paying for outsourced content, you want material that you can use again and again because it offers real value to your reader. With a little due diligence beforehand, and care in the selection of your content writers, you can give your organic traffic campaign the best possible chances of success from the get-go!

Give your online campaign the boost it deserves with high-quality, professional content you can be proud of. Timeliness, widespread promotion and unique content created just for you is what Content Divas thrives on. Find out what the Divas can do for you.

Source: http://ezinearticles.com/?Outsourced-Content---3-Mistakes-to-Avoid-When-Setting-Up-Your-Project&id=4183023