python pandas: how to use concat() to do a SQL union?

Question

I understand from the documentation that I can use concat() to do the equivalent of a SQL UNION.

However, this only seems to work when the columns have the same name. What can I do when the columns have different names?

Let's say I have a table with 3 fields: id, value1, value2 and I want a new table with 2 columns, where id is repeated, and value2 is appended below value1.

In SQL I'd do:

select id, value1 from df
UNION
select id, value2 from df

and value1 and value2 would end up in the same column even if they have different names. If I try this in pandas:

import pandas as pd

df=pd.DataFrame()
df['id']=np.arange(0,10)
df['value1']=np.arange(100,110)
df['value2']=np.arange(300,310)

dfnew = pd.concat( [ df[['id','value1']], df[['id','value2']] ])

I end up with a dataframe with 3, not 2 columns.

One solution would be to manually rename the columns. This works and gives me the result I want:

dfnew = pd.concat( [ df[['id','value1']], df[['id','value2']].rename(columns={'value2':'value1'}) ])

but it seems very convoluted to me.

Any better ideas? Thanks

Answer 1

You could try the melt function

pd.melt(df, id_vars=['id'])[['id', 'value']]