Merging Dataframes not based on index but values

Question

I have 2 dataframes that I'd like to merge.

The first df summarizes the top 5 most common venues in each town:

The second df summarizes the frequencies of each venue category in each town:

I'd like to merge both dataframes so that the frequency of each of the top 5 venues would also appear in the first df.

For eg.

Output on row 0:

Ang Mo Kio | Food Court | Coffee Shop | Dessert Shop | Chinese Restaurant | Jap Restaurant | 0.64 | 0.2 | 0.1 | ....

I've tried using .merge pandas

sg_venues_sorted.merge(sg_onehot_grouped, on='Town')

but that seems to be only for merging on index or column names. What if my merge is on column names of 1 df, and values of the other df?

Thanks!

"I've tried using .merge pandas" where is the code for that? — cs95
– cs95, Commented Jun 10, 2020 at 6:45

sau · Accepted Answer · 2020-06-10 08:57:44Z

1

I think you can do this without merge. A row wise operation like this

    import pandas as pd
    df1 = pd.DataFrame({"Town":['t1','t2','t3','t4','t5'],
                       "1stcommon":["c1","c2","c3","c4","c5"],
                       "2ndcommon":["c3","c8","c1","c9","c10"]})

    df2 = pd.DataFrame({"Town":['t1','t2','t3','t4','t5'],
                       "c1":[0,0.1,0.1,0.2,0],
                       "c2":[0,0.1,0.1,0.2,0],
                       "c3":[0,0.1,0.1,0.2,0],
                       "c4":[0,0.1,0.1,0.2,0],
                       "c5":[0,0.1,0.1,0.2,0],
                       "c6":[0,0.1,0.1,0.2,0],
                       "c7":[0,0.1,0.1,0.2,0],
                       "c81":[0,0.1,0.1,0.2,0],
                       "c9":[0,0.1,0.1,0.2,0],
                        "c10":[0,0.1,0.1,0.2,0]})

    def create_col(x):
        return df2.loc[df2.Town==x['Town'],x[['1stcommon','2ndcommon']]].values[0]

    df1['1st_common'],df1['2nd_common'] = zip(*df1.apply(lambda x: create_col(x),axis=1))

edited Jun 10, 2020 at 8:57

answered Jun 10, 2020 at 8:35

sau

1,3564 gold badges16 silver badges38 bronze badges

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

2 Comments

lyh198 Over a year ago

This works thanks! But I'm still confused about how lambda works... seems like it's a really useful tool

sau Over a year ago

towardsdatascience.com/…

Collectives™ on Stack Overflow

Merging Dataframes not based on index but values

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related