update df columns to match the order on a different column - pandas

Question

Let's say a have a .csv file like this:

Symbol,Address,Website,Price,Vol
URBT,,,0.99,12345
TSPG,,,1.99,12346
CRBO,,,2.99,12347
PVSP,,,3.99,12348
TPRP,,,4.99,12349
VMHG,,,5.99,12350
TORM,,,6.99,12351
SORT,,,7.99,12352
MRTI,,,8.99,12353
VTMC,,,9.99,12354

I want to update the Address and Website columns with this:

[{'ticker': 'VMHG', 'address': '555 NE 34th St. Suite 1207', 'website': 'http://www.VictoryYachts.com'}, {'ticker': 'CRBO', 'address': '1700 Broadway', 'website': 'http://www.carbonenergycorp.com'}, {'ticker': 'PVSP', 'address': '800 Westchester Ave.', 'website': 'https://www.pervasip.net'}, {'ticker': 'VTMC', 'address': '55 W. 47 Street', 'website': 'http://www.vtmc.us'}, {'ticker': 'SORT', 'address': '31 Clinton Ave', 'website': 'http://www.incjet.com'}, {'ticker': 'URBT', 'address': '11705 Willake Street', 'website': 'https://urbt.tv'}, {'ticker': 'TORM', 'address': '722 Burleson Street', 'website': 'http://www.torminerals.com'}, {'ticker': 'MRTI', 'address': '104 Armour Road', 'website': 'http://www.mrti.com'}, {'ticker': 'TPRP', 'address': '1000 Walnut St.', 'website': 'http://www.towerproperties.com'}, {'ticker': 'TSPG', 'address': '525 Milltown Rd,', 'website': 'http://www.tgipower.com/'}]

I can do this like so:

import pandas as pd

if __name__ == "__main__":
    df = pd.read_csv("tickers.csv")
    symbols = df["Symbol"].to_list()
    scraped_data = [{'ticker': 'VMHG', 'address': '555 NE 34th St. Suite 1207', 'website': 'http://www.VictoryYachts.com'}, {'ticker': 'CRBO', 'address': '1700 Broadway', 'website': 'http://www.carbonenergycorp.com'}, {'ticker': 'PVSP', 'address': '800 Westchester Ave.', 'website': 'https://www.pervasip.net'}, {'ticker': 'VTMC', 'address': '55 W. 47 Street', 'website': 'http://www.vtmc.us'}, {'ticker': 'SORT', 'address': '31 Clinton Ave', 'website': 'http://www.incjet.com'}, {'ticker': 'URBT', 'address': '11705 Willake Street', 'website': 'https://urbt.tv'}, {'ticker': 'TORM', 'address': '722 Burleson Street', 'website': 'http://www.torminerals.com'}, {'ticker': 'MRTI', 'address': '104 Armour Road', 'website': 'http://www.mrti.com'}, {'ticker': 'TPRP', 'address': '1000 Walnut St.', 'website': 'http://www.towerproperties.com'}, {'ticker': 'TSPG', 'address': '525 Milltown Rd,', 'website': 'http://www.tgipower.com/'}]
    sorted_scraped_data = sorted(
        scraped_data, key=lambda i: symbols.index(i["ticker"])
    )
    df.loc[:, ["Address", "Website"]] = [
        [i["address"], i["website"]] for i in sorted_scraped_data
    ]
    df.to_csv("tickers.csv", index=False)

This works, but feels like a hacky workaround to match the order of the Symbol column with the list of dicts.

If I want to do this with pure pandas, without sorting first, for example:

import pandas as pd

if __name__ == "__main__":
    df = pd.read_csv("tickers.csv")
    symbols = df["Symbol"].to_list()
    scraped_data = [{'ticker': 'VMHG', 'address': '555 NE 34th St. Suite 1207', 'website': 'http://www.VictoryYachts.com'}, {'ticker': 'CRBO', 'address': '1700 Broadway', 'website': 'http://www.carbonenergycorp.com'}, {'ticker': 'PVSP', 'address': '800 Westchester Ave.', 'website': 'https://www.pervasip.net'}, {'ticker': 'VTMC', 'address': '55 W. 47 Street', 'website': 'http://www.vtmc.us'}, {'ticker': 'SORT', 'address': '31 Clinton Ave', 'website': 'http://www.incjet.com'}, {'ticker': 'URBT', 'address': '11705 Willake Street', 'website': 'https://urbt.tv'}, {'ticker': 'TORM', 'address': '722 Burleson Street', 'website': 'http://www.torminerals.com'}, {'ticker': 'MRTI', 'address': '104 Armour Road', 'website': 'http://www.mrti.com'}, {'ticker': 'TPRP', 'address': '1000 Walnut St.', 'website': 'http://www.towerproperties.com'}, {'ticker': 'TSPG', 'address': '525 Milltown Rd,', 'website': 'http://www.tgipower.com/'}]
    df.loc[:, ["Address", "Website"]] = [
        [i["address"], i["website"]] for i in scraped_data
        if df[(df["Symbol"] == i["ticker"])]
    ]
    df.to_csv("tickers.csv", index=False)

