Is there a way to concatenate the content of the respective cells of two different n * n data-frames within cells in pandas?

Is there a way to concatenate the content of the respective cells of two different n * n data-frames within cells in pandas?

For example: Say I have two data-frames df1 and df2.

df1 looks like this:

Index	Score_1	Score_2
Family_1	123	456
Family_2	789	1011

df2 looks like this:

Index	Score_1	Score_2
Family_1	A	B
Family_2	C	D

I want to make a third data-frame that takes each n * n cell from df1 and concatenate it with the content from a respective n * n cell from df2 and make a third data-frame that looks something like this:

Index	Score_1	Score_2
Family_1	A:123	B:456
Family_2	C:789	D:1011

Just to make sure: One table has strings and the other has numbers but I want to do string concatenation so using str() is necessary is fine.

pd.concat() concats whole data-frames like this:

Index	Score_1	Score_2	Score_1	Score_2
Family_1	A	B	123	456
Family_2	C	D	789	1011

I am trying to superimpose the cells instead.

I tried to do it from scratch with zip and intertuples roughly like this:

for i,j in zip(df1.itertuples(),df2.itertuples()):
    n=1
    while n < len(i):
        print(i[n], ":", j[n])
        n = n + 1
    print('n')

Whose output is along the lines of:

A:123

B:456

C:789

D:1011

Which I think I can reroute to dicts or an intermediate file and reshape from there but that seems a little unwieldy. Is there a more efficient way to do this?

Answer

You can temporarily set “Index” as index, convert to string, and add:

out = (df2
 .set_index('Index') # skip if already index
 .add(':'+df1.set_index('Index').astype(str))
 .reset_index()      # skip if already index
)

output:

      Index Score_1 Score_2
0  Family_1   A:123   B:456
1  Family_2   C:789  D:1011

If “Index” is already the index:

out = df2.add(':'+df1.astype(str))

output:

         Score_1 Score_2
Index                   
Family_1   A:123   B:456
Family_2   C:789  D:1011