how to combine two data frame in 1 new data frame

Question

df1:

  Cell id  lac  Lac
0     ab1    2    2
1     ab2    3    3
2     ab3    4    4

df2:

  Cell id  psc  psc
0     ab1    4    4
1     ab3    6    6
2     ab5    7    7

result--

Cell id  attribute  require  dump
    ab1        LAC        2     2
    ab2        LAC        3     3
    ab3        LAC        4     4
    ab1        PSC        4     4
    ab3        PSC        6     6
    ab5        PSC        7     7

i don't know how to combine,

score 1 · Answer 1 · answered Mar 23 '22 at 07:52

Use:

df1.columns = ['Cell id', 'require', 'dump']
df2.columns = ['Cell id', 'require', 'dump']
df1.insert(1, 'attribute', 'LAC')
df2.insert(1, 'attribute', 'PSC')
out = pd.concat([df1, df2])
print(out)

# Output
  Cell id attribute  require  dump
0     ab1       LAC        2     2
1     ab2       LAC        3     3
2     ab3       LAC        4     4
0     ab1       PSC        4     4
1     ab3       PSC        6     6
2     ab5       PSC        7     7

many many thanks, thank u so much – Rati Goyal Mar 23 '22 at 08:59 — Rati Goyal, Mar 23 '22 at 08:59

score 1 · Answer 2 · answered Mar 23 '22 at 08:00

Here is a generic method (works with any number of dataframes as input):

dfs = [df1, df2]
cols = ['require', 'dump']
(pd.concat({d.columns[1]: d.set_index('Cell id').set_axis(cols, axis=1)
           for d in dfs}, names=['attribute', 'Cell id'])
   .swaplevel() # optional, just to have columns in order
   .reset_index()
)

output:

  Cell id attribute  require  dump
0     ab1       lac        2     2
1     ab2       lac        3     3
2     ab3       lac        4     4
3     ab1       psc        4     4
4     ab3       psc        6     6
5     ab5       psc        7     7

score 0 · Answer 3 · answered Mar 23 '22 at 08:15

One way using pandas.concat with reset_index:

df1.columns = ['Cell id', 'require', 'dump']
df2.columns = ['Cell id', 'require', 'dump']

new_df = pd.concat({"LAC":df1, "PSC": df2}, names=["attribute"]).reset_index(level=0)

Output:

  attribute Cell id  require  dump
0       LAC     ab1        2     2
1       LAC     ab2        3     3
2       LAC     ab3        4     4
0       PSC     ab1        4     4
1       PSC     ab3        6     6
2       PSC     ab5        7     7

how to combine two data frame in 1 new data frame

3 Answers3