Create new rows in dataframe with values from a list/dictionary

Question

I am using several data frames that I will combine integer values to deduce a final score. Sort of like a 'final grade' calculation, say with perhaps a 'Test' weigh and a 'HW' weigh, to give a picture.

Anyway, I would like to use a list of index values, and iterate through each of the data frames to see if they existed in each. If the data frame doesn't have an 'entry' for an index, or character, in this dataframe, I would like to add a row for the character in the dataframe, then assign a constant value to each month entry. The dataframes that I am looking in have months by column and rows by characters.

I have thought about using either a list or a dictionary. Here is the list that I want to use, just to show you each of the characters:

my_list = 
['Sonic','Knuckles','Shadow','Tails','Amy']

Here's an example of one of the dataframes that I would like to add it to.

penalties_df = 
                       Jan 18  Feb 18  Mar 18  Apr 18
Character                                     
Sonic                  1.0     2.0     3.0    1.0
Knuckles               2.0     2.0     2.0    2.0
Amy                    1.0     2.0     1.0    1.0

I haven't received anything from Shadow and Tails which is why they don't have a row.

Shadow                 xxx     xxx     xxx    xxx
Tails                  xxx     xxx     xxx    xxx

My desired output:

penalties_df = 
                           Jan 18  Feb 18  Mar 18  Apr 18
    Character                                     
    Sonic                  1.0     2.0     3.0    1.0
    Knuckles               2.0     2.0     2.0    2.0
    Shadow                 0.0     0.0     0.0    0.0
    Tails                  0.0     0.0     0.0    0.0
    Amy                    1.0     2.0     1.0    1.0

The 'xxx's are just placeholders to indicate where I haven't received any data from that character (Shadow and Tails). I run my code periodically, and for times that I run my code up until I receive data for a character, I would like to create a row and assign a value of 0, by checking if the character exists in the dataframe.

I have considered .append(), but I do not have an existing data frame to append to penalties_df. I only want to create new rows.

Could I create a dictionary to access for this scenario? I would think my dictionary would be something like this, however, it would be redundant to have a bunch of repeating 0's typed in (I only have a piece of what I would use here to keep my question short):

penalties_none = {
        "Sonic" : 0,
        "Knuckles" : 0,
        "Shadow" : 0,
        "Tails" : 0,
        "Amy" : 0,
    }

Thanks in advance.

harpan · Accepted Answer · 2018-06-04 16:08:42Z

1

You need to get the differences in names between df.index and my_list. Then you need to make a df with these differences and append to your main df.

df.append(pd.DataFrame(index=list(set(my_list) - set(df.index)))).fillna(0)

Output:

res = df.append(pd.DataFrame(index=list(set(my_list) - set(df.index)))).fillna(0)
res.index.name='Character'

Output:

             Apr_18  Feb_18  Jan_18  Mar_18
Character                                
Sonic         1.0     2.0     1.0     3.0
Knuckles      2.0     2.0     2.0     2.0
Amy           1.0     2.0     1.0     1.0
Tails         0.0     0.0     0.0     0.0
Shadow        0.0     0.0     0.0     0.0

edited Jun 4, 2018 at 16:08

answered Jun 4, 2018 at 16:02

harpan

8,6412 gold badges22 silver badges40 bronze badges

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

Comments

YOLO · Accepted Answer · 2018-06-04 18:27:07Z

1

I think you need reindex function:

my_list = ['Sonic','Knuckles','Shadow','Tails','Amy']

penalties_df = penalties_df.reindex(mylist, fill_value = 0)

answered Jun 4, 2018 at 18:27

YOLO

22k5 gold badges25 silver badges42 bronze badges

Collectives™ on Stack Overflow

Create new rows in dataframe with values from a list/dictionary

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related