'How to get uniq values of a column into multiple column and another column values its value [duplicate]
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 .
Solution 1:[1]
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
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 |