How to separate elements in a pandas Dataframe

user_19388882 0
Viewed 49

I am trying to handle the following dataframe

import pandas as pd

df =pd.DataFrame(
    data = {'m1' : [0,0,1,0,0,0,0,0,0,0,0],
            'm2' : [0,0,0,0,0,1,0,0,0,0,0],
            'm3' : [0,0,0,0,0,0,0,0,1,0,0],
            'm4' : [0,1,0,0,0,0,0,0,0,0,0],
            'm5' : [0,0,0,0,0,0,0,0,0,0,0],
            'm6' : [0,0,0,0,0,0,0,0,0,1,0]}
) 
df
#
  m1 m2 m3 m4 m5 m6
0  0  0  0  0  0  0
1  0  0  0  1  0  0
2  1  0  0  0  0  0
3  0  0  0  0  0  0
4  0  0  0  0  0  0
5  0  1  0  0  0  0
6  0  0  0  0  0  0
7  0  0  0  0  0  0
8  0  0  1  0  0  0
9  0  0  0  0  0  1
10 0  0  0  0  0  0

From the above dataframe, I want to separate m1 and other features.

Assign 1 to m_other if any of m2 to m6 is 1.

Ideal results are shown below.

   m1 m_other
0  0  0  
1  0  1  
2  1  0  
3  0  0 
4  0  0  
5  0  1  
6  0  0  
7  0  0  
8  0  1  
9  0  1 
10 0  0

I thought about adapting the any function, but I stumbled and couldn't figure it out.

If anyone has any good ideas, I would appreciate it if you could share them with me.

pandas python
4 Answers

Use DataFrame.any or DataFrame.max with left join to m1 column:

#select all columns without first
df1 = df[['m1']].assign(m_other=df.iloc[:, 1:].max(axis=1))
df1 = df[['m1']].assign(m_other=df.iloc[:, 1:].any(axis=1).astype(int))

#select all columns without m1
df1 = df[['m1']].assign(m_other=df.drop('m1',1).max(axis=1))

#seelct columns between m2 and m6
df1 = df[['m1']].assign(m_other=df.loc[:, 'm2':'m6'].max(axis=1))

print (df1)
    m1  m_other
0    0        0
1    0        1
2    1        0
3    0        0
4    0        0
5    0        1
6    0        0
7    0        0
8    0        1
9    0        1
10   0        0
user_29010020

Does this work:

pd.concat([df['m1'], df.iloc[:,1:].apply(lambda x : 1 if x.any() == 1 else 0, axis = 1)], axis = 1, keys = ['m1','m_other'])

    m1  m_other
0   0   0
1   0   1
2   1   0
3   0   0
4   0   0
5   0   1
6   0   0
7   0   0
8   0   1
9   0   1
10  0   0
user_107227520

A more simplistic way is to seperate it into two dataframes then recombine it.

#data is the datframe of the result
data=pd.DataFrame(columns=['m1','m_other'])
# there's no change in m1 so we assign it directly
data.m1=df.m1
# we create a data for the other columns
data_other=df[['m2','m3','m4','m5','m6']]
# we assign True if anyone from 2 to 6 has 1 value
data.m_other=[any(data_other.iloc[i]==1) for i in range(len(df))]
# we map it to 1 and 0 instead of True and False
data.m_other=data.m_other.astype(int)
# this is our final results
data

results

user_171060240

Here is one way to do it using concat to combine the first column and the max of the renaming columns and then renaming the column name

df2=pd.concat([df.iloc[:,:1],(df.iloc[:,1:].max(axis=1))], axis=1)
df2=df2.rename(columns={0:'m_other'})
df2

    m1  m_other
0   0   0
1   0   1
2   1   0
3   0   0
4   0   0
5   0   1
6   0   0
7   0   0
8   0   1
9   0   1
10  0   0
user_34947540
Related