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

Retrieving data using Beautiful Soup
How can I get this series to a pandas dataframe?
Python - How to create a folder with a user entered name?
python win32com powerpoint ribbon xml
Read and write data to new file Python
How can I create a figure with optimal resolution for printing?
creating a for loop where xpath increases
Beautifulsoup - scraping everything but table data
Django-Haystack returns no results
How did I end up creating list from dataframe in Python Spark?
pandas: create single size & sum columns after group by multiple columns
How to check the shape of multiple arrays contained in a list?
Understanding net_surgery in caffe
Plotting a heatmap of temperatures
JWT Signature Validation Failed after deploying new backend version to server
How to automatically call a text file with today's date in Python

Categories

HOME
caching
google-cloud-bigtable
oauth
rotation
raspbian
google-contacts
qpython3
search-engine
dataframe
proguard
xcode8.3
openwrt
desktop
xamarin-studio
iso
arm-template
primes
sonarqube-msbuild-runner
countif
hhvm
novnc
data-cleansing
jenkins-job-dsl
osmdroid
classpath
pcre
protobuf-net
graph-databases
spring-insight
m2e
pycparser
npm-publish
liquid-xml
coreclr
magiczoomplus
active-model-serializers
s3cmd
cross-entropy
rule
intellij-idea-2016
homekit
xmgrace
component-pascal
struts-layout
crystal-reports-8.5
android-bitmap
mcrypt
etherpad
infinite-scroll
castle-dynamicproxy
addin-express
festival
svn-merge
gradle-script-kotlin
rstudio-server
mailcatcher
foxit
amf
gradle-release-plugin
mfc-feature-pack
asp.net-mvc-2
file-diffs
site-prism
selendroid
mptcp
castle-windsor-3
pylearn
ember-cli-addons
hana-xs
facebook-wall
ember-addon
slick-2.0
svcutil.exe
healthvault
ruboto
sharpmap
symphony-cms
soa-suite
vertical-scrolling
qt4.6
legacy-code
supersized
v4l
path-manipulation
qtembedded
nagle
drawtext
hibernate3-maven-plugin
revert
communicationexception

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