1
$\begingroup$

I work in Rome for the Italian National Health Service (www.iss.it); I am currently working on a European clinic project called EMTICS (www.emtics.eu), I developed both the database and the website to collect clinician data. I need an advice on how to export data from SQL database to a file that will be then analyzed by STATA or SPSS. A quick summary: the clinician study is about TICS in child. Data is about 1,000 patients all over the Europe. Every patient can do a number of visits and in every visit about 700 variables are recorded. Every visit is also identified by a “VisitType” code (BS=baseline; UP=follow up;FC=final; etc.) and by a “VisitNumber. For example, if we consider the variable “ThroatSwab”, I have the dataset:

 
| idPatient | VisitType | VisitNumber | ThroatSwab | ... | other variable | ... |
| 15 | BS | 01 | 1 | ... | other value | ... |
| 15 | UP | 01 | 1 | ... | other value | ... |
| 15 | UP | 02 | 1 | ... | other value | ... |
| 15 | UP | 03 | 1 | ... | other value | ... |
| 15 | FC | 01 | 1 | ... | other value | ... |
| 44 | BS | 01 | 0 | ... | other value | ... |
| 44 | UP | 01 | 1 | ... | other value | ... |
| 44 | UP | 02 | 0 | ... | other value | ... |
| 44 | UP | 03 | 0 | ... | other value | ... |
| 44 | FC | 01 | 0 | ... | other value | ... |
..................................

I don’t understand why the statistician in London asked for a dataset like the following:

| idPatient | BS01_ ThroatSwab | UP01_ ThroatSwab | UP02_ ThroatSwab | UP03_ ThroatSwab | FC01_ ThroatSwab | ... | BS01_ ThroatSwab |...|
| 15 | 1 | 1 | 1 | 1 | 1 | ... | other value | ... |
| 44 | 0 | 1 | 0 | 0 |0 | ... | other value | ... |
......................................................

And a  “DataDictionary” file:

| Variable_Label | VisitType | VisitNumber | Variable_Name |
| ThroatSwab | BS | 01 | BS01_ ThroatSwab |
| ThroatSwab | UP | 01 | UP01_ ThroatSwab |
| ThroatSwab | UP | 02 | UP02_ ThroatSwab |
| ThroatSwab | UP | 03 | UP03_ ThroatSwab |
| ThroatSwab | FC | 01 | FC01_ ThroatSwab |
..................................................................

Where there are different variable names on every different visit type and visit number... Plus a colum “Variable_Label” that group similar variables…

In other words: I'm not the person that have to analyze the data with SPSS or R: I must simply provide the dataset to a statistician. The first dataset sample indicated is the result of complex processing operations (transposed and other stuff) so I was wondering, as it is relatively simple to make groupings with R or SPSS: is not better to provide the statistician the dataset already just as it is (thus the visit as single statistical unit, than to waste time re-codind the original data? Now the file is about 10,000 lines (the visits: 10 visits per patients per 1000 patients) and 700 columns (variables recorded at each visit), while the statistician wants a file with 1000 lines (the patients) and 7000 columns (different variables for each kind of visit type and number.). I really can't figure out how can he remap multiple observations of the same variable that would have different names for each visit.

Is there a clear reason because the statistician wants data in this way (for example the software he will use)? Or is it only an unnecessary complication?

Forgive me for my English, I hope I was clear. Thank you in advance, Marco

$\endgroup$
1
$\begingroup$

I would rather post a comment instead of an answer but I can't, so there it is.

A straight answer your question :

Is there a clear reason because the statistician wants data in this way (for example the software he will use)?

It's indeed software (sort of). SPSS would use (for most analysis at least) a so called wide data format (table 2 from your example) - that is: each variable has it's own column and each observation (eg. participant) is in one row. In R on the other hand one would (most likely) prefer a so called long data format (table 1 from your example). That said both software provide tools for data manipulation (aggregate and / or restructure in SPSS for example) allowing for a quick (or not so quick if data is messy) change from one format to an other.

As for your second question

Or is it only an unnecessary complication?

I would have to guess your analyst motivation here so I put this into an example. If I where to analyse someone's data very closely I'd prefer a long format, so that in the process of aggregating, transposing etc I could seek for data errors by my own. On the other hand, if I had to produce only summary tables, charts, etc, a wide format would spare me a lot of time.

Regards

$\endgroup$
  • $\begingroup$ Thank you! I think your simple answer explains well my curiosity. $\endgroup$ – mtallon Oct 21 '16 at 9:17
  • 1
    $\begingroup$ I still have a doubt: in wide data format, how do you aggregate variables with different names (BS01_ ThroatSwab, UP01_ ThroatSwab, UP02_ ThroatSwab, UP03_ ThroatSwab, FC01_ ThroatSwab) ? By data dictionary? If the answer is yes, then I don’t understand the wide data format, since the same aggregate operation is more simple using long data format. Or am I wrong? I talked to the statistician and I proposed him to use long data format, with “neutral” variable names, in conjunction with a codebook with his variable names/labels, so he will able to manipulate data as he want. $\endgroup$ – mtallon Oct 21 '16 at 9:31
  • $\begingroup$ I'm not sure if I understood your doubt. Wide data format would be an aggregated one (at least I'd think so), so each variable (BS01_ ThroatSwab, UP01_ ThroatSwab, UP02_ ThroatSwab, UP03_ ThroatSwab, FC01_ ThroatSwab) would constitute a separate column with a single observation for every patient. $\endgroup$ – blazej Oct 21 '16 at 10:34
  • $\begingroup$ Yes.But each separate column refer to a precise time when the patient made the visit and value of "ThroatSwab" is recorded: if you want to see its evolution, how do you know that these different variable names are the same? $\endgroup$ – mtallon Oct 21 '16 at 11:54
  • $\begingroup$ Can you post an example? I'm don't understand your question, sorry. $\endgroup$ – blazej Oct 21 '16 at 12:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.