How to get uniq values of a column into multiple column and another column values its value

user_10694247 0
Viewed 32

Hi i abelow data and dataframe where ..

I have a DataFrame values like below:

name        managed_by
host1       sam
host2       sam
host3       sam
host4       sam
host5       Peter
host6       Peter
host7       Jesse
host8       Jesse
host9       Patrick
host10      Banu
host11      Banu

Dataframe:

df2 = pd.read_csv("server.csv", usecols=['name', 'managed_by'])
df2 = df2.value_counts('managed_by')
print(df2)

When i run it, it displays below..

managed_by
Sam     4
Peter   2
Jesse   2
Patrick 1
Banu    2

If Values are like below after pivot the i want all column values to be stripped for Nan

Sam         Peter   Jesse   Patrick     Banu
host1       host5   host7   host9       host10
host2       host6   host8               host11
host3       Nan     Nan                 Nan
host4       Nan     Nan                 Nan
Nan         host22  Nan                 Nan
host23      Nan     Nan                 Nan

I want below:

Sam         Peter   Jesse   Patrick     Banu
host1       host5   host7   host9       host10
host2       host6   host8               host11
host3       host22                      
host4                           
host23

What i did:

df2 = df2.pivot(columns='managed_by', values='name').replace(np.nan, '')

this did worked but if i have huge file then it will fill Nan for Blank rows in a column .

pandas dataframe
1 Answers
df = pd.DataFrame({
    "name":["host1","host2","host3","host4","host5","host6","host7","host8","host9","host10","host11"],
    "managed_by":["sam","sam","sam","sam","Peter","Peter","Jesse","Jesse","Patrick","Banu","Banu"]
})

df = df.assign(key=df.groupby(['managed_by']).cumcount()).pivot(
index="key",columns=["managed_by"],values=["name"]).reset_index()

df

           key    name                             
managed_by        Banu  Jesse Patrick  Peter    sam
0            0  host10  host7   host9  host5  host1
1            1  host11  host8     NaN  host6  host2
2            2     NaN    NaN     NaN    NaN  host3
3            3     NaN    NaN     NaN    NaN  host4


# edit (author wants to strip the NaN values)
df.fillna("")
           key    name                             
managed_by        Banu  Jesse Patrick  Peter    sam
0            0  host10  host7   host9  host5  host1
1            1  host11  host8          host6  host2
2            2                                host3
3            3                                host4
user_162361180
Related