Using df.isin() function over a column of tuples | Pandas

user_13434796 0
Viewed 487

I have a dataframe consisting of Wikipedia articles with geocoordinates and some statistics. The column 'Availability' contains a tuple of the languages that article is available in (out of a selection).

What I'm trying to do is plot a bubble map with plotly, and the legend being the availability in those languages. For example, out of ['ca','es'] you would have [],['ca'],['es'],['ca','es'] meaning not available, only in catalan, only in spanish or available in both respectively.

The problem is that when trying to use those combinations to create a dataframe with only the matching rows using Dataframe.isin(), it always returns an empty df. The columns of the dataframe are: Columns: [French Title, Qitem, Pageviews, page_title_1, page_title_2, Availability, Lat, Lon, Text]

Here is my code:

    fig = go.Figure()
    scale = 500
    for comb in combinations:

        df_sub = df[df['Availability'].isin(tuple(comb))] #The problem is here. This returns an empty DF
        
        if(len(df_sub.index)) == 0: continue #There are no occurrencies with that comb
        fig.add_trace(go.Scattergeo(
            lat=df_sub['Lat'],
            lon=df_sub['Lon'],
            text=df_sub['Text'],
            marker = dict(
                size = df[order_by],
                sizeref=2. * max(df[order_by]) / (scale ** 2),
                line_color='rgb(40,40,40)',
                line_width=0.5,
                sizemode='area'
            ), name = comb #Here is the underlying restriction. I need to separate the traces according to their availability.
        ))

PS: I guess it has something to do with pandas not working very good with using lists or tuples as a column value, but didn't figure out how to achieve what I want. Any of you has any idea? Thank you in advance. Comb appears as a string or a tuple of strings: ('es','ca') , but the values in df['Availability] when I print them appear like (es,ca)

Sample dataframe (sorry for the style I'm new to Stack overflow)**

    French Title      Qitem  Pageviews  \
0  Liban                       Q822       53903       
1  France                      Q142       25728       
2  Biélorussie                 Q184       21688       
3  ÃŽle Maurice                 Q2656389   20478       
4  Affaire Dupont de Ligonnès  Q16010109  16075       

                                        page_title_1  page_title_2  \
0  Líbano                                             Líban          
1  Francia                                            França         
2  Bielorrusia                                        Bielorússia    
3  Isla de Mauricio                                   Illa Maurici   
4  Asesinatos y desapariciones de Dupont de Ligonnès                 

  Availability              Lat              Lon  \
0  (es, ca)     33.90000000      35.53330000       
1  (es, ca)     48.86700000      2.32650000        
2  (es, ca)     53.528333333333  28.046666666667   
3  (es, ca)     -20.30084200     57.58209200       
4  (es,)        47.23613230      -1.56848610       

                                                                    Text  
0  Liban<br>(33.90000000, 35.53330000)<br>Q822                            
1  France<br>(48.86700000, 2.32650000)<br>Q142                            
2  Biélorussie<br>(53.528333333333, 28.046666666667)<br>Q184              
3  ÃŽle Maurice<br>(-20.30084200, 57.58209200)<br>Q2656389                 
4  Affaire Dupont de Ligonnès<br>(47.23613230, -1.56848610)<br>Q16010109
pandas plotly coordinates list python
2 Answers

You can use Series.apply() to achieve your goal:

df['Availability'].apply(lambda x: 'ca' in x)

That will return True if 'ca' is in the tuple. It can easily be modified to return some label, eg. Catalan.

user_6480630

In the end I turned the tuple into a list because due to not using df.isin() it doesn't raise the Unhashable Type Error, and was able to separate the traces via combinations using df.apply() (thanks to mkos for the idea):

 for comb in combinations:

    if len(comb) ==0:
        name ='Not available'
        df_sub = df[df['Availability'].apply(lambda x:  len(x)==0)]
    else:
        df_sub = df[df['Availability'].apply(lambda x: set(comb) == set(x))]
        name = ','.join(comb)
    if(len(df_sub.index)) == 0: continue
   

    fig.add_trace(go.Scattergeo(
        lat=df_sub['Lat'],
        lon=df_sub['Lon'],
        text=df_sub['Text'],
        marker = dict(
            size = df[order_by],
            sizeref=2. * max(df[order_by]) / (scale ** 2),
            line_color='rgb(40,40,40)',
            line_width=0.5,
            sizemode='area'
        ), name =name
    ))

You can see the result here.

user_134347960
Related