Pandas create new column with all the entries from another column corresponding to a unique value

user_9417884 0
Viewed 46

I am sorry if the question is not clear enough. Say I have this dataframe:

timestamp source dest size

1          a      b     5
1          c      d     6
2          c      e     7
2          d      a     8

From this dataframe I want something like this:

timestamp      link        size
 1             a b c d     5 6
 2             c e d a     7 8

How can I achieve this?

Thank you

pandas python
2 Answers

This is a pivot with a couple added steps since you want to pivot on two columns independently of each other.

u = df.melt('timestamp')
m = u['variable'].isin(['source', 'dest'])

u.loc[m, 'variable'] = 'link'

u.pivot_table(
  'value', 'timestamp', 'variable', aggfunc=list)


variable           link    size
timestamp
1          [a, c, b, d]  [5, 6]
2          [c, d, e, a]  [7, 8]

An alternative using rename first

d = dict(source='link', dest='link')

df.rename(columns=d).melt('timestamp').pivot_table(
  'value', 'timestamp', 'variable', aggfunc=list)


variable           link    size
timestamp
1          [a, c, b, d]  [5, 6]
2          [c, d, e, a]  [7, 8]
user_34832030

You can also use the groupby method of pandas dataframe. Make sure that you size column contains strings.

df['link'] = df['source'] + ' ' + df['dest']
df = df.drop(['source', 'dest'], axis = 1)
newDf = df.groupby('timestamp').agg(lambda col: ' '.join(col))
user_51353970
Related