## Sunday, July 31, 2016

### TCS Code Vita Season-5 Round-1 set-2 Program-7

Problem : Pivot Table
According to Wikipedia, a Pivot Table is a useful summarization tool in data processing. Your task is to implement a Pivot tableaccording to given requirements

Requirements
1.    Must work against a data table of arbitrary sizes
2.    Data types to be supported are String and Numbers
3.    Must support the following aggregation functions - Sum, Count, Average, Invert (transpose)
4.    Must take inputs on which column to use as a Pivot and which column to use an aggregation function on
5.    The aggregated Pivot table must be sorted in ascending order
a.     For string data type, sorting function must sort rows, alphabetically
b.    For numeric data type, sorting function must sort rows from smallest to largest

We explain the aggregation functions using example for better understanding of the specifications Lets' say, the data is as follows

 A B C D Match1 Sachin 2 12.678 Match4 Rahul 23 14 Match5 Sonal 13 35.333 Match6 Rajiv 6 56.33 Match2 Manish 87 37 Match6 Nakul 34 45 Match2 Mukul 48 12.87 Match7 Srinath 24 29.625 Match1 Dora 83 51.667 Match8 James 79 59 Match4 Munna 45 53.333 Match6 Sachin 53 52.5 Match3 Sonal 21 58.93 Match2 Rajiv 69 61.5 Match7 Nakul 36 63.75 Match9 Mukul 96 56.222 Match5 Dora 43 51.714 Match3 Munna 80 49.417

Sum Function

• Can only be applied to Numeric columns i.e. C and D in this case
• Let's say we are grouping on column B and applying Sum function on column C then, the output will look as follows :

Dora 126
James 79
Manish 87
Mukul 144
Munna 125
Nakul 70
Rahul 23
Rajiv 75
Sachin 55
Sonal 34
Srinath 24

Count Function

• Let's say we are grouping on column A and applying Count function on column A then, the output will look as follows :

Match1 2
Match2 3
Match3 2
Match4 2
Match5 2
Match6 3
Match7 2
Match8 1
Match9 1

Average Function

• Can only be applied to Numeric columns i.e. C and D in this case
• Let's say we are grouping on column B and applying Average function on column D then, the output will look as follows :

Dora 52
James 59
Manish 37
Mukul 35
Munna 52
Nakul 55
Rahul 14
Rajiv 59
Sachin 33
Sonal 48
Srinath 30

Invert Function

• Let's say we are grouping on column B and applying Invert function on column D then, the output will look as follows :

Dora 51.667 # # # 51.714 # # # #
James # # # # # # # 59 #
Manish # 37 # # # # # # #
Mukul # 12.87 # # # # # # 56.222
Munna # # 49.417 53.333 # # # # #
Nakul # # # # # 45 63.75 # #
Rahul # # # 14 # # # # #
Rajiv # 61.5 # # # 56.33 # # #
Sachin 12.678 # # # # 52.5 # # #
Sonal # # 58.93 # 35.333 # # # #
Srinath # # # # # # 29.625 # #

The first column of output is column B in ascending order. The column A in the data table becomes column headers in the output (which is not printed in output). The non-hash values are values of column D for a combination of column A and column B in the original data table. Column A and column B combination in test cases for Invert function are unique. '#' character is a place holder for null values.

Now that the workings of the four functions have been explained, let us understand the input and output specifications.

Input Format:

First line of input contains total number of test cases, denoted by N
Each test case comprises of 5 parts-
• First line of a test case contains a function name (Sum , Average, Invert and Count)
• Second line contains the column no. on which grouping will be applied.(Column index starts from 1)
• Third line contains the column no. on which the aggregation function will be applied
• Next variable number of lines contain the actual data delimited by space
• A test case is terminated by -1

Output Format:

For each test case print the output of the appropriate function as explained above.
• Perform rounding up when printing output for Sum and Average functions.
• Do not perform rounding when printing output for Invert function.

Sample Input and Output
 SNo. Input Output 1 4 Sum23idx 49.865 30.071 blkqtywhw 17.909 96.138 nqngodb 69.900 34.593 hcf-1 Count3279.424 bwipx eqmz 15.80061.570 uhaci fifo 36.9339.881 alt cjven 63.37331.110 mpcq pdg 9.170-1 Average3276.114 69.394 nuvr 1.99280.233 5.676 kxf 2.7831.940 85.761 zlnv 5.53774.016 79.417 atp 89.162-1 Invert23cky foq 57.193 80.148mkf kjqs 15.449 43.623ohu turnf 95.211 57.271-1 17.909 9749.865 3169.9 35 cjven 1eqmz 1fifo 1pdg 1 atp 80kxf 6nuvr 70zlnv 86foq 57.193 # #kjqs # 15.449 #turnf # # 95.211