# import pandas as pd

# df = pd.read_excel("42003_2021_2533_MOESM6_ESM.xlsx", sheet_name="De novo CNV in SSC and AGRE")

# loc = df["location"].astype(str).str.extract(r'^(?P<chrom>[^:]+):(?P<pos_beg>\d+)-(?P<pos_end>\d+)$')
# loc[["pos_beg","pos_end"]] = loc[["pos_beg","pos_end"]].astype(int)

# df = df.rename(columns={"in affected status": "in_affected_status"})

# out = pd.concat([loc, df[["collection", "in_affected_status", "variant"]]], axis=1).dropna(subset=["chrom", "pos_beg", "pos_end"])
# out.to_csv("Iossifov_Lab_SSC_AGRE_2021.tsv", sep="\t", index=False)



import pandas as pd

# Input
xlsx  = "42003_2021_2533_MOESM6_ESM.xlsx"
sheet = "De novo CNV in SSC and AGRE"

# Output
out_tsv = "Iossifov_Lab_SSC_AGRE_2021.tsv"

df = pd.read_excel(xlsx, sheet_name=sheet)

# location looks like: chr1:1305145-1314126
loc = df["location"].astype(str).str.extract(r'^([^:]+):(\d+)-(\d+)$')
loc.columns = ["chrom", "pos_begin", "pos_end"]
loc[["pos_begin", "pos_end"]] = loc[["pos_begin", "pos_end"]].astype(int)

# # also normalize the "in affected status" header (spaces -> underscores)
# df = df.rename(columns={"in affected status": "in_affected_status"})

out = pd.concat([loc, df[["collection", "in affected status", "variant"]]], axis=1) \
        .dropna(subset=["chrom", "pos_begin", "pos_end"])

out.to_csv(out_tsv, sep="\t", index=False)
print(f"Wrote {len(out)} rows to {out_tsv}")