pandas use a value based on the equal value in another row when looking for other values

Assuming that all your states have weeks 10 and 11, you can sort and use loc as a label accessor to set new values once you have sorted your frame accordingly. Also I fixed the dictionary values for your dataframe.

import pandas as pd

df = pd.DataFrame([
    { "state": "CA", "region":"West", "total" :2, "week": 10 },
    { "state": "UT", "region":"Midwest", "total": 7, "week": 10 },
    { "state": "CA", "region":"CA", "total" :14, "week": 11 },
    { "state": "UT", "region":"UT", "total" :18, "week": 11 },
    { "state": "CA", "region":"West", "total": 21, "week": 12 },
    { "state": "UT", "region":"Midwest", "total" :30, "week": 12 }
]).sort_values(by = ['state','week'])

df.loc[df['week'] == 11, 'region'] = df.loc[df['week'] == 10, 'region'].to_numpy()

df

>>
    state   region  total   week
0   CA  West    2   10
2   CA  West    14  11
4   CA  West    21  12
1   UT  Midwest 7   10
3   UT  Midwest 18  11
5   UT  Midwest 30  12

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top