Coverage for datakiln/build_redcap.py: 0%
130 statements
« prev ^ index » next coverage.py v7.17.3, created at 2026-11-16 23:23 +0000
« prev ^ index » next coverage.py v7.17.3, created at 2026-11-16 23:23 +0000
1"""
2Builds table metadata from redcap api
3"""
5import time
6import json
7import logging
8import re
9from pathlib import Path
11from redcap import Project # pylint: disable=import-error
13from datakiln.arguments import (
14 add_directory, add_export, add_include, add_log
15)
18logger = logging.getLogger(__name__)
21def get_id(project):
22 """Get subject ID and REDCAP event fields.
24 :param project: PyCap project.
25 :type project: redcap.Project
27 :return: Subject ID and Event ID columns metadata.
28 :rtype: dict
29 """
31 # Retrieve project's ID field
32 logger.info("REDCap: Retrieving ID field.")
34 # REDCAP's ID and event name (project dependent)
35 subject_id = {
36 "name": project.def_field,
37 "description": 'Subject identifier.',
38 "dtype": {"postgresql": 'TEXT'},
39 "required": True
40 }
41 if project.is_longitudinal:
42 event_id = {
43 "name": "redcap_event_name",
44 "description": 'Redcap event name.',
45 "dtype": {"postgresql": 'TEXT'},
46 "required": True
47 }
48 else:
49 event_id = None
51 return subject_id, event_id
54def get_metadata(project):
55 """Get metadata for project forms.
57 :param project: PyCap project.
58 :type project: redcap.Project
60 :return: Forms metatada.
61 :rtype: pandas.DataFrame
62 """
64 # Retrieve project metadata
65 logger.info("REDCap: Retrieving metadata.")
66 metadata = project.export_metadata(format_type="df")
67 metadata = metadata.loc[metadata.field_type != "descriptive"]
69 # Retrieve field names
70 field_names = project.export_field_names(format_type="df")
71 field_names = field_names.groupby(level=0)['export_field_name'].apply(list)
72 metadata["field_names"] = field_names
74 return metadata.where(metadata.notnull(), None).copy()
77def get_postgres_type(field_type, validation):
78 """Define PostgreSQL column types from redcap metadata.
80 :param c_type: Python type.
81 :type c_type: type
83 :return: PostgreSQL data type.
84 :rtype: str
85 """
86 if field_type == "checkbox":
87 return "INTEGER[]"
88 if validation == "integer":
89 return "INTEGER"
90 if validation == "number":
91 return "DOUBLE PRECISION"
92 if validation in ["date_dmy", "date_ymd", "date_mdy"]:
93 return "DATE"
94 if validation in ["datetime_dmy", "datetime_seconds_dmy",
95 "datetime_ymd", "datetime_seconds_ymd",
96 "datetime_mdy", "datetime_seconds_mdy"]:
97 return "TIMESTAMP"
98 return "TEXT"
101def generate_column_meta(field_name, field_meta):
102 """Generate column metadata from redcap.
104 :param field_name: Name of the redcap form field.
105 :type field_name: str
107 :param field_meta: PyCap metadata record.
108 :type field_meta: pandas.DataFrame
110 :return: Column metatada.
111 :rtype: dict
112 """
114 # Get description fields from metadata
115 description = []
116 if field_meta.field_label is not None:
117 description.append(
118 f"label: {field_meta.field_label.strip()}")
119 if field_meta.select_choices_or_calculations is not None:
120 description.append(
121 "choices_or_calcs: "
122 f"{field_meta.select_choices_or_calculations.strip()}")
123 if field_meta.field_note is not None:
124 description.append(
125 f"note: {field_meta.field_note.strip()}")
127 # Set delimiter \n\n between description fields
128 if description:
129 description = "\n\n ".join(description)
130 else:
131 description = None
133 column_meta = {
134 "name": field_name,
135 "description": description,
136 "dtype": {
137 "postgresql": get_postgres_type(
138 field_meta.field_type,
139 field_meta.text_validation_type_or_show_slider_number)
140 },
141 "required": field_meta.required_field == "y"
142 }
144 # Replace ' for '' standard in PostgreSQL
145 if column_meta["description"] is not None:
146 column_meta["description"] = column_meta["description"].replace(
147 "'", "''")
149 return column_meta
152def generate_table_meta(form_name, form_meta, is_data_empty,
153 subject_id, event_id,
154 primary_key="meta_row_id",
155 schema_name="",
156 encoding="utf-8"):
157 # pylint: disable=too-many-arguments, too-many-positional-arguments
158 """Generate table metadata from redcap.
160 :param form_name: Name of the redcap form.
161 :type form_name: str
163 :param form_meta: PyCap metadata records.
164 :type form_meta: pandas.DataFrame
166 :param is_data_empty: Flag identifying if data contain relevant fields.
167 :type form_name: bool
169 :param primary_key: Primary key for the table.
170 :type schema_name: str
172 :param schema_name: Name of the schema in PostgreSQL db.
173 :type schema_name: str
175 :param encoding: Encoding type
176 :type encoding: str
178 :return: Table metadata.
179 :rtype: dict
180 """
181 table_meta = {
182 "name": form_name,
183 "schema": schema_name,
184 "encoding": encoding,
185 "columns": [],
186 "constraints": {"primary_key": [primary_key]}
187 }
189 # Generate metadata for each column
190 for field_name, field_meta in form_meta.iterrows():
192 # Ensure RedCap's columns are not present in meta
193 # (those are added in generate_table_meta)
194 if field_name not in (subject_id["name"], event_id["name"]):
195 table_meta["columns"].append(
196 generate_column_meta(field_name, field_meta))
198 # Add subject and redcap identifiers
199 table_meta["columns"].insert(0, subject_id)
200 if event_id:
201 table_meta["columns"].insert(1, event_id)
203 if not is_data_empty:
204 # Add form completion column
205 table_meta["columns"].append({
206 "name": f"{form_name}_complete",
207 "dtype": {"postgresql": "TEXT"},
208 "required": False, # Should be True, using False for one project
209 "description": None
210 })
212 return table_meta
215def generate_table_data(project, form_name, form_meta,
216 subject_id, event_id, na_values=None):
217 # pylint: disable=too-many-locals, too-many-arguments, too-many-positional-arguments # noqa: E501
218 """Retrieve table data from redcap.
220 :param project: PyCap project.
221 :type project: redcap.Project
223 :param form_name: Name of the redcap form.
224 :type form_name: str
226 :param form_meta: PyCap metadata records.
227 :type form_meta: pandas.DataFrame
229 :return: Table data.
230 :rtype: pandas.DataFrame
231 """
232 # Retrieve data from REDCap
233 logger.info("Form '%s': Generating table data.", form_name)
234 data = project.export_records(
235 format_type='df', forms=[form_name], decimal_character=".",
236 df_kwargs={"index_col": None, "na_values": na_values or []})
238 # Get metadata columns names
239 meta_columns = form_meta.index.tolist()
241 # Get data column names for comparison
242 # Remove columns that don't exist in metadata
243 if event_id:
244 data_columns = data.drop(
245 [subject_id["name"], event_id["name"],
246 f"{form_name}_complete"],
247 axis='columns',
248 errors='ignore' # Ignore errors for _complete columns
249 ).columns.values.tolist()
250 else:
251 data_columns = data.drop(
252 [subject_id["name"], f"{form_name}_complete"],
253 axis='columns',
254 errors='ignore' # Ignore errors for _complete columns
255 ).columns.values.tolist()
257 # Data is empty if it doesn't contain any columns besides the three above
258 is_data_empty = not data_columns
260 # Compare meta and data columns before one hot encoding checkbox fields
261 if meta_columns != data_columns:
262 diff_columns = list(set(data_columns) - set(meta_columns))
264 # Exist in data but not in meta
265 if diff_columns:
267 # Find checkbox columns in data to validate differences
268 r = re.compile(".*___")
269 diff_columns_meta = list(filter(r.match, diff_columns))
270 diff_columns = list(set(diff_columns_meta) - set(diff_columns))
272 # If columns still differ
273 if diff_columns:
274 logger.warning(
275 "Form '%s': Data and metadata fields differ: %s.",
276 form_name, diff_columns)
278 # Exist in meta but not in data
279 elif not diff_columns:
280 diff_columns = list(set(meta_columns) - set(data_columns))
281 logger.warning(
282 "Form '%s': Metadata and data fields differ: %s.",
283 form_name, diff_columns)
284 form_meta = form_meta.drop(diff_columns, axis='rows')
285 logger.warning("Form '%s': Metadata columns dropped.", form_name)
287 # Checkbox fields are multiple columns, so we group them into an array
288 # comorbidity_1, comorbidity_2 to comorbidity
289 # 1, 1 [1, 2]
291 dummies = form_meta[form_meta.field_type == "checkbox"]
292 for dummy_target, dummy in dummies.iterrows():
293 melted = data[dummy.field_names].melt(ignore_index=False)
294 codes = melted[melted.value == 1].variable.str.extract(r"(\d+)\Z")
295 grouped = codes.astype(int).groupby(level=0, group_keys=False)
296 data[dummy_target] = grouped[0].apply(list).astype(str)
297 if data[dummy_target].any():
298 data[dummy_target] = data[dummy_target].replace(
299 {r"\[": "{", r"\]": "}"}, regex=True).fillna("{}")
300 data = data.drop(dummy.field_names, axis=1)
302 # Columns with integers and null values are parsed to float in pandas
303 # so we need to parse it back
304 type_col = "text_validation_type_or_show_slider_number"
305 integers = form_meta[form_meta[type_col] == "integer"].index
306 data[integers] = data[integers].astype("Int64")
308 return data, form_meta, is_data_empty
311def add_arguments(parser):
312 """Adds the options of the command.
314 :param parser: Parser of the command.
315 :type parser: argparse.ArgumentParser
316 """
317 add_directory(parser)
318 add_log(parser)
319 add_include(parser, "Extract only these forms. All by default.")
320 parser.add_argument("--exclude", metavar="name", type=str, nargs="+",
321 help="Do not extract these forms.")
322 add_export(parser)
323 parser.add_argument('--api-url', metavar="api_url", type=str,
324 required=True, help='REDCap API URL.')
325 parser.add_argument('--api-key', metavar="api_key", type=str,
326 required=True, help='REDCap API token.')
327 parser.add_argument('--schema', metavar='schema', type=str,
328 help='Schema of the tables in the metadata. None '
329 'by default.')
332def run(args):
333 # pylint: disable=too-many-locals
334 """Downloads REDCap data and creates sample metadata for tables.
336 :param args: Arguments of the command.
337 :type args: argparse.Namespace
338 """
339 directory = Path(args.directory).absolute()
341 # API parameters
342 logger.info("REDCap: Connecting to API.")
343 project = Project(args.api_url, args.api_key)
345 # Get subject ID field and redcap event ID
346 subject_id, event_id = get_id(project)
348 # # Get all metatada for each redcap form
349 metadata = get_metadata(project)
351 # # Export all forms if not specified
352 exclude = set(args.exclude or [])
353 include = set(args.include or metadata.form_name.unique()) - exclude
355 # # Get all data files in directory
356 for form_name, form_meta in metadata.groupby("form_name"):
358 # Timeout for API handle
359 time.sleep(1)
361 # Skip if form not specified
362 if form_name not in include:
363 continue
365 # Create data directory if not exists
366 data_dir = directory / "data"
367 data_dir.mkdir(exist_ok=True)
369 # Generate table data
370 data, form_meta, is_data_empty = generate_table_data(
371 project, form_name, form_meta,
372 subject_id=subject_id,
373 event_id=event_id)
375 # Export to file
376 if args.data:
377 file_path = data_dir / f"{form_name}.csv"
378 data.to_csv(
379 file_path,
380 encoding="utf-8",
381 index=False) # Remove index for datakiln
383 # Generate table metadata from form
384 if args.metadata:
385 logger.info("Form '%s': Generating table metadata.", form_name)
386 table_meta = generate_table_meta(
387 form_name,
388 form_meta,
389 is_data_empty,
390 subject_id=subject_id,
391 event_id=event_id,
392 schema_name=args.schema)
394 # Create tables directory if not exists
395 tables_dir = directory / "metadata"
396 tables_dir.mkdir(exist_ok=True)
398 # Write json file
399 target_fn = f"{tables_dir/form_name}.json"
400 with open(target_fn, "w", encoding="utf-8") as target_file:
401 json.dump(table_meta, target_file, indent=4)