Building a dataframe based on various columns from other dataframes

Question

I have various dataframes that look like this:

df1
    ID    Number   Score   Time   Result
    a     45       0.3     2535   0.9
    b     46       0.5     345    0.8
    c     34       0.94    346    0.6
    d     36       1       356    0.7

df2 
ID Image Video
a   0     0
b   0     0
c   1     0
d   0     1
e   1     0
f   1     0

df3
ID Length
a   35
b   57
c   75
d   57
e   85
f   48

How can I merge them to look like:

ID   Time   Image   Video   Length  Score
a    2535   0        0       35      0.3
b    345    0        0       57      0.5
c    346    1        0       75      0.94
d    356    0        1       57      1

My idea is to use pd.merge (on="ID") (this will only give me the rows from a to d, right?) and then delete the unnecessary columns. But how do I move Score at the end? Is there any other approach?

jezrael · Accepted Answer · 2021-06-29 05:51:53Z

1

Chain multiple merge and then set Score to last column:

df = df1.merge(df2, on='ID').merge(df3, on='ID')
df['Score'] = df.pop('Score')

Or if there is list of DataFrames use:

from functools import reduce

dfs = [df1, df2, df3]
df = reduce(lambda df1,df2: pd.merge(df1,df2,on='ID'), dfs)
cols = df.columns.drop(['Score']).tolist() + ['Score']
df = df[cols]

edited Jun 29, 2021 at 5:51

answered Jun 29, 2021 at 5:46

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Building a dataframe based on various columns from other dataframes

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related