CODEWITHSUNDEEP
pythonpandasdataframe
n.mathfreak
n.mathfreak

Reputation: 145

Building a dataframe based on various columns from other dataframes

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?

Upvotes: 1

Views: 26

Answers (1)

jezrael
jezrael

Reputation: 863291

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]

Upvotes: 1

Related Questions