Add column during loop with specific value

Question

Imagine that I have the following dict:

 configs = {
    'CONFIG1': [
        {
            "server": "SERVER_1",
            "description": "Testing server 1.",
        },
        {
            "server": "SERVER_2",
            "description": "Testing server 2.",
        }
    ],
    'CONFIG2': [
        {
            "server": "SERVER_3",
            "description": "Testing server 3.",
        },
        {
            "server": "SERVER_4",
            "description": "Testing server 4.",
        }
    ],
    'CONFIG3': [
        
    ]
}

I want to organize this config into a dataframe so that it is like this:

server	description	config_name
SERVER_1	Testing server 1.	CONFIG1
SERVER_2	Testing server 2.	CONFIG1
SERVER_3	Testing server 3.	CONFIG2
SERVER_4	Testing server 4.	CONFIG2

I also want to prevent empty configuration keys such as CONFIG3 from being added to the dataframe.

I've tried doing it like this:

import pandas as pd

df = pd.DataFrame()

for config in configs:
    if configs[config]:
        df = df.append(configs[config], ignore_index=True)
        df['config_name'] = config
    

print(df)

But the configuration name is not right. The output is:

server	description	config_name
SERVER_1	Testing server 1.	CONFIG2
SERVER_2	Testing server 2.	CONFIG2
SERVER_3	Testing server 3.	CONFIG2
SERVER_4	Testing server 4.	CONFIG2

Every time you do df['config_name'] = config you are setting the value for the entire column. — Kris
– Kris, Commented Mar 10, 2021 at 17:29

Quang Hoang · Accepted Answer · 2021-03-10 17:29:27Z

2

Do not repeatedly append to a dataframe. concat is almost always a better choice:

pd.concat([pd.DataFrame(d).assign(config_name=k) 
           for k,d in configs.items()
          ])

Output:

     server        description config_name
0  SERVER_1  Testing server 1.     CONFIG1
1  SERVER_2  Testing server 2.     CONFIG1
0  SERVER_3  Testing server 3.     CONFIG2
1  SERVER_4  Testing server 4.     CONFIG2

answered Mar 10, 2021 at 17:29

Quang Hoang

151k11 gold badges64 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

BENY · Accepted Answer · 2021-03-10 17:31:37Z

0

Let us try explode

out = pd.Series(configs).explode().dropna().apply(pd.Series)
Out[17]: 
           server        description
CONFIG1  SERVER_1  Testing server 1.
CONFIG1  SERVER_2  Testing server 2.
CONFIG2  SERVER_3  Testing server 3.
CONFIG2  SERVER_4  Testing server 4.

answered Mar 10, 2021 at 17:31

BENY

324k22 gold badges176 silver badges250 bronze badges

Comments

Barmar · Accepted Answer · 2021-03-10 17:31:43Z

0

df['config_name'] = config assigns this to all rows in the df, not just the rows you just added.

Add it as an entry in the dictionaries before appending to the df.

for name, dicts in configs.items():
    if dicts:
        for d in dicts:
            d['config_name'] = name
        df = df.append(dicts, ignore_index=True)

answered Mar 10, 2021 at 17:31

Barmar

789k57 gold badges554 silver badges669 bronze badges

3 Comments

Vishnudev Krishnadas Over a year ago

This will be slow for large data. @Barmar You could append to a list instead

Barmar Over a year ago

Not significantly slower than the original code. This just fixes the bug, it's not the optimal way to do it.

Vishnudev Krishnadas Over a year ago

Yes. I agree with that.

Vishnudev Krishnadas · Accepted Answer · 2021-03-10 17:54:10Z

0

A one-liner would be using list comprehension

df = pd.DataFrame([{**d, 'config_name': k} for k,v in configs.items() for d in v])

Output

     server        description config_name
0  SERVER_1  Testing server 1.     CONFIG1
1  SERVER_2  Testing server 2.     CONFIG1
2  SERVER_3  Testing server 3.     CONFIG2
3  SERVER_4  Testing server 4.     CONFIG2

answered Mar 10, 2021 at 17:54

Vishnudev Krishnadas

11k2 gold badges29 silver badges58 bronze badges

Collectives™ on Stack Overflow

Add column during loop with specific value

4 Answers 4

Comments

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related