sort dataframe by string and set a new id

Question

is there a possibility to adjust the strings according to the order for example 1.wav, 2.wav 3.wav etc. and the ID accordingly with ID: 1, 2, 3 etc? i have already tried several sorting options do any of you have any ideas? Thank you in advance

dataframe output

    data = []
    for file in Path(audioPath).glob('*.wav'):
        print(file)
        data.append([os.path.basename(file), file])

    df_dataSet = pd.DataFrame(data, columns= ['audio_name', 'filePath'])
    df_dataSet['ID'] = df_dataSet.index+1
    df_dataSet = df_dataSet[['ID','audio_name','filePath']]
    df_dataSet.sort_values(by=['audio_name'],inplace=True)
    return df_dataSet

def createSamples(myAudioPath,savePath, sampleLength, overlap = 0):
    cutSamples(myAudioPath=myAudioPath,savePath=savePath,sampleLength=sampleLength)
    df_dataSet=createSampleDF(audioPath=savePath)
    return df_dataSet

Can you please provide a minimal example of a DataFrame containing two rows as well as your expected output (both in separate code blocks)? — oda

Coup Coup · Accepted Answer · 2022-04-09T18:03:31

You can split the string, make it an integer, and then sort on multiple columns. See the pandas.Dataframe.sort_values for more info. If your links are more complicated you may need to design a regex to pull out the integers you want to sort on.

df = pd.DataFrame({
'ID':[1,2,3,4, 5],
'audio_name' : ['1.wav','10.wav','96.wav','3.wav','55.wav']})

(df
.assign(audio_name=lambda df_ : df_.audio_name.str.split('.', expand=True).iloc[:,0].astype('int'))
.sort_values(by=['audio_name','ID']))

sort dataframe by string and set a new id

1 Answers