Create Dataframe from another Dataframe

Question

I have a dataframe that looks like below

   Index  Batch    Name
    0        1      Jon
    1    
    2        2      Adam
    3         
    4        3      Voges
    5       
    6        4      Jon

I want to create another dataframe from this dataframe clubbing the batch numbers

Batch   Name/Batches
1        Jon(1,4)
2        Adam(2)
3        Voges(3)
4        Jon(1,4)

How can i do this, should i create a new list or ordereddict from the existing DF and then convert that to another DF or this can be done on the fly.

UPDATE: Edited with Spaces in between them

MaxU - stand with Ukraine · Accepted Answer · 2018-06-24 11:03:35Z

4

In [33]: df['Name/Batches'] = \
             df['Name'] + '(' + \
             df.groupby('Name')['Batch'].transform(lambda x: x.astype(str).str.cat(sep=',')) \
             + ')'

In [34]: df
Out[34]:
   Batch   Name Name/Batches
0      1    Jon     Jon(1,4)
1      2   Adam      Adam(2)
2      3  Voges     Voges(3)
3      4    Jon     Jon(1,4)

answered Jun 24, 2018 at 11:03

MaxU - stand with Ukraine

212k37 gold badges402 silver badges436 bronze badges

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

Comments

jpp · Accepted Answer · 2018-06-24 11:28:21Z

Here's one way using groupby + transform with a custom function:

def stringer(x):
    return '('+', '.join(map(str, x))+')'

df['Name'] += df.groupby('Name')['Batch'].transform(stringer)

print(df)

   Batch       Name
0      1  Jon(1, 4)
1      2    Adam(2)
2      3   Voges(3)
3      4  Jon(1, 4)

Update accounting for empty rows

You can use numpy.where in this case:

import numpy as np

def stringer(x):
    return '('+', '.join(map(str, map(int, x)))+')'

s = df.dropna(subset=['Name']).groupby('Name')['Batch'].apply(stringer)

df['Name/Batch'] = np.where(df['Name'].notnull(),
                            df['Name'] + df['Name'].map(s),
                            df['Name'])

print(df)

   Index  Batch   Name Name/Batch
0      0    1.0    Jon  Jon(1, 4)
1      1    NaN    NaN        NaN
2      2    2.0   Adam    Adam(2)
3      3    NaN    NaN        NaN
4      4    3.0  Voges   Voges(3)
5      5    NaN    NaN        NaN
6      6    4.0    Jon  Jon(1, 4)

Collectives™ on Stack Overflow

Create Dataframe from another Dataframe

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related