CODEWITHSUNDEEP
pythonpandasmerge
Peter
Peter

Reputation: 564

Merge df in two keys, just working for one key

so I've two df, They need to be merged in two keys Channel and Week. When attempting the merging it returns NaN values for all rows in Column Contacts_F..

Data:

df = pd.DataFrame({ 'week' : ['01', '02', '45'] ,
                    'Channel' : ['AA', 'BB', 'CC'],
                    'level' : ['1degre','2degre','3degre']  })
df.dtypes
out[131]:
week               object
Channel            object
level              object



reff_table = pd.DataFrame({ 'week' : ['01', '02', '45'] ,
                            'Channel' : ['AA', 'BB', 'CC'],
                            'Contacts_F' : ['0.11','0.546','0.06458']  })
reff_table.dtypes
out[132]:
week               object
Channel            object
Contacts_F         float64



print(df.week[:3]), print(reff_table.week[:3])

0    00
1    00
2    00
Name: week, dtype: object
0    103
1    102
2    101
Name: week, dtype: object
(None, None)

My attempts:

It is returning NaN values for all rows in column= Contacts_F

/.

df = pd.merge(df, reff_table [["Channel", "week" , "Contacts_F"]],on=['Channel','week'], how='left')

It works if I merge only ON Column=Channel, but I need match both columns.

df = pd.merge(df, reff_table [["Channel", "week" , "Contacts_F"]],on=['Channel', how='left')

Upvotes: 1

Views: 212

Answers (2)

Peter
Peter

Reputation: 564

The Problem was.. in the Reff table the numbers was wrong like 41.0 Vs 41. So Pandas was printing and Displaying it as 41. I had to go verify it in the actual database, then I saw the problem.

df = pd.DataFrame({ 'week' : ['01', '02', '45'] ,
                    'Channel' : ['AA', 'BB', 'CC'],
                    'level' : ['1degre','2degre','3degre']  })



reff_table = pd.DataFrame({ 'week' : ['01', '02', '45'] ,
                            'Channel' : ['AA', 'BB', 'CC'],
                            'Contacts_F' : ['0.11','0.546','0.06458']  })

# This fix the problem
df.week.str.replace('.', '').astype(str)



df = pd.merge(df, reff_table [["Channel", "week" , "Contacts_F"]],on=['Channel','week'], how='left')

Upvotes: 0

skullgoblet1089
skullgoblet1089

Reputation: 614

See below for working example of how to perform merge. Various syntax errors in prompt are corrected. 

    import pandas as pd

    df = pd.DataFrame({'week': [1, 2, 45],
                       'Channel': ['AA', 'BB', 'CC'],
                       'level': ['1degre', '2degre', '3degre']})

    reff_table = pd.DataFrame({'week': [1, 2, 45],
                               'Channel': ['AA', 'BB', 'CC'],
                               'Contacts_F': ['0.11', '0.546', '0.06458']})

    pd.merge(df, reff_table, on=['Channel', 'week'], how='left')

    ------

    Out[8]:
        week Channel   level Contacts_F
     0     1      AA  1degre       0.11
     1     2      BB  2degre      0.546
     2    45      CC  3degre    0.06458

Upvotes: 1

Related Questions