python


shift values from another column python pandas to another column


I am new to Python and facing and issue with the following:
a) How do I shift right the values from Rank to Total with values from Rank to Bronze if the total containing NAN at column Total
b) How to fill the missing value of Rank (After the shift value) to a value derived from the row above it.
Issue:
Rank NOC Gold Silver Bronze Total
0 1 United States (USA) 46 37 38 121
1 2 Argentina (ARG) 3 1 0 4
2 3 Denmark (DEN) 2 6 7 15
3 4 Sweden (SWE) 2 6 3 11
4 5 South Africa (RSA) 2 6 2 10
5 6 Sweden (SWE) 2 6 3 11
**6 Tajikistan (TJK) 1 0 0 1 NaN**
7 7 Malaysia (MAS) 0 4 1 5
Expected Outcome:
Rank NOC Gold Silver Bronze Total
[0 1 United States (USA) 46 37 38 121
1 2 Argentina (ARG) 3 1 0 4
2 3 Denmark (DEN) 2 6 7 15
3 4 Sweden (SWE) 2 6 3 11
4 5 South Africa (RSA) 2 6 2 10
5 6 Sweden (SWE) 2 6 3 11
**6 6 Tajikistan (TJK) 1 0 0 1**
7 7 Malaysia (MAS) 0 4 1 5]
1
I would do this by adding up the Gold, Silver and Bronze (with some weights to ensure gold count more any number of silvers etc.) then you can use rank:
In [11]: (df["Gold"] * 10000 + df["Silver"] * 100 + df["Bronze"])
Out[11]:
0 463738
1 30100
2 20607
3 20603
4 20602
5 20603
6 10000
7 401
dtype: int64
In [12]: (df["Gold"] * 10000 + df["Silver"] * 100 + df["Bronze"]).rank(method='first', ascending=False)
Out[12]:
0 1.0
1 2.0
2 3.0
3 4.0
4 6.0
5 5.0
6 7.0
7 8.0
dtype: float64
This is how I did it. It works but not sure how optimised it is. As long the answer is correct for me.
from pandas import DataFrame, Series
import pandas as pd
import matplotlib.pyplot as plt
import numpy as np
%matplotlib inline
import re
# Step 1
# Cleanup values within NOC and Rank. Start off with changing the values within Total
# Replace the value of Total which is Null to NaN
df.loc[:, 'Total2'] = df['Total'].isnull()
# Step 2
# Filter Total equal to Nan and shift the row values from Rank to Total - Rank to Bronze
df.ix[df.Total2 == True, 'Total'] = df['Bronze']
df.ix[df.Total2 == True, 'Bronze'] = df['Silver']
df.ix[df.Total2 == True, 'Silver'] = df['Gold']
df.ix[df.Total2 == True, 'Gold'] = df['NOC']
df.ix[df.Total2 == True, 'NOC'] = df['Rank']
# Step 3
# Clean up the Rank column. Create a new column which reveal only digit value
df['Rank2'] = pd.to_numeric(df['Rank'], errors='coerce')
df['fill_forward'] = df['Rank2'].fillna(method='ffill')
del df['Rank']
del df['Rank2']
del df['Total2']
df = df.rename(columns={'fill_forward': 'Rank'})

Related Links

How does one install librets on Mac OSX Yosemite?
Unhashable type error trying to pass list to function in python
Similarity Measure/Matrix for data (recommender system)- Python
How can you add movement to shapes in pygame?
Extract integer from list using python
Having trouble using “in” function to check for containment of one array in another
Getting next two indexes regardless of current index
How to apply custom maya manipulator with user defined context
Issue with parsing a function in Python (optionally with pyparsing)
Function that iterates through a linked list
convert pandas dataframe column to np.datetime64
How can I embed django code in html?(pagination)
export multiple datasets from a .hdf5 to csv in python
How to test whether one of three keys is in a dict?
pythonic way to print variables in class Person
Parsing commands+input in BF variant- Python 2.7.12

Categories

HOME
google-apps-script
admin-on-rest
voip
ocaml
smartphone
amazon-product-api
zend-framework2
search-engine
rocketmq
defragmentation
snap.svg
tizen-wearable-sdk
requirejs
httpclient
slim-3
cockroachdb
onsen-ui2
xlsx
game-physics
phoenix
jacoco
scaling
cairo
eclipselink
sensu
servicemix
piwik
java-ee-7
metis
kong
google-api-dotnet-client
pcre
percentage
oracle-xml-db
idl
google-drive-realtime-api
transient
rhino
threshold
viewstate
office365connectors
linq-to-entities
flex4.5
wsadmin
socketscan
constants
excel-interop
homekit
email-parsing
fqdn
livefyre
gauss
hpcc
paas
objloader
rsqlite
libreadline
audioqueue
aescryptoserviceprovider
zurb-foundation-apps
amf
soda
spring-repositories
achievements
mongo-c-driver
freefem++
twgl.js
handlebars.java
getrusage
programming-paradigms
mptcp
google-earth-plugin
java-melody
kendo-mvvm
data-generation
reserved-words
uipickerviewcontroller
baucis
confusion-matrix
fogbugz-api
dynamic-binding
ienumerator
iconv
zend-framework-modules
mozart-mvc
android-4.0
attachevent
fotoware
rose-db-object
point-sprites
objectbrowser
surf
handwriting
django-nose
phpcrawl
getresource
querypath
openwysiwyg
castle-validators

Resources

Mobile Apps Dev
Database Users
javascript
java
csharp
php
android
MS Developer
developer works
python
ios
c
html
jquery
RDBMS discuss
Cloud Virtualization
Database Dev&Adm
javascript
java
csharp
php
python
android
jquery
ruby
ios
html
Mobile App
Mobile App
Mobile App