CODEWITHSUNDEEP
pythonpandasdictionary
Kenneth Rivadeneira Guadamud
Kenneth Rivadeneira Guadamud

Reputation: 147

Create pandas series using a dictionary as mapper

Is there a built-in function to create a pandas.Series column using a dictionary as mapper and index levels in the data frame ?

The idea is to create a new column based on values in index levels and a dictionary. For instance:

Let's suppose the following data frame, where id, name and code and different levels in indexes

df

                  col1    col2
id  name  code  
 0    a    x       7       10
           y       8       11
           z       9       12

 1    b    x       13      16
           y       14      17
           z       15      18

and the following dictionary d = {'a': {'y', 'z'}, 'b': {'x'}}

The output of the new column should look like:

                  col1    col2    new
id  name  code  
 0    a    x       7       10      0
           y       8       11      1
           z       9       12      1

 1    b    x       13      16      1
           y       14      17      0
           z       15      18      0

As a result of mapping in which new = 1 if code index value was in the dictionary list of values with key name, 0 otherwise.

I was trying to manually make this mapping but I am not sure how to iterate over index levels.

This is my attempt so far:

df['y'] = [1 if i in d[k] else 0 for k, v in d.items() for i
                 in df.index.get_level_values('code')]

But I am getting the following error which makes me thing that I am not iterating the index levels properly or as expected in conjunction with the dictionary.

ValueError: Length of values does not match length of index

Any suggestion?

Upvotes: 2

Views: 56

Answers (3)

G.G
G.G

Reputation: 765

pd.DataFrame.from_dict(d, orient='index').stack().reset_index().sql.set_alias("tb2").join(df1.sql.set_alias("tb1"),condition='tb1.name = tb2.level_0 and tb1.code = tb2."0"',how="right").select('tb1.*,case when tb2.level_0 is null then 0 else 1 end "new"').df().set_index(["id","name","code"])

              index  col1  col2  new
id name code                        
0  a    y         1     8    11    1
        z         2     9    12    1
1  b    x         3    13    16    1
0  a    x         0     7    10    0
1  b    y         4    14    17    0
        z         5    15    18    0

Upvotes: 0

Fenil
Fenil

Reputation: 396

A super non-pythonic and inefficient way of the above answer of @WebDev

k = list(zip(df.index.get_level_values('Brand'), 
df.index.get_level_values('Metric')))
tmp_list = [0]*df.shape[0]
for keys in d:
    for vals in d[keys]:
        for i,pairs in enumerate(k):
            if pairs[0] == keys and pairs[1] == vals:
                tmp_list[i] = 1
df['new'] = tmp_list

Upvotes: 0

WebDev
WebDev

Reputation: 1371

Use this for the new column you need:

df['new'] = [1 if j in d[i] else 0 for (i, j) in zip(df.index.get_level_values('name'), df.index.get_level_values('code'))]

Upvotes: 2

Related Questions