Combine Multiple Rows in Dataframe with Same Key Value

Question

I have a data structure that looks like this:

idtenifier    amount    dist_type    new_value    new_value2
1              1.0       normal
1              2.0      new_value
1              1.0      new_value2    
3              1.0       normal    
5              3.0       normal    
5              23.0     new_value2    
2              1.0       normal

I am looking to get a structure like this:

idtenifier    amount    dist_type    new_value    new_value2
1              1.0       normal         2.0          1.0  
3              1.0       normal                      23.0    
5              3.0       normal     
2              1.0       normal

I have a feeling the way I am trying to do this is grossly inefficient and I cannot even assign the values in the columns

df['new_value'] = np.nan

for idx, row in df.iterrows():
    identifier = row['identifier']
    dist_type = row['dist_type']
    amount = row['amount']
    if idx > 0 and identifier == df.loc[idx-1, 'identifier']:
        print(dist_type)
        if dist_type == 'new_value':
            df.loc[idx-1, 'new_value'] == amount

BENY · Accepted Answer · 2019-03-15 00:42:00Z

1

We do not need using for loop here , after split the dataframe by two , for dist_type not equal to normal , we do pivot , then merge it back

df1=df.loc[df.dist_type=='normal'].copy()
df2=df.loc[df.dist_type!='normal'].copy()
yourdf=df1.merge(df2.pivot('idtenifier','dist_type','amount').reset_index(),how='left')
yourdf
Out[33]: 
   idtenifier  amount dist_type  new_value  new_value2
0           1     1.0    normal        2.0         1.0
1           3     1.0    normal        NaN         NaN
2           5     3.0    normal        NaN        23.0
3           2     1.0    normal        NaN         NaN

answered Mar 15, 2019 at 0:42

BENY

324k22 gold badges176 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Combine Multiple Rows in Dataframe with Same Key Value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related