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 do I write this in Ruby/Python? Or, can you translate my LINQ to Ruby/Python?
Best Python templating library to facilitate code generation [closed]
Code samples for Django + SWFUpload?
CMake output name for dynamic-loaded library?
Python: undo write to file
Python: “1-2-3-4” to [1, 2, 3, 4]
Problem sub-classing BaseException in Python
How can I render a tree structure (recursive) using a django template?
Is there a simple, elegant way to define singletons? [closed]
python cgi on IIS
Is it pythonic for a function to return multiple values?
Python dictionary from an object's fields
Python subprocess issue with ampersands
Directory listing in Python
SVG rendering in a PyGame application
Do you use the “global” statement in Python? [closed]

Categories

HOME
raspbian
formal-verification
netflix-feign
hosts
rfid
google-shopping
flann
cs-cart
cosmicmind
eclipse-luna
lenskit
scala-native
opera-mini
tree-traversal
google-api-java-client
facebook4j
multiplayer
jquery-scrollify
fabric
geopandas
net-snmp
unpack
system.data.sqlite
dpi
superscript
salesforce-chatter
broadleaf-commerce
codesys
freetts
alchemy.js
stdclass
nsexception
jupyter-console
upsert
facebook-chatbot
production-environment
redux-router
spyne
system.web.ui.webcontrols
android-navigationview
packagemaker
chessboard.js
mix
nofollow
coordinate-transformation
string-parsing
gmsmapview
search-regex
function-fitting
eyeql
ipojo
manjaro
callstack
alphablending
docopt
genetic-programming
outline
ember-addon
dache
getimagedata
funkload
indexing-service
gfs
logentries
magicalrecord-2.2
robospice
driver-signing
swrl
abnf
signals2
ubuntu-11.04
msgbox
django-nose
hibernate3-maven-plugin
external-assemblies
aio
web-statistics
nt4
dirty-data
document-library

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