# Line up the CSV

### Overview:

Your job is to take CSV input in key=value format and line it up in a more organized (see below) way.

### Input:

Always via stdin. Records will always be in the following form key=value:

foo=bar,baz=quux
abc=123,foo=fubar
baz=qwe,abc=rty,zxc=uiop,foo=asdf

• There will be no list of possible keys in advance, you must find them in the input text.
• End of input will be signalled by EOF, whatever implementation of EOF is appropriate for your OS.

### Output:

The first row of your output will be a list of all the keys, in alphabetical order (even if the keys are all numbers). After that, print each record in the same CSV format the appropriate number heading, without the keys listed. So, for the example above, the correct output would be:

abc,baz,foo,zxc
,quux,bar,
123,,fubar,
rty,qwe,asdf,uiop


### FAQ:

• Do I have to worry about improperly formatted input?
• No. Your program may do whatever it wants (throw an exception, ignore, etc.) if the input is not correctly formatted, e.g. a line of foo,bar,baz
• How do I handle escaping special characters?
• You may assume that there will be no additional , or = in the data that are not part of the key=value format. " does not have any special meaning in this contest (even though it does in traditional CSV). is also not special in any way.
• Lines should match the following regex: ^([^=,]+=[^=,]+)(,[^=,]+=[^=,]+)*$ • Therefore, both keys and values will match [^=,]+ • What about CRLF vs. LF? • You may choose whatever delimiter is appropriate for your platform. Most languages handle this without special delimiting code. • Do I need to print trailing commas if the last few columns don't exist? • Yes. See the example. • Are CSV parsers or other similar external tools allowed? • No. You must parse the data yourself. • FAQ when no one asked questions yet. :-) – Justin Apr 21 '14 at 17:21 • @Quincunx If I ask myself the question that counts ;) – durron597 Apr 21 '14 at 17:23 • I have a feeling that's how all FAQs work. – Martin Ender Apr 21 '14 at 19:02 • Can I have a trailing comma in my list of keys and values? It would make my code a lot shorter... – PlasmaPower Apr 22 '14 at 1:18 • @PlasmaPower I don't understand the question; however, your program must exactly match the example output for the given example input – durron597 Apr 22 '14 at 4:42 ## 10 Answers ### GolfScript, 64 characters n%{','/{'='/}%}%:I{{0=}/}%.&$:K','*n{{{(2$=*}%''*\;}+K%','*n}I/  The code is a straight-forward implementation in GolfScript, you can test the example online. Annotated code: # Split the input into lines, each line into tuples [key, value] # and assign the result to variable I n%{','/{'='/}%}%:I # From each tuple take the 0'th element (i.e the key) {{0=}/}% # Take the unique items (.&), sort ($) and assign the result to variable K
.&$:K # Output: join values with , and append a newline ','*n # {...}I/: Loop over all lines of the input { # {...}+K%: Loop over all keys and initially push the current # line for each of the keys { # stack here is [current key, current line] # {}%: map to all the items of the current line { # extract the key from the current item and compare (2$=
# if equal keep [value], otherwise multiply with 0, i.e. discard
*
}%
# join the results (may be one or zero) and drop the key
''*\;
}+K%
# Output: join values of current line with , and append a newline
','*n
}I/


# Perl 6: 119 characters, 120 bytes

