1
$\begingroup$

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: enter image description here

output:enter image description here

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']
Improve this question
$\endgroup$
0
$\begingroup$

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: enter image description here

Improve this answer
$\endgroup$
4
  • $\begingroup$ 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(?). $\endgroup$ – San990 Jun 24 '18 at 21:31
  • $\begingroup$ 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? $\endgroup$ – San990 Jun 24 '18 at 21:43
  • $\begingroup$ You mean something like lista=[str(id)+ seq for id, seq in df_agg.iterrows()]? $\endgroup$ – Vincenzo Lavorini Jun 25 '18 at 8:01
  • $\begingroup$ 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 $\endgroup$ – San990 Jul 1 '18 at 0:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.