dmd7
dmd7

Reputation: 641

Create new Dataframe from matching two dataframe index's

I'm looking create a new dataframe from data in two separate dataframes - effectively matching the index of each cell and input into a two column dataframe. My real datasets have the exact same number of rows and columns, FWIW. Example below:

 DF1:
 Col1  Col2  Col3
  1     2     3
  3     8     7

 DF2:
 Col1  Col2  Col3
  A     B     E
  R     S     W

Desired Dataframe:

 Col1  Col2
   1    A
   2    B
   3    E
   3    R
   8    S
   7    W

Thank you for your help!

Upvotes: 1

Views: 141

Answers (5)

anky
anky

Reputation: 75080

Another way (alternative):

pd.concat((df1.stack(),df2.stack()),axis=1).add_prefix('Col').reset_index(drop=True)

or:

d = {'Col1':df1,'Col2':df2}
pd.concat((v.stack() for k,v in d.items()),axis=1,keys=d.keys()).reset_index(drop=True)
#or pd.concat((d.values()),keys=d.keys()).stack().unstack(0).reset_index(drop=True)

  Col1 Col2
0     1    A
1     2    B
2     3    E
3     3    R
4     8    S
5     7    W

Upvotes: 1

Shubham Sharma
Shubham Sharma

Reputation: 71689

Use, DataFrame.to_numpy and .flatten:

df = pd.DataFrame(
    {'Col1': df1.to_numpy().flatten(), 'Col2': df2.to_numpy().flatten()})

# print(df)

   Col1 Col2
0     1    A
1     2    B
2     3    E
3     3    R
4     8    S
5     7    W

Upvotes: 2

r.ook
r.ook

Reputation: 13878

pd.concat(map(lambda x: x.unstack().sort_index(level=-1), (df1, df2)), axis=1).reset_index(drop=True).rename(columns=['Col1', 'Col2'].__getitem__)

Result:

   Col1 Col2
0     1    A
1     2    B
2     3    E
3     3    R
4     8    S
5     7    W

Upvotes: 1

Sahith Kurapati
Sahith Kurapati

Reputation: 1715

You can do it easily like so:

list1 = df1.values.tolist()
list1 = [item for sublist in list1 for item in sublist]

list2 = df2.values.tolist()
list2 = [item for sublist in list2 for item in sublist]

 df = {
          'Col1': list1,
          'Col2': list2
      }

df = DataFrame(df)
print(df)

Hope this helps :)

Upvotes: 1

Dev
Dev

Reputation: 387

here is your code

df3 = pd.Series(df1.values.ravel('F'))
df4 = pd.Series(df2.values.ravel('F'))
df = pd.concat([df3, df4], axis=1)

Upvotes: 2

Related Questions