I have an epidemiological dataset that I would like to prepare for analysis.
Currently the data has a group identifier in the first column (CL/P vs CP) and data is contained in the following columns.
Below a short example:
|Type||BW||F age birth||M age birth||F age conc||M age conc|
To be processed in my statistical package that unfortunately does not support grouping like SPSS, I need to have the data as follows (different data from above):
|CLP BW||CLP F age birth||CLP M age birth||CLP F age conc||CLP M age conc||CP BW||CP F age birth||CP M age birth||CP F age conc||CP M age conc|
The columns will have a different number of rows depending on missing values and differences in frequencies in the groups.
I can filter and segregate the CL/P from the CP, but how can I then bring together the filtered columns from two different branches to create a new table? With a pivot I get to the result for a single variable but I have empty cells that I cannot simply delete shifting up the cells below (wish for a future release…)
Currently I filter for CL/P and export the data and redo for CP.
Is there a smarter way?
Thank you in advance,