Pandas read \u csv有没有办法将sqlachemy数据类型转换为Pandas数据类型?

2024-03-29 14:04:00 发布

您现在位置:Python中文网/ 问答频道 /正文

我试图读取一个csv文件,我最终会把它推到一个数据库中。我有一个预先确定的数据模型,这些数据最终将作为。csv也没有标题。你知道吗

例如,如果我定义一个模型:

class SamFoo(Base):
    __tablename__ = 'foo'

    duns = Column(Text)
    duns_plus_four = Column(Text)
    cage_code = Column(Text)
    dodaac = Column(Text)
    sam_extract_code = Column(Text)
    purpose_of_registration = Column(Text)
    initial_registration_date = Column(DateTime)
    ...

如果我尝试read_csv

sam_name_type_dict = {c.name: c.type for c in SamFoo.__table__.c}
sam_name_type_dict.pop('id', None) # id isn't in csv data.
raw_data = pd.read_csv(
        data,
        sep='|',
        skiprows=1,
        header=None,
        names=list(sam_name_type_dict.keys()),
        dtype=sam_name_type_dict,
    )

我得到了一个TypeError: data type not understood,所以我的问题是,有没有办法将我的数据类型映射到熊猫数据类型?你知道吗


Tags: csvtextnameinidreaddatasam
1条回答
网友
1楼 · 发布于 2024-03-29 14:04:00

我是这样解决的:

from sqlalchemy.types import DateTime, Integer, Boolean, Text
import numpy as np
types_map = {
    Text: object,
    DateTime: np.datetime64,
    Integer: np.float64, # NA integer gotcha https://pandas.pydata.org/pandas-docs/stable/gotchas.html#support-for-integer-na
    Boolean: bool,
}

# had to split dates and regular dtypes into separate dicts
sam_sql_types = {c.name: c.type for c in SamFoo.__table__.c}
sam_sql_types.pop('id', None) # no id in csv
sam_dtypes = {k: types_map[type(v)] for k, v in sam_sql_types.items()}
sam_dates = {k: v for k, v in sam_dtypes.items() if v == np.datetime64}
sam_dtypes_non_dates = {k: v for k, v in sam_dtypes.items() if v != np.datetime64}

raw_data = pd.read_csv(
    data,
    sep='|',
    skiprows=1,
    header=None,
    names=list(sam_sql_types.keys()),
    dtype=sam_dtypes_non_dates,
    parse_dates=list(sam_dates.keys())
)

相关问题 更多 >