用streamlit搭建数据交互式app

### streamlit_app/main.py
import streamlit as st
import pandas as pd
import os 

st.set_page_config(
    page_title="Excel toolkits",
    layout="wide"
)

@st.cache_data
def read_data(upload_file):
    # Read the uploaded file into a dataframe
    df = pd.read_excel(upload_file)

    # Rename the columns
    df.columns = ["n"+str(i+1) for i in range(df.shape[1])]

    # Fill missing values with empty string
    df = df.fillna('')

    return df

def read_data2(upload_file):
    # Read the file into a Pandas dataframe
    duiying = pd.read_excel(upload_file)

    # Rename the columns
    duiying.columns = ["sampleName","trueName"]

    return duiying

# This code makes sure that all of the names in the dataframe are unique by adding a number at the end of each name that is not unique.
# The code also adds a number at the end of each name that is unique, but not the first one.

def makenames(df,col):
    s='_'+df.groupby(col).cumcount().add(1).astype(str)
    df.loc[:,col]+=s.mask(s=="_1","")
    return df


## Add a title
st.title("Excel tools for Glims")
## Split the page into 2 columns
col1, col2 = st.columns([0.35,0.65],gap="medium")

## 这个是左边的栏目
with col1:
    ###组件数据交互
    upload_data = st.file_uploader(label="Excel file from Glims...",
                     type=['xlsx','xls'])
    SM_index = st.number_input(label="Index for sampleName:",
                    min_value=1,
                    max_value=62,
                    value=4)
    BC_index = st.number_input(label="Index for barcode:",
                    min_value=1,
                    max_value=62,
                    value=23)
    CP_index = st.number_input(label="Index for chip:",
                    min_value=1,
                    max_value=62,
                    value=27)
    LN_index = st.number_input(label="Index for lane:",
                    min_value=1,
                    max_value=62,
                    value=28)
    Path_index = st.number_input(label="Index for DataPath:",
                    min_value=1,
                    max_value=62,
                    value=48)
    Remote_path = st.text_input(label="Remote path:",
                                value="10.2.100.1:/pakpox/pob8d1/",
                                max_chars=1000)
    isSubmit = st.button("Submit!")
    st.divider()
    placeholder = st.empty()
    upload_data2 = st.file_uploader(label="Input Barcode with sampleName:",
                     type=['xlsx','xls'])

### 这个是右边的栏目
with col2:
    ### 分两个tab
    tab1, tab2 = st.tabs(["ViewData", "Scipts"])
    tab21, tab22 = tab2.tabs(["Script1", "Script2"])
    ### container是容器占位符，作用是可以把后面运行的命令得到的结果放到一个容器里面渲染在前面去
    upcontent = tab1.container()
    upcontent.subheader("View of the data:")
    if upload_data is not None: ###这个判断很重要，不然会报错

        ###这是一个缓存函数
        dataframes = read_data(upload_data)
        df = dataframes.iloc[:,[SM_index-1,BC_index-1,CP_index-1,LN_index-1,Path_index-1]]
        df.columns = ["sampleName", "barcode", "chip", "lane", "dataPath"]
        ## if sampleName is duplicated, fix the name
        if df.sampleName.duplicated().any():
            tab1.warning("Duplicated sampleName found, fixing...")
        df = makenames(df,"sampleName")
        ### upload2的作用是，有的excel文件并没有样本名列，它提供了额外的样本名和barcode对应关系，需要我们提交后再次上传
        if upload_data2 is not None:
            placeholder.info("After upload data, please click the button again!")
            df2 = read_data2(upload_data2)
            df = df.merge(df2,how="left",on="sampleName")
            df = df.drop(columns=["sampleName"])
            df = df.rename(columns={"trueName":"sampleName"})
            df = df[~df["sampleName"].isna()]
        ### 这个判断导致延迟执行，每次更新数据后，只有重新点击提交按钮才会重新执行
        if isSubmit:
            upcontent.dataframe(df,hide_index=True,height=600)
            df = df.assign(filename1=df.apply(lambda row : "_".join([row['chip'],row['lane'],str(row['barcode']),"1.fq.gz"]),axis=1))
            df = df.assign(filename2=df.apply(lambda row : "_".join([row['chip'],row['lane'],str(row['barcode']),"2.fq.gz"]),axis=1))
            cmd1 = df.apply(lambda row: "scp " + os.path.join(row['dataPath'],row['filename1']) + " " + os.path.join(Remote_path,row['sampleName'] + "_R1.fastq.gz"),axis=1).tolist()
            cmd2 = df.apply(lambda row: "scp " + os.path.join(row['dataPath'],row['filename2']) + " " + os.path.join(Remote_path,row['sampleName'] + "_R2.fastq.gz"),axis=1).tolist()
            tab21.code("\n".join(cmd1))
            tab22.code("\n".join(cmd2))