pandas 中dataframe的常用总结

• 获取某一列中元素的个数

df_demo['userid'].value_counts()

• 给列名重命名

 df_demo.rename(columns={"update_time_x":"update_time","department_x":"department"}, inplace=True)

• 删除指定列

 df_demo.drop(["update_time","department,"],axis=1)

• 根据某两列进行排序

df = df.sort_values(by=["end_time", "userid"])

• pandas to_sql方法写入myql时的内部事务操作：

from sqlalchemy import create_engine
    def run(self):
      engine = create_engine('mysql+pymysql://user:password@host:port/database',encoding='utf8')        
      with engine.connect() as conn:
            trans = conn.begin()
            try:
                conn.execute("""delete from table1 where end_time ='2099-12-31'""")
                my_df.to_sql(name="mytable", con=conn, index=False,if_exists="append")
            except Exception as e:
                trans.rollback()
                raise e
            else:
                trans.commit()
                trans.close()
        return "ok"

• 判断一个dataframe 是否为空：

changed_un.empty == True    #True 表示为空，False表示不为空

• 将dataframe 通过某一列或几列进行分组，生成多个dataframe，将每个datafame导出到一个excel工作簿中

gropuyby_df = pd.read_excel("aa.xlsx").groupby(['邮箱','所属销售'])
for i in gropuyby_df:
      i[1].to_excel("./FileDir/{}.xlsx".format(i[0][1]),index=False)

• 去掉dataframe中的某一列中字符串中的空格

df_today['department'] = df_today['department'].str.replace(' ', '')

• dataframe 中某一列中数值类型为字符串时，将其进行格式化输出

df_today['update_time'] = df_today['update_time'].apply(lambda x: x.strftime("%Y-%m-%d"))