CODEWITHSUNDEEP
pythonpandas
Jason Strimpel
Jason Strimpel

Reputation: 15466

Update DataFrame index by matching column value

Consider the following pandas.DataFrame:

>>> import pandas as pd
>>> df = pd.DataFrame({
...     "sym": ["a", "b", "c"],
...     "del": [1, 2, 3]
... })

And consider the following dict:

>>> d = [{"sid": 99, "sym": "b"}, {"sid": 88, "sym": "c"}]

I need to update df's index with the value of sid where sym matches. For this example, my output would like like this:

>>> df
   sym  del
0    a    1
99   b    2
88   c    3

How might I do this?

Upvotes: 1

Views: 202

Answers (2)

jezrael
jezrael

Reputation: 862581

Use Series.map with dictionary, then replace missing values by original index values:

d = [{"sid": 99, "sym": "b"}, {"sid": 88, "sym": "c"}]

d1 = {x['sym']:x['sid'] for x in d}
df.index = df['sym'].map(d1).fillna(df.index.to_series()).astype(int).rename(None)

print (df)
   sym  del
0    a    1
99   b    2
88   c    3

df = pd.DataFrame({
    "sym": ["a", "b", "c"],
   "del": [1, 2, 3]
}, index=[50,51,52])
print (df)
   sym  del
50   a    1
51   b    2
52   c    3

d = [{"sid": 99, "sym": "b"}, {"sid": 88, "sym": "c"}]

d1 = {x['sym']:x['sid'] for x in d}
df.index = df['sym'].map(d1).fillna(df.index.to_series()).astype(int).rename(None)

print (df)
   sym  del
50   a    1
99   b    2
88   c    3

Upvotes: 2

anky
anky

Reputation: 75080

here is one way using merge after converting the list of dict to a dataframe:

m = df.merge(pd.DataFrame(d),on='sym',how='left')
df.index = m['sid'].fillna(df.index.to_series()).astype(int).rename(None)
print(df)

   sym  del
0    a    1
99   b    2
88   c    3

Upvotes: 1

Related Questions