1

Let us say I have this data frame.

df

line  to_line   priority
10    20          1
10    30          1
50    40          3
60    70          2
50    80          3

Based on the line and priority column values (when the are the same or duplicate as shown above), I want to combine to_line values. The proposed result should look like the following. 

line  to_line    priority
10        20/30         1
50        40/80         3
60         70           2

I tried something like this but I couldn't get what I want.

df.groupBy(col("line")).agg(collect_list(col("to_line")) as "to_line").withColumn("to_line", concat_ws(",", col("to_line")))

Could you please help to figure out this? I appreciate your time and effort.

Improve this question
2
  • if the last line will be 100 80 3 ? Commented Oct 17, 2019 at 14:37
  • 3
    Please include your data as text rather than images. Commented Oct 17, 2019 at 14:37

3 Answers 3

Reset to default
5

You can achieve this by custom aggregation function.

Code

df = pd.DataFrame({
    'line': [10,10,50,60,50],
    'to_line': [20,30,40,70,80],
    'priority': [1,1,3,2,3] 
})

array_agg = lambda x: '/'.join(x.astype(str))

grp_df = df.groupby(['line', 'priority']).agg({'to_line': array_agg})

, or if you do not want grouped columns to be indexes, you can pass as_index argument to groupbymethod

grp_df = df.groupby(['line', 'priority'], as_index=False).agg({'to_line': array_agg})

Output

              to_line
line priority        
10   1          20/30
50   3          40/80
60   2             70
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

Maybe something like this: 

res = []
df.to_line = df.to_line.astype(str)
for line_priority, df_chunk in df.groupby(['line','priority']):
    df_chunk = df_chunk.reset_index().sort_values('to_line')
    to_line = "/".join(df_chunk.to_line.values)
    res.append({'to_line':to_line,'priority':line_priority[1],'line':line_priority[0]})
pd.DataFrame(res)
Improve this answer

Comments

0

You can use

df.groupby(['line','priority'])['to_line'].apply(lambda x: '/'.join(str(y) for y in x)).reset_index(name='to_line')

output

  line  priority    to_line
0   10        1     20/30
1   50        3     40/80
2   60        2     70
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.