I am none too proficient with Python, so I like things clear and simple, so I can see exactly what is going on.
I think, the best way to get rid of multiple key values is a dictionary .
If you are using pandas, you must be opening a .txt or ,csv file I suppose. I've read, pandas is good for large amounts of data.
This will do what you want, just using the csv module, and I can see exactly what is happening.
You may need to adjust if, for example, key c has perhaps 5 columns, then you will need to find the longest data list and set the fieldnames according to that.
If you know in advance the maximum number of columns in the source file, no need to get complicated.
import csv
# Step 1
# read the data from a csv file
# csv.reader is annoying, it's gone if you have to repeat, so read to a data list first
# when everything is ok, you can forget data
# can't pass on csv_reader. It will be closed at the end of with ... as
# so you would need to combine Step 1, Step 2 and Step 3
with open(path + 'transpose_me.txt') as csv_file:
csv_reader = csv.reader(csv_file, delimiter=',')
data = []
for row in csv_reader:
data.append(row)
# returns [['a', '1'], ['a', '2'], ['b', '3'], ['b', '4'], ['c', '5'], ['c', '6']]
#Step 2
# make a dictionary with value as an empty list
mydict = {}
for d in data:
print(d[0])
key = d[0]
mydict[key] = []
#Step 3
# now put the data in mydict
for bit in data:
key = bit[0]
# possibly more than 1 data value in bit
for v in range(1, len(bit)):
print(key, bit[v])
value = bit[v]
mydict[key].append(value)
# Step 4
# simple write
# now we have the values in an easy to handle dictionary
with open(path + 'transposed.txt', mode='w') as f:
f_writer = csv.writer(f, delimiter=',', quotechar='"', quoting=csv.QUOTE_MINIMAL)
fieldnames = ['key', 'data1', 'data2']
f_writer.writerow(fieldnames)
for key in mydict.keys():
list1 = [key]
values = mydict[key]
list1.extend(values)
f_writer.writerow(list1)
The output looks like:
Quote:key,data1,data2
a,1,2
b,3,4
c,5,6