I would like to sort columns like that as now it just fills in from 0 to make sure there are none
websiteName 0 1 2 3
0 websiteName1 TLSv1.0 TLSv1.1 TLSv1.2 None
1 websiteName1 TLSv1.2 None None None
2 websiteName2 TLSv1.2 TLSv1.3 None None
3 websiteName3 TLSv1.0 TLSv1.2 TLSv1.3 None
4 websiteName4 TLSv1.2 TLSv1.3 None None
Expected result
websiteName 0 1 2 3
0 websiteName1 TLSv1.0 TLSv1.1 TLSv1.2 None
1 websiteName1 None None TLSv1.2 None
2 websiteName2 None None TLSv1.2 TLSv1.3
3 websiteName3 TLSv1.0 None TLSv1.2 TLSv1.3
4 websiteName4 None None TLSv1.2 TLSv1.3
CodePudding user response:
You can melt
, then pivot
:
(df
.replace({'None': pd.NA}) # assuming 'None' strings
.melt(id_vars='websiteName')
.assign(TLS=lambda d: d['value'])
.pivot_table(index='websiteName', columns='TLS', values='value', aggfunc='first')
.set_axis(range(len(df.columns)-1), axis=1) # comment to keep TLSvx.x names
.reset_index()
)
output:
websiteName 0 1 2 3
0 websiteName1 TLSv1.0 TLSv1.1 TLSv1.2 NaN
1 websiteName2 NaN NaN TLSv1.2 TLSv1.3
2 websiteName3 TLSv1.0 NaN TLSv1.2 TLSv1.3
3 websiteName4 NaN NaN TLSv1.2 TLSv1.3