Is there an analog to SQL's STRING_AGG (or FOR XML PATH) function in Python? Asked this in SE but maybe this is too data-oriented so trying to post it here. I am trying to find the analog to the SQL function STRING_AGG so that I can concatenate across columns and rows of a table (or dataframe).

Here is an example of what I am trying to achieve:

input:


output:

With SQL I can easily group by the ID_No and also specify the order by via the RUN_No. The syntax for achieving what I want would be:

SELECT ID_NO,
STRING_AGG(CONCAT('(', RUN_No, ') ', Start, ' to ', Stop)) 
WITHIN GROUP (ORDER BY RUN_No ASC) AS "Sequence"
FROM X_TBL GROUP BY ID_NO


So what would be the way to achieve the same grouping, concatenating and ordering in Python? I do have my data stored as a dataframe. I am able to concatenate across columns using the following code, but then wasn't sure how to group by the "ID_No", or concatenate across the rows within each ID_No.

sample['Merge'] = sample['Start'].map(str) + ", " + sample['Stop']
Here a versatile solution. As you can see, you can modify the aggregation function in order to format the data as you want.

#the original DataFrame
df=pd.DataFrame({'ID_NO': [20, 20, 30, 30, 30], 'RUN_NO': [1,2,1,2,3], 'START': ['F2','F3','F9','F11','F14'],
                'STOP': ['F3','F2','F11','F14','F6',]})

#convert 'RUN_NO' to string. This will make the aggregation formula easier to read
df['RUN_NO']=df['RUN_NO'].apply(str)

#The aggregation function
def agg_f(x):
     return pd.Series(dict(Sequence = "%s" %  ' '.join('(' +x['RUN_NO']+ ') ' + x['START'] +' to ' + x['STOP'])))


df_agg=df.groupby('ID_NO').apply(agg_f)


The output will be: