CODEWITHSUNDEEP
python-3.xpandas
Lokkii9
Lokkii9

Reputation: 95

Pandas copy from one sheet to another based on row

There are many posts similar, and they have solutions, but they are for much more complicated data/questions, which makes it irrelevant and I fail to understand it all-together. I want to make this simple.

Below is the data from "New_File.csv"

Name,Rating
Dr_Banner,8.6
Tony_Stark,9.6
Steve_Rogers,8.1
Mary_Jane,6.1
Peter_Parker,8.4
Natasha_Romanoff,9.2

Here is the data from "Old_File.csv", to which I need to update the Rating column, which does not have the "Name" column in the same order as the "new_file.csv

# Please note that the Rating column is empty and needs to filled from the "New_File.csv"..

Name,Movies,Rating
Tony_Stark,11,
Peter_Parker,5,
Steve_Rogers,8,
Natasha_Romanoff,10,
Dr_Banner,9,
Mary_Jane,2,

I have tried this code below to achieve it. But obviously it does not do anything, and I am totally confused.

import pandas as pd
New_File = pd.read_csv("New_File.csv")
Old_File = pd.read_csv("Old_File.csv")

character_names = ["Dr_Banner", "Tony_Stark", "Steve_Rogers", "Mary_Jane", "Peter_Parker", "Natasha_Romanoff"]

for names in character_names:
    # Find the value to Update from New_File
    filter1 = New_File.loc[New_File['Name'] == names]
    filter2 = filter1["Rating"]
    # Find the value to Update in Old_File
    filter3 = (Old_File['Name'] == names)
    # Updating the Value
    Old_File.loc[filter3, 'Rating'] = filter2
    # Saving the file
    Old_File.to_csv("Old_File.csv", index=False)

I need to update the "rating column, based on the "Names" column, as they are shuffled. Please help.

Upvotes: 0

Views: 117

Answers (1)

Serge de Gosson de Varennes
Serge de Gosson de Varennes

Reputation: 11512

You can do it this way:

New = pd.read_csv(r"C:/users/k_sego/New.csv", sep=",")
Old = pd.read_csv(r"C:/users/k_sego/Old.csv", sep=",")
Old = New.merge(Old, on="Name", how='inner')
Old =Old.drop(['Rating_y'],axis=1)
Old = Old.rename(columns={'Rating_x':'Rating'})

which gives the updated Old file

              Name  Rating  Movies
0         Dr_Banner     8.6       9
1        Tony_Stark     9.6      11
2      Steve_Rogers     8.1       8
3         Mary_Jane     6.1       2
4      Peter_Parker     8.4       5
5  Natasha_Romanoff     9.2      10

And then to_csv

Old.to_csv("Old_File.csv", index=False)

EDIT

To keep the order of columns, change the order of the merge

New = pd.read_csv(r"C:/users/k_sego/New.csv", sep=",")
Old = pd.read_csv(r"C:/users/k_sego/Old.csv", sep=",")
Old = Old.merge(New, on="Name", how='inner')
Old =Old.drop(['Rating_x'],axis=1)
Old = Old.rename(columns={'Rating_y':'Rating'})

which returns

               Name  Movies  Rating
0        Tony_Stark      11     9.6
1      Peter_Parker       5     8.4
2      Steve_Rogers       8     8.1
3  Natasha_Romanoff      10     9.2
4         Dr_Banner       9     8.6
5         Mary_Jane       2     6.1

Upvotes: 1

Related Questions