(Jul-07-2022, 08:59 AM)Pedroski55 Wrote: Where are you getting your dataframes from? Are you loading Excel files? From an SQL database?

import pandas as pd
import glob

savepath = '/home/pedro/myPython/pandas/xl_files/'
saveoutputpath = '/home/pedro/myPython/pandas/outputxl/'
# I put an extra column in each excel file named country with either Canada, Mexico or USA
filelist = glob.glob(savepath + "*.xlsx")
 
df = pd.concat((pd.read_excel(f) for f in filelist))
#df = df.fillna('') # replace nan
#df_usa = pd.read_excel(csv_file)

# change the indexes to get unique indexes
# df.index.size gets how many indexes there are
indexes = []
for i in range(df.index.size):
    indexes.append(i)
# now change the indexes pass a list to df.index
# never good to have 2 indexes the same
df.index = indexes

# this gets the dictionary ok
mydict = {i:[df.iloc[i, 0], df.iloc[i, 1], df.iloc[i,2]] for i in indexes}
print(len(mydict))
# get a list of the ids
mylist = df["id"].values.tolist()
# get a set of the unique ids
myset = set(mylist)

#create new DataFrame
df_out = pd.DataFrame(columns=['id', 'Canada', 'Mexico', 'USA'], index=range(0, len(myset)))

# make a list of unique ids
id_names = list(myset)
id_names.sort()
# populate the id column with id_names
df_out["id"] = id_names
# see how many rows and columns
print(df_out.shape)

# mydict[key][0] is the id column , mydict[key][2]]is the country
for key in mydict.keys():    
    df_out.loc[df_out["id"] == mydict[key][0], mydict[key][2]] = "X"

# put zeroes in all cells with NaN
df_out.fillna(0)
df_out.to_excel(saveoutputpath + 'my_saved_data.xlsx', sheet_name='mydata', index=True)