Pandas Merge: How to create a counter field in the format of "Group Count - Subgroup Cumcount" to better mark the many-to-one join rows

user_5336013 0
Viewed 227

I have a data frame that is the left join result of the two following dataframes:

e1 = pd.DataFrame({'ID': ['A', 'B', 'C'], 'Hour': [9, 10, 11]})
e2 = pd.DataFrame({'ID': ['A', 'B', 'B', 'C', 'C', 'C'], 'Price': [25, 32, 33, 110, 108, 112]})

ex = pd.merge(e1, e2, on = 'ID', how = 'left')

The dataframe looks like this:

ex

My desired result is this:

desired result

Right now my process of doing this is quite lengthy:

es1 = pd.DataFrame(ex.ID.drop_duplicates().reset_index(drop = True))
es1['new_count'] = es1.index + 1

ex['sub_group_count'] = ex.groupby(['ID']).cumcount()+1
ex_m = pd.merge(ex, es1, on='ID', how = 'left')

ex_m['many_to_one_id'] = ex_m.new_count.astype(str) + '-' + ex_m.sub_group_count.astype(str)
ex_m.loc[ex_m.duplicated(subset= ['ID']), ['ID', 'Hour']] = np.nan

Basically I manually created the group counter field and sub group cumcount field, and then I joined them together to get the 'many_to_one_id' field. I wonder if there's a better and more Pythonic way to achieve this.

pandas merge python
1 Answers

You can use cumcount create the additional key

e2['key']=e2.groupby('ID').cumcount()
e1['key']=e1.groupby('ID').cumcount()
ex = pd.merge(e2, e1, on = ['ID', 'key'], how = 'left')
ex
Out[15]: 
  ID  Price  key  Hour
0  A     25    0   9.0
1  B     32    0  10.0
2  B     33    1   NaN
3  C    110    0  11.0
4  C    108    1   NaN
5  C    112    2   NaN
user_79645270
Related