Using Join Field in ArcPy to merge multiple tables

Question

I'm trying to merge multiple tables using the Join Field function in ArcPy. This script currently runs perfectly to merge the multiple tables but gives the columns new names. However, I will like the file name appended to the new columns as they are added to the table.

How can I modify my code to add columns with the file name appended?

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")      

Answer 1

There are lots of issues you have not discussed that make this question non-trivial.

1. Firstly you are joining to an existing dbf file, that file format has a maximum field name length of 10 character.
2. You don't give any indication of what the table names are; are they the usually 30+ characters file names you get with satellite data? You would be unable to create a field name from these.
3. Your table names may have invalid characters that could not be used in creating a field name
4. Your table names might be starting with a number that would also be invalid as a field name.

So you need resolve those issues as you build your field name, this is nothing more than simple string manipulation which you could research on the internet.

To overcome the limitation of short field names you would need to join your data to a file geodatabase table as they support much long field names and also field aliases.