How Do I Make New Columns In Dataframe From A Row Of A Different Column?

Here's my current dataframe: >>>df = {'most_exhibitions' : pd.Series(['USA (1) Netherlands (5)' , 'United Kingdom (2)','China (3) India (5) Pakistan (8)','USA (11) India (

Solution 1:

You can try this approach, which use mainly string methods. Then I pivot and fillna dataframe. I lost original column most_exhibitions, but I hope it is unnecessary.

import pandas as pd

df = {'most_exhibitions' : pd.Series(['USA (1) Netherlands (5)' ,
'United Kingdom (2)','China (3) India (5) Pakistan (8)','USA (11) India (4)'], index=['a', 'b', 'c','d']), 
              'name' : pd.Series(['Bob', 'Joe', 'Alex', 'Bill'], index=['a', 'b', 'c','d'])}

df = pd.DataFrame(df)
#cange ordering of columns
df = df[['name', 'most_exhibitions']]
print df
#   name                  most_exhibitions
#a   Bob           USA (1) Netherlands (5)
#b   Joe                United Kingdom (2)
#c  Alex  China (3) India (5) Pakistan (8)
#d  Bill                USA (11) India (4)


#remove '(' and last ')'
df['most_exhibitions'] = df['most_exhibitions'].str.replace('(', '')
df['most_exhibitions'] = df['most_exhibitions'].str.strip(')')

#http://stackoverflow.com/a/34065937/2901002
s = df['most_exhibitions'].str.split(')').apply(pd.Series, 1).stack()
s.index = s.index.droplevel(-1)
s.name = 'most_exhibitions'
print s
#a               USA 1
#a       Netherlands 5
#b    United Kingdom 2
#c             China 3
#c             India 5
#c          Pakistan 8
#d              USA 11
#d             India 4
#Name: most_exhibitions, dtype: object

df = df.drop( ['most_exhibitions'], axis=1)
df = df.join(s)
print df
#   name  most_exhibitions
#a   Bob             USA 1
#a   Bob     Netherlands 5
#b   Joe  United Kingdom 2
#c  Alex           China 3
#c  Alex           India 5
#c  Alex        Pakistan 8
#d  Bill            USA 11
#d  Bill           India 4

#exctract numbers and convert them to integer
df['numbers'] = df['most_exhibitions'].str.extract("(\d+)").astype('int')
#exctract text of most_exhibitions
df['most_exhibitions'] = df['most_exhibitions'].str.rsplit(' ', n=1).str[0]
print df
#   name most_exhibitions  numbers
#a   Bob              USA        1
#a   Bob      Netherlands        5
#b   Joe   United Kingdom        2
#c  Alex            China        3
#c  Alex            India        5
#c  Alex         Pakistan        8
#d  Bill              USA       11
#d  Bill            India        4

#pivot dataframe
df = df.pivot(index='name', columns='most_exhibitions', values='numbers')
#NaN to empty string 
df = df.fillna('')

print df
#most_exhibitions  India  Netherlands  Pakistan China USA United Kingdom
#name                                                                   
#Alex                  5                      8     3                   
#Bill                  4                               11               
#Bob                                5                   1               
#Joe                                                                   2

EDIT:

I try add all columns as recommended output by function merge:

import pandas as pd

df = {'most_exhibitions' : pd.Series(['USA (1) Netherlands (5)' ,
'United Kingdom (2)','China (3) India (5) Pakistan (8)','USA (11) India (4)'], index=['a', 'b', 'c','d']), 
              'name' : pd.Series(['Bob', 'Joe', 'Alex', 'Bill'], index=['a', 'b', 'c','d'])}

df = pd.DataFrame(df)
#cange ordering of columns
df = df[['name', 'most_exhibitions']]
print df
#   name                  most_exhibitions
#a   Bob           USA (1) Netherlands (5)
#b   Joe                United Kingdom (2)
#c  Alex  China (3) India (5) Pakistan (8)
#d  Bill                USA (11) India (4)

#copy original to new dataframe for joining original df
df1 = df.reset_index().copy()

#remove '(' and last ')'
df['most_exhibitions'] = df['most_exhibitions'].str.replace('(', '')
df['most_exhibitions'] = df['most_exhibitions'].str.strip(')')

#http://stackoverflow.com/a/34065937/2901002
s = df['most_exhibitions'].str.split(')').apply(pd.Series, 1).stack()
s.index = s.index.droplevel(-1)
s.name = 'most_exhibitions'
print s
#a               USA 1
#a       Netherlands 5
#b    United Kingdom 2
#c             China 3
#c             India 5
#c          Pakistan 8
#d              USA 11
#d             India 4
#Name: most_exhibitions, dtype: object

df = df.drop( ['most_exhibitions'], axis=1)
df = df.join(s)
print df
#   name  most_exhibitions
#a   Bob             USA 1
#a   Bob     Netherlands 5
#b   Joe  United Kingdom 2
#c  Alex           China 3
#c  Alex           India 5
#c  Alex        Pakistan 8
#d  Bill            USA 11
#d  Bill           India 4

#exctract numbers and convert them to integer
df['numbers'] = df['most_exhibitions'].str.extract("(\d+)").astype('int')
#exctract text of most_exhibitions
df['most_exhibitions'] = df['most_exhibitions'].str.rsplit(' ', n=1).str[0]
print df
#   name most_exhibitions  numbers
#a   Bob              USA        1
#a   Bob      Netherlands        5
#b   Joe   United Kingdom        2
#c  Alex            China        3
#c  Alex            India        5
#c  Alex         Pakistan        8
#d  Bill              USA       11
#d  Bill            India        4

#pivot dataframe
df = df.pivot(index='name', columns='most_exhibitions', values='numbers')
#NaN to empty string 
df = df.fillna('')
df = df.reset_index()

print df
#most_exhibitions  name  India  Netherlands  Pakistan China USA United Kingdom
#0                 Alex      5                      8     3                   
#1                 Bill      4                               11               
#2                  Bob                   5                   1               
#3                  Joe                                                      2
print df1
#  index  name                  most_exhibitions
#0     a   Bob           USA (1) Netherlands (5)
#1     b   Joe                United Kingdom (2)
#2     c  Alex  China (3) India (5) Pakistan (8)
#3     d  Bill                USA (11) India (4)
df = pd.merge(df1,df, on=['name'])
df = df.set_index('index')

print df
#       name                  most_exhibitions  India  Netherlands  Pakistan  \
#index                                                                         
#a       Bob           USA (1) Netherlands (5)                   5             
#b       Joe                United Kingdom (2)                                 
#c      Alex  China (3) India (5) Pakistan (8)      5                      8   
#d      Bill                USA (11) India (4)      4                          
#
#      China USA United Kingdom  
#index                           
#a             1                 
#b                            2  
#c         3                     
#d            11