Make a list from a data frame that has repeated and non repeat values in columns-CodePudding

I have a data frame like this


data = [['Ma', 1,'too'], ['Ma', 1,'taa'], ['Ma', 1,'tuu',],['Ga', 2,'too'], ['Ga', 2,'taa'], ['Ga', 2,'tuu',]]
 

df = pd.DataFrame(data, columns = ['NAME', 'AID','SUBTYPE'])
 

NAME ID SUBTYPE
Ma    1     too
Ma    1     taa
Ma    1     tuu
Ga    2     too
Ga    2     taa
Ga    2     tuu

There are repeated NAME and ID and different SUBTYPE

And I want a list like this

Ma-1-[too,taa,too],Ga-2-[too,taa,tuu]

EDIT: NAME and ID should be always the same.

CodePudding user response：

Generally, to achieve this in Python we would use dictionaries as the keys cannot be duplicated.

# We combine the NAME and ID keys, so we can use them together as a key.
df["NAMEID"] = df["NAME"]   "-"   df["ID"].astype(str)

# Convert the desired fields to lists.
name_id_list = df["NAMEID"].tolist()
subtype_list = df["SUBTYPE"].tolist()

# Loop through the lists by zipping them together.
results_dict = {}
for name_id, subttype in zip(name_id_list, subtype_list):
    if results_dict.get(name_id):
        # If the key already exists then instead we append them to the end of the list. 
        results_dict[name_id].append(subttype)
    else:
        # If key not exists add them as key-value pairs to a dictionary.
        results_dict[name_id] = [subtype]

Results dict will end up looking like:

{'Ma-1': ['too', 'taa', 'tuu'], 'Ga-2': ['too', 'taa', 'tuu']}