Mapping Columns/Rows from One Dataframe to Another Based on Row Number

Mapping columns/rows from one dataframe to another based on row number

Using tidyverse

library(tidyverse)
df1 %>%
   mutate(Row_Num = row_number()) %>%
   left_join(df2) %>%
   mutate(Row_Num = replace(Row_Num, !Row_Num %in% c(1, 3), NA))
# A tibble: 10 x 5
#   Name  StimulusName PupilLeft Row_Num Label 
#   <chr> <chr>            <dbl>   <dbl> <chr> 
# 1 sub7  Alpha1            10.0       1 Onset 
# 2 sub7  Alpha1            10.0      NA <NA>  
# 3 sub7  Alpha1            10.1       3 Offset
# 4 sub7  Alpha1            10.1      NA <NA>  
# 5 sub7  Alpha1            10.1      NA <NA>  
# 6 sub7  Alpha1            10.1      NA <NA>  
# 7 sub7  Alpha1            10.1      NA <NA>  
# 8 sub7  Alpha1            10.1      NA <NA>  
# 9 sub7  Alpha1            10.1      NA <NA>  
#10 sub7  Alpha1            10.1      NA <NA>  

---

If it is to join by row names

rownames_to_column(df1, "Row_Num") %>% 
    mutate(Row_Num = as.numeric(Row_Num)) %>% 
    left_join(., df2 %>% 
                   ungroup %>% 
                   select(Row_Num, Label), by = "Row_Num") %>%
    mutate(Row_Num = replace(Row_Num, !Row_Num %in% c(1, 3), NA))

---

Or using match from base R

i1 <- match(row.names(df1), df2$Row_Num)
df1[names(df2)[3:4]] <- lapply(df2[3:4], `[`, i1)
df1
# A tibble: 10 x 5
#   Name  StimulusName PupilLeft Row_Num Label 
#   <chr> <chr>            <dbl>   <dbl> <chr> 
# 1 sub7  Alpha1            10.0       1 Onset 
# 2 sub7  Alpha1            10.0      NA <NA>  
# 3 sub7  Alpha1            10.1       3 Offset
# 4 sub7  Alpha1            10.1      NA <NA>  
# 5 sub7  Alpha1            10.1      NA <NA>  
# 6 sub7  Alpha1            10.1      NA <NA>  
# 7 sub7  Alpha1            10.1      NA <NA>  
# 8 sub7  Alpha1            10.1      NA <NA>  
# 9 sub7  Alpha1            10.1      NA <NA>  
#10 sub7  Alpha1            10.1      NA <NA>  

Mapping columns from one dataframe to another to create a new column

df.merge

out = (df1.merge(df2, left_on='store', right_on='store_code')
          .reindex(columns=['id', 'store', 'address', 'warehouse']))
print(out)

   id  store address warehouse
0   1    100     xyz      Land
1   2    200     qwe       Sea
2   3    300     asd      Land
3   4    400     zxc      Land
4   5    500     bnm       Sea

---

pd.concat + df.sort_values

u = df1.sort_values('store')
v = df2.sort_values('store_code')[['warehouse']].reset_index(drop=1)
out = pd.concat([u, v], 1)

print(out)

   id  store address warehouse
0   1    100     xyz      Land
1   2    200     qwe       Sea
2   3    300     asd      Land
3   4    400     zxc      Land
4   5    500     bnm       Sea
    

The first sort call is redundant assuming your dataframe is already sorted on store, in which case you may remove it.

---

df.replace/df.map

s = df1.store.replace(df2.set_index('store_code')['warehouse'])
print(s) 
0    Land
1     Sea
2    Land
3    Land
4     Sea

df1['warehouse'] = s
print(df1)

   id  store address warehouse
0   1    100     xyz      Land
1   2    200     qwe       Sea
2   3    300     asd      Land
3   4    400     zxc      Land
4   5    500     bnm       Sea

Alternatively, create a mapping explicitly. This works if you want to use it later.

mapping = dict(df2[['store_code', 'warehouse']].values)
df1['warehouse'] = df1.store.map(mapping)
print(df1)

   id  store address warehouse
0   1    100     xyz      Land
1   2    200     qwe       Sea
2   3    300     asd      Land
3   4    400     zxc      Land
4   5    500     bnm       Sea

Mapping column dataframe with another dataframe

You can mapping column reporting_date_id by another DataFrame by Series.map and then use it for replace missing values in Series.fillna:

s = df2.set_index('reporting_date_id')['filing_date_id']
df1['filing_date_id'] = df1['filing_date_id'].fillna(df1['reporting_date_id'].map(s))

Map Dataframe Columns to Another Dataframe

If you refer to a general function that returns a dataframe, I think this works:

df1[['col1','col2','col3','col4','col5']] = pd.concat(df1['colc'].map(mult).to_list(), ignore_index=True)

Map index of one dataframe to column of another dataframe

That's because you are trying to map the index. You first need to create a dictionary with keys being the index of df2 and values being the paid value using dict(zip()). Then you can map that on df1.index and return it into your Payment column:

df1['payment'] = df1.index.map(dict(zip(df2.index,df2['paid value'])))

           Sale Price  payment
Order ID1                     
OD1                45       44
OD2                55       33
OD3                56       41

---

Related Topics