Count by Pair/Pivot Table… here is a solution to the problem.
Count by Pair/Pivot Table
I have the following data in CSV format:
Date Name Color
12/11 Thomas Blue
12/31 Andy Black
12/21 Luise Red
12/41 Mark Blue
12/11 Ronda Black
12/11 Thomas Blue
12/21 Mark Green
12/11 Ronda Black
12/31 Luise Red
12/41 Luise Green
I want to create a pivot table based on pair counts as shown below. Preferably as a CSV file as well
Blue Black Red Green
Thomas 2
Andy 1
Luise 2 1
Mark 1 1
Ronda 1 1
I’m not entirely sure how to fix this. Pandas cannot be used. 🙁
Solution
You can use defaultdict
The int of defaultdict
is used to store the color count.
import csv, collections
counts = collections.defaultdict(lambda: collections.defaultdict(int))
colors = set()
with open("data.csv") as f:
reader = csv.reader(f, delimiter="\t")
next(reader) # skip first line
for date, name, color in reader:
counts[name][color] += 1
colors.add(color)
Then, print the count of different colors (or write CSV):
colors = list(colors)
print(colors)
for name in counts:
print(name + "\t" + "\t".join(str(counts[name][color]) for color in colors))
Results (I’ll leave the fine-tuning to you):
['Red', 'Blue', 'Green', 'Black']
Ronda 0 0 0 2
Thomas 0 2 0 0
Andy 0 0 0 1
Luise 2 0 1 0
Mark 0 1 1 0