R function to add a hierarchical column in order from multiple rows to columns?

Question

I'm using the below sample data as my input.

5 Columns - 1) Part_ID 2)Station_ID 3)Time 4)Parameters 5)Values

Sample.CSV

<table><tbody><tr><th>Part_ID</th><th>Station_ID</th><th>Time</th><th>Parameters</th><th>Values</th></tr><tr><td>A_01</td><td>S_01</td><td>4/30/19 3:00 PM</td><td>ABC_01</td><td>10</td></tr><tr><td>A_01</td><td>S_01</td><td>4/30/19 3:05 PM</td><td>ABC_02</td><td>12</td></tr><tr><td>A_01</td><td>S_01</td><td>4/30/19 3:10 PM</td><td>ABC_03</td><td>11</td></tr><tr><td>A_01</td><td>S_01</td><td>4/30/19 3:15 PM</td><td>ABC_04</td><td>10</td></tr><tr><td>A_01</td><td>S_02</td><td>4/30/19 3:20 PM</td><td>XYZ_01</td><td>15</td></tr><tr><td>A_01</td><td>S_02</td><td>4/30/19 3:25 PM</td><td>XYZ_02</td><td>9</td></tr><tr><td>A_01</td><td>S_02</td><td>4/30/19 3:30 PM</td><td>XYZ_03</td><td>11.5</td></tr><tr><td>A_01</td><td>S_02</td><td>4/30/19 3:35 PM</td><td>XYZ_04</td><td>12.5</td></tr><tr><td>B_01</td><td>S_01</td><td>4/31/2019  3:00:00 PM</td><td>ABC_01</td><td>5</td></tr><tr><td>B_01</td><td>S_01</td><td>4/31/2019  3:06:00 PM</td><td>ABC_02</td><td>6</td></tr><tr><td>B_01</td><td>S_01</td><td>4/31/2019  3:12:00 PM</td><td>ABC_03</td><td>7</td></tr><tr><td>B_01</td><td>S_01</td><td>4/31/2019  3:18:00 PM</td><td>ABC_04</td><td>4.5</td></tr><tr><td>B_01</td><td>S_02</td><td>4/31/2019  3:24:00 PM</td><td>XYZ_01</td><td>5.5</td></tr><tr><td>B_01</td><td>S_02</td><td>4/31/2019  3:30:00 PM</td><td>XYZ_02</td><td>9</td></tr><tr><td>B_01</td><td>S_02</td><td>4/31/2019  3:36:00 PM</td><td>XYZ_03</td><td>6</td></tr><tr><td>B_01</td><td>S_02</td><td>4/31/2019  3:42:00 PM</td><td>XYZ_04</td><td>7</td></tr></tbody></table>

I want to transform this data into below output data format as attached,

But When I tried to use tapply() function in R to transform my input data,

Sample_Data = read.csv("C:\\Users\\Downloads\\Sample.csv")

OutPut_Table = with(Sample_Data, tapply(Sample_Data$Value,
                                              list(COMPOSE_ID=Sample_Data$Part_ID , PARAMETERS=Sample_Data$Parameters)                                            , tail, 1))

I'm getting the data as

Could you help me in how to add the hierarchical columns (Station_ID & Time) in between each set of parameters columns. Thanks

It's easier to help you if you include a simple [reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) with sample input and desired output that can be used to test and verify possible solutions. Pictures of data are not helpful. Share data in a reproducible format so we can run and test code. — MrFlick, Sep 05 '19 at 16:00

score 1 · Answer 1 · answered Sep 05 '19 at 16:57

This is basically a question of going from "long" format to "wide" format. There are a number of ways of doing this. I like the reshape2 package, which uses the cast() function to go from long format to wide format. Since there's no reproducible example here, I'll just point you to some informative materials, like the reshape2 reference manual and this helpful blog post.

R function to add a hierarchical column in order from multiple rows to columns?

1 Answers1