Using DataFrame cross join throw no common columns to perform merge on-CodePudding

I'd like to create a third column as a result of a cross join between my Columns A and B:

import pandas as pd
import numpy as np
df = pd.read_csv("data.csv", sep=",")
df
#    A    B    
# 0  0  Yes 
# 1  8   No 
# 2  2  Yes 
# 3  4  Maybe
# 4  6  NA

They have the following unique values:

>>> df['A'].drop_duplicates()
0       0
2       8
41      4
119     2
1246    3
1808    1
Name: A, dtype: int64

>>> df['B'].drop_duplicates()
                
0              NA
2           Maybe
320            No
5575          Yes
Name: B, dtype: object

I'd like to have a df['C'] with the combination of all cross joins, thus we should have 6 * 4 = 24 unique values in it:

#Column C should have 6 * 4 classes:

(1,Yes)=1  (1,No)=6  (1, Maybe)=12  (1, NA)=18
(2,Yes)=2  (2,No)=7  (2, maybe)=13    ...
(3,Yes)=3  (3,No)=8  ...
(4,Yes)=4  (4,No)=9
(8,Yes)=5   ...
(0,Yes)=0

Thus we should have the following:

Newdf
#    A    B    C  
# 0  0  Yes    0
# 1  8   No    9
# 2  2  Yes    2
# 3  4  Maybe  15
# 4  8  NA     22

Using this method, I have the following error:

out = df.merge(df[['B']].drop_duplicates().merge(df['A'].drop_duplicates(),how='cross').assign(C=lambda x : x.index 1))

Throws:

"No common columns to perform merge on. "
pandas.errors.MergeError: No common columns to perform merge on. Merge options: left_on=None, right_on=None, left_index=False, right_index=False

Any help would be appreciated.

CodePudding user response：

Why don't you use the good old itertools:

from itertools import product   
cats = list(product(df['A'].unique(), df['B'].unique()))

# merge with this
pd.DataFrame(cats, columns=['A','B']).assign(C=range(len(cats)))

Output for the example data:

    A      B   C
0   0    Yes   0
1   0     No   1
2   0  Maybe   2
3   0    NaN   3
4   8    Yes   4
5   8     No   5
6   8  Maybe   6
7   8    NaN   7
8   2    Yes   8
9   2     No   9
10  2  Maybe  10
11  2    NaN  11
12  4    Yes  12
13  4     No  13
14  4  Maybe  14
15  4    NaN  15
16  6    Yes  16
17  6     No  17
18  6  Maybe  18
19  6    NaN  19