How to run join on pandas crosstab

user_13552385 0
Viewed 567

I am trying to create a Pandas crosstab, but I then want to run a join and it won't let me since I think it's a special type of DataFrame. See below for an example.

df = pd.DataFrame({'A': ["Alice", "Alice", "Alice", "Bob","Bob","Bob","Charlie"], 'B': ["X","X","Y","X","Y","Z","Z"]})

z = pd.crosstab(df['A'],df['B'])
z.index.name="ID"
z.reset_index(inplace=True)

zz = pd.DataFrame({"ID":["Alice","Daniel","Bob","Charlie"})
zz.join(z,on="DT_ID")

And then I get the following error message:

ValueError: You are trying to merge on object and int64 columns. If you wish to proceed you should use pd.concat

However, if I check the dtypes, they are objects in both ID columns. Am I missing something here?

pandas crosstab dataframe python
3 Answers

join is for the index , you are looking for the merge 

df=zz.merge(z.reset_index(),on="ID")
user_79645270

You can use merge.

#inner join
pd.merge(z,zz,on='ID')

    ID      X   Y   Z
0   Alice   2   1   0
1   Bob     1   1   1
2   Charlie 0   0   1

#right join
pd.merge(z,zz,on='ID',how='right')
    ID      X   Y   Z
0   Alice   2.0 1.0 0.0
1   Bob     1.0 1.0 1.0
2   Charlie 0.0 0.0 1.0
3   Daniel  NaN NaN NaN
user_6678830

It can occur in two scenarios 1. using the join method: you are probably joining DataFrames on labels and not on indices 2. using the merge method: you are probably joining DataFrames on two columns that are not of the same type.

You are trying to join on labels and not on indices using the join method

data_x.join(data_y, on='key')

In the first scenario, you can edit your code to join the index. In the following code, I set the index on the columns I want to join.

data_x.set_index('key').join(data_y.set_index('key'))

But what might even be more simple, is replacing the join method with the merge method.

data_x.merge(data_y)

You are joining on columns of different types using the merge method

data_x.merge(data_y, on='key')

In this second scenario, you can simply change the column type of one of the columns — or both. A convenient way is through the astype method.

data_x.key.astype(int)
data_y.key.astype(int)
data_x.merge(data_y, on='key')
user_134424910
Related