How to do pandas equivalence of SQL outer join without a key

user_7525365 0
Viewed 6349

In SQL, you can join two tables without a key so that all records of both tables merge with each other. If pandas.concat() or pandas.merge() or some other pandas syntax supported this, it could help me with one step of a problem I am trying to solve. I found an outer join option on the help documentation, but I could not find an exact syntax to do what I wanted (join all records without a key).

To explain this a little better:

import pandas as pd

lunchmenupairs2 = [["pizza", "italian"],["lasagna", "italian"],["orange", "fruit"]]
teamcuisinepreferences2 = [["ian", "*"]]

lunchLabels = ["Food", "Type"]
teamLabels = ["Person", "Type"]

df1 = pd.DataFrame.from_records(lunchmenupairs2, columns=lunchLabels)
df2 = pd.DataFrame.from_records(teamcuisinepreferences2, columns=teamLabels)

print(df1)
print(df2)

Outputs these tables:

      Food     Type
0    pizza  italian
1  lasagna  italian
2   orange    fruit

  Person     Type
0    ian        *

I want the final result of the merge to be:

  Person     Type Food     Type
0  ian        *   pizza     italian
1  ian        *   lasagna   italian
2  ian        *   orange    fruit

Then I can easily drop the columns I don't want and move to the next step in the code I am working on. This doesn't work:

merged_data = pd.merge(left=df2,right=df1, how='outer')

Is there a way to do this type of DataFrame merging?

dataframe merge join python
4 Answers
user_100068230

Building on @EFT answer, I often need some combo of values and dates, solution below. Can easily be generalized.

df1=pd.DataFrame({'ticker':['a','b']})
df2=pd.DataFrame({'date':pd.date_range('2010-01-01','2010-03-01',freq='1M')})
pd.DataFrame({'ticker':df1['ticker'].unique(),'key':np.nan}).merge(pd.DataFrame({'date':df2['date'].unique(),'key':np.nan}),on='key').drop('key',1)

  ticker       date
0      a 2010-01-31
1      a 2010-02-28
2      b 2010-01-31
3      b 2010-02-28
user_31409920
Related