Solved

Columns to Rows

  • 29 January 2024
  • 2 replies
  • 55 views

Badge +1

Hi, I have my input like below example. May I know how can I transpose the data from columns to rows? 

Input Data: 

Pilot_Yr10 Vermont_Yr10 Pilot_Yr9 Vermont_Yr9 Pilot_Yr8 Vermont_Yr8
2014 2014 2015 2015 2016 2016
9.67 9.67 -3.76 -3.76 18.4 18.4
9.02 9.02 -4.33 -4.33 17.7 17.7
13.69 13.69 1.41 1.41 11.96 11.96

 

Expected Output Data: 

 

Period Pilot Vermont
SI Since Inception 1/1/91 Since Inception 1/1/91
Yr1 2023 2023
Yr10 2014 2014
Yr2 2022 2022
Yr3 2021 2021
Yr4 2020 2020
Yr5 2019 2019
Yr6 2018 2018
Yr7 2017 2017
Yr8 2016 2016
Yr9 2015 2015
icon

Best answer by Pele 30 January 2024, 18:14

View original

2 replies

Badge

Hi @Raj Yalaka,

if I understand correctly, you just need to take the first data line (or 2nd line if we include the header line) of your input table (values that look like years) and transpose it to a 3 column output with column names: Period, Pilot, Vermont.

I suggest you build the following step composition in your plan/component:

  1. Text File Reader to read your CSV
  2. Alter Format to “add_output_columns” with the relevant output names and data types (add Period, Pilot, Vermont)
  3. Multiplicator step (name it e.g. “fork_periods”)
  4. set of Column Assigner steps for each mapping of the input column (name it e.g. map_yr1, map_yr2, map_yr3, ...) - add as many Column Assigners as you need to map the triplets of your output attributes inside each Column Assigner step using the following 3 assignments:
    1. map Pilot_Yr1 attribute to output attribute called Pilot
    2. map Vermont_Yr1 attribute to output attribute called Vermont
    3. map a respective string constant “Yr1” to output attribute called Period (don’t forget to adjust the constant when copy&pasting the CA Steps, and check the same for Pilot and Vermont mapping)
  5. Union Same - merge all the flows from your Column Assigners to a UnionSame step (not just plain Union) and name it e.g. sink_periods
  6. Use the union flow as you need / e.g. Text File Writer

This way you “generate” the additional lines you need from your “single” line input. What is not entirely clear to me is the first data line in the output table with

SI Since Inception 1/1/91 Since Inception 1/1/91

 

Maybe you will need another specific Column Assigner step to map it with this content as a static text (similar to Period mapping above).

 

Hope this helps.

Regards,

Petr

Userlevel 6
Badge +7

Hi @Raj Yalaka - I’m closing this thread for now. If you have any questions please feel free to follow up here or create a new post 🙋‍♀️

Reply