CODEWITHSUNDEEP
pandaspivot-tablemulti-index
Carmen
Carmen

Reputation: 793

Select data from pandas multiindex pivottable

I have a multiindex dataframe (pivottable) with 1703 rows that looks like this: 

Local code     Ex Code    ...  Value      
159605         FR1xx      ...  30               
159973         FR1xx      ...  50    
...
ZZC923HDV906   XYxx       ...  20

There are either numerical local codes (e.g. 159973) or local codes consisting of both characters and strings (e.g. ZZC923HDV906) I'd like to select the data by the first index column (Local code). This works well for the string characters with the following code

pv_comb[(pv_comb.index.get_level_values("Local code") == "ZZC923HDV906")]

However I don't manage to select the numerical values:

pv_comb[(pv_comb.index.get_level_values("Local code") == 159973)]

This returns an empty dataframe. Is it possible to convert the values in the first column of the multiindex into string characters and then select the data?

Upvotes: 0

Views: 1250

Answers (1)

jezrael
jezrael

Reputation: 862511

IIUC you need '', because your numeric values are strings - so 159973 change to '159973':

pv_comb[(pv_comb.index.get_level_values("Local code") == '159973')]

If need convert some level of MultiIndex to string need create new index and then assign:

#if python 3 add list
new_index = list(zip(df.index.get_level_values('Local code').astype(str),
                df.index.get_level_values('Ex Code')))

df.index = pd.MultiIndex.from_tuples(new_index, names = df.index.names)

Also is possible there are some whitespaces, you can remove them by strip:

#change multiindex
new_index = zip(df.index.get_level_values('Local code').astype(str).str.strip(),
                df.index.get_level_values('Ex Code')
df.index = pd.MultiIndex.from_tuples(new_index, names = df.index.names)

Solution if many levels is first reset_problematic level, do operations and set index back. Then is possible sortlevel is necessary:

df = pd.DataFrame({'Local code':[159605,159973,'ZZC923HDV906'],
                   'Ex Code':['FR1xx','FR1xx','XYxx'],
                   'Value':[30,50,20]})
pv_comb = df.set_index(['Local code','Ex Code'])
print (pv_comb)
                      Value
Local code   Ex Code       
159605       FR1xx       30
159973       FR1xx       50
ZZC923HDV906 XYxx        20

pv_comb = pv_comb.reset_index('Local code')
pv_comb['Local code'] = pv_comb['Local code'].astype(str)
pv_comb = pv_comb.set_index('Local code', append=True).swaplevel(0,1)
print (pv_comb)
                      Value
Local code   Ex Code       
159605       FR1xx       30
159973       FR1xx       50
ZZC923HDV906 XYxx        20

Upvotes: 2

Related Questions