Excel 및 JSON 파일 읽기

While CSV is the universal format, Excel files (.xlsx/.xls) are the dominant format for business data shared via email and reporting tools. JSON is the native format of REST APIs and NoSQL databases. Pandas provides pd.read_excel() and pd.read_json() that mirror the CSV reader's API, so skills transfer directly.

Reading Excel files requires an engine: openpyxl for .xlsx files (modern format) and xlrd for older .xls files. Install with pip install openpyxl. Pandas auto-selects the engine based on the file extension. For writing, use xlsxwriter for advanced formatting. Without the engine installed, read_excel() raises a ModuleNotFoundError.

Excel workbooks can have multiple sheets. sheet_name= specifies which one to read: pass a string (sheet name), an integer (0-based position), or a list to read multiple sheets into a dict. Pass sheet_name=None to read all sheets into a dict keyed by sheet name. Inspecting the available sheets with pd.ExcelFile('file.xlsx').sheet_names is a good starting point.

pd.read_excel() supports most of the same parameters as read_csv(): header=, index_col=, usecols=, skiprows=, dtype=, and nrows=. For Excel, usecols also accepts an Excel column range string like 'A:C' or 'A,C,E', which is convenient when the column letters are known from the spreadsheet layout.

pd.read_json() reads JSON into a DataFrame. The orient= parameter specifies the JSON structure: 'records' (list of row dicts), 'columns' (dict of column arrays, the default), 'index' (dict of row dicts keyed by index), or 'values' (raw array). Most REST APIs return 'records' format — always inspect the raw JSON first to determine the correct orient.

pd.read_json() accepts a file path, a URL, or a JSON string directly. For deeply nested JSON (e.g., API responses with nested objects), use json_normalize() from the pandas.io.json module instead, which flattens nested dicts into columns with dot-separated names.

JSON Lines (NDJSON) stores one JSON object per line, making it easy to stream large datasets. Use pd.read_json('file.jsonl', lines=True) to parse this format. It is common in log files, Kafka exports, and machine learning dataset formats. Each line must be a valid JSON object; malformed lines cause the read to fail.

JSON has no native date type — dates are stored as strings or Unix timestamps (milliseconds or seconds since epoch). Set convert_dates=True (default) to let Pandas attempt auto-conversion of columns whose names contain 'date', 'time', or 'at'. For custom column names or timestamps, convert explicitly with pd.to_datetime(df['col'], unit='ms').

Excel gotchas: merged cells produce NaN rows, hidden rows/columns are included in the output, and number formatting (e.g., dates stored as floats) must be corrected after loading. JSON gotchas: inconsistent field presence across records produces NaN, and integer keys in a JSON object become string column names.

When you need to read multiple sheets from the same file efficiently, open a pd.ExcelFile context manager and call parse(sheet_name) for each sheet. This avoids re-opening and re-parsing the file for each sheet — important for large workbooks. The context manager closes the file handle automatically.

For REST API data, use the requests library to fetch JSON and pass the parsed Python object to pd.DataFrame() or pd.json_normalize(). This pattern separates the HTTP concern from the data parsing concern and gives you access to headers, authentication, and pagination before Pandas touches the data.

Test your understanding of reading Excel and JSON files with Pandas from this lesson.

In this lesson you learned: pd.read_excel() reads xlsx files and lets you specify which sheet to load via sheet_name, pd.read_json() handles multiple JSON structures controlled by the orient parameter, and json_normalize() flattens nested JSON objects into a flat DataFrame. Next up we export DataFrames to CSV and Excel files for sharing and downstream processing.