Remove none values from dataframe

Question

I have a dataframe like :

    Country Name    Income Group
 1  Norway          High income
 2  Switzerland     Middle income
 3  Qatar           Low income
 4  Luxembourg      Low income
 5  Macao           High income
 6  India           Middle income

i need something like:

    High income     Middle income   Low income
1   Norway           Switzerland     Qatar
2    Macao              India         Luxembourg

I have used pivot tables : df= df.pivot(values='Country Name', index=None, columns='Income Group') and i get something like :

    High income     Middle income   Low income
1   Norway           none            none
2    none           Switzerland      none
 .
 .
 .

Can someone suggest a better solution than pivot here so that i don't have to deal with none values?

unutbu · Accepted Answer · 2017-04-22 01:49:57Z

The trick is to introduce a new column index whose values are groupby/cumcount values. cumcount returns a cumulative count -- thus numbering the items in each group:

df['index'] = df.groupby('Income Group').cumcount()
#   Country Name   Income Group  index
# 1       Norway    High income      0
# 2  Switzerland  Middle income      0
# 3        Qatar     Low income      0
# 4   Luxembourg     Low income      1
# 5        Macao    High income      1
# 6        India  Middle income      1

Once you have the index column, the desired result can be obtained by pivoting:

import pandas as pd
df = pd.DataFrame({'Country Name': ['Norway', 'Switzerland', 'Qatar', 'Luxembourg', 'Macao', 'India'], 'Income Group': ['High income', 'Middle income', 'Low income', 'Low income', 'High income', 'Middle income']})

df['index'] = df.groupby('Income Group').cumcount() + 1
result = df.pivot(index='index', columns='Income Group', values='Country Name')
result.index.name = result.columns.name = None
print(result)

yields

  High income  Low income Middle income
1      Norway       Qatar   Switzerland
2       Macao  Luxembourg         India

Collectives™ on Stack Overflow

Remove none values from dataframe

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related