my@l=lines.map:{/[(\w+)\=(\w+)]+%\,/;push $!,~«@0;$%(@0 Z=>@1)}
say .join(",") for$!.=sort.=uniq,($(.{@$!}X//"") for@l)  De-golfed: my@l=lines.map: { # Parse the key=value pairs, # put all the keys in$/[0] (or $0) # put all the values in$/[1] (or $1) / [ (\w+) \= (\w+) ]+ % \, /; # Push all the keys into$!
# (@0 just means @$0 or$/[0].list)
push $!, ~«@0; # Return a hash of keys zipped into pairs with the values$%( @0 Z=> @1 )
}

$!.=sort.=uniq; # …i.e.,$! = $!.sort.uniq; # Print the CSV for the keys ($!),
# followed by the CSVs for the hashes we made for each line,
# as accessed by our sorted key list. (… .{@$!} …) # If the value doesn't exist, just use "" instead. (… X// "" …) say .join(",") for$!, ($( .{@$!} X// "" ) for @l)


# perl, 129/121

129 bytes, no command line switches:

for(<>){push@x,{%g=map{split/=/}split/[,
]/};@h{keys%g}=()}@k=sort keys%h;$"=",";sub P{print"@_ "}P@k;for$x(@x){P map{$$x{_}}@k}  As @Dennis points out below, you can get this to 120+1=121 by using -n: push@x,{%g=map{split/=/}split/[, ]/};@h{keys%g}=()}@k=sort keys%h;"=",";sub P{print"@_ "}P@k;forx(@x){P map{$$x{$_}}@k  Basically, for each line, we split by commas to get the list of pairs. For each pair, we split by the equals sign to get the key and value. We set the key/value pair in %h and a local hashref. The former is used to determine the list of keys. The latter is used to remember the values for this line. • You can save a few chars by: 1. Using the -n switch instead of for(<>){...}. 2. Splitting around [, ] instead of using chomp. 3. Omitting the semicolon after the curly brackets. – Dennis Apr 21 '14 at 21:19 • Thanks @Dennis. I've implemented the latter 2 of your suggestions. I may yet throw -n in to the mix but I'm feeling like a purist who is too lazy to type on his phone ATM :-) Also that would require an END block, but I suppose it'd still be a net win. – skibrianski Apr 21 '14 at 23:21 • Yeah, adding -n only saves 3 chars (two points) with the END block. I prefer the "purer" solution. At least until one of the other answers gets closer =) – skibrianski Apr 21 '14 at 23:48 • Perl literally wraps while (<>) { ... } around the entire script, so there's no need for an END block. Just remove for(<>){ at the beginning and } at the end of the script. – Dennis Apr 22 '14 at 0:07 • It will work nevertheless, as long as you remove the } at the end of the script, not the one corresponding to the for loop. Also, you can save one more char by using an actual newline instead of \n. – Dennis Apr 22 '14 at 0:41 # JavaScript (ES5) 191183179 168 bytes Assuming the code is run in spidermonkey command line: for(b=[a={}];l=readline(i=0);b.push(c))for(c={},d=l.split(/,|=/);e=d[i++];)c[a[e]=e]=d[i++];for(;c=b[i++];)print(Object.keys(a).sort().map(function(x){return c[x]})+[])  Result: > js test.js < input.txt abc,baz,foo,zxc ,quux,bar, 123,,fubar, rty,qwe,asdf,uiop  This shim can be used in a browser to simulate spidermonkey's readline and print: var I = 0, LINES = '\ foo=bar,baz=quux\n\ abc=123,foo=fubar\n\ baz=qwe,abc=rty,zxc=uiop,foo=asdf'.split('\n'), readline = function(){ return LINES[I++]; }, print = function(){ return console.log.apply(console, arguments); };  Ungolfed: a = {}; // this object holds all keys found b = [a]; // array key:value pairs of each line, initialized with our key holder object in position 0 for(;l = readline();){ // store each line in l, loop until blank/undefined line c = {}; // create a new object for this line's key:value pairs d = l.split(/,|=/); // split line by commas and equals for(i = 0; e = d[i++];){ // loop through each key a[e] = e; // set the key=key for key holder object c[e] = d[i++]; // set key=value for the line object } b.push(c); // push line object onto array } for(i = 0; c = b[i++];){ // loop through all line objects until undefined print( // print line Object.keys(a).sort(). // get sorted list of keys map(function(x){ return c[x] // map values from line object }) + [] // cast array to string ); }  • The question doesn't say that you have to use "stdout" - you can use alert in place of console.log and save some bytes thus. – Gaurang Tandon Apr 22 '14 at 10:34 • @GaurangTandon Then, I'd have to concatenate all the outline lines. I might update my answer to use spidermonkey command line and instead use readline and print for actual stdin/out – nderscore Apr 22 '14 at 12:16 # Bash + coreutils, 188 138 bytes p=paste\ -sd, f=cat h=tr , '\n'<<<$f|cut -d= -f1|sort -u
$p<<<"$h"
for l in $f;{ join -o2.2 -a1 - <(tr =, ' \n'<<<$l|sort)<<<"$h"|$p
}


Output:

$./lineupcsv.sh < input.csv abc,baz,foo,zxc ,quux,bar, 123,,fubar, rty,qwe,asdf,uiop$


import Data.List
o=1<2
s u|u==""=""|o=tail u
t=takeWhile
d=dropWhile
c=(/=',')
e=(/='=')
p x|x/=""=Just((t e x,t c.s.d e$x),s.d c$x)|o=Nothing
g=m(unfoldr p).lines
k=nub.sort.(m fst=<<).g
[]#_=[]
(t@(x,u):z)#n@(a,b)|x==a=n:z|o=t:z#n
i=intercalate
main=interact$\d->i"\n"$(i","$k d):m(i",".m snd.foldl(#)(m(flip(,)"").k$d))(g d)
m=map


g is doing the parsing - it splits the input into lines and maps each line to a list of (key,value) pairs. k, by concatenating all the keys into a list and removing duplicates, creates a list with all unique keys which I can later use for sorting. I do this by creating a "Set" inside main (m(flip(,)"").k$d == [("abc",""),("baz",""),("foo",""),("zxc","")]) for each line, and then taking every (key,value) pair from a line and put it where it belongs in the list (foldl). Line 1 from the example yields [("abc",""),("baz","quux"),("foo","bar"),("zxc","")], which I concatenate into a single String (",quux,bar,"), concatenate with the other lines, and print. >>> csv.exe < input.txt abc,baz,foo,zxc ,quux,bar, 123,,fubar, rty,qwe,asdf,uiop  # Python 2.7 - 242 bytes bleh import os c=[r.split(',')for r in os.read(0,99).split('\n')] k=sorted(list(set(sum([[s.split('=')[0]for s in r]for r in c],[])))) print','.join(k) for l in c: t=[''for i in k] for s in l: o,v=s.split('=') t[k.index(o)]=v print','.join(t)  Note that the second layer of indentation is a single tab character, not four spaces like SE renders it. Ungolfed: #!/bin/python2 import os # I do input as a list comprehension in the original but this is equivalent c = [] # For each line in the input for r in os.read(0,99).split('\n'): # Add a list of key=value pairs in that row to c c.append(r.split(',')) # Another thing done as a list comprehension, but I'll space it out k = [] # For each list of key=value pair s in c for r in c: # For each actual key=value pair in that list for s in r: # Get the key k.append(s.split('=')[0]) # Discard dupes by converting to set and back, then sort k = sorted(list(set(k))) # Seperate these keys by commas, then print print ','.join(k) # For each line in c for l in c: # t has one empty string for each key in the input t = ['' for i in k] # For each key=value pair in the line for s in l: # o = key, v = value o, v = s.split('=') # Find the position that the key is in the list of keys, then put the # value in t at that position t[k.index(o)] = v # Now each value is in the right position and the keys with no values on this # line have an empty string. Join everything with commas and print print ','.join(t)  # Python 3: 200195192189 187 import sys r=[dict(p.split('=')for p in l[:-1].split(','))for l in sys.stdin] x=set() for d in r:x|=d.keys() k=sorted(x) for l in[k]+[[r.get(k,'')for k in k]for r in r]:print(*l,sep=',')  ## k4 (40? 51? 70? 46?) the basic expression is ","0:{(x@<x:?,/?!:'x)#/:x}(!).'"S=,"0:/:  this both accepts and returns a list of strings to match the spec, we could interactively do -1","0:{(x@<x:?,/?!:'x)#/:x}(!).'"S=,"0:/:."\\cat";  which accepts the input from stdin and prints the output to stdout for a standalone app accepting input from a pipe, we could do this: $ cat i.k
.z.pi:{t,:\:x}
.z.exit:{-1","0:{(x@<x:?,/?!:'x)#/:x}(!).'"S=,"0:/:t}
$cat i.txt|q i.k abc,baz,foo,zxc ,quux,bar, 123,,fubar, rty,qwe,asdf,uiop$


altho if you're willing to consider my pre-existing k-as-filter wrapper, awq.k, as an acceptable tool for this kind of puzzle, then we can do this:

\$ cat i.txt|awq.k '","0:{(x@<x:?,/?!:'\''x)#/:x}(!).'\''"S=,"0:/:'


which is either 46 characters or 40, depending on how you count shell quote wrangling

• What kind of environment is needed to run this? q command? Is awq.k published somewhere? – Digital Trauma Apr 22 '14 at 20:37
• 32-bit q is now available as freeware from kx.com/software-download.php. (they used to have only a time-limited trial version for free.) hmm, it looks like awq isn't actually published anywhere; i should do something about that. – Aaron Davies Apr 22 '14 at 20:46

## C# - 369

void C(string a){var k=a.Split(new[]{',','\n'}).Select(s=>s.Split('=')[0]).OrderBy(o=>o).Distinct();var t=string.Join(",", k)+"\n";foreach(var x in a.Split('\n')){for(int i=0;i<k.Count();i++){foreach(var y in x.Split(',').OrderBy(o=>o.Split('=')[0]))if(k.ElementAt(i)==y.Split('=')[0])t+=y.Split('=')[1];t+=",";}t=t.Remove(t.LastIndexOf(','),1)+"\n";}Console.Write(t);}


Ungolfed

void C(string a)
{
var k=a.Split(new[]{',','\n'}).Select(s=>s.Split('=')[0]).OrderBy(o=>o).Distinct();
var t=string.Join(",", k)+"\n";
foreach(var x in a.Split('\n'))
{
for(int i=0;i<k.Count();i++)
{
foreach(var y in x.Split(',').OrderBy(o=>o.Split('=')[0]))
if(k.ElementAt(i)==y.Split('=')[0])
t+=y.Split('=')[1];
t+=",";
}
t=t.Remove(t.LastIndexOf(','),1)+"\n";
}
Console.Write(t);
}


Test string input

C("foo=bar,baz=quux\nabc=123,foo=fubar\nbaz=qwe,abc=rty,zxc=uiop,foo=asdf");


Output

abc,baz,foo,zxc
,quux,bar,
123,,fubar,
rty,qwe,asdf,uiop

• Just curious, this is C# so it should work in windows, but does it? (See my CRLF vs. LF FAQ question) Unfortunately I don't have a copy of Visual Studio to test with. – durron597 Apr 22 '14 at 13:05
• I originally should have added the note, but i have now. Yes it works. I've created & tested it in linqpad. I haven't tested it in console app, but there isn't any reason why it wouldnt work. But a console app would obviously add more bytes to the code. – jzm Apr 22 '14 at 22:00