How to Do Row-To-Column Transposition of Data in CSV Table

How to do row-to-column transposition of data in csv table?

The solution in general to transpose a sequence of iterables is: zip(*original_list)

sample input:

1   2   3   4   5
6   7   8   9   10
11  12  13  14  15

program:

with open('in.txt') as f:
  lis = [x.split() for x in f]

for x in zip(*lis):
  for y in x:
    print(y+'\t', end='')
  print('\n')

output:

1   6   11  

2   7   12  

3   8   13  

4   9   14  

5   10  15

How to transpose a dataset in a csv file?

If the whole file contents fits into memory, you can use

import csv
from itertools import izip
a = izip(*csv.reader(open("input.csv", "rb")))
csv.writer(open("output.csv", "wb")).writerows(a)

You can basically think of zip() and izip() as transpose operations:

a = [(1, 2, 3),
     (4, 5, 6),
     (7, 8, 9)]
zip(*a)
# [(1, 4, 7),
#  (2, 5, 8),
#  (3, 6, 9)]

izip() avoids the immediate copying of the data, but will basically do the same.

Reading a CSV and transposing the contents into an ArrayList

So, assuming "1, 0.1, 1.1" would be the first desired string in the array list, this is the code:

public void handleCSV() {
    Path path = Paths.get("C:\\Temp\\", "loadtest.csv");

    Charset charset = Charset.forName("ISO-8859-1"); // <- the actual
                                                        // charset of you
                                                        // csv

    List<String> finalLine = new ArrayList<>();

    try {
        List<String> lines = Files.readAllLines(path, charset);

        boolean isFirstLine = true;
        int index = 0;
        for (String line : lines) {
            String[] lineContent = line.split(",");
            for (String column : lineContent) {
                if (isFirstLine) {
                    finalLine.add(column);
                } else {
                    String myEntry = finalLine.get(index);
                    myEntry.concat("," + column);
                    finalLine.set(index, myEntry);
                }
                index++; // adding an index for each element in the row
            }
            index = 0;
            isFirstLine = false;
        }
    } catch (IOException e) {
        System.out.println(e);
    }
}

This code should produce the results you want if every line has the same number of entries

Access, transpose, and join data from a .csv file

Use an array of strings for columns instead of $column1,2,3..:

    ...
    $num = count($data);
    $row++;

    for($i=0; $i<$num; $i++){
        $columns[$i] = $columns[$i] . ", ". $data[$i];
    }
    ...

How to partially transpose a CSV table in Python

Also,

df.set_index(['Product','Store']).stack().reset_index()

Output:

   Product  Store  level_2   0
0   vacuum    123   9/1/18   1
1   vacuum    123   9/8/18   5
2   vacuum    123  9/15/18   3
3   vacuum    123  9/22/18   3
4  toaster    456   9/1/18   5
5  toaster    456   9/8/18   7
6  toaster    456  9/15/18   4
7  toaster    456  9/22/18  10

With cleaned up column naming,

(df.set_index(['Product','Store'])
   .rename_axis('Week', axis=1)
   .stack()
   .rename('Sales')
   .reset_index())

Output:

   Product  Store     Week  Sales
0   vacuum    123   9/1/18      1
1   vacuum    123   9/8/18      5
2   vacuum    123  9/15/18      3
3   vacuum    123  9/22/18      3
4  toaster    456   9/1/18      5
5  toaster    456   9/8/18      7
6  toaster    456  9/15/18      4
7  toaster    456  9/22/18     10

Python3 reading data from cvs file: Converting columns to Lists

csv.reader() objects read data from the underlying file object, and file objects have file positions that move from start to end as you read. If you want to read again you need to rewind the file pointer to the start:

xVal = [row[0] for row in data]
inputData.seek(0)
yVal = [row[1] for row in data]

However, you'd be better of reading just once, and transposing the rows to columns:

xVal, yVal = zip(*data)[:2]

---

Related Topics