Function read_any_format

src/openai/lib/_validators.py:451–537

This function will read a file saved in .csv, .json, .txt, .xlsx or .tsv format using pandas. - for .xlsx it will read the first sheet - for .txt it will assume completions and split on newline

(
    fname: str, fields: list[str] = ["prompt", "completion"]
)

def read_any_format(
    fname: str, fields: list[str] = ["prompt", "completion"]
) -> tuple[pd.DataFrame | None, Remediation]:
    """
    This function will read a file saved in .csv, .json, .txt, .xlsx or .tsv format using pandas.
     - for .xlsx it will read the first sheet
     - for .txt it will assume completions and split on newline
    """
    remediation = None
    necessary_msg = None
    immediate_msg = None
    error_msg = None
    df = None

    if os.path.isfile(fname):
        try:
            if fname.lower().endswith(".csv") or fname.lower().endswith(".tsv"):
                file_extension_str, separator = ("CSV", ",") if fname.lower().endswith(".csv") else ("TSV", "\t")
                immediate_msg = (
                    f"\n- Based on your file extension, your file is formatted as a {file_extension_str} file"
                )
                necessary_msg = f"Your format `{file_extension_str}` will be converted to `JSONL`"
                df = pd.read_csv(fname, sep=separator, dtype=str).fillna("")
            elif fname.lower().endswith(".xlsx"):
                immediate_msg = "\n- Based on your file extension, your file is formatted as an Excel file"
                necessary_msg = "Your format `XLSX` will be converted to `JSONL`"
                xls = pd.ExcelFile(fname)
                sheets = xls.sheet_names
                if len(sheets) > 1:
                    immediate_msg += "\n- Your Excel file contains more than one sheet. Please either save as csv or ensure all data is present in the first sheet. WARNING: Reading only the first sheet..."
                df = pd.read_excel(fname, dtype=str).fillna("")
            elif fname.lower().endswith(".txt"):
                immediate_msg = "\n- Based on your file extension, you provided a text file"
                necessary_msg = "Your format `TXT` will be converted to `JSONL`"
                with open(fname, "r") as f:
                    content = f.read()
                    df = pd.DataFrame(
                        [["", line] for line in content.split("\n")],
                        columns=fields,
                        dtype=str,
                    ).fillna("")
            elif fname.lower().endswith(".jsonl"):
                df = pd.read_json(fname, lines=True, dtype=str).fillna("")  # type: ignore
                if len(df) == 1:  # type: ignore
                    # this is NOT what we expect for a .jsonl file
                    immediate_msg = "\n- Your JSONL file appears to be in a JSON format. Your file will be converted to JSONL format"
                    necessary_msg = "Your format `JSON` will be converted to `JSONL`"
                    df = pd.read_json(fname, dtype=str).fillna("")  # type: ignore
                else:
                    pass  # this is what we expect for a .jsonl file
            elif fname.lower().endswith(".json"):
                try:
                    # to handle case where .json file is actually a .jsonl file
                    df = pd.read_json(fname, lines=True, dtype=str).fillna("")  # type: ignore
                    if len(df) == 1:  # type: ignore
                        # this code path corresponds to a .json file that has one line
                        df = pd.read_json(fname, dtype=str).fillna("")  # type: ignore
                    else: