I am trying to join two very big dask dataframes on an object column named patent_string.
I am doing this through the following command:
dask_df1.join(dask_df2, on='patent_string',how='inner',lsuffix='left', rsuffix='right')
after having convered the column "patent_string" for the two into an object type:
dask_df1['patent_string']=dask_df1['patent_string'].astype(object)
dask_df2['patent_string']=dask_df2['patent_string'].astype(object)
However, the following error appears when I try to join the 2:
---------------------------------------------------------------------------
ValueError Traceback (most recent call last)
Cell In [90], line 4
1 ###OCCORRE RIMUOVERE I DUPLICATI IN STATA AD ESEMPIO:
2 #df_emakg_cpc_y02y04 = vaex_df1.join(vaex_df2, how='inner', on='patent_string',lsuffix='left', rsuffix='right',allow_duplication=True)
----> 4 df_emakg_cpc_y02y04=dask_df1.join(dask_df2, on='patent_string',lsuffix='_caller', rsuffix='_other')
File ~/opt/anaconda3/envs/bocconi/lib/python3.8/site-packages/dask/dataframe/core.py:5363, in DataFrame.join(self, other, on, how, lsuffix, rsuffix, npartitions, shuffle)
5352 other = _recursive_pairwise_outer_join(
5353 other,
5354 on=on,
(...)
5358 shuffle=shuffle,
5359 )
5361 from dask.dataframe.multi import merge
-> 5363 return merge(
5364 self,
5365 other,
5366 how=how,
5367 left_index=on is None,
5368 right_index=True,
5369 left_on=on,
5370 suffixes=(lsuffix, rsuffix),
5371 npartitions=npartitions,
5372 shuffle=shuffle,
5373 )
File ~/opt/anaconda3/envs/bocconi/lib/python3.8/site-packages/dask/dataframe/multi.py:607, in merge(left, right, how, on, left_on, right_on, left_index, right_index, suffixes, indicator, npartitions, shuffle, max_branch, broadcast)
605 left_empty = left._meta_nonempty
606 right_empty = right._meta_nonempty
--> 607 meta = left_empty.merge(
608 right_empty,
609 how=how,
610 on=on,
611 left_on=left_on,
612 right_on=right_on,
613 left_index=left_index,
614 right_index=right_index,
615 suffixes=suffixes,
616 indicator=indicator,
617 )
618 categorical_columns = meta.select_dtypes(include="category").columns
620 if merge_indexed_left and left.known_divisions:
File ~/.local/lib/python3.8/site-packages/pandas/core/frame.py:9351, in DataFrame.merge(self, right, how, on, left_on, right_on, left_index, right_index, sort, suffixes, copy, indicator, validate)
9332 @Substitution("")
9333 @Appender(_merge_doc, indents=2)
9334 def merge(
(...)
9347 validate: str | None = None,
9348 ) -> DataFrame:
9349 from pandas.core.reshape.merge import merge
-> 9351 return merge(
9352 self,
9353 right,
9354 how=how,
9355 on=on,
9356 left_on=left_on,
9357 right_on=right_on,
9358 left_index=left_index,
9359 right_index=right_index,
9360 sort=sort,
9361 suffixes=suffixes,
9362 copy=copy,
9363 indicator=indicator,
9364 validate=validate,
9365 )
File ~/.local/lib/python3.8/site-packages/pandas/core/reshape/merge.py:107, in merge(left, right, how, on, left_on, right_on, left_index, right_index, sort, suffixes, copy, indicator, validate)
90 @Substitution("\nleft : DataFrame or named Series")
91 @Appender(_merge_doc, indents=0)
92 def merge(
(...)
105 validate: str | None = None,
106 ) -> DataFrame:
--> 107 op = _MergeOperation(
108 left,
109 right,
110 how=how,
111 on=on,
112 left_on=left_on,
113 right_on=right_on,
114 left_index=left_index,
115 right_index=right_index,
116 sort=sort,
117 suffixes=suffixes,
118 copy=copy,
119 indicator=indicator,
120 validate=validate,
121 )
122 return op.get_result()
File ~/.local/lib/python3.8/site-packages/pandas/core/reshape/merge.py:704, in _MergeOperation.__init__(self, left, right, how, on, left_on, right_on, axis, left_index, right_index, sort, suffixes, copy, indicator, validate)
696 (
697 self.left_join_keys,
698 self.right_join_keys,
699 self.join_names,
700 ) = self._get_merge_keys()
702 # validate the merge keys dtypes. We may need to coerce
703 # to avoid incompatible dtypes
--> 704 self._maybe_coerce_merge_keys()
706 # If argument passed to validate,
707 # check if columns specified as unique
708 # are in fact unique.
709 if validate is not None:
File ~/.local/lib/python3.8/site-packages/pandas/core/reshape/merge.py:1261, in _MergeOperation._maybe_coerce_merge_keys(self)
1255 # unless we are merging non-string-like with string-like
1256 elif (
1257 inferred_left in string_types and inferred_right not in string_types
1258 ) or (
1259 inferred_right in string_types and inferred_left not in string_types
1260 ):
-> 1261 raise ValueError(msg)
1263 # datetimelikes must match exactly
1264 elif needs_i8_conversion(lk.dtype) and not needs_i8_conversion(rk.dtype):
ValueError: You are trying to merge on object and int64 columns. If you wish to proceed you should use pd.concat
Am I doing something wrong here? Maybe the object type is not the one I should adopt in dask?
EDIT:
I tried the following:
df_emakg_cpc_y02y04=dask_df1.set_index('patent_string').join(dask_df2.set_index('patent_string'))
but ended up with an empty dataframe!
EDIT2: This is my whole code:
import dask.dataframe as dd
dask_df1 = dd.from_pandas(df_emakg_cpc_green, npartitions=10) # Dask DataFrame has 10 partitions
dask_df2 = dd.from_pandas(df_emakg, npartitions=10)
dask_df1['patent_string']=dask_df1['patent_string'].astype(str)
dask_df2['patent_string']=dask_df2['patent_string'].astype(str)
df_emakg_cpc_y02y04=dask_df1.join(dask_df2, on='patent_string',lsuffix='_caller', rsuffix='_other')
Unfortunately, I cannot provide you with an MWE since the dask dataframes are very long, and cannot figure out a way to provide you some representative data. The variable that I am trying to join on is an object type which I am trying to revert to a string type without success. Yet, while in dask_df2 the variable looks good, in dask_df1 it looks as in the picture (see right most variable):
Thank you