Replacing values in pandas dataframe with their column order
How can we replace specific values in a dataframe in such a way that the replacements are equal to the order of the i-th column that these specific values are in? For example, I have this DF:
A B C
0 0 1
1 0 0
1 0 0
0 1 0
1 0 1
Replacing everything in this data frame with the order of the ith column (1st, 2nd, 3rd, etc.) where 1 is, so it looks like this:
A B C
0 0 3
1 0 0
1 0 0
0 2 0
1 0 3
This is what I thought would work, but it doesn't:
DF_2= [(0 if i== 0 else j for i in DF.iloc[:,j] ) for j in range(DF.shape[1]) ]
source to share
If only values 1
and 0
you can use multiple numpy arrays converted values
with np.arrange
:
print (np.arange(1, len(df.columns)+1))
[1 2 3]
print (df.values * np.arange(1, len(df.columns)+1))
[[0 0 3]
[1 0 0]
[1 0 0]
[0 2 0]
[1 0 3]]
df = pd.DataFrame(df.values * np.arange(1, len(df.columns)+1),
index=df.index, columns=df.columns)
print (df)
A B C
0 0 0 3
1 1 0 0
2 1 0 0
3 0 2 0
4 1 0 3
A more general solution, (if 0
also numeric), converts the values to bool:
print (df)
A B C
0 0 0 4
1 1 0 0
2 1 0 0
3 0 6 0
4 1 0 1
df = pd.DataFrame(df.astype(bool).values * np.arange(1, len(df.columns)+1),
index=df.index, columns=df.columns)
print (df)
A B C
0 0 0 3
1 1 0 0
2 1 0 0
3 0 2 0
4 1 0 3
Thanks for the other solutions ( Jon Clements and MaxU ):
df = df.replace({col: {1: n} for n, col in enumerate(df.columns[1:], 2)})
print (df)
A B C
0 0 0 3
1 1 0 0
2 1 0 0
3 0 2 0
4 1 0 3
df = df * np.arange(1, df.shape[1]+1)
print (df)
A B C
0 0 0 3
1 1 0 0
2 1 0 0
3 0 2 0
4 1 0 3
Delay
N = 100
cols = ['col' + str(i) for i in range(N)]
df = pd.DataFrame(np.random.choice([0,1], size=(100000,N)), columns=cols)
[100000 rows x 100 columns]
#print (df)
In [101]: %timeit pd.DataFrame(df.values * np.arange(1, len(df.columns)+1), index=df.index, columns=df.columns)
10 loops, best of 3: 25.1 ms per loop
In [102]: %timeit df.replace({col: {1: n} for n, col in enumerate(df.columns[1:], 2)})
1 loop, best of 3: 1.39 s per loop
In [103]: %timeit df * np.arange(1, df.shape[1]+1)
10 loops, best of 3: 21 ms per loop
#Wen solution
In [104]: %timeit (df.mul(list(range(1, len(df.columns)+1))))
10 loops, best of 3: 38.7 ms per loop
source to share