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

Java: Cost of Volatile Variables

Introduction Use of volatile variables is common among Java developers as a way of implicit synchronization. JIT compilers may reorder program execution to increase performance. Java memory model[1] constraints reordering of volatile variables. Thus volatile variable access should has a cost which is different than a non-volatile variable access. This article will not discuss technical details on use of volatile variables. Performance impact of volatile variables is explored by using a test application. Objective Exploring volatile variable costs and comparing with alternative approaches. Audience This article is written for developers who seek to have a view about cost of volatile variables. Test Configuration Test application runs read and write actions on java variables. A non volatile primitive integer, a volatile primitive integer and an AtomicInteger is tested. Non-volatile primitive integer access is controlled with ReentrantLock and ReentrantReadWriteLock  to c...
Post a Comment
Read more