Skip to main content

Convert Tabular Data to Matrix Form Data

 

1.   Introduction


Select count(*), category, subcategory from table where somecontions=true group by category, subcategory

Suppose you extracted data from database which contains data according to a category and a shared subcategory. You need to display this data with an excel chart. Unfortunately it is not easy to build a chart using structure of data.

It is required to reorganize this data to easily build a chart out of this data.

2.   Problem Definition



Category is hour. Sub-category is status. This table contains hourly status values of some variable. To use this data in excel to create a chart, data needs to be re-organized.
New table should have a matrix like layout. Row of the matrix should contain category values while Column of the matrix needs to contain sub-category values. Resulted table should look like picture below.

3- Solution Implementation


A simple java application is developped to help us do required conversion of data. After conversion it is easy to create a chart to display data.

To use the application donwload the application and run it using the bat file provided. Before running the but file make sure it is modified to contain appropriate command line argument values.

Argument Name
Definition
file
name of csv the file to parse
row
name of the csv column to convert to excel row
column
name of the csv column to convert to excel column
value
name of the csv column to use as excel cell value
coltypes
comma separated list of csv column types. Supported types are 'i' for integer,'s' for string, 'f' for floats and 'd' for date values.
colLabelMap
comma separated mappings list to use as label for values contained column which is pointed with 'column' parameter.

After running bat file with correct arguments a xlsx file is created which contains data in desired format. Use this file to create chart:
1.       Format the table



2.       Create chart


3.       Chart is ready

 




4.   Conclusion


Java application can be downloaded from this link.



Labels:

Comments

Post a Comment

Popular posts from this blog

Obfuscating Spring Boot Projects Using Maven Proguard Plugin

Introduction Obfuscation is the act of reorganizing bytecode such that it becomes hard to decompile. Many developers rely on obfuscation to save their sensitive code from undesired eyes. Publishing jars without obfuscation may hinder competitiveness because rivals may take advantage of easily decompilable nature of java binaries. Objective Spring Boot applications make use of public interfaces, annotations which makes applications harder to obfuscate. Additionally, maven Spring Boot plugin creates a fat jar which contains all dependent jars. It is not viable to obfuscate the whole fat jar. Thus obfuscating Spring Boot applications is different than obfuscating regular java applications and requires a suitable strategy. Audience Those who use Spring Boot and Maven and wish to obfuscate their application using Proguard are the target audience for this article. Sample Application As the sample application, I will use elastic search synch application from my G...
4 comments
Read more

Hadoop Installation Document - Standalone Mode

This document shows my experience on following apache document titled “Hadoop:Setting up a Single Node Cluster”[1] which is for Hadoop version 3.0.0-Alpha2 [2]. A. Prepare the guest environment Install VirtualBox. Create a virtual 64 bit Linux machine. Name it “ubuntul_hadoop_master”. Give it 500MB memory. Create a VMDK disc which is dynamically allocated up to 30GB. In network settings in first tab you should see Adapter 1 enabled and attached to “NAT”. In second table enable adapter 2 and attach to “Host Only Adaptor”. First adapter is required for internet connection. Second one is required for letting outside connect to a guest service. In storage settings, attach a Linux iso file to IDE channel. Use any distribution you like. Because of small installation size, I choose minimal Ubuntu iso [1]. In package selection menu, I only left standard packages selected.  Login to system.  Setup JDK. $ sudo apt-get install openjdk-8-jdk Install ssh and pdsh, if...
3 comments
Read more

How To Use Keras Trained CNN Models

Introduction Keras is a popular deep learning api. It can run on top of Tensorflow , CNTK and Theano frameworks. Keras provides an easy to use interface which makes deep learning practice straight forward. It is widely used thus resources are easily accessible. Objective This article aims to give an introductory information about using a Keras trained CNN model for inference. This article does not contain information about CNN training. Audience This article assumes introductory information about python and Convolutional Neural Networks. For those who lack information may first begin with information from following resources. For python use  Python For Beginners For Convolutional Neural Networks use  CS231n Convolutional Neural Networks for Visual Recognition Software Installation Keras is a high level API. It requires a back-end framework to be installed. In this article, Tensorflow is used. Keras can transparently select CPU or GPU for processing. If use ...
2 comments
Read more