How to merge integers from multiple cells to one in pandas?-CodePudding

I am giving up the SQL solution, and now switching to Pandas. My goal is to merge the integer data as below:

Data input:

ACCT	SOURCES
A	1
A	2
B	1
C	4

expected output:

ACCT	SOURCES
A	1,2
B	1
C	4

CodePudding user response：

Given:

  ACCT  SOURCES
0    A        1
1    A        2
2    B        1
3    C        4

Doing:

df.SOURCES = df.SOURCES.astype(str)
df = df.groupby('ACCT', as_index=False)['SOURCES'].agg(','.join)
print(df)

Output:

  ACCT SOURCES
0    A     1,2
1    B       1
2    C       4

CodePudding user response：

You can use XMLAGG to concatenate them together. It puts spaces between the values, you can replace those with a comma. The innermost cast is if sources is actually defined as integer, not char/varchar.

select
acct,
oreplace(cast(xmlagg(cast(sources as varchar(5))) as varchar(10000)),' ',',')
from
<your table>
group by
acct

CodePudding user response：

I am not familiar with Teradata SQL environment.

Another solution I found is

select acct,
group_concat(distinct sources order by sources) as sources 

from ...

group by acct

But it shows syntax error: expected something between '(' and the 'distinct' keyword.