Concatenate String Columns and Order in Alphabetical Order

Concatenating two columns of text Alphabetically in Excel

With Excel one can use mathematical inequalities for text strings in many cases. In C2 put:

=IF(A2<B2,A2&"-"&B2,B2&"-"&A2)

Then copy down

Concatenate columns in alphabetic order in BigQuery

try the following. you can use order by inside string_agg. here is the documentation.

select
    c1,
    string_agg(c2 order by c2) as c2
from yourTable
group by
    c1

Pandas merging strings in columns and sorting them

You can filter out Nones, sorting and join in list comprehension should be very fast:

df['merged'] = [', '.join(sorted(filter(None, x))) for x in df.to_numpy()]

Alternative with lambda function is slowier:

df['merged'] = df.apply(lambda x: ', '.join(sorted(filter(None, x))), axis=1)
print (df)
   x1    x2    x3    x4      merged
0  US    DE  None  None      DE, US
1  FR    DE    US  None  DE, FR, US
2  FR  None  None  None          FR
3  DE    CA  None  None      CA, DE

If use pure pandas methods and large DataFrame this should be slowiest:

s = df.stack().sort_values().groupby(level=0).agg(', '.join)
print (s)
0        DE, US
1    DE, FR, US
2            FR
3        CA, DE
dtype: object

mysql combine columns into one and sort in alphabetic order

bumped into solution myself. since Abbreviation column can be NULL or have value, I can use COALESCE that'll get me the first not null column, in other words:

select id, coalesce(abbreviation, name) as thename from sometable order by thename;

Creating new column combining info in two columns, alphabetically

df = read.table(text = "
Home_team Away_team Home_score Away_score
Arsenal    Chelsea      1        3
ManchesterU  Blackburn 2        9
Liverpool      Leeds    0        8
Chelsea     Arsenal     4        1
", header=T, stringsAsFactors=F)

library(dplyr)

df %>%
  rowwise() %>%      # for each row
  mutate(Teams = paste(sort(c(Home_team, Away_team)), collapse = " - ")) %>%  # sort the teams alphabetically and then combine them separating with -
  ungroup()          # forget the row grouping

# # A tibble: 4 x 5
#   Home_team   Away_team Home_score Away_score Teams                  
#   <chr>       <chr>          <int>      <int> <chr>                  
# 1 Arsenal     Chelsea            1          3 Arsenal - Chelsea      
# 2 ManchesterU Blackburn          2          9 Blackburn - ManchesterU
# 3 Liverpool   Leeds              0          8 Leeds - Liverpool      
# 4 Chelsea     Arsenal            4          1 Arsenal - Chelsea 

An alternative solution without rowwise:

# create function and vectorize it
f = function(x,y) {paste(sort(c(x, y)), collapse = " - ")}
f = Vectorize(f)

# apply function to your dataset
df %>% mutate(Teams = f(Home_team, Away_team))

---

Related Topics