import pandas as pd

class pandas():

    def __int__(self):
        pass

    def creat_dataframe(self):
        data = {"a":[1,2] , "b":["test1","test2"]}
        # 使用字典加列表的形式創建dataframe， colums為字典的key ，index可以自定義
        df = pd.DataFrame(data=data,index=["一","二"],columns=["a","b"])
        df.columns = ["修改—1","修改-2"] # 修改colums值
        df.index = ["5","6"] # 修改index
        print(df) # colums是字典的key, index可以自定義，也可以不指定。

        data1 = {"a":{"一":1,"二":2},"b":{"一":10,"二":20},"c":{}}
        df1 = pd.DataFrame(data=data1)
        print(df1) # 字典外層key作為colums索引，內層key作為了Index索引

        data2 = [{"一":1,"二":2},{"一":10,"二":20}]
        df2 = pd.DataFrame(data=data2)  # 字典的key作為colums
        print(df2)

    def get_dataframe(self):
        df = pd.read_excel("總結.xlsx",sheet_name="Sheet5")
        #獲取全量數據轉換成字典
        result = df.to_dict(orient="list") # orient 可選records,list,dict
        print(result)

        # 獲取某行的數據
        #row_result = df.loc[1]
        row_result = df.loc[1,:]  # 前面代表行,后面代表列（:代表所有列）
        #print(row_result)

        # 獲取某列的數據
        cls_result = df.loc[:,"中文名字"] # 前面代表行（：代表所有行）,后面代表列（""中代表colums的key值）
        #print(cls_result)

        # 獲取某個單元格的數據
        cell_result = df.loc[0,"中文名字"] # 第一行的中文名字 第一行從0開始（為index編號）
        # print(cell_result)

        # 獲取某個區域的數據
        area_result = df.loc[0:3,"中文名字":"中文姓"]
        #print(area_result)

        # 根據條件篩選數據

    def writ_to_excel(self,excel_name):
        data = {"a":[1,2] , "b":["test1","test2"]}
        df = pd.DataFrame(data=data,index=["一","二"],columns=["a","b"])

        data1 = {"a":[10,20] , "b":["test10","test20"]}
        df1 = pd.DataFrame(data=data1,index=["一","二"],columns=["a","b"])
        # mode中w是新建excel，excel不存在也會新建
        # a是追加sheet頁，不能變更原有sheet數據。excel必須存在
        with pd.ExcelWriter(excel_name + ".xlsx",mode="w") as writer:
            df.to_excel(writer,sheet_name="test10")
            df1.to_excel(writer,sheet_name="test20")









yc = pandas()
#yc.creat_dataframe()
#yc.get_dataframe()
yc.writ_to_excel("excel_writer") # 傳入參數為想要寫入的excel名字