I get a ValueError:

ValueError: The truth value of a DataFrame is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().

Question:

Why is that so? How can I update the df with the list of dicts to match the order of the Symbol column?

Desired output would be:

Symbol,Address,Website,Price,Vol
URBT,11705 Willake Street,https://urbt.tv,0.99,12345
TSPG,"525 Milltown Rd,",http://www.tgipower.com/,1.99,12346
CRBO,1700 Broadway,http://www.carbonenergycorp.com,2.99,12347
PVSP,800 Westchester Ave.,https://www.pervasip.net,3.99,12348
TPRP,1000 Walnut St.,http://www.towerproperties.com,4.99,12349
VMHG,555 NE 34th St. Suite 1207,http://www.VictoryYachts.com,5.99,12350
TORM,722 Burleson Street,http://www.torminerals.com,6.99,12351
SORT,31 Clinton Ave,http://www.incjet.com,7.99,12352
MRTI,104 Armour Road,http://www.mrti.com,8.99,12353
VTMC,55 W. 47 Street,http://www.vtmc.us,9.99,12354

user_9377366 · Answer

You are getting this error because you're only slicing the DataFrame with the following snippet:

df.loc[:, ["Address", "Website"]] = [
    [i["address"], i["website"]] for i in scraped_data
    if df[(df["Symbol"] == i["ticker"])]  # This part fails
]

You still cannot check its truth value. You'd have to actually get the first match for example.

Here is one possibly easier alternative turning your scraped data into a DataFrame first:

scraped_df = pd.DataFrame(scraped_data).set_index('ticker')                                                                  
df['Address', 'Website'] = scraped_df.loc[df.Symbol, ['address', 'website']].values

Output:

  Symbol                     Address                          Website  Price    Vol
0   URBT        11705 Willake Street                  https://urbt.tv   0.99  12345
1   TSPG            525 Milltown Rd,         http://www.tgipower.com/   1.99  12346
2   CRBO               1700 Broadway  http://www.carbonenergycorp.com   2.99  12347
3   PVSP        800 Westchester Ave.         https://www.pervasip.net   3.99  12348
4   TPRP             1000 Walnut St.   http://www.towerproperties.com   4.99  12349
5   VMHG  555 NE 34th St. Suite 1207     http://www.VictoryYachts.com   5.99  12350
6   TORM         722 Burleson Street       http://www.torminerals.com   6.99  12351
7   SORT              31 Clinton Ave            http://www.incjet.com   7.99  12352
8   MRTI             104 Armour Road              http://www.mrti.com   8.99  12353
9   VTMC             55 W. 47 Street               http://www.vtmc.us   9.99  12354

user_8986975 · Answer

First replace the blnaks with NaN. Secondly normalize the json int a new df.Lastly use combine_first to merge the original df and the one resulting from normalizattion. Combined code below

s=pd.json_normalize(j).rename(columns={"ticker": "Symbol", "address": "Address","website":"Website"})
df1=s.combine_first(df.replace('', np.nan))
df1=df1[['Symbol','Address','Website','Price','Vol']]

Where

