0

I'm trying to combine two lists into a csv, and have it output a line per each line of a second list.

a.csv 

1  
2  
3

b.csv 

a,x  
b,y  
c,z

Output:
c.csv 

1|a|x  
2|a|x  
3|a|x  
1|b|y  
2|b|y  
3|b|y  
1|c|z  
2|c|z  
3|c|z

So for each line of "a" combine each line of "b", and get a list in "c".

Note, I have no need to separate "b" to reorder the columns, keeping the original order is fine.
A loop seems needed, but I'm having zero luck doing it.

Answered (output is not perfect, but ok for what i was needing):

import csv
from itertools import product

def main():
    with open('a.csv', 'rb') as f1, open('b.csv', 'rb') as f2:
        reader1 = csv.reader(f1, dialect=csv.excel_tab)
        reader2 = csv.reader(f2, dialect=csv.excel_tab)

        with open('output.csv', 'wb') as output:
            writer = csv.writer(output, delimiter='|', dialect=csv.excel_tab)
            writer.writerows(row1 + row2 for row1, row2 in product(reader1, reader2))

if __name__ == "__main__":
    main()

Output file:

1|a,x
1|b,y
1|c,z
2|a,x
2|b,y
2|c,z
3|a,x
3|b,y
3|c,z

Yes the "|" is only one of the separators.
It would be nice to know how to get "1|a|x" and so on.

jpp
  • 159,742
  • 34
  • 281
  • 339
Mobs
  • 15
  • 6
  • I think you should provide some more details: are you using Excel or trying to do this in a program (which language)? Or are you just looking for the algorithm to do it (which is 2 foreach loops, outer one for B and the inner one for A, according to your output specification). – M. F. Mar 05 '18 at 17:55
  • It's not clear can you mention output as you want... – Narendra Mar 05 '18 at 18:34
  • The details are there. This is a python or python-2.7 question. The output is the c.csv file and the desired results are included. – Mobs Mar 05 '18 at 20:20

2 Answers2

0

One way is to use pandas:

import pandas as pd

df = pd.concat([pd.read_csv(f, header=None) for f in ('a.csv', 'b.csv')], axis=1)

df.to_csv('out.csv', sep='|', index=False, header=False)
jpp
  • 159,742
  • 34
  • 281
  • 339
  • This produced: 1,a,x ; 2,b,y ; 3,c,z ; Not the desired result. But this got me to install pandas. Note, the "delimiter='|'" did not go through, I took it out to get the out.csv. – Mobs Mar 05 '18 at 20:05
  • @Mobs, should be `sep` instead of `delimiter`, updated now. You're right, you need to do some extra manipulation with pandas to get it working. Will have a look if I have a moment. – jpp Mar 05 '18 at 20:38
  • I've found it, well found it without "pandas". But thanks. It's not perfect but gets done the job. – Mobs Mar 05 '18 at 22:02
0

A native Python approach, using itertools.product:

from itertools import product

#read file a, remove newline, replace commas with new delimiter and ignore empty lines
a = [line[:-2].strip().replace(",", "|") for line in open("a.csv", "r") if line[:-2].strip()]
#read file b, leave newline in string
b = [line.replace(",", "|") for line in open("b.csv", "r") if line[:-2].strip()]
#combine the two lists
c = ["|".join([i, j]) for i, j in product(a, b)]
#write into a new file
with open("c.csv", "w") as f:
    for item in c:
        f.write(item)
#output
1|a|x  
1|b|y  
1|c|z 
2|a|x  
2|b|y  
2|c|z 
3|a|x  
3|b|y  
3|c|z
Mr. T
  • 11,960
  • 10
  • 32
  • 54
  • I didn't get your output. The c.csv is blank. It shows no errors. – Mobs Mar 05 '18 at 20:31
  • Mhm. I tried it with Python2 and 3. Did you print out `a`, `b` and `c` to see, if import, combination or export the problem is? – Mr. T Mar 05 '18 at 20:49
  • Mm, i was able to put together something, i put what i did in my question. Thanks. Note, its still not perfect, close enough. – Mobs Mar 05 '18 at 22:11
  • Are you sure, you want to open your files with ["rb" and "wb" instead of "r" and "w"](https://stackoverflow.com/a/16212401/8881141)? – Mr. T Mar 05 '18 at 23:01
  • Honestly, I'm not sure... But i believe, with the little i know, that sounds correct. If i have to produce this data again I'll try with "r" and "w". Thanks. – Mobs Mar 07 '18 at 00:43