def to_tde(df, extractname, data_type, index = False, new_extract = False, append = True):
'''
change pandas dataframe to tableau data extract
'''
table_definition_map = {
'bool' : Type.BOOLEAN,
'integer': Type.INTEGER,
'double': Type.DOUBLE,
'date': Type.DATE,
'datetime': Type.DATETIME,
'unicode': Type.UNICODE_STRING,
'char': Type.CHAR_STRING
}
value_set_map = {
Type.BOOLEAN: lambda row, col_num, value: row.setBoolean( col_num, bool(value) ),
Type.INTEGER: lambda row, col_num, value: row.setInteger( col_num, int(value) ),
Type.DOUBLE: lambda row, col_num, value: row.setDouble( col_num, float(value) ),
Type.UNICODE_STRING: lambda row, col_num, value: row.setString( col_num, unicode(value) ),
Type.CHAR_STRING: lambda row, col_num, value: row.setCharString( col_num, value ),
Type.DATE: lambda row, col_num, value: row.setDate(col_num, value.year, value.month, value.day),
Type.DATETIME: lambda row, col_num, value: row.setDateTime( col_num, value.year, value.month, value.day,
value.hour, value.minute, value.second,
value.microsecond/100 )
}
if index:
df_new = df.reset_index()
else:
df_new = df
if extractname[-4:] != '.tde':
extractname += '.tde'
if new_extract & (extractname in os.listdir('.')):
os.remove(extractname)
extract_to_save = Extract(extractname)
if extract_to_save.hasTable("Extract") & append:
table_to_save = extract_to_save.openTable("Extract")
table_definition = table_to_save.getTableDefinition()
else:
table_definition = TableDefinition()
for colname in df_new.columns:
type_code = table_definition_map[data_type.get(colname, 'unicode').lower()]
table_definition.addColumn(colname, type_code)
table_to_save = extract_to_save.addTable("Extract", table_definition) # currnetly table cannot be Extract!!!
for df_row_tuple in df_new.iterrows():
new_row = Row(table_definition)
# new_rwo is a tuple of len 2, index and the row as series
df_row = df_row_tuple[1]
for col_num, (col_val, null_col) in enumerate(zip(df_row, df_row.isnull())):
if null_col:
new_row.setNull(col_num)
else:
value_set_map[table_definition.getColumnType(col_num)](new_row, col_num, col_val)
table_to_save.insert(new_row)
extract_to_save.close()
ExtractAPI.cleanup()
def to_tde(df, tde_filename='extract.tde'):
if os.path.isfile(tde_filename):
os.remove(tde_filename)
ExtractAPI.initialize()
new_extract = Extract(tde_filename)
df = dedup_column_name(df)
row_cnt, col_cnt = df.shape
list_dtypes = df.dtypes
table_definition = make_table_definition(df)
new_table = new_extract.addTable('Extract', table_definition)
# for j, row in df.iterrows():
for j, row in tqdm.tqdm(df.iterrows(), total=row_cnt):
new_row = Row(table_definition)
for i, (cell, column_data_type) in enumerate(zip(row, list_dtypes)):
value = mapper[column_data_type]['value_modifier'](cell)
if value:
params = [new_row, i] + value
mapper[column_data_type]['tableau_set_function'](*params)
# for i,column in enumerate(df.columns):
# column_data_type = df[column].dtype
# value = mapper[column_data_type]['value_modifier'](row[1][i])
#
# if value:
# params = [new_row,i]+value
# mapper[column_data_type]['tableau_set_function'](*params)
#
new_table.insert(new_row)
new_extract.close()
ExtractAPI.cleanup()
def insert_tde(x):
new_row = Row(table_definition)
for i, (cell, column_data_type) in enumerate(zip(x, list_dtypes)):
value = mapper[column_data_type]['value_modifier'](cell)
if value:
params = [new_row, i] + value
mapper[column_data_type]['tableau_set_function'](*params)
new_table.insert(new_row)
def insert_data_to_extract(self, db_data_row):
"""
Inserts the data row by row into the tableau extract skeleton
:param db_data_row: row from the database
"""
# Get the row of data to insert
insert_row = Row(self._table_definition)
# Map the column type to the TDE insert function
extract_type_map = {
Type.INTEGER: insert_row.setInteger,
Type.DOUBLE: insert_row.setDouble,
Type.BOOLEAN: insert_row.setBoolean,
Type.DATE: insert_row.setDate,
Type.DATETIME: insert_row.setDateTime,
Type.CHAR_STRING: insert_row.setCharString,
Type.UNICODE_STRING: insert_row.setString,
}
# Iterate through each column of the row to identify the type
for column_pos, column_type in self._column_metadata.iteritems():
extract_col_pos_ = column_pos - 1
insert_row.Insert = extract_type_map[column_type]
# If there is any NULL data replace with corresponding NULL type data for the field
column_data = db_data_row[extract_col_pos_]
column_data = self._replace_null(column_type, column_data)
# Identify the insert function for the data
try:
# Date time field
if column_type == 13:
year, month, day, hour, minute, sec, frac = format_datetime(
column_data)
insert_row.Insert(extract_col_pos_, year, month, day, hour,
minute, sec, frac)
# Date field
elif column_type == 12:
year, month, day = format_date(column_data)
insert_row.Insert(extract_col_pos_, year, month, day)
# Other fields
else:
insert_row.Insert(extract_col_pos_, column_data)
except TableauException as e:
raise e
# Insert the row
self._table.insert(insert_row)
insert_row.close()
def to_tde(df,tde_filename = 'extract.tde'):
ExtractAPI.initialize()
new_extract = Extract(tde_filename)
df = dedup_column_name(df)
table_definition = make_table_definition(df)
new_table = new_extract.addTable('Extract', table_definition)
for row in df.iterrows():
new_row = Row(table_definition)
for i,column in enumerate(df.columns):
column_data_type = df[column].dtype
value = mapper[column_data_type]['value_modifier'](row[1][i])
if value:
params = [new_row,i]+value
mapper[column_data_type]['tableau_set_function'](*params)
new_table.insert(new_row)
new_extract.close()
ExtractAPI.cleanup()
def get_tde_row(table_definition, row_data):
row = Row(table_definition)
for idx, data in enumerate(row_data):
t = table_definition.getColumnType(idx)
try:
if t is Type.DOUBLE:
row.setDouble(idx, data)
elif t is Type.BOOLEAN:
row.setBoolean(idx, data)
elif t is Type.CHAR_STRING:
row.setCharString(idx, data)
elif t is Type.DATE:
row.setDate(idx, data.year, data.month, data.day)
elif t is Type.DATETIME:
row.setDateTime(idx, data.year, data.month, data.day, data.hour, data.minute, data.second, 0)
elif t is Type.INTEGER:
row.setInteger(idx, data)
elif t is Type.UNICODE_STRING:
row.setString(idx, data)
except Exception as ex:
row.setNull(idx)
return row