j=[{'ticker': 'VMHG', 'address': '555 NE 34th St. Suite 1207', 'website': 'http://www.VictoryYachts.com'}, {'ticker': 'CRBO', 'address': '1700 Broadway', 'website': 'http://www.carbonenergycorp.com'}, {'ticker': 'PVSP', 'address': '800 Westchester Ave.', 'website': 'https://www.pervasip.net'}, {'ticker': 'VTMC', 'address': '55 W. 47 Street', 'website': 'http://www.vtmc.us'}, {'ticker': 'SORT', 'address': '31 Clinton Ave', 'website': 'http://www.incjet.com'}, {'ticker': 'URBT', 'address': '11705 Willake Street', 'website': 'https://urbt.tv'}, {'ticker': 'TORM', 'address': '722 Burleson Street', 'website': 'http://www.torminerals.com'}, {'ticker': 'MRTI', 'address': '104 Armour Road', 'website': 'http://www.mrti.com'}, {'ticker': 'TPRP', 'address': '1000 Walnut St.', 'website': 'http://www.towerproperties.com'}, {'ticker': 'TSPG', 'address': '525 Milltown Rd,', 'website': 'http://www.tgipower.com/'}]






     Symbol                     Address                          Website  Price  \
0   VMHG  555 NE 34th St. Suite 1207     http://www.VictoryYachts.com   0.99   
1   CRBO               1700 Broadway  http://www.carbonenergycorp.com   1.99   
2   PVSP        800 Westchester Ave.         https://www.pervasip.net   2.99   
3   VTMC             55 W. 47 Street               http://www.vtmc.us   3.99   
4   SORT              31 Clinton Ave            http://www.incjet.com   4.99   
5   URBT        11705 Willake Street                  https://urbt.tv   5.99   
6   TORM         722 Burleson Street       http://www.torminerals.com   6.99   
7   MRTI             104 Armour Road              http://www.mrti.com   7.99   
8   TPRP             1000 Walnut St.   http://www.towerproperties.com   8.99   
9   TSPG            525 Milltown Rd,         http://www.tgipower.com/   9.99   

       Vol  
0  12345.0  
1  12346.0  
2  12347.0  
3  12348.0  
4  12349.0  
5  12350.0  
6  12351.0  
7  12352.0  
8  12353.0  
9  12354.0

user_5032941 · Answer

df1 is the data you'd like to join to df1. Pandas does a join on the index when you assign column values - this is why I've made Symbol and ticker:

scraped_data = [{'ticker': 'VMHG', 'address': '555 NE 34th St. Suite 1207', 'website': 'http://www.VictoryYachts.com'}, {'ticker': 'CRBO', 'address': '1700 Broadway', 'website': 'http://www.carbonenergycorp.com'}, {'ticker': 'PVSP', 'address': '800 Westchester Ave.', 'website': 'https://www.pervasip.net'}, {'ticker': 'VTMC', 'address': '55 W. 47 Street', 'website': 'http://www.vtmc.us'}, {'ticker': 'SORT', 'address': '31 Clinton Ave', 'website': 'http://www.incjet.com'}, {'ticker': 'URBT', 'address': '11705 Willake Street', 'website': 'https://urbt.tv'}, {'ticker': 'TORM', 'address': '722 Burleson Street', 'website': 'http://www.torminerals.com'}, {'ticker': 'MRTI', 'address': '104 Armour Road', 'website': 'http://www.mrti.com'}, {'ticker': 'TPRP', 'address': '1000 Walnut St.', 'website': 'http://www.towerproperties.com'}, {'ticker': 'TSPG', 'address': '525 Milltown Rd,', 'website': 'http://www.tgipower.com/'}]

df_scraped = pd.DataFrame.from_dict(q).set_index("ticker")
df = df.set_index("Symbol")
df[["Address", "Website"]] = df_scraped[["address", "website"]]
df = df.reset_index()

output df:

  Symbol                     Address                          Website  Price    Vol
0   URBT        11705 Willake Street                  https://urbt.tv   0.99  12345
1   TSPG            525 Milltown Rd,         http://www.tgipower.com/   1.99  12346
2   CRBO               1700 Broadway  http://www.carbonenergycorp.com   2.99  12347
3   PVSP        800 Westchester Ave.         https://www.pervasip.net   3.99  12348
4   TPRP             1000 Walnut St.   http://www.towerproperties.com   4.99  12349
5   VMHG  555 NE 34th St. Suite 1207     http://www.VictoryYachts.com   5.99  12350
6   TORM         722 Burleson Street       http://www.torminerals.com   6.99  12351
7   SORT              31 Clinton Ave            http://www.incjet.com   7.99  12352
8   MRTI             104 Armour Road              http://www.mrti.com   8.99  12353
9   VTMC             55 W. 47 Street               http://www.vtmc.us   9.99  12354

update df columns to match the order on a different column - pandas

Question:

3 Answers