CODEWITHSUNDEEP
pythonpandas
Lauren Lombardo
Lauren Lombardo

Reputation: 11

Merging two dataframes, with different lengths, and repeating values

I have two dataframes with the same col 'A' that I want to merge on. However, in df2 col A is replicated a random number of times. This replication is important to my problem and I cannot drop it. I want the final dataframe to look like df3. Where Col A merges Col B values to each replication. 

df1                       df2
Col A   Col B             Col A    Col B
1       v                 1        a
2       w                 2        b    
3       x                 2        c
4       y                 3        d
                          3        e
                          4        f

df3
Col A    Col B    Col C
1        a        v
2        b        w
2        c        w
3        d        x
3        e        x
4        f        y

Upvotes: 1

Views: 1514

Answers (2)

user2285236
user2285236

Reputation:

Use merge:

df2.merge(df1, on='Col A')
Out: 
   Col A Col B_x Col B_y
0      1       a       v
1      2       b       w
2      2       c       w
3      3       d       x
4      3       e       x
5      4       f       y

And if necessary, rename afterwards:

df = df2.merge(df1, on='Col A')
df.columns = ['Col A', 'Col B', 'Col C']

for more info, see the Pandas Documentation on merging and joining.

Upvotes: 1

jezrael
jezrael

Reputation: 862481

I believe you need map by Series created by set_index:

print (df1.set_index('Col A')['Col B'])
Col A
1    v
2    w
3    x
4    y
Name: Col B, dtype: object

df2['Col C'] = df2['Col A'].map(df1.set_index('Col A')['Col B'])
print (df2)
   Col A Col B Col C
0      1     a     v
1      2     b     w
2      2     c     w
3      3     d     x
4      3     e     x
5      4     f     y

Upvotes: 0

Related Questions