删除SQLAlchemy或Pandas中的空白

Question

我使用SQLAlchemy从MSSQL数据库查询数据，然后用pandas保存为excel文件。我正在寻找类似于T-SQL的RTRIM的东西，以便从我的数据中删除任何尾随的空白。

我知道如何从列标题中删除空白，但不知道从数据本身。因此，我要么需要在查询时删除空白，要么需要在pandas数据框架中删除空白，但我不知道如何删除空白(因为大多数搜索在解析时检索如何删除空白，而不是写入数据)。

目前为止我的代码是:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import scoped_session,sessionmaker
from sqlalchemy import (Column, Integer, String, Boolean, ForeignKey, DateTime, Sequence, Float)
from sqlalchemy import create_engine
import pandas as pd
import openpyxl

pd.core.format.header_style = None 
pd.core.format.number_format = None 
def data_frame(query, columns):
    def make_row(x):
        return dict([(c, getattr(x, c)) for c in columns]) 
    return pd.DataFrame([make_row(x) for x in query])
engine = create_engine('mssql+pyodbc://u:pass@MyServer/MYDBt?driver=SQL Server', echo=False)
Session = sessionmaker(bind=engine)
session = Session()
Base = declarative_base()
class Tranv(Base):
    __tablename__ = "Transactions"
    part_number = Column(String(20), primary_key=True)
    time_stamp = Column(String(20))
    employee_number = Column(String(6))
    action = Column(String(20)) 
newvarv = session.query(Tranv).filter_by(employee_number='001841').filter_by(time_stamp='2015-10-01 10:49:53.230')
dfx = data_frame(newvarv, [c.name for c in Tranv.__table__.columns])
dfx.columns = dfx.columns.str.strip()
dfx = dfx.rename(columns=lambda x: x.strip())
writer = pd.ExcelWriter('C:\Users\grice\Desktop\Auto_Scrap_Report\testy.xlsx')
writer.date_format = None
writer.datetime_format = None
dfx.to_excel(writer, sheet_name='Sheet1', index=False)
writer.save()

Answer 1

好吧，可能有一种更优雅的方式，但这一种对我来说很有效:

In [2]:
df = pd.DataFrame(data={"names": ["John ", "Jack"], "surnames": ["Andrews", " McAllister"]})
In [3]:
df
Out[3]:
    names   surnames
0   John    Andrews
1   Jack    McAllister
2 rows × 2 columns
In [9]:
df = df.apply(lambda x: x.str.strip())
In [10]:
df.loc[0, "names"]
Out[10]:
'John'