Tuesday, August 22, 2017
Transposing csv data using Python
Transposing csv data using Python
Here is the case:
- You have this original csv data:
name,course,number
A,Genius,12
A,Super,13
B,Goddess,15
C,The Man,17
C,The Woman,10
C,Harvard,11
C,StanFord,18
- and you want to create another csv file that uses names values as columns and group the courses value as rows:
A,B,C
Genius,Goddess,The Man
Super,,The Woman
,,Harvard
,,StanFord
Here is what I did to achieve that:
1. Read the original csv file and convert it into a list of dicts
2. Transpose the list by grouping the name column:
- Get all the names value
group_cols = []
for o in original_list:
if o[group_col_name].strip() not in group_cols:
group_cols.append(o[group_col_name].strip())
- For each of the name, loop through the original list and append the according courses value to a tmp list (with the name as the first item), then add all of the tmp lists to a new list:
thelist = []
for c in group_cols:
line = []
line.append(c)
for o in original_list:
if o[group_col_name].strip() == c:
line.append(o[data_col_name].strip())
thelist.append(line)
=> thelist = [[A, Genius, Super], [B, Goddess], [C, The Man, The Woman, Harvard, Stanford]]
- Equalize all the child listss length (here):
thelist = equalize_lists_length(thelist)
=> thelist = [[A, Genius, Super, ,], [B, Goddess,,,], [C, The Man, The Woman, Harvard, Stanford]]
- Transpose the new list using itertools.izip:
thelist = list(izip(*thelist))
=> thelist = [[A, B, C], [Genius, Goddess, The Man],...]
3. Write the new list to the csv file
Here is the full code:
Usage:
>>> transposed_list_to_csv(original_csv_path, new_csv_path, name, course)
download file now
