Current table
Desired result
Not getting any results with this right now. Not sure where to go from here.
import csv
csv_dict = {}
with open("Test.csv", "r") as source:
reader = csv.reader(source)
header = next(reader)
for row in reader:
if row[0] in csv_dict:
csv_dict[row[0]] += row
else:
csv_dict[row[0]] = row
print (row)
what you are trying to do doesn't work because you're adding the whole row each time.
You want to filter out blanks and accumulate values on one key.
There are 2 ways (with 2 different outcomes):
import csv,collections
csv_dict = collections.defaultdict(list)
with open("test.csv", "r") as source:
reader = csv.reader(source)
header = next(reader)
for key,*rest in reader:
csv_dict[key] += filter(None,rest)
print(csv_dict)
You get this dictionary: {'b': ['2', '4'], 'a': ['1', '2', '3', '4']}
In that case, you have to increase the list size if the index doesn't fit in.
import collections,csv
csv_dict = collections.defaultdict(list)
with open("test.csv", "r") as source:
reader = csv.reader(source)
header = next(reader)
for key,*rest in reader:
for i,r in enumerate(rest):
if r:
d = csv_dict[key]
while i>=len(d):
d.append("")
d[i] = r
print(csv_dict)
that yields:
{'a': ['1', '2', '3', '4'], 'b': ['', '2', '', '4']}
In both cases, there are several solutions to write this dictionary in a resulting csv file (using a csv.DictWriter, csv.writer, pandas module).
csv_dict[row[0]] += row: you don't want to do that. You want to add the non blank values (except for the first row) to the dictionary