Mixing
Pandas dataframe get value of last nonzero column
Clash Royale CLAN TAG#URR8PPP
up vote
6
down vote
favorite
I have a pandas dataframe which contains 3 columns, each containing a site that a user has visited during a session.
In some cases, a user may have not visited 3 sites in a single session. This is shown by a 0, denoting that no site has been visited.
import pandas as pd
df = pd.DataFrame(data=[[5, 8, 1],[8,0,0],[1,17,0]],
columns=['site1', 'site2', 'site3'])
print(df)
site1 site2 site3
0 5 8 1
1 8 0 0
2 1 17 0
In the example above, user 0 has visited sites 5, 8 and 1. User 1 has visited site 8 only, and user 2 has visited sites 1 and 17.
I would like to create a new column, last_site, which shows the last site visited by the user in that session.
The result I want is this:
site1 site2 site3 last_site
0 5 8 1 1
1 8 0 0 8
2 1 17 0 17
How can I do this in a concise way using pandas?
python pandas dataframe
asked 50 mins ago
kskyriacou
2,21411533
add a comment |Â
up vote
6
down vote
favorite
I have a pandas dataframe which contains 3 columns, each containing a site that a user has visited during a session.
In some cases, a user may have not visited 3 sites in a single session. This is shown by a 0, denoting that no site has been visited.
import pandas as pd
df = pd.DataFrame(data=[[5, 8, 1],[8,0,0],[1,17,0]],
columns=['site1', 'site2', 'site3'])
print(df)
site1 site2 site3
0 5 8 1
1 8 0 0
2 1 17 0
In the example above, user 0 has visited sites 5, 8 and 1. User 1 has visited site 8 only, and user 2 has visited sites 1 and 17.
I would like to create a new column, last_site, which shows the last site visited by the user in that session.
The result I want is this:
site1 site2 site3 last_site
0 5 8 1 1
1 8 0 0 8
2 1 17 0 17
How can I do this in a concise way using pandas?
python pandas dataframe
asked 50 mins ago
kskyriacou
2,21411533
add a comment |Â
up vote
6
down vote
favorite
up vote
6
down vote
favorite
I have a pandas dataframe which contains 3 columns, each containing a site that a user has visited during a session.
In some cases, a user may have not visited 3 sites in a single session. This is shown by a 0, denoting that no site has been visited.
import pandas as pd
df = pd.DataFrame(data=[[5, 8, 1],[8,0,0],[1,17,0]],
columns=['site1', 'site2', 'site3'])
print(df)
site1 site2 site3
0 5 8 1
1 8 0 0
2 1 17 0
In the example above, user 0 has visited sites 5, 8 and 1. User 1 has visited site 8 only, and user 2 has visited sites 1 and 17.
I would like to create a new column, last_site, which shows the last site visited by the user in that session.
The result I want is this:
site1 site2 site3 last_site
0 5 8 1 1
1 8 0 0 8
2 1 17 0 17
How can I do this in a concise way using pandas?
python pandas dataframe
asked 50 mins ago
kskyriacou
2,21411533
I have a pandas dataframe which contains 3 columns, each containing a site that a user has visited during a session.
In some cases, a user may have not visited 3 sites in a single session. This is shown by a 0, denoting that no site has been visited.
import pandas as pd
df = pd.DataFrame(data=[[5, 8, 1],[8,0,0],[1,17,0]],
columns=['site1', 'site2', 'site3'])
print(df)
site1 site2 site3
0 5 8 1
1 8 0 0
2 1 17 0
In the example above, user 0 has visited sites 5, 8 and 1. User 1 has visited site 8 only, and user 2 has visited sites 1 and 17.
I would like to create a new column, last_site, which shows the last site visited by the user in that session.
The result I want is this:
site1 site2 site3 last_site
0 5 8 1 1
1 8 0 0 8
2 1 17 0 17
How can I do this in a concise way using pandas?
python pandas dataframe
python pandas dataframe
asked 50 mins ago
kskyriacou
2,21411533
asked 50 mins ago
kskyriacou
2,21411533
asked 50 mins ago
kskyriacou
2,21411533
asked 50 mins ago
kskyriacou
2,21411533
asked 50 mins ago
kskyriacou
2,21411533
2,21411533
add a comment |Â
add a comment |Â
2 Answers
2
active
oldest
votes
up vote
8
down vote
accepted
Use forward filling of misisng values created by replacing 0 values and thenselect last column by iloc:
df['last'] = df.replace(0, np.nan).ffill(axis=1).iloc[:, -1].astype(int)
print (df)
site1 site2 site3 last
0 5 8 1 1
1 8 0 0 8
2 1 17 0 17
If performance is important is possible use numpy:
a = df.values
m = a != 0
df['last'] = a[np.arange(m.shape[0]), m.shape[1]-m[:,::-1].argmax(1)-1]
print (df)
site1 site2 site3 last
0 5 8 1 1
1 8 0 0 8
2 1 17 0 17
answered 47 mins ago
jezrael
301k20229304
3
This forward filling logic here is excellent across the rows :) +1
– pygo
41 mins ago
Yes forward filling across rows is out of the box thinking
– Vishnudev
37 mins ago
Great and Instant logic indeed :-) .
– pygo
37 mins ago
add a comment |Â
up vote
2
down vote
Code:
df['last_site'] = df.apply(lambda x: x.iloc[x.nonzero()].iloc[-1], axis=1)
Output:
site1 site2 site3 last_site
0 5 8 1 1
1 8 0 0 8
2 1 17 0 17
answered 42 mins ago
Vishnudev
602315
Good one @Vishnudev +1 !
– pygo
37 mins ago
add a comment |Â
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
8
down vote
accepted
Use forward filling of misisng values created by replacing 0 values and thenselect last column by iloc:
df['last'] = df.replace(0, np.nan).ffill(axis=1).iloc[:, -1].astype(int)
print (df)
site1 site2 site3 last
0 5 8 1 1
1 8 0 0 8
2 1 17 0 17
If performance is important is possible use numpy:
a = df.values
m = a != 0
df['last'] = a[np.arange(m.shape[0]), m.shape[1]-m[:,::-1].argmax(1)-1]
print (df)
site1 site2 site3 last
0 5 8 1 1
1 8 0 0 8
2 1 17 0 17
answered 47 mins ago
jezrael
301k20229304
3
This forward filling logic here is excellent across the rows :) +1
– pygo
41 mins ago
Yes forward filling across rows is out of the box thinking
– Vishnudev
37 mins ago
Great and Instant logic indeed :-) .
– pygo
37 mins ago
add a comment |Â
up vote
8
down vote
accepted
Use forward filling of misisng values created by replacing 0 values and thenselect last column by iloc:
df['last'] = df.replace(0, np.nan).ffill(axis=1).iloc[:, -1].astype(int)
print (df)
site1 site2 site3 last
0 5 8 1 1
1 8 0 0 8
2 1 17 0 17
If performance is important is possible use numpy:
a = df.values
m = a != 0
df['last'] = a[np.arange(m.shape[0]), m.shape[1]-m[:,::-1].argmax(1)-1]
print (df)
site1 site2 site3 last
0 5 8 1 1
1 8 0 0 8
2 1 17 0 17
answered 47 mins ago
jezrael
301k20229304
3
This forward filling logic here is excellent across the rows :) +1
– pygo
41 mins ago
Yes forward filling across rows is out of the box thinking
– Vishnudev
37 mins ago
Great and Instant logic indeed :-) .
– pygo
37 mins ago
add a comment |Â
up vote
8
down vote
accepted
up vote
8
down vote
accepted
Use forward filling of misisng values created by replacing 0 values and thenselect last column by iloc:
df['last'] = df.replace(0, np.nan).ffill(axis=1).iloc[:, -1].astype(int)
print (df)
site1 site2 site3 last
0 5 8 1 1
1 8 0 0 8
2 1 17 0 17
If performance is important is possible use numpy:
a = df.values
m = a != 0
df['last'] = a[np.arange(m.shape[0]), m.shape[1]-m[:,::-1].argmax(1)-1]
print (df)
site1 site2 site3 last
0 5 8 1 1
1 8 0 0 8
2 1 17 0 17
answered 47 mins ago
jezrael
301k20229304
Use forward filling of misisng values created by replacing 0 values and thenselect last column by iloc:
df['last'] = df.replace(0, np.nan).ffill(axis=1).iloc[:, -1].astype(int)
print (df)
site1 site2 site3 last
0 5 8 1 1
1 8 0 0 8
2 1 17 0 17
If performance is important is possible use numpy:
a = df.values
m = a != 0
df['last'] = a[np.arange(m.shape[0]), m.shape[1]-m[:,::-1].argmax(1)-1]
print (df)
site1 site2 site3 last
0 5 8 1 1
1 8 0 0 8
2 1 17 0 17
answered 47 mins ago
jezrael
301k20229304
edited 31 mins ago
answered 47 mins ago
jezrael
301k20229304
answered 47 mins ago
jezrael
301k20229304
answered 47 mins ago
jezrael
301k20229304
301k20229304
3
This forward filling logic here is excellent across the rows :) +1
– pygo
41 mins ago
Yes forward filling across rows is out of the box thinking
– Vishnudev
37 mins ago
Great and Instant logic indeed :-) .
– pygo
37 mins ago
add a comment |Â
3
This forward filling logic here is excellent across the rows :) +1
– pygo
41 mins ago
Yes forward filling across rows is out of the box thinking
– Vishnudev
37 mins ago
Great and Instant logic indeed :-) .
– pygo
37 mins ago
3
3
This forward filling logic here is excellent across the rows :) +1
– pygo
41 mins ago
This forward filling logic here is excellent across the rows :) +1
– pygo
41 mins ago
Yes forward filling across rows is out of the box thinking
– Vishnudev
37 mins ago
Yes forward filling across rows is out of the box thinking
– Vishnudev
37 mins ago
Great and Instant logic indeed :-) .
– pygo
37 mins ago
Great and Instant logic indeed :-) .
– pygo
37 mins ago
add a comment |Â
up vote
2
down vote
Code:
df['last_site'] = df.apply(lambda x: x.iloc[x.nonzero()].iloc[-1], axis=1)
Output:
site1 site2 site3 last_site
0 5 8 1 1
1 8 0 0 8
2 1 17 0 17
answered 42 mins ago
Vishnudev
602315
Good one @Vishnudev +1 !
– pygo
37 mins ago
add a comment |Â
up vote
2
down vote
Code:
df['last_site'] = df.apply(lambda x: x.iloc[x.nonzero()].iloc[-1], axis=1)
Output:
site1 site2 site3 last_site
0 5 8 1 1
1 8 0 0 8
2 1 17 0 17
answered 42 mins ago
Vishnudev
602315
Good one @Vishnudev +1 !
– pygo
37 mins ago
add a comment |Â
up vote
2
down vote
up vote
2
down vote
Code:
df['last_site'] = df.apply(lambda x: x.iloc[x.nonzero()].iloc[-1], axis=1)
Output:
site1 site2 site3 last_site
0 5 8 1 1
1 8 0 0 8
2 1 17 0 17
answered 42 mins ago
Vishnudev
602315
Code:
df['last_site'] = df.apply(lambda x: x.iloc[x.nonzero()].iloc[-1], axis=1)
Output:
site1 site2 site3 last_site
0 5 8 1 1
1 8 0 0 8
2 1 17 0 17
answered 42 mins ago
Vishnudev
602315
answered 42 mins ago
Vishnudev
602315
answered 42 mins ago
Vishnudev
602315
answered 42 mins ago
Vishnudev
602315
602315
Good one @Vishnudev +1 !
– pygo
37 mins ago
add a comment |Â
Good one @Vishnudev +1 !
– pygo
37 mins ago
Good one @Vishnudev +1 !
– pygo
37 mins ago
Good one @Vishnudev +1 !
– pygo
37 mins ago
add a comment |Â
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53133183%2fpandas-dataframe-get-value-of-last-nonzero-column%23new-answer', 'question_page');
);
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password