Python 3: How to combine values from a csv and append them to a new csv depending on a key value

Question

I have a .csv table that looks like this:

original csv

I want to get a new .csv data that looks like this:

new csv

I already got to the point that I have the second csv with the unique values of the SITENAMES in the first column, but now I'm struggling to append the SPECIESNAMES into the second column.

uri = 'file:///C:/Users/t/Desktop/T/Natura/Python/20220214_Natura2000_specieslist.txt'
csvLyr = QgsVectorLayer(uri, "csvLayer", "delimitedtext")

spalten = ["SITECODE"]
sitecodes = pd.read_csv(uri, usecols=spalten)

spalten2 = ["SPECIESNAME_deutsch"]
species = pd.read_csv(uri, usecols=spalten2)

#### Schritt 2: Mithilfe von unique() die unique values der Sidecodes erhalten und als neue Spalte in eine csv schreiben

sitecodes_unique = sitecodes.SITECODE.unique()
print(sitecodes_unique)
print(len(sitecodes_unique))

path = 'C:/Users/t/Desktop/T/Natura/Python/Ergebnisse'
if not os.path.isdir(path):
   os.makedirs(path)


with open('C:/Users/t/Desktop/T/Natura/Python/Ergebnisse/sitecodes_namen.csv', 'w+', newline='') as f:
   wr = csv.writer(f)
   for line in sitecodes_unique:
       sitecodes_unique_split = line.split(',')
       wr.writerow(sitecodes_unique_split)

Answer 1

Try this natural python code a viable alternative which calls directly a csv file instead of txt. I've tried to use collections as mentioned by @JonSG :

sitecodes = pd.read_csv('file:///C:/Users/t/Desktop/T/Natura/Python/20220214_Natura2000_specieslist.csv', index_col= False)
sitecodes_df = pd.DataFrame(sitecodes,columns = sitecodes.columns)
sitecodes_namen = defaultdict(list)
for i in range(len(sitecodes_df)):
    if sitecodes_df['SITECODE'][i] in sitecodes_namen.keys():
        sitecodes_namen[sitecodes_df['SITECODE'][i]]+=','+sitecodes_df['SPECIESNAME_deutsch'][i]
else:
    sitecodes_namen[sitecodes_df['SITECODE'][i]] = sitecodes_df['SPECIESNAME_deutsch'][i]
df = pd. DataFrame(list(sitecodes_namen.items()), columns = sitecodes.columns)
df.to_csv('C:/Users/t/Desktop/T/Natura/Python/Ergebnisse/sitecodes_namen.csv',index=False)