Transpose csv While Maintaining ID

Question

I have a fairly large comma-separated csv file with similar structure to:

ZipCd Var1 Var2 Var 3
12345 12    45   10
67890 13    23    5
  .   .     .    .
  .   .     .    .
  .   .     .    .
30010 99    17   6

For each ZipCd there are many Variables going to the right (approximately 250 total variables). I would like for the following output to be produced:

ZipCd Var Value
12345 1    12
12345 2    45
12345 3    10
67890 1    13
67890 2    23
67890 3    5
30010 1    99
30010 2    17
30010 3    6

I have attempted the following:

with open("file.csv") as f, open("out.csv","w") as out:
   headers = next(f).split()[0:] #Get first row of original csv for headers and variable names
   for row in f:
      row = row.split(",") #split row into values delimited by comma
      ZipCd = row[0]
      Var1 = row[1]
      Var2 = row[2]
      Var3 = row[3]
      data = zip(headers, row[1:])
      for a, b in data:
         out.write("{} {} {}\n".format(ZipCd,a,b))

And that produces:

12345 ZipCd,Var1,Var2,Var3 12
67890 ZipCd,Var1,Var2,Var3 13

Any help in producing the desired output would be greatly appreciated.

Answer 1

There seems to be a mixup with the input file separator. It is clearly comma, but you're splitting the titles using no argument: the title is not split, and contains all fields, comma separated.

I'm proposing a solution

• using the csv module to read the input file, much cleaner.
• accepting as many variables as wished
• using zip like you did to "transpose" data
• the nice trick for zipcd,*vars in cr is used to get zipcd as the first field and vars as the remaining fields (called Extended Iterable Unpacking aka the "*target feature" as Martineau explained in another answer today)

code:

import csv

with open("file.csv") as f, open("out.csv","w") as out:
    cr = csv.reader(f)  # default separator is comma
    variable_names = next(cr)[1:]  # ignore first field in the title line
    out.write("ZipCd Var Value\n")
    for zipcd,*vars in cr:
        for vn,vv in zip(variable_names,vars):  # interleave data
            out.write("{} {} {}\n".format(zipcd,vn,vv))

sample input:

ZipCd,Var1,Var2,Var3
12345,12,45,10
67890,13,23,5
30010,99,17,6

Resulting output:

ZipCd Var Value
12345 Var1 12
12345 Var2 45
12345 Var3 10
67890 Var1 13
67890 Var2 23
67890 Var3 5
30010 Var1 99
30010 Var2 17
30010 Var3 6