Home > Mobile >  How to merge integers from multiple cells to one in pandas?
How to merge integers from multiple cells to one in pandas?

Time:06-07

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.

  • Related