CODEWITHSUNDEEP
pythondatabasepandascsv
DHANANJAY CHAUBEY
DHANANJAY CHAUBEY

Reputation: 117

Data Rearrangement using python Pandas | Create a column based on repeated index and fill with the column value

Rearranging python csv data into rows and different column I have csv dtabase which contains the name and friend list in below format

enter image description here

Expected output like as below: Name and Value in one row with the number of repeated columns as per the name repetition.

enter image description here

What is the best way to perform this output?

Upvotes: 1

Views: 118

Answers (2)

ldoe
ldoe

Reputation: 340

You could also use groupby and create a new Dataframe with from_dict :

new_dict = (df.groupby('Name')
              .apply(lambda x: list(map(lambda x: x, x['Value'])))
              .to_dict())

new_df = pd.DataFrame.from_dict(new_dict, orient='index')

This will give you :

           0      1      2
Ajay    C529   C530   None
Djna   A-506  A-507  A-508
Patc2  B-526  B-527  B-528

Upvotes: 1

anky
anky

Reputation: 75100

IIUC you would need df.pivot() and then shift the values to the left:

df_new=df.pivot(index='Name',columns='Value',values='Value')\
.apply(lambda x: pd.Series(x.dropna().values), axis=1).fillna(np.nan)
df_new.columns=['value_'+str(i+1) for i in df_new.columns]
print(df_new)

    value_1 value_2 value_3 value_4 value_5 value_6 value_7 value_8 value_9  \
Name                                                                            
Ajay     C529    C530    C531    C532    C533    C534    C535     NaN     NaN   
Djna    A-506   A-507   A-508   A-509   A-510   A-511   A-512   A-513   A-514   
Patc2   B-526   B-527   B-528     NaN     NaN     NaN     NaN     NaN     NaN   

      value_10  
Name            
Ajay       NaN  
Djna     A-515  
Patc2      NaN

Upvotes: 1

Related Questions