When working with the computer, in some cases, you have to deal with large amounts of data that must be made as readable as possible. These lists can be imported from an external database but can also be copied from a web page. For example, when you want to bring the report of the banking movements of the last year to your computer, downloading or copying it from the web pages of your online bank, you generally get a csv file or a text file, often poorly formatted and difficult to read or print. Another example would be the list of music that you have on your computer or a phone book. In the business environment, however, you can find yourself with large amounts of data to be summarized and made more readable for quick reference. All these activities of manipulation of aggregate data, you can certainly do with Excel but, much more simply, you can use some truly innovative and productive free programs .
The newest program is Open Refine, an open source program from Google that allows you to read even a huge set of disordered data and put them in place, so that they are uniform and can be copied and analyzed statistically. Before using Open Refine it is better to watch the three videos published on the download page to understand the main functions that I summarize briefly. Refine can be downloaded for free and installed on Windows, Linux or Mac PCs.
For example, when copying and pasting data into a table from a list on a web page, it can be pasted into an Excel sheet or text file, and it can be verified that it is almost illegible and you must put them back. Even in cases where a download link for the csv file is available, there would still be problems managing the data in a row, above all to modify it consistently. Here then you can use Open Refine, which allows you to import this data sheet and to manipulate it quickly and easily by creating a table from raw and copied data.
As you can see in the videos, you can use the " Text Facet " function to see the summary of the data of a column and to manage it in an aggregate way, thus unifying the badly written ones and deleting the invalid ones. The Trim function allows you to rename groups of records all together.
Regular expressions can then be created to rename data uniformly, with precise rules. With Cluster you can unify entire groups of data, summarizing them with two clicks. For numerical values it is possible to do addition, average and create graphs. All these operations on tables and lists that are usually done with Excel, can be managed with Open Refine in a much faster and easier way.
What prompted me to dedicate an article to this technical tool and what I would like to understand is that it is not a program intended only for companies or for those who work with huge databases . In fact, Refine can be used to quickly sort any list that comes from copying and pasting from a web page and to create tables by copying lists from any source. For example, you could go to Wikipedia, copy the list of American actors and quickly put them in a table with the data transformation tool. At advanced levels then you can create tannte programmable expressions to extract data from web applications such as Google Maps or others.
Open Refine is a program that does not replace Excel but allows you to manage data and tables that come from other sources.
Another free program for Windows, Mac and Linux is CleanHaven which is used to copy and create tables as well as to clean up lists, data and texts. Not only can you convert the text by transforming the capital letters to lowercase or by canceling the non-printable characters but you can also, for example, aggregate lines separated by carriage returns, filter words, merge columns of a table, search and replace text, removals of empty spaces and correction of punctuation. CleanHaven also has a nice clean interface with menus that are easy to understand and use. CleanHaven supports tabular view, so you can paste any text or list from Excel, from a CSV file, or any other source and view these in CleanHaven. The first row can automatically become the column heading and the data can be sorted easily with a click.
Both Refine and CleanHaven can manage a large number of tables and become two useful programs for everyone for basic operations of copying and pasting tables from the internet and indispensable for those who work with Excel sheets, for those who do accounting, for those who do cost analysis, warehouse, archiving, secretariat and project management.
