This month I’ll complete 1 year riding with Uber. The process of booking a ride and the response time has been amazing. This post however is on a different matter. As a data aficionado I was curious on the various locations I had travelled over the year and the cost each month. Heading over to the Uber API docs was a disappointment as Uber does not provide any api for getting the ride history data. My next plan was to scrape the data from Uber pages using PHP or Python. Just when I was going to start the project to scrape Uber trip data, a little Google search returned a nice bookmarklet by @ummjackson that scrapes the data and exports it to a CSV file.
(more…)
data
Data Quality in web applications
There is nothing painful than having to rebuild a database after you find that the records in the database are corrupted. Data corruption in databases can take many forms – from allowing inaccurate data to be entered to data field truncation due to inadequate field length. This post gives an introduction to the data quality issues you will encounter when designing database applications.
(more…)
Quick way to display CSV files as HTML tables
During database projects I frequently need to export certain views or intermediate results as CSV files. These are further used for importing into other databases. However, many times it is required to display the CSV files in web pages as HTML tables. As most times the data is generated on the fly it requires some JavaScript to turn the CSV data to HTML. One of my current favorite is the csv-to-html-table library. This allows you to easily integrate CSV files into your web pages.
(more…)
Semantic Normalization of data
Every database programmer knows about database normalization – the process of organizing the columns and tables a relational database to minimize data redundancy. Semantic Normalization is the process of reshaping free form entered data. I first encountered the concept in Nathan Marzs Big Data: Principles and best practices of scalable realtime data systems
(more…)
5 Tools to visualize database schemas
Database schema visualization is an essential process during the initial project design phase. Also, it can be useful when you need to reverse engineer a big schema and need to understand the various relationships between different tables. Below are given various database diagramming and visualization tools that will help you make sense of your complex database designs.
(more…)
SQL like query on CSV and other tabular data
CSV files are ubiquitous in the programming world and most data interchange is done using the CSV format. One problem however is that you cannot quickly check for certain rows depending on a particular field criteria. As an example, say you need to find all the rows from a CSV file that has a particular value in a field. The most probable path would be to import the CSV in a spreadsheet and then process it to find the particular information.
In this post we will look into a command-line tool that allows you to query CSV data with SQL like syntax. q – Text as Data is a command line tool that allows direct execution of SQL-like queries on CSVs/TSVs (and any other tabular text files). You can look at q as a meta-tool which provides access to all the data-related tools that SQL provides (e.g. expressions, ordering, grouping, aggregation etc.), but on tabular data files.
(more…)
Data cleaning in PHP applications
One of the important tasks in any web application is proper sanitization and standardization of data. Any data stored in a database should be in a standardized format, specially data that comes from a variety of sources.
Scrubbers or data cleaners are an important part of the data transformation process. Whenever you are involved in some data import or export process, data scrubbers can help you clean and standardize your data elements before storing.
(more…)
Calculating descriptive statistics in MySQL
Descriptive statistics can be quite useful for simple analysis of records in a database. For example, to calculate average numbers of sales or products for a particular duration, or the Variance of sales for a month etc.
We can easily calculate standard descriptive statistic measures in MySQL such as MEAN, SUM, STANDARD DEVIATION, VARIANCE, MIN and MAX using built-in functions. Below is a sample student table with student id and scores which we will use for calculating descriptive statistical measures.
(more…)
Migrating legacy data to WordPress
With an increasing number of users migrating to WordPress, moving data from an existing CMS to WordPress has become an essential process in the transition. Although migrating small databases from another CMS can be done in a few hours, large databases can take days or even weeks.
In one of my recent endeavors of the same, I had to migrate a relatively small user database from an old CMS to WordPress. Although a small task, some points tripped me and took a little longer than planned. Below are some of the points and recommended tools to consider when migrating existing databases to WordPress.
(more…)
Efficiently Replicating RETS data to MySQL
A recent project of mine entailed replicating a RETS database on a local MySQL database server. The client had a new real-estate mobile search app in development and wanted to have a local copy of the RETS database for search queries instead of a remote RETS server. This approach has several advantages:
- Response times for accessing your local database are significantly faster than connecting to a remote RETS server for each request.
- RETS servers requires that you query using certain combination of fields and also limits the number of fields you can search on. Using a local database would allow you to search on any field with any combination.
- RETS servers also limit the time of day you’re allowed to access the RETS server and also restricts how much queries you can run per hour. You may also be limited to the number of records you can retrieve per query from a RETS server while a local database wouldn’t have that limit.