Merging two pandas data frames, only getting column header

Question

I have 2 df like these: df1

Pressure    merge_key
923.3383179 1/1/2012 0:00
923.2299805 1/1/2012 0:30
923.1216431 1/1/2012 1:00
923.1466675 1/1/2012 1:30

df2

merge_key       value
1/1/2012 0:30   16.9444
1/1/2012 0:30   16.6837
1/1/2012 0:30   16.6837
1/1/2012 0:30   16.9444
1/1/2012 0:30   16.1623
1/1/2012 0:30   16.6837
1/1/2012 1:00   16.1623
1/1/2012 1:00   16.423
1/1/2012 1:00   16.1623
1/1/2012 1:00   17.2051
1/1/2012 1:00   16.9444
1/1/2012 1:00   16.423

try all sort of merge command and it still give me a df with no data except for column header. Any solution? Thanks!

I have done pd.merge(df2,df1), pd.merge(df2, df1, on = 'merge_key'), df3 = df1.merge(df2.left_on='merge_key',right_on = 'merge_key') ... They all run but the result doesn't have any data — Anh Phuong Bui
– Anh Phuong Bui, Commented Jun 7, 2018 at 22:51

Ben Fasoli · Accepted Answer · 2018-06-07 22:51:57Z

1

It sounds like you're not correctly calling the .merge() method from your data frame. Try

import pandas as pd

# Your reproducible example
d1 = {'Pressure': {0: 923.3383179, 1: 923.2299805, 2: 923.1216431, 3: 923.1466675}, 'merge_key': {0: '1/1/2012 0:00', 1: '1/1/2012 0:30', 2: '1/1/2012 1:00', 3: '1/1/2012 1:30'}}
d2 = {'merge_key': {0: '1/1/2012 0:30', 1: '1/1/2012 0:30', 2: '1/1/2012 0:30', 3: '1/1/2012 0:30', 4: '1/1/2012 0:30', 5: '1/1/2012 0:30', 6: '1/1/2012 1:00', 7: '1/1/2012 1:00', 8: '1/1/2012 1:00', 9: '1/1/2012 1:00', 10: '1/1/2012 1:00', 11: '1/1/2012 1:00'}, 'value': {0: 16.9444, 1: 16.6837, 2: 16.6837, 3: 16.9444, 4: 16.1623, 5: 16.6837, 6: 16.1623, 7: 16.423, 8: 16.1623, 9: 17.2051, 10: 16.9444, 11: 16.423}}

# Convert above dictionary to pandas data frame
df1 = pd.DataFrame(d1)
df2 = pd.DataFrame(d2)

print(df1)
#      Pressure      merge_key
# 0  923.338318  1/1/2012 0:00
# 1  923.229981  1/1/2012 0:30
# 2  923.121643  1/1/2012 1:00
# 3  923.146668  1/1/2012 1:30
print(df2)
#         merge_key    value
# 0   1/1/2012 0:30  16.9444
# 1   1/1/2012 0:30  16.6837
# 2   1/1/2012 0:30  16.6837
# 3   1/1/2012 0:30  16.9444
# 4   1/1/2012 0:30  16.1623
# 5   1/1/2012 0:30  16.6837
# 6   1/1/2012 1:00  16.1623
# 7   1/1/2012 1:00  16.4230
# 8   1/1/2012 1:00  16.1623
# 9   1/1/2012 1:00  17.2051
# 10  1/1/2012 1:00  16.9444
# 11  1/1/2012 1:00  16.4230
print(df1.merge(df2))
#       Pressure      merge_key    value
# 0   923.229981  1/1/2012 0:30  16.9444
# 1   923.229981  1/1/2012 0:30  16.6837
# 2   923.229981  1/1/2012 0:30  16.6837
# 3   923.229981  1/1/2012 0:30  16.9444
# 4   923.229981  1/1/2012 0:30  16.1623
# 5   923.229981  1/1/2012 0:30  16.6837
# 6   923.121643  1/1/2012 1:00  16.1623
# 7   923.121643  1/1/2012 1:00  16.4230
# 8   923.121643  1/1/2012 1:00  16.1623
# 9   923.121643  1/1/2012 1:00  17.2051
# 10  923.121643  1/1/2012 1:00  16.9444
# 11  923.121643  1/1/2012 1:00  16.4230

answered Jun 7, 2018 at 22:51

Ben Fasoli

5263 silver badges7 bronze badges

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

6 Comments

Anh Phuong Bui Over a year ago

Hmmm I have it in a forloop so it apply to a bunch of data file I have with that merge_key. It is hard to show it here. It is still giving me blank data... not sure...

Ben Fasoli Over a year ago

Can you find an example of two data frames that this does not work for, run df.to_dict() to print each as a dictionary, and paste in your question? Would be helpful to see the "broken" case

Anh Phuong Bui Over a year ago

If I do it separately for each file it works but it doesnt run as a whole program

Anh Phuong Bui Over a year ago

Hi Ben, I figured it out. Just a small bug have to turn my column merge_key into same datatype so i put astype(str)

Ben Fasoli Over a year ago

Happy to hear it! Please consider accepting the answer if you found it helpful.

|

Collectives™ on Stack Overflow

Merging two pandas data frames, only getting column header

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related