Merging multiple columns with different column lengths

user_9871643 0
Viewed 104

I have the following two dataframes:

values = pd.DataFrame(data={'val1': ['A', 'C', 'B'], 
                     'val2': [None, 'A', None], 
                     'val3': [None, 'B', 'A']},
                     index=pd.Series([1, 2, 3]))

desc = pd.DataFrame(data={'value': ['A', 'B', 'C', 'D'], 
                    'description': ['Description for A', 'Description for B', 'Description for C', 'Description for D']},
                    index=pd.Series([1, 2, 3, 4]))
val1 val2 val3
1 A None None
2 C A B
3 B None A
value description
1 A Description for A
2 B Description for B
3 C Description for C
4 D Description for D

How do I merge the two dataframes I keep the left table, and for each column, I am joining the description from the right table?

val1 val2 val3 val1_desc val2_desc val3_desc
1 A None None Description for A None None
2 C A B Description for C Description for A Description for B
3 B None A Description for B None Description for A

I've tried

pd.merge(values, desc, how='left', left_on=['val1', 'val2', 'val3'], right_on=['value'])

but I get the following error:

ValueError: len(right_on) must equal len(left_on)
pandas join python
1 Answers

One way to approach this would be to flip values into long form, merge with desc, pivot back to wide form, and make adjustments to the columns:

df = (values.melt(ignore_index=False)
            .reset_index()
            .merge(desc, on='value')
            .rename(columns={"description":"desc", "value":""})
            .pivot('index', 'variable')
            .swaplevel(axis='columns')
     )

Flatten the columns:

 df.columns = df.columns.map("_".join)

Some more cleanup, before the final output:

df.rename(columns=lambda df: df.rstrip("_")).rename_axis(index=None)

  val1 val2 val3          val1_desc          val2_desc          val3_desc
1    A  NaN  NaN  Description for A                NaN                NaN
2    C    A    B  Description for C  Description for A  Description for B
3    B  NaN    A  Description for B                NaN  Description for A
user_71757130
Related