CODEWITHSUNDEEP
pythonexcelpandascsvpandas-groupby
charanReddy
charanReddy

Reputation: 177

Creating a custom column after groupby in csv, pandas

Given the below csv file:

ID  A   B   C   R1  R2
-----------------------
1   A1  B1  C1  abc ghi
1   A1  B1  C2  def jkl
2   A2  B3  C1  mno stu
2   A2  B3  C2  pqr vwx

Need below output with a new column 'R':

ID  A   B  R
-------------------------
1   A1  B1 (abc, def) & (ghi, jkl)
2   A2  B3 (mno, pqr) & (stu, vwx)

Assume only C1, C2 values for column C

import pandas as pd
df = pd.read_csv(r'file.csv')
grouped_df = df.groupby(['ID', 'A', 'B'])

# Here how do we create our new and custom column?

Upvotes: 0

Views: 76

Answers (2)

MrNobody33
MrNobody33

Reputation: 6483

You could use agg, ''.join and filter:

df.groupby(['ID','A','B'])
  .agg(lambda x: '('+', '.join(x)+')')
  .filter(like='R').agg('&'.join,1)
  .reset_index().rename(columns={0:'R'})

Details

df.groupby(['ID','A','B']).agg(lambda x: '('+', '.join(x)+')')
#                 C          R1          R2
#ID A  B                                   
#1  A1 B1  (C1, C2)  (abc, def)  (ghi, jkl)
#2  A2 B3  (C1, C2)  (mno, pqr)  (stu, vwx)


df.groupby(['ID','A','B']).agg(lambda x: '('+', '.join(x)+')').filter(like='R')
#                  R1          R2
#ID A  B                         
#1  A1 B1  (abc, def)  (ghi, jkl)
#2  A2 B3  (mno, pqr)  (stu, vwx)


df.groupby(['ID','A','B']).agg(lambda x: '('+', '.join(x)+')').filter(like='R').agg('&'.join,1).reset_index()
#   ID   A   B                      0
#0   1  A1  B1  (abc, def)&(ghi, jkl)
#1   2  A2  B3  (mno, pqr)&(stu, vwx)


df.groupby(['ID','A','B']).agg(lambda x: '('+', '.join(x)+')').filter(like='R').agg('&'.join,1).reset_index().rename(columns={0:'R'})
#   ID   A   B                      R
#0   1  A1  B1  (abc, def)&(ghi, jkl)
#1   2  A2  B3  (mno, pqr)&(stu, vwx)

Upvotes: 2

bigbounty
bigbounty

Reputation: 17408

In [54]: df
Out[54]:
   ID    A   B   C   R1   R2
0   1   A1  B1  C1  abc  ghi
1   1   A1  B1  C2  def  jkl
2   2   A2  B3  C1  mno  stu
3   2   A2  B3  C2  pqr  vwx

In [55]: grouped_df = df.groupby(['ID', 'A', 'B'])[["R1","R2"]].agg(tuple).reset_index()

In [56]: grouped_df["R"] = grouped_df["R1"].astype(str) + " & " + grouped_df["R1"].astype(str)

In [57]: grouped_df.drop(["R1","R2"],axis=1,inplace=True)

In [58]: grouped_df
Out[58]:
   ID    A   B                                R
0   1   A1  B1  ('abc', 'def') & ('abc', 'def')
1   2   A2  B3  ('mno', 'pqr') & ('mno', 'pqr')

Upvotes: 1

Related Questions