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 to shift a list nth time in Left or Right and fill it up with 0 in python
NumPy, why equality check does not work for an array of objects?
String formatting of timedeltas in Pandas
Celery - How to send task from remote machine?
YouTube Api v3 handling Exceptions in Python
Copy data from one oracle database to another with Python
converting python program into executable
How to use multidatabase across multiproject in django?
SSLHandshakeError when connecting to Google Analytics using Google APIs Client Library for Python
Pandas Pivot Table Display in ReportLab
python multiprocessing behaviour
How do You Hack the Enthought Canopy Editor Background Color?
Why does PySide still require QString for textChanged SIGNAL?
Calling to a Sikuli script from Python (Selenium)
Add optional part in python regular expression
Django QueryDict empty with request.POST but populated in request.GET

Categories

HOME
nlp
mapping
angular2-routing
cocos2d-x-3.0
couchdb-2.0
phaser-framework
jrebel
quill
gspread
requirejs
packer
ndis
jacoco
rapidjson
eclipse-luna
google-openid
jogl
clover
plsqldeveloper
raphael
header-files
piwik
cronet
fabric
printdocument
metadata-extractor
classpath
forum
cell-array
vegan
backup-strategies
graph-databases
geo
data-extraction
.net-assembly
autocompletetextview
ntfs-mft
delphi-10.1-berlin
html-agility-pack
z3py
nstouchbar
jags
livefyre
pinvoke
android-bitmap
collapsingtoolbarlayout
restivejs
nsfetchedresultscontrolle
cfeclipse
wcf-ria-services
json-schema-validator
multifile-uploader
coordinate-transformation
picking
eml
handlebars.java
servlet-3.0
ninject.web.mvc
android-snackbar
xjc
criteria-api
lines-of-code
stackframe
jcr-sql2
inputaccessoryview
line-numbers
libsndfile
qtestlib
odftoolkit
dmp
ghostdoc
firebug-lite
java-collections-api
getimagedata
coin-flipping
dbsetup
crystal-reports-10
operations
threadx
argb
justgage
dwolla
xml-libxml
abnf
dynamic-data
ubuntu-11.10
zookeeper
webkit-transform
scraperwiki
sendfile
nagle
linfu
web-statistics
nt4
configurable
web-analytics-tools
interface-design

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