Using "Make Query Table" in ArcPy

Question

I'm trying to join over a hundred tables (arranged in columns) in ArcGIS using Python (ArcPy). They all have the same number of rows and columns (same data, but different values). I had them as dBase tables but converted them to geodatabase tables to use the "Make Query Table" function.

Here's my script:

import arcpy
arcpy.env.workspace = " d:/INDICES5/Pixelnum/output.gdb "
tables = arcpy.ListTables()
#print (tables)
for table in tables:
 if table.endswith("NDVI_TBL"):
 #print(table)
 arcpy.MakeQueryTable_management (table, "summarystats","USE_KEY_FIELDS", "", "Pixel_num", "") ---

The problem appears to be in the last line. How can I debug this? I'm not sure what I'm missing out.

Also, if anyone has any suggestion on how I could join the tables using a different method, I would like to know.

• Vince: MakeQueryTable generates an object, but it doesn't really do anything unless it's added to the map project. This appears to be an XY Problem. Please Edit the question to focus on what you want to accomplish, that you thought MakeQueryTable could solve, because query tables aren't of much use in joins.
• FelixIP: I'd use join field tool or default dictionary with values stored in list, depending on what you're going to do with output.
• Cheee: Thanks @Vince for your response. I'm trying to join data for different indices (NDVI, NDWI, WDRI etc.), collected based on field observation for different dates. The tables are zonal statistics based on various habitat sites (polygon file). So, I'm trying to have a table for each index that contains all the different dates of observation in one file. The end goal is to export them to excel and run some statistics.
• Cheee: Thanks for your response @FelixIP. I tried using the join tool but, I didn't get the expected result. Could you explain a bit how to use the default dictionary with values stored in list?

Answer 1

I'd say join field is the tool you need. To test it I created 3 identical tables named "A","B","C": enter image description here

after running this script:

import arcpy
for i,letter in enumerate("ABC"):
 if i==0:
 first = letter
 else:
 arcpy.AddMessage("Joining %s to %s" %(letter,first))
 arcpy.management.JoinField(first,"fromn", letter, "fromn")

Table "A" looks like this:

enter image description here

As one can see you don't have to bother with field naming, ArcGIS handles duplicate names nicely.

As alternative you might use dictionary:

import arcpy
from collections import defaultdict
import pandas as pd
joined = defaultdict(list)
KEY = "fromn"
for i,letter in enumerate("ABC"):
 sourceList=[f.name for f in arcpy.ListFields(letter)]
 sourceList.remove("OBJECTID")
 rows = arcpy.da.TableToNumPyArray(letter,sourceList)
 for line in rows:
 key = line[KEY]
 reducedList = list(line)
 reducedList.remove(key)
 joined[key]+=reducedList
df = pd.DataFrame.from_dict(joined,orient='index')
df.to_csv ('C:/scratch/bca.csv', header=True)

This is how output csv table looks in Excel:

enter image description here

As one can see table is not sorted - minor issue. Column names is much bigger one, although you can assign names to DataFrame columns and index field inside your code.

• Cheee: Thank you so much @FelixIP. This is very helpful. I'll try them out, will provide an update once I do. Thanks again!
• Cheee: I was able to use join to merge the tables @FelixIP. Here's the code I worked with: import arcpy arcpy.env.workspace = "d:/INDICES5/Pixelnum/SAVI" tables = arcpy.ListTables() in_data = "d:/INDICES5/Pixelnum/SAVI/PixelnumSAVI2020.dbf" for table in tables: arcpy.JoinField_management(in_data,"Pixel_num", table,"Pixel_num") The only problem with the script is it doesn't save the new columns with their file name but creates new names (e.g. ZONE_CODE_1).
• FelixIP: Solution with pandas gives you more flexibility with columns naming, but this is a different topic. BTW a) title of your post is misleading and b) accepting solution is the way to thank here .