CODEWITHSUNDEEP
pythonpandasmapping
lipos
lipos

Reputation: 1

Pandas mapping column to column (single value) and column to column (multi value)

I have following two DataFrame with Addresses IP and Rules. I would like to map IP addresses to rules. I'm partially successful when Name is a single IP, but unsuccessful with ServiceGroups and then Rules have more than one Service.

Here's my code:

df_addresses = pd.DataFrame(
    {
        'Name': ['ServiceIP1', 'ServiceIP2', 'ServiceIP3', 'ServiceGRP4', 'ServiceGRP5', 'ServiceGRP6', 'ServiceIP7', 'ServiceIP8', 'ServiceIP9','ServiceIP10'],
        'Addresses': ['1.1.1.1', '2.2.2.2', '3.3.3.3', 'ServiceIP7, ServiceIP8', 'ServiceIP9, ServiceIP10', '6.6.6.6', '7.7.7.7',  '8.8.8.8', '9.9.9.9', '10.10.10.10']
    }
)

df_rules = pd.DataFrame(
    {
        'SrcNet': ['ServiceIP1', 'ServiceIP3', 'ServiceIP1, ServiceIP2', 'ServiceGRP4','ServiceGRP5'],
        'DstNet': ['ServiceIP2', 'ServiceIP1', 'ServiceIP3', 'ServiceGRP5','ServiceIP10'],
    }
)
df_rules['SrcIP'] = df_rules.SrcNet.map(df_addresses.set_index('Name').Addresses)
df_rules['DstIP'] = df_rules.DstNet.map(df_addresses.set_index('Name').Addresses)

The outcome is:

print(df_addresses)
          Name                Addresses
0   ServiceIP1                  1.1.1.1
1   ServiceIP2                  2.2.2.2
2   ServiceIP3                  3.3.3.3
3  ServiceGRP4   ServiceIP7, ServiceIP8
4  ServiceGRP5  ServiceIP9, ServiceIP10
5  ServiceGRP6                  6.6.6.6
6   ServiceIP7                  7.7.7.7
7   ServiceIP8                  8.8.8.8
8   ServiceIP9                  9.9.9.9
9  ServiceIP10              10.10.10.10

print(df_rules)
                   SrcNet       DstNet                    SrcIP                    DstIP
0              ServiceIP1   ServiceIP2                  1.1.1.1                  2.2.2.2
1              ServiceIP3   ServiceIP1                  3.3.3.3                  1.1.1.1
2  ServiceIP1, ServiceIP2   ServiceIP3                      NaN                  3.3.3.3
3             ServiceGRP4  ServiceGRP5   ServiceIP7, ServiceIP8  ServiceIP9, ServiceIP10
4             ServiceGRP5  ServiceIP10  ServiceIP9, ServiceIP10              10.10.10.10

I would like it to have ONLY IPs in the SrcIP and DstIP column! Like this

                   SrcNet       DstNet                    SrcIP                    DstIP
0              ServiceIP1   ServiceIP2                  1.1.1.1                  2.2.2.2
1              ServiceIP3   ServiceIP1                  3.3.3.3                  1.1.1.1
2  ServiceIP1, ServiceIP2   ServiceIP3         1.1.1.1, 2.2.2.2                  3.3.3.3
3             ServiceGRP4  ServiceGRP5         7.7.7.7, 8.8.8.8     9.9.9.9, 10.10.10.10
4             ServiceGRP5  ServiceIP10     9.9.9.9, 10.10.10.10              10.10.10.10

Any idea how I can map it with pandas or any alternative solutions?

Upvotes: 0

Views: 150

Answers (1)

mozway
mozway

Reputation: 261900

You need a double mapping, with an intermediate DataFrame with split and exploded Addresses:

# initial mapper
s = df_addresses.set_index('Name')['Addresses']

# split/explode addresses
s2 = df_addresses['Addresses'].str.split(',\s*').explode()

mapper = (df_addresses
          .assign(Addresses=s2.map(s).fillna(s2).groupby(level=0).agg(', '.join))
          .set_index('Name')['Addresses']
         ) 

df_rules['SrcIP'] = df_rules['SrcNet'].map(mapper)
df_rules['DstIP'] = df_rules['DstNet'].map(mapper)

output:

                   SrcNet       DstNet                 SrcIP                 DstIP
0              ServiceIP1   ServiceIP2               1.1.1.1               2.2.2.2
1              ServiceIP3   ServiceIP1               3.3.3.3               1.1.1.1
2  ServiceIP1, ServiceIP2   ServiceIP3                   NaN               3.3.3.3
3             ServiceGRP4  ServiceGRP5      7.7.7.7, 8.8.8.8  9.9.9.9, 10.10.10.10
4             ServiceGRP5  ServiceIP10  9.9.9.9, 10.10.10.10           10.10.10.10

Upvotes: 1

Related Questions