How to set column name from column value in Pandas Python?

user_19742259 0
Viewed 58

I have a data frame that looks like the following.

0 1 2 3 4 5
0: 2 57: 9 None 436: 77 11469: 1018 203: 44
0: 0 57: 15 None 436: 47 None 203: 89
0: 45 57: 0 11469: 1116 436: 7 None 203: 0
0: 1 57: 23 None 436: 0 11469: 18 None
0: 23 57: 5 None 436: 63 None 203: 4

Here, the column values represent the distance and time, in meters and seconds (57: 9 means 57 meters and 9 seconds). I want to rename my column such that the meter value becomes column name and the seconds value remains as a column value. Moreover, the columns where values are None, they should be replaced by Zero (0).

Desired output:

0 57 11469 436 11469 203
2 9 0 77 1018 44
0 15 0 47 0 89
45 0 1116 7 0 0
1 23 0 0 18 0
23 5 0 63 0 4

I am new to python so I don't know how I can achieve that.

pandas dataframe python split
1 Answers

First split each column by : with select last splitted values and replace to missing values, for columns forward filling missing values with select last row and after split select first values:

df1 = df.apply(lambda x: x.str.split(': ').str[-1]).fillna(0)
df1.columns = df.ffill().iloc[-1].str.split(': ').str[0].tolist()
print (df1)
    0  57 11469 436 11469 203
0   2   9     0  77  1018  44
1   0  15     0  47     0  89
2  45   0  1116   7     0   0
3   1  23     0   0    18   0
4  23   5     0  63     0   4
user_29010020
Related