Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2451
  • Last Modified:

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
0
Ricky Ng
Asked:
Ricky Ng
  • 5
  • 3
1 Solution
 
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
 
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now