what is the python code to transpose rows of data to columns separated by commas?

Hi Experts,

I have these fields:

TXN_ID     CAT_ID
123              45
123              42
123              56
222              10
455              22
455              11


I want to transpose to this without column headers using Python code

123, 45, 42, 56
222, 10
455, 22, 11

Please help?

Thanks
Ricky NgAsked:
Who is Participating?
 
Walter RitzelSenior Software EngineerCommented:
filename = 'Q_28491431.txt'
f = open(filename,'rt')
dataAgg = {}
for line in f.readlines():
	cols = filter(None,line.replace('\n','').split(' '))
	if cols[0] in dataAgg.keys():
		dataAgg[cols[0]].append(cols[1]) 
	else:
		arr = []
		arr.append(cols[1])
		dataAgg[cols[0]] = arr

fileToWrite = 'Q_28491431_out.txt'
f2 = open(fileToWrite,'wb')
for item in dataAgg.keys():
	f2.write((item + ',' + ','.join(dataAgg[item])) + '\n')

f2.close()

Open in new window

0
 
Walter RitzelSenior Software EngineerCommented:
In theory, this is your code:
transposedArr = zip(*regularArray)

Open in new window

0
 
Ricky NgAuthor Commented:
Sorry I am new to Python coding.

Is regularArray my file I am transposing?

transposedArr = zip(*regularArray)
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Walter RitzelSenior Software EngineerCommented:
Sorry, I did not understand that you were using a file from your question.

Your case is a little more complicated, because you are not simply transposing, you are agregating the information. zip(* will turn rows into columns and vice versa, but without the summarization you are requesting.
To do that, the simplest way is to do this:
filename = 'Q_28491431.txt'  #this a file containing the initial lines you had posted.
f = open(filename,'rt')
dataAgg = {}
for line in f.readlines():
	cols = line.split(',') #I have assumed this is a csv file, using , to separate columns
	if cols[0] in dataAgg.keys():
		dataAgg[cols[0]].append(cols[1]) 
	else:
		arr = []
		arr.append(cols[1])
		dataAgg[cols[0]] = arr

for item in dataAgg.keys():
	print item,',',dataAgg[item]

Open in new window


Please observe that this may not be the most "pythonic" way of doing it, but I think it is very clear if you understand other programming languages. If you want to write "pythonic" code, using what is state of art in python code, you need to read documentation and lots of books.
0
 
Ricky NgAuthor Commented:
Hi Walter,

Thanks for your reply. I am getting this error when I run your code.

Traceback (most recent call last):
  File "test.py", line 10, in <module>
    arr.append(cols[1])
IndexError: list index out of range
hpc2879@qsbcomp1$

Thanks
0
 
Walter RitzelSenior Software EngineerCommented:
your file entry should be like this to work:
TXN_ID,CAT_ID
123,45
123,42
123,56
222,10
455,22
455,11

Open in new window


Or you can change the split command to separate by space.
0
 
Walter RitzelSenior Software EngineerCommented:
More especifically, use this line:
 
	cols = filter(None,line.split(' '))

Open in new window


instead of this line:
cols = line.split(',')

Open in new window

0
 
Ricky NgAuthor Commented:
Hi Walter,

It worked. Just wondering how the output can be written to a file.

Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.