# 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:

• Wow, thanks so much for laying this out for me. That's quite a neat trick with the join and then applying the groupby on the resulting dictionary(?). – San990 Jun 24 '18 at 21:31
• Couldn't edit my comment above: had a follow-up: I was then trying to iterate over the new df_agg via for i, row in df_agg.iterrows() and append that to a list but that caused a misalignment of the results. Would you know if there is a better way to take each row of this df and input into a list? As another point, I wrote df_agg into a text file but sequence appears in the first line, then ID_NO, as also seen in the screen shot you provided. Was not understanding why that is so? – San990 Jun 24 '18 at 21:43
• You mean something like lista=[str(id)+ seq for id, seq in df_agg.iterrows()]? – Vincenzo Lavorini Jun 25 '18 at 8:01
• I guess I was looking for like a nice formatted table. In your screenshot for the output, "Sequence" is not aligned with "ID_NO" for example. Eventually I came across the enumerate function and that probably does work though I am stuck with the baggage of pandas. For example: 0: Sequence (1) F2 to F3 (2) F3 to F2 Name: 20, dtype: object 1: Sequence (1) F9 to F11 (2) F11 to F14 (3) F14 to F6 Name: 30, dtype: object – San990 Jul 1 '18 at 0:05