Hello I have a dateframe1 of values and i want to transform it into a new dataframe2 by concatenating values of columns in the original dataframe1 i.e
dataframe1
ProductName Value otherValue
Product1 2 5
Product2 3 2
Product1 1 5
Product3 4 7
Product3 5 7
Product1 5 5
Product2 9 2
dataframe2
ProductName Value otherValue
Product1 2 1 5 5
Product2 3 9 2
Product3 4 5 7
You could groupby ProductName and aggregate using ' '.join on Value and first on otherValue:
result = df.assign().groupby('ProductName', as_index=False).agg({ 'Value' : lambda x : ' '.join(map(str, x)), 'otherValue' : 'first' } )
print(result)
Output
ProductName Value otherValue
0 Product1 2 1 5 5
1 Product2 3 9 2
2 Product3 4 5 7
Note that this solution assumes the column Value is not a string, otherwise you can use ' '.join directly
.agg function.You can try with this in two lines. first we need to turn the column Value into strings so we can perform join and operations and the second are all the operations to return the desired output:
import pandas as pd
import numpy as np
df = pd.DataFrame(data={'ProductName':['Product1','Product2','Product1','Product3','Product3','Product1','Product2'],'Value':[2,3,1,4,5,5,9],'otherValue':[5,2,5,7,7,5,2]})
df['Value'] = df['Value'].astype(str)
df = df.merge(df.groupby('ProductName',as_index=True)['Value'].apply(' '.join).reset_index(),how='left',left_on='ProductName',right_on='ProductName').drop('Value_x',axis=1).drop_duplicates().rename(columns={'Value_y':'Value'})
print(df) Output:
ProductName otherValue Value
0 Product1 5 2 1 5
1 Product2 2 3 9
3 Product3 7 4 5