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
Sum
2
3
idx 49.865 30.071 blkqt
ywhw 17.909 96.138 nqng
odb 69.900 34.593 hcf
-1

Count
3
2
79.424 bwipx eqmz 15.800
61.570 uhaci fifo 36.933
9.881 alt cjven 63.373
31.110 mpcq pdg 9.170
-1

Average
3
2
76.114 69.394 nuvr 1.992
80.233 5.676 kxf 2.783
1.940 85.761 zlnv 5.537
74.016 79.417 atp 89.162
-1
Invert
2
3
cky foq 57.193 80.148
mkf kjqs 15.449 43.623
ohu turnf 95.211 57.271
-1 

17.909 97
49.865 31
69.9 35

cjven 1
eqmz 1
fifo 1
pdg 1

atp 80
kxf 6
nuvr 70
zlnv 86

foq 57.193 # #
kjqs # 15.449 #
turnf # # 95.211