How to join two df's on the basis of column values and row values?

Question

I have a column of df (df1), let's say, column1 consisting of random months and years in the format %b-%y and column2 consisting of some numbers. I have another df (df2) which has column headers of random months and years in the same format as df1, let's say Jan-21 to Dec-21 for the sake of simplicity. Both df's also have some columns in common.

Is there a way to join df1 and df2 on the basis of the columns and populate df2 with numbers from column2?

df1,

    column1 column3 column4 column5 column6
0   ABCD    CT      AA      Apr-21  123
1   ABCD    CT      EE      Jun-21  24
2   ABCD    CT      CS      Jul-21  123
3   ABCD    CT      UUU     Jan-21  123
4   ABCD    CT      MMM     Apr-20  432
5   ABCD    CT      CCC     Aug-21  312
6   ABCD    CP      AA      Jul-21  4
7   ABCD    CP      EE      Jun-21  2
8   ABCD    CP      CS      Nov-21  0
9   ABCD    CP     UUU      Mar-21  34
10  ABCD    CP     MMM      Sep-21  234
11  ABCD    CP     CCC      Oct-21  123

df2 is the following and the format is fixed, need to populate it with column6 on the basis of column5 with columns 1,3,4 the same in df2.

{'column1': ['ABCD', 'ABCD', 'ABCD', 'ABCD', 'ABCD', 'ABCD', 'ABCD', 'ABCD', 'ABCD', 'ABCD', 'ABCD', 'ABCD', 'ABCD', 'ABCD', 'ABCD', 'ABCD', 'ABCD', 'ABCD', 'ABCD', 'ABCD', 'ABCD', 'ABCD', 'ABCD', 'ABCD'], 'column2': ['V', 'V', 'V', 'V', 'V', 'V', 'V', 'V', 'V', 'V', 'V', 'V', 'V', 'V', 'V', 'V', 'V', 'V', 'V', 'V', 'V', 'V', 'V', 'V'], 'column3': ['CT', 'CT', 'CT', 'CT', 'CT', 'CT', 'CT', 'CT', 'CT', 'CT', 'CT', 'CT', 'CP', 'CP', 'CP', 'CP', 'CP', 'CP', 'CP', 'CP', 'CP', 'CP', 'CP', 'CP'], 'column4': ['OCP', 'AC', 'TC', 'AA', 'EE', 'CS', 'UUU', 'MMM', 'CCC', 'TOQ', 'OC', 'FR', 'OCP', 'AC', 'TC', 'AA', 'EE', 'CS', 'UUU', 'MMM', 'CCC', 'TOQ', 'OC', 'FR'], 'Jan-21': [nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan], 'Feb-21': [nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan], 'Mar-21': [nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan], 'Apr-21': [nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan], 'May-21': [nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan], 'Jun-21': [nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan], 'Jul-21': [nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan], 'Aug-21': [nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan], 'Sep-21': [nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan], 'Oct-21': [nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan], 'Nov-21': [nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan], 'Dec-21': [nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan]}

The final output should look like this,

I cannot use pivot because I want to retain the month column headers in df2.

Does this answer your question? [Merge two data frames based on common column values in Pandas](https://stackoverflow.com/questions/43297589/merge-two-data-frames-based-on-common-column-values-in-pandas) — deadvoid, Feb 28 '21 at 15:38

score 3 · Answer 1 · answered Feb 28 '21 at 16:43

You do not need to introduce "placeholder" months to achieve this. You can set a DatetimeIndex and reindex your DataFrame to fill in the missing dates. Afterward, it's just a pivot table.

Here is an example:

import pandas as pd
import io
from datetime import (date, datetime)

df1_text = """
index  column1 column3 column4 column5 column6
0   ABCD    CT      AA      Apr-21  123
1   ABCD    CT      EE      Jun-21  24
2   ABCD    CT      CS      Jul-21  123
3   ABCD    CT      UUU     Jan-21  123
4   ABCD    CT      MMM     Apr-20  432
5   ABCD    CT      CCC     Aug-21  312
6   ABCD    CP      AA      Jul-21  4
7   ABCD    CP      EE      Jun-21  2
8   ABCD    CP      CS      Nov-21  0
9   ABCD    CP     UUU      Mar-21  34
10  ABCD    CP     MMM      Sep-21  234
11  ABCD    CP     CCC      Oct-21  123
"""


def s2d(s):
    return(datetime.strptime(s, '%b-%y'))


df1 = pd.read_csv(io.StringIO(df1_text), sep='\s+',
                  index_col=[0], converters={'column5': s2d})

# set datetime as index and group by columns
df1 = df1.set_index('column5').groupby(['column1', 'column3', 'column4'])

# reindex by full daterange
df1 = df1.apply(lambda x: x.reindex(pd.date_range(
    date(2021, 1, 1), date(2021, 12, 31), freq='MS')))

# drop extra columns
df1.drop(['column1', 'column3', 'column4'], axis=1, inplace=True)

# reset group by index
df1.reset_index(['column1', 'column3', 'column4'], inplace=True)

# reset datetime index
df1.reset_index(inplace=True)

# pivot table
df1 = df1.pivot_table(
    index=['column1', 'column3', 'column4'],
    columns='index',
    values='column6',
    dropna=False  # make sure empty months do not get dropped
)

# reformat the columns back to '%b-&y'
df1.columns = [datetime.strftime(d, '%b-%y') for d in df1.columns]

# reset_index
df1.reset_index(inplace=True)

# df2 without months
df2 = pd.DataFrame({
    'column1': ['ABCD', 'ABCD', 'ABCD', 'ABCD', 'ABCD', 'ABCD', 'ABCD', 'ABCD',
                'ABCD', 'ABCD', 'ABCD', 'ABCD', 'ABCD', 'ABCD', 'ABCD', 'ABCD',
                'ABCD', 'ABCD', 'ABCD', 'ABCD', 'ABCD', 'ABCD', 'ABCD', 'ABCD'],
    'column2': ['V', 'V', 'V', 'V', 'V', 'V', 'V', 'V', 'V', 'V', 'V', 'V', 'V',
                'V', 'V', 'V', 'V', 'V', 'V', 'V', 'V', 'V', 'V', 'V'],
    'column3': ['CT', 'CT', 'CT', 'CT', 'CT', 'CT', 'CT', 'CT', 'CT', 'CT',
                'CT', 'CT', 'CP', 'CP', 'CP', 'CP', 'CP', 'CP', 'CP', 'CP',
                'CP', 'CP', 'CP', 'CP'],
    'column4': ['OCP', 'AC', 'TC', 'AA', 'EE', 'CS', 'UUU', 'MMM', 'CCC', 'TOQ',
                'OC', 'FR', 'OCP', 'AC', 'TC', 'AA', 'EE', 'CS', 'UUU', 'MMM',
                'CCC', 'TOQ', 'OC', 'FR']})

# merge df2 with df1
df2 = df2.merge(df1, on=['column1', 'column3', 'column4'], how='left')

anky · Answer 2 · 2021-02-28T17:07:21.970

You can try pivot_table with set_index+fillna

cols = ['column1', 'column3', 'column4']
u = df1.pivot_table(index=cols,columns='column5',values='column6')
out = df2.set_index(cols).fillna(u).reset_index().reindex(df2.columns,axis=1)

Output:

print(out)

   column1 column2 column3 column4  Jan-21  Feb-21  Mar-21  Apr-21  May-21  \
0     ABCD       V      CT     OCP     NaN     NaN     NaN     NaN     NaN   
1     ABCD       V      CT      AC     NaN     NaN     NaN     NaN     NaN   
2     ABCD       V      CT      TC     NaN     NaN     NaN     NaN     NaN   
3     ABCD       V      CT      AA     NaN     NaN     NaN   123.0     NaN   
4     ABCD       V      CT      EE     NaN     NaN     NaN     NaN     NaN   
5     ABCD       V      CT      CS     NaN     NaN     NaN     NaN     NaN   
6     ABCD       V      CT     UUU   123.0     NaN     NaN     NaN     NaN   
7     ABCD       V      CT     MMM     NaN     NaN     NaN     NaN     NaN   
8     ABCD       V      CT     CCC     NaN     NaN     NaN     NaN     NaN   
9     ABCD       V      CT     TOQ     NaN     NaN     NaN     NaN     NaN   
10    ABCD       V      CT      OC     NaN     NaN     NaN     NaN     NaN   
11    ABCD       V      CT      FR     NaN     NaN     NaN     NaN     NaN   
12    ABCD       V      CP     OCP     NaN     NaN     NaN     NaN     NaN   
13    ABCD       V      CP      AC     NaN     NaN     NaN     NaN     NaN   
14    ABCD       V      CP      TC     NaN     NaN     NaN     NaN     NaN   
15    ABCD       V      CP      AA     NaN     NaN     NaN     NaN     NaN   
16    ABCD       V      CP      EE     NaN     NaN     NaN     NaN     NaN   
17    ABCD       V      CP      CS     NaN     NaN     NaN     NaN     NaN   
18    ABCD       V      CP     UUU     NaN     NaN    34.0     NaN     NaN   
19    ABCD       V      CP     MMM     NaN     NaN     NaN     NaN     NaN   
20    ABCD       V      CP     CCC     NaN     NaN     NaN     NaN     NaN   
21    ABCD       V      CP     TOQ     NaN     NaN     NaN     NaN     NaN   
22    ABCD       V      CP      OC     NaN     NaN     NaN     NaN     NaN   
23    ABCD       V      CP      FR     NaN     NaN     NaN     NaN     NaN   

    Jun-21  Jul-21  Aug-21  Sep-21  Oct-21  Nov-21  Dec-21  
0      NaN     NaN     NaN     NaN     NaN     NaN     NaN  
1      NaN     NaN     NaN     NaN     NaN     NaN     NaN  
2      NaN     NaN     NaN     NaN     NaN     NaN     NaN  
3      NaN     NaN     NaN     NaN     NaN     NaN     NaN  
4     24.0     NaN     NaN     NaN     NaN     NaN     NaN  
5      NaN   123.0     NaN     NaN     NaN     NaN     NaN  
6      NaN     NaN     NaN     NaN     NaN     NaN     NaN  
7      NaN     NaN     NaN     NaN     NaN     NaN     NaN  
8      NaN     NaN   312.0     NaN     NaN     NaN     NaN  
9      NaN     NaN     NaN     NaN     NaN     NaN     NaN  
10     NaN     NaN     NaN     NaN     NaN     NaN     NaN  
11     NaN     NaN     NaN     NaN     NaN     NaN     NaN  
12     NaN     NaN     NaN     NaN     NaN     NaN     NaN  
13     NaN     NaN     NaN     NaN     NaN     NaN     NaN  
14     NaN     NaN     NaN     NaN     NaN     NaN     NaN  
15     NaN     4.0     NaN     NaN     NaN     NaN     NaN  
16     2.0     NaN     NaN     NaN     NaN     NaN     NaN  
17     NaN     NaN     NaN     NaN     NaN     0.0     NaN  
18     NaN     NaN     NaN     NaN     NaN     NaN     NaN  
19     NaN     NaN     NaN   234.0     NaN     NaN     NaN  
20     NaN     NaN     NaN     NaN   123.0     NaN     NaN  
21     NaN     NaN     NaN     NaN     NaN     NaN     NaN  
22     NaN     NaN     NaN     NaN     NaN     NaN     NaN  
23     NaN     NaN     NaN     NaN     NaN     NaN     NaN

I am getting the following error `InvalidIndexError: `. Seems like there is problem with the `out` statement. — royalewithcheese, Mar 01 '21 at 06:32
@royalewithcheese For me this works with the data you shared. There is some inconsistency maybe. What is the exact error? — anky, Mar 01 '21 at 08:42

How to join two df's on the basis of column values and row values?

2 Answers2