Data Import, Export, and File Formats

Why File Formats Matter in Data Science

Real-world data arrives in many shapes: comma-separated value (CSV) files from databases, Excel workbooks from business teams, and JSON objects from web APIs. Each format stores information differently, and small differences in how a tool reads or writes a file can silently corrupt your data. A numeric column that arrives as text, a date that loses its timezone, or a floating-point value rounded by Excel are all common integrity failures. Your job as a data professional is to detect and document these issues every time you move data across a format boundary.

CSV Files

CSV (Comma-Separated Values) is the most portable tabular format. Each row is a line of text; columns are separated by a delimiter, usually a comma. CSV has no data-type information — every value is stored as plain text, so the reading library must infer types. This makes CSV fast and universal but fragile: a column of US ZIP codes such as `07030` will often be read as the integer `7030` unless you tell the library to keep it as a string.

Python: `pandas.read_csv("file.csv")` loads a CSV into a DataFrame. Use `dtype={"zip": str}` to override inferred types. `df.to_csv("out.csv", index=False)` writes back without the row-index column (omitting `index=False` adds an unwanted column on export).

R: `readr::read_csv("file.csv")` reads CSV and returns a tibble. `readr` prints a column-specification message — read it; it tells you what types were guessed. `write_csv(df, "out.csv")` exports without row names.

Excel Files

Excel workbooks (`.xlsx`) can hold multiple sheets, cell formatting, formulas, and merged cells. Only the displayed value is exported, not the formula. Dates are stored as numeric serial numbers internally, which can cause type errors when a library does not recognize the date format.

Python: `pandas.read_excel("file.xlsx", sheet_name=0)` reads the first sheet. Install `openpyxl` (for `.xlsx`) as a backend. `df.to_excel("out.xlsx", index=False)` writes an Excel file.

R: The `readxl` package provides `read_excel("file.xlsx")`. There is no base-R write function for Excel; use `writexl::write_xlsx(df, "out.xlsx")` or export to CSV instead if Excel output is not required.

JSON Files

JSON (JavaScript Object Notation) is the standard format for web APIs. Tabular data in JSON is usually represented as an array of objects, where each object is one row. JSON preserves string versus number distinctions but does not have a native date type — dates are stored as strings.

Python: `pandas.read_json("file.json")` reads a JSON array of objects into a DataFrame. `df.to_json("out.json", orient="records", indent=2)` writes one record per object with readable indentation. The `orient` parameter controls shape; `"records"` matches the most common API format.

R: `jsonlite::fromJSON("file.json")` returns a data frame. `jsonlite::toJSON(df, pretty=TRUE)` exports it. Open the exported file in a text editor to verify the structure is what you expect.

Data-Integrity Checking

After every round-trip (import → transform → export → re-import), verify that no data was lost or corrupted. The minimum checks are:

1. Row count — the number of rows must match before and after.

2. Column checksum — compute the sum of a numeric column before export and again after re-import. If the sums differ, a value was changed.

3. Assertion statements — in Python, use `assert` to halt execution if a check fails. In R, use `stopifnot()` for the same effect. Assertions that pass silently mean the check succeeded; an error message means data was corrupted.

```python

assert df_reimported.shape[0] == original_row_count, "Row count mismatch"

assert round(df_reimported["sales"].sum(), 2) == round(original_sum, 2), "Checksum mismatch"

```

```r

stopifnot(nrow(df_reimported) == original_row_count)

stopifnot(round(sum(df_reimported$sales), 2) == round(original_sum, 2))

```

Rounding before comparison handles floating-point representation differences between formats.

Safety and Professional Standards

Data work carries its own safety responsibilities:

• Never overwrite the original source file. Always write exports to a new filename. Treat raw data as read-only. This is the data equivalent of lockout/tagout — isolate the source before working on it.
• Version your files. Use clear names such as `sales_v1_cleaned.csv` so any team member can trace what was changed.
• Protect personally identifiable information (PII). If the dataset contains names, email addresses, or ID numbers, do not export it to a shared or unsecured folder. Check your program's and employer's data-handling policy before moving files.
• Log every operation. The lab log is not busywork — it is a professional record. In industry, an undocumented data transformation is a liability.

Lab Workflow Summary

1. Load each of the three provided datasets (CSV, Excel, JSON) in both Python and R.

2. Record `shape`, `dtypes` (Python) or `spec()` (R), and `head()` output in the lab log.

3. Export modified versions using the correct write function for each format.

4. Open each exported file in a text editor to visually confirm structure.

5. Re-import each exported file and run row-count and checksum assertions.

6. Record pass/fail for every assertion in the lab log.

Zero failed assertions and a complete log entry for all six import/export operations are required to pass this lab.