CODEWITHSUNDEEP
pythonsqlpython-3.xpandasappend
singmotor
singmotor

Reputation: 4180

"data type not understood" when appending DataFrames

So I'm starting with a blank DataFrame, pulling a bunch of new dataFrames from SQL statements based on a list of table names, and then appending each one to the starting dataFrame. ex:

df1 = pd.DataFrame()
for name in tableslist:
    perT = pd.read_sql_query("SELECT FROM myschema.{0} WHERE username = '{1}'.format(name,user),engine)
    df1 = df1.append(perT)

That returns the error: data type not understood in the terminal. I'm positive that it is caused by the append line (I've checked with print statements). What could cause that? It's possible for perT to be a table with no rows. Is that an issue? I've tried printing out the head of each table, and still can't determine what is causing that error/failure.

Upvotes: 3

Views: 5861

Answers (1)

Ido S
Ido S

Reputation: 1352

I've found that with older versions of pandas this can happen with when date fields are present in the dataframe (specifically datetime64 variants). In the past I've used a number of workarounds (see below), hard to know exactly what can help you without seeing the data.

  1. Cast all date fields into pandas timestamps (df[col_name].apply(pd.Timestamp)).
  2. Cast all date fields into strings (df[col_name].astype(str)).

  3. Your initial dataframe is an empty dataframe. Instead of trying to append a non-empty dataframe to an empty one, set the initial one to equal the first non-empty dataframe, and then keep appending.

    if df1.empty:
    df1 = perT
else:
    df1 = df1.append(perT)

  4. Upgrade pandas :)

Upvotes: 5

Related Questions