1 / 22

Best Practices for Preparing Data for Statisticians in Excel and Beyond

Collaborating with statisticians requires careful attention to data preparation. While normalized databases are ideal, most data are still in Excel spreadsheets, which pose challenges for statisticians. Understanding their preferences, such as the need for CSV exports and clear data formatting, can lead to smoother interactions. Essential guidelines include using a single table per worksheet, consistent column naming, and maintaining links to raw data for reproducibility. Following rigorous metadata and validation standards will help minimize errors and ensure compliance with publication requirements.

dian
Télécharger la présentation

Best Practices for Preparing Data for Statisticians in Excel and Beyond

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Working with Statisticians At some point, a statistician is likely to be asked to analyze your data. This can lead to much unhappiness.

  2. Statisticians come in many shapes and sizes

  3. But

  4. Data formats • Ideally, use a normalized database with validated data entry as part of LIMS… • But 99% of the time => Excel spreadsheet • Some statisticians prefer to work with raw data (i.e. FCS files) but not common • Scott will cover consistent annotation for raw data at another lecture

  5. Basic principle #1 • Statisticians do not like Excel • The first thing they will try to do is export to a CSV or delimited file, for import into SAS or R • If this is difficult to do, they will not like you

  6. Excel rules for happy statisticians • 1 worksheet = 1 table • 1 cell = 1 value • Data? • Metadata? • Formatting? • Validation?

  7. 1 worksheet = 1 table • A table has column headers and a number of rows and nothing else – it is RECTANGULAR • Do not put more than 1 table in a worksheet • Do not use non-rectangular tables • Example of good worksheet

  8. 1 worksheet = 1 table

  9. 1 cell = 1 value • Easy to filter by tube, sample or subject • Easy to write validation rules or lookup table

  10. 1 cell = 1 value • ID column has 3 different values • Need to do text parsing to recover information – very error prone

  11. Data: column names • Consistent column names across worksheets • Singlets/Lymphocytes • Singlet/Lymphs • Singlets / Lymphocytes • Singlets/Lymphoctyes • Use full gating path for column name • Singlets/Lymphocytes/Viable/CD4+/CM/IFN+

  12. Data: What to record • Better to have more data than less data • Sample type (PBMC, whole blood) • Recovery • Viability • Better to have basic than derived data • Counts better than relative frequencies • Keep link to raw data for reproducibility • Path to FCS file on server • Use special indicator for missing data (e.g. NAN), not zero • Can have extra column for notes • Ideally codified so Error 23 rather than “Sample sat > 8 hours before processing”

  13. Data: Versioning • Do not change the data in the worksheet once it has been handed to statistician. • If there are errors that must be corrected, make a new copy, label the filename with date and version, and send that to statistician • ArcticRatExperiment_07May2013_Version01.xlsx • ArcticRatExperiment_17May2013_Version02.xlsx

  14. Metadata • Should have SOP document for metadata • How missing data is represented (e.g. NA or blank) • Keys for interpretation – e.g. Table of error codes • Contact person: phone #, email • Metadata can be in 2nd worksheet or separate document • Gating scheme with labeled gates matching cell subsets used in column names (PDF or PPT) • Panel information • Antibodies, clones, batches, fluorochromes, peptide mixes • Path to Flowjo .jo or .xml analysis file

  15. Metadata • There are minimal information standards that should be followed • MiFlowcyte • MIATA • Google for them if you’re not familiar with them – increasingly these are required by journals for publication, so worth making it an SOP for documentation of results

  16. Formatting • Don’t do it. • Avoid putting information via: • Highlighting • Fancy spacing • Different fonts and font effects • Merging cells • Comments • Will it survive a round-trip from Excel to CSV and back again?

  17. Formatting - Before

  18. Formatting - After Comments are lost Highlighting is lost Bad cell formatting is lost Merged cells become missing information

  19. Validation • Can set up validation rules in Excel to minimize data entry errors: • Number range (0, 10000000) • Can use lookup tables for codes to use • E.g. Error codes with explanation • If possible, once format for data is decided, get local Excel wizard to create template and lookup rules

  20. Questions? • If no questions and need to kill time, watch Biologist talks to Statistician video • http://www.youtube.com/watch?v=Hz1fyhVOjr4

More Related