# Transpose columns to rows

**URL:** <https://discourse.julialang.org/t/transpose-columns-to-rows/105508>\
**Category:** New to Julia\
**Tags:** excel\
**Created:** [October 28, 2023, 2:40pm UTC](https://discourse.julialang.org/t/transpose-columns-to-rows/105508 "2023-10-28T14:40:12Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![davide445](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/davide445/32/33681_2.png) [@davide445](https://discourse.julialang.org/u/davide445)\
**Post date:** [October 28, 2023, 2:40pm UTC](https://discourse.julialang.org/t/transpose-columns-to-rows/105508/1 "2023-10-28T14:40:12Z")

</div>

To prepare the import into an ERP system of a list of customers (identified from an id) and their related 10 years historical usage data of a system, I needed to shift the data from

customer1, y1:value1, y2:value2, … y10:value10  
customer2, y1:value1, y2:value2…  
…

to  
customer1, y1:value1  
customer1, y2:value2  
…  
customer1, y10:value10  
customer2, y1:value1  
…

,  
There are 889 customers in the file, not finding a way to do this in Excel even usig chatgpt I decided to retrieve from the dust my Julia installation and try to do this excercise.

I come up with this solution, for the sake of knowledge wanted to kindly ask if there is (I’m sure) a better and easier way to get this done

```julia
using DelimitedFiles

inv = DelimitedFiles.readdlm("/home/Documents/Cumulative_import.csv", ',';header=false, skipstart=0)

work = zeros(Float64, 8890,2)

for i = 1:10
    work[i,1]=inv[1,1]
    work[i,2]=inv[1,2]
end

for i = 2:889
    for j = ((i-1)*10)+1:((i-1)*10)+10
        work[j,1] = inv[i,1]
    end
end

v=inv[1,2:11]

work[1:10,2]=v

for i = 2:889
    v=inv[i,2:11]
    for j = ((i-1)*10)+1:((i-1)*10)+10
        if mod(j/10,1) > 0
            k=trunc(Int,round(mod(j/10,1);digits=1)*10)
        else
            k=10
        end
        work[j,2]=v[k]
    end
end

DelimitedFiles.writedlm("/home/Documents/Cumulative_import_export.csv", work,',')

```

If someone is interested I can provide the origin csv and final result.

---

<div class="post-metadata">

**Author:** ![aplavin](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/aplavin/32/222056_2.png) [@aplavin](https://discourse.julialang.org/u/aplavin)\
**Post date:** [October 28, 2023, 3:03pm UTC](https://discourse.julialang.org/t/transpose-columns-to-rows/105508/2 "2023-10-28T15:03:38Z")

</div>

Probably the simplest no-dependency solution:

```julia
work = mapreduce(vcat, eachrow(inv)) do row
    stack(row[2:end], dims=1) do value
        (row[1], value)
    end
end

```

(using your `inv` variable, and writing to the `work` variable you want)

---

<div class="post-metadata">

**Author:** ![Dan](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/dan/32/42581_2.png) [@Dan](https://discourse.julialang.org/u/Dan)\
**Post date:** [October 28, 2023, 3:05pm UTC](https://discourse.julialang.org/t/transpose-columns-to-rows/105508/3 "2023-10-28T15:05:13Z")

</div>

Here is one function which might be fashioned into a working solution:

```julia
function inout(fname)
    for line in readlines(fname)
        fields = strip.(split(line, ','))
        for i in 2:length(fields)
            println(fields[1], ", ", fields[i])
        end
    end
end

```

With this I got:

```julia
julia> inout("in.txt")
customer1, y1:value1
customer1, y2:value2
customer1, y10:value10
customer2, y1:value1
customer2, y2:value2

```

from:

```julia
customer1, y1:value1, y2:value2, y10:value10
customer2, y1:value1, y2:value2

```

There are many ways to go about this… but very probably the problem is not so accurately specified.

---

<div class="post-metadata">

**Author:** ![davide445](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/davide445/32/33681_2.png) [@davide445](https://discourse.julialang.org/u/davide445)\
**Post date:** [October 28, 2023, 4:04pm UTC](https://discourse.julialang.org/t/transpose-columns-to-rows/105508/4 "2023-10-28T16:04:33Z")

</div>

To clarify I think will be useful to show the data.  
This is the origin data excerpt

 ![image](https://global.discourse-cdn.com/julialang/original/3X/a/1/a10058b14405fd29c833ff7577e4134af36b4884.png)  
And this the resulting output excerpt  
 ![image](https://global.discourse-cdn.com/julialang/original/3X/5/5/55898e344cf5202ca4d0072afd07c00c2f7c9093.png)

---

<div class="post-metadata">

**Author:** ![Dan](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/dan/32/42581_2.png) [@Dan](https://discourse.julialang.org/u/Dan)\
**Post date:** [October 28, 2023, 6:41pm UTC](https://discourse.julialang.org/t/transpose-columns-to-rows/105508/5 "2023-10-28T18:41:31Z")

</div>

It is best if you do this transformation in Excel (Excel is powerful as any programming language but might not be so efficient).

I think functions such as VLOOKUP() and IF() might be enough.

---

<div class="post-metadata">

**Author:** ![davide445](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/davide445/32/33681_2.png) [@davide445](https://discourse.julialang.org/u/davide445)\
**Post date:** [October 28, 2023, 7:00pm UTC](https://discourse.julialang.org/t/transpose-columns-to-rows/105508/6 "2023-10-28T19:00:00Z")

</div>

If was only shifting the position of the column data I have had no doubt working in Excel  
But need to generate new rows programmatically and I have no skill in VB or scripting language (not that in Julia I’m much more advanted, still I’m able to achieve something)

---

<div class="post-metadata">

**Author:** ![Dan](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/dan/32/42581_2.png) [@Dan](https://discourse.julialang.org/u/Dan)\
**Post date:** [October 28, 2023, 7:02pm UTC](https://discourse.julialang.org/t/transpose-columns-to-rows/105508/7 "2023-10-28T19:02:20Z")

</div>

Well, Excel has many tricks (and I picked up just a few of them).  
In any case, I’ve managed to make the transformation you wanted in Google Sheets (very similar), and it used: IF, VLOOKUP, MATCH, INDEX functions.  
I’ll try to add a bit of details in a bit.

The data is in a table $A$1:$J$21 …

Then on row 23, below the table, the first row has:

```julia
A23 B23 C23
21002342 2 =vlookup(A23,$A$1:$J$21,B23,0)

```

which are the first customer, column number with first data and formula to obtain that data.

From then on, all the rows contain:

```julia
A24 B24 C24
=if(B23=10, index($A$1:$A$21,match(A23,$A$1:$A$21)+1), A23)
              =if(B23=10,2,B23+1)
                          =vlookup(A24,$A$1:$J$21,B24,0)

```

The 10 in the first two formulas is the number of the last column with data (and a trigger to move on to the next row).

I hope you can understand this, because it is quite excruciating to write down these formulas in this post.

---

<div class="post-metadata">

**Author:** ![rafael.guerra](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/rafael.guerra/32/216610_2.png) [@rafael.guerra](https://discourse.julialang.org/u/rafael.guerra)\
**Post date:** [October 28, 2023, 7:46pm UTC](https://discourse.julialang.org/t/transpose-columns-to-rows/105508/8 "2023-10-28T19:46:53Z")

</div>

You could also try `DataFrames,jl`:

```julia
using CSV, DataFrames
inv = CSV.read("input.csv", DataFrame; header=false)
sort(stack(inv,2:11), 1)

```

---

<div class="post-metadata">

**Author:** ![rocco\_sprmnt21](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/rocco_sprmnt21/32/20127_2.png) [@rocco\_sprmnt21](https://discourse.julialang.org/u/rocco_sprmnt21)\
**Post date:** [October 29, 2023, 3:57pm UTC](https://discourse.julialang.org/t/transpose-columns-to-rows/105508/9 "2023-10-29T15:57:56Z")

</div>

You could also do this (it being understood that I prefer @rafael’s solution): line up the columns from 2 to the last one after the other; you make 10 copies of the index column and, finally, you put them side by side.

```julia
values=reshape(inv[:,2:11],:,1)
ids=repeat(inv[:,1],10)
res=[ids values]

```

---

<div class="post-metadata">

**Author:** ![davide445](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/davide445/32/33681_2.png) [@davide445](https://discourse.julialang.org/u/davide445)\
**Post date:** [October 30, 2023, 6:37pm UTC](https://discourse.julialang.org/t/transpose-columns-to-rows/105508/10 "2023-10-30T18:37:23Z")

</div>

I discovered the produced csv has some formatting problem, so was impossible to load the data correctly in Excel.  
In the end I was able to generate new rows using this simple method

[![](https://global.discourse-cdn.com/julialang/original/3X/f/e/fe15fec7fee03bbbf15a1434f34547167e177b52.jpeg "How to insert multiple blank rows in-between rows of data (Excel)") ](https://www.youtube.com/watch?v=rKdCAGDRXXs)

using TRANSPOSE to shift data from column to rows, and also copying the data in the block of 10 rows using this way

> **[How to Repeat Formula in Every nth Row in Excel (2 Easy Ways)](https://www.exceldemy.com/excel-repeat-formula-every-nth-row/#1_Use_of_Fill_Handle)**
>
> 2 easy ways to repeat formula in every nth row in Excel. Download the workbook, modify data, and practice to learn the methods.

and also a combination of IF and INDIRECT with some external ID to regenerate the ids in between

 ![image](https://global.discourse-cdn.com/julialang/original/3X/2/5/25950a9abc3b33349b1b04e372503c740ccd2b26.png)  
Testing again julia was fun, but in the end for this small activty Excel was more productive