How to join two tables with adding id of table?-CodePudding

I have two tables:

name   value
a1      45
a2      77
b1      99

name   value
e1      11
f5      99
z9      99

I want to join them and add new column id showing from which table row is:

name   value   id 
a1      45     old
a2      77     old
b1      99     old
e1      11     new
f5      99     new
z9      99     new

How to do that?

CodePudding user response：

Use assign and append:

>>> df1.assign(id="old").append(df2.assign(id="new")).reset_index(drop=True)
  name  value   id
0   a1     45  old
1   a2     77  old
2   b1     99  old
3   e1     11  new
4   f5     99  new
5   z9     99  new

CodePudding user response：

Another solution, using pd.concat:

df = (
    pd.concat([df1, df2], keys=["old", "new"], names=["id"])
    .droplevel(1)
    .reset_index()
)
print(df)

Prints:

    id name  value
0  old   a1     45
1  old   a2     77
2  old   b1     99
3  new   e1     11
4  new   f5     99
5  new   z9     99