-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathGSpreadsheet_to_ratings.py
186 lines (152 loc) · 7.6 KB
/
GSpreadsheet_to_ratings.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
import os
import sys
import csv
from typing import List, Dict, Any, Tuple, Union
import pydantic
import re
import argparse
from discourseer.rater import Rater, Rating
class Question(pydantic.BaseModel):
id: str
single_choice: bool
options: Union[List[str], Dict[int, str]] = []
def get_option(self, index: int) -> str:
if isinstance(self.options, dict):
return self.options[index]
else:
return self.options[index]
def parse_dir(input_path: str, output_path: str):
os.makedirs(output_path, exist_ok=True)
for file in os.listdir(input_path):
if not file.endswith('.csv'):
continue
file_path = os.path.join(input_path, file)
print(f'\nParsing {file}')
# load csv file
with open(file_path, 'r') as f:
reader = csv.reader(f)
data = list(reader)
print(f'Loaded {len(data)} rows')
rater = parse_spreadsheet(data)
rater.name = file
out_file = file if file.endswith('.csv') else f'{file}.csv'
rater.save_to_csv(os.path.join(output_path, out_file))
def parse_spreadsheet(data) -> Rater:
question_ids_and_indexes = [(d, i) for i, d in enumerate(data[0]) if d != '']
questions = parse_question_headers(data, question_ids_and_indexes)
print(f'Parsed {len(questions)} questions from headers: {[q.id for q in questions]}')
ratings = parse_ratings(data, questions)
print(f'Parsed {len(ratings)} ratings with {sum([len(r.rated_option_ids) for r in ratings])} answers')
return Rater(ratings=ratings)
def parse_question_headers(data, question_ids_and_indexes) -> List[Question]:
questions: List[Question] = []
for i, (question_id, index) in enumerate(question_ids_and_indexes):
last_question = i == len(question_ids_and_indexes) - 1
if last_question:
next_question_index = len(data[1])
else:
next_question_index = question_ids_and_indexes[i+1][1]
if next_question_index == index+1:
# single choice question
question = Question(id=question_id, single_choice=True, options=parse_single_choice_options([data[1][index]]))
else:
# multiple choice question
options = [strip_stuff(o) for o in data[1][index:next_question_index]]
question = Question(id=question_id, single_choice=False,
options=options)
questions.append(question)
return questions
def strip_stuff(data: str) -> str:
return data.strip().strip(';,.:)').strip()
def parse_single_choice_options(data: str) -> List[str]:
if isinstance(data, list) and len(data) == 1:
data = data[0]
# print(f'data: {data}')
pattern = r"(\d+[\)\.\s]?)\s+"
matches = [(m.start(0), m.end(1)) for m in re.finditer(pattern, data)]
options_dict: Dict[int, str] = {}
for i, match in enumerate(matches[:-1]):
index = data[match[0]:match[1]]
# print(f'index: {index}')
start = match[1]
end = matches[i+1][0]
option = data[start:end]
options_dict[int(strip_stuff(index))] = strip_stuff(option)
# add last option
options_dict[int(strip_stuff(data[matches[-1][0]:matches[-1][1]]))] = strip_stuff(data[matches[-1][1]:])
# print(f'options_dict: {options_dict}')
return options_dict
def parse_ratings(data, questions: List[Question]) -> List[Rating]:
answer_lines = data[2:]
ratings: List[Rating] = []
for row_id, row in enumerate(answer_lines):
# print(f'row: {row}')
# every row should have: name of file, answers to individual questions (single or multiple columns according to question type)
file = row[0].strip()
if file == '':
continue
row_index = 1
for question in questions:
# print('')
# print(f'row_index: {row_index}')
# print(f'question: {question.model_dump()}')
if question.single_choice:
# print(f'row[{row_index}]: {row[row_index]}')
answer = get_single_choice_answer(row[row_index], question)
if len(answer) == 0:
print(f'WARNING: single choice question "{question.id}" has no answer on row {row_id} ({row[0]}...)')
# print(f'answer: {answer}')
rating = Rating(file=file, question_id=question.id, rated_option_ids=answer)
else:
# print(f'row[{row_index}:]: {row[row_index:]}')
answers = get_multi_choice_answers(row[row_index:], question)
rating = Rating(file=file, question_id=question.id, rated_option_ids=answers)
# print(f'rating: {rating.model_dump()}')
row_index += len(question.options) if not question.single_choice else 1
# print(f'Adding {len(question.options)} to row_index (number of question options), whole question: ({question.model_dump()})')
# if len(rating.rating_results) > 0: # this can cause unhandled errors in calculating IRRs for some reason...
ratings.append(rating)
return ratings
def clean_single_choice_answer(answer: str) -> str:
answer = answer.strip().strip(';,.').strip()
match_result = re.match(r'(\d+)', answer)
if match_result:
answer = match_result.group(1)
return answer
def get_single_choice_answer(answer: str, question: Question) -> List:
answer = clean_single_choice_answer(answer)
if not answer:
return []
try:
return [question.get_option(int(answer))]
except KeyError:
return []
def get_multi_choice_answers(row: List[str], question: Question) -> List[str]:
binary_answers = row[:len(question.options)]
answers = []
for i, answer in enumerate(binary_answers):
answer = clean_single_choice_answer(answer)
# options signaling positive rating (everything else is meant as negative, typically 0 or "")
if answer in ['ano', 'yes', '1', '2', '3', '4']:
answers.append(question.options[i])
return answers
def parse_args():
parser = argparse.ArgumentParser(description='Parse Google Spreadsheet to ratings')
parser.add_argument('input_path', help='Path to directory with csv files')
parser.add_argument('output_path', help='Path to directory where to save parsed csv files')
return parser.parse_args()
if __name__ == '__main__':
args = parse_args()
parse_dir(args.input_path, args.output_path)
# example: parse_dir('experiments/gaza/coder_results_original/', 'experiments/gaza/coder_results_ratings/')
print('\n')
print('--------------------------------------')
print('Parsing ratings from GSpreadsheet DONE')
print('--------------------------------------')
print('\n')
"""Example input document:
,Zpravodajské hodnoty,,,,,Hlavní téma článku,Mediální rámce,,,,,,Mluvčí,,,,,,,,,,,
,Negativita negativity,Blízkost proximity,Elitní osoby prominence,Personalizace personalization,Dopad impact,"1) Vnitřní politické dění; 2) Mezinárodní politické reakce, 3) Vojenské operace Izraele 4) Teroristické operace, 5) Dílčí konflikty a střety, 6) Dopad na civilisty a humanitární krize, 7) Humanitární pomoc 8) Globální reakce veřejnosti, 9) Jiné",Rámec konfliktu,Rámec budování míru,Humanitární rámec,Historicko-kulturní rámec,Geopolitický rámec,Lokální rámec,Političtí představitelé Izraele,Političtí představitelé Palestiny,Politici z blízkovýcho...
2023-10-07T00-00-00_2023WN280011.txt,1,0,0,1,1,5,1,0,0,0,0,1,1,0,0,0,0,0,0,0,0,0,0,0
2023-10-08T21-44-00_2023WN281071.txt,1,0,1,0,1,1,1,0,0,0,0,1,0,0,0,0,0,0,0,1,0,0,0,1 (intermediální agenda)
"""