hot to update a database in pandas-CodePudding

I'm trying to update my database with new information, but i don't know how.

here's is my code:

import pandas as pd

listofplayers = [['player1', 5,1,300,100],['player2', 10,5,650,150],['player3', 17,6,1100,1050]]
listofplayers2 = [['player1', 105,101,10300,10100],['player11', 1010,105,10650,10150],['player23', 1017,106,101100,101050]]


dictdataframe = {
    'player': [],
    'win': [],
    'loss': [],
    'moneywin': [],
    'moneyloss': []
    }


for i in listofplayers:
    y = 0
    for k in dictdataframe.keys():
        dictdataframe[k].append(i[y])
        y  =1

db = pd.DataFrame(dictdataframe)

my output should be :

index,player,win,loss,moneywin,moneylost,
0,'player1', 105,101,10300,10100,
1,'player2', 10,5,650,150,
2,'player3', 17,6,1100,1050,
3,'player11', 1010,105,10650,10150,
4,'player23', 1017,106,101100,101050

i've spent all day on the web, but i never found what i needed

CodePudding user response：

EDIT: Whoops, I missed the double player1! You are right, that is a much more difficult question. The best way I have found is to create two data frames, index them by player and call combine_first. Note that for this, you would call df1.combine_first(df2) if you want the values in df1 to supersede df2. If you wanted to set your own rules, you could use combine, as described here. Sorry for that! Below is the code:

add_to_dict(listofplayers,dictdataframe1)
add_to_dict(listofplayers2,dictdataframe2)
df1 = pd.DataFrame.from_dict(dictdataframe1).set_index('player')
df2 = pd.DataFrame.from_dict(dictdataframe2).set_index('player')

a = df2.combine_first(df1)

We are using the add_to_dict function from below. Now our output is

           win  loss  moneywin  moneyloss
player                                   
player1    105   101     10300      10100
player11  1010   105     10650      10150
player2     10     5       650        150
player23  1017   106    101100     101050
player3     17     6      1100       1050

Original Answer

There is no need for a double for loop. You were on the right track with dictdataframe. Instead of going too fancy with multiple loops, you can just loop through the elements and append them to that! Then all you need to do is send to a data frame with the built in pandas method.

import pandas as pd

listofplayers = [['player1', 5,1,300,100],['player2', 10,5,650,150],['player3', 17,6,1100,1050]]
listofplayers2 = [['player1', 105,101,10300,10100],['player11', 1010,105,10650,10150],['player23', 1017,106,101100,101050]]

dictdataframe = {
    'player': [],
    'win': [],
    'loss': [],
    'moneywin': [],
    'moneyloss': []
    }


for x in listofplayers:
    dictdataframe['player'].append(x[0])
    dictdataframe['win'].append(x[1])
    dictdataframe['loss'].append(x[2])
    dictdataframe['moneywin'].append(x[3])
    dictdataframe['moneyloss'].append(x[4])

for x in listofplayers2:
    dictdataframe['player'].append(x[0])
    dictdataframe['win'].append(x[1])
    dictdataframe['loss'].append(x[2])
    dictdataframe['moneywin'].append(x[3])
    dictdataframe['moneyloss'].append(x[4])


df = pd.DataFrame.from_dict(dictdataframe)

This gives us

     player   win  loss  moneywin  moneyloss
0   player1     5     1       300        100
1   player2    10     5       650        150
2   player3    17     6      1100       1050
3   player1   105   101     10300      10100
4  player11  1010   105     10650      10150
5  player23  1017   106    101100     101050

This is pretty verbose though. We can abstract the loop away with a function, like so:

def add_to_dict(listplayers,dictionary):
    for x in listplayers:
        dictionary['player'].append(x[0])
        dictionary['win'].append(x[1])
        dictionary['loss'].append(x[2])
        dictionary['moneywin'].append(x[3])
        dictionary['moneyloss'].append(x[4])
    
    return None #we are editing the input dictionary.

Now we can just call the function on all of our lists and turn it into a data frame!

add_to_dict(listofplayers,dictdataframe)
add_to_dict(listofplayers,dictdataframe)
df = pd.DataFrame.from_dict(dictdataframe)

This gives us the same output. If you REALLY wanna expedite it, you can make the function take in a list of listofplayers and spit out the data frame, but I will leave that as an exercise to you.