英文:

how to match data in rows from one data frame to the column names in another data frame?

问题

我有2个数据框。一个是2021年所有服务呼叫的详细信息，我将其命名为data_1。第二个是一个代码文件，允许您将编码字段转换为普通描述，我将其命名为code_file。code_file看起来是这样的

id	codevalue	descr
abc.1	021	Action taken
abc.2	078	search and rescue
inc.type	7845	transport

data_1包含所有呼叫的详细信息，看起来是这样的

state	inc.type	abc.1	abc.2	city
FL	321	33	44	ORL
FL	781	59	317	MIAMI
FL	777	0154	441	MIAMI

如何以一种方式合并这两个数据框，使code_file中“id”列的值与data_1中的列名匹配？

state	inc.type	abc.1	abc.2	city	codevalue	descr
FL	321	33	44	ORL	021	Action taken
FL	781	59	317	MIAMI	078	search and rescue
FL	777	0154	441	MIAMI	7845	transport

输出应该类似于这样

我现在没有为此编写任何代码，因为我很困扰。

英文:

I have 2 data frames. One is the details of all calls for service in 2021 and I named it data_1. And the second is a code file that will allow you to convert coded fields into normal descriptions and I named it code_file. The code_file looks something like this

id	codevalue	descr
abc.1	021	Action taken
abc.2	078	search and rescue
inc.type	7845	transport

The data_1 df with details for all calls looks something like this

state	inc.type	abc.1	abc.2	city
FL	321	33	44	ORL
FL	781	59	317	MIAMI
FL	777	0154	441	MIAMI

How do I merge the two data frames in a way that the values in column "id" from code_file match with the column names in data_1?

state	inc.type	abc.1	abc.2	city	codevalue	descr
FL	321	33	44	ORL	021	Action taken
FL	781	59	317	MIAMI	078	search and rescue
FL	777	0154	441	MIAMI	7845	transport

The output should look something like this

I don't have any code for this right now as I am struggling.

答案1

得分: 1

或许

library(dplyr)
library(tidyr)
df2 %>%
mutate(rn = row_number()) %>%
pivot_longer(cols = all_of(df1$id), names_to = 'id') %>%
left_join(df1) %>%
pivot_wider(names_from = 'id', values_from = value)%>%
group_by(rn) %>%
fill(all_of(df1$id), .direction = "downup") %>%
slice(cur_group_id()) %>%
ungroup %>%
select(any_of(union(names(df2), names(df1)))

-输出

A tibble: 3 × 7

state inc.type abc.1 abc.2 city codevalue descr
<chr> <int> <int> <int> <chr> <int> <chr>
1 FL 321 33 44 ORL 21 Action taken
2 FL 781 59 317 MIAMI 78 search and rescue
3 FL 777 154 441 MIAMI 7845 transport

### 数据

df1 <- 结构(list(id = c("abc.1", "abc.2", "inc.type"), codevalue = c(21L,
78L, 7845L), descr = c("Action taken", "search and rescue", "transport"
)), class = "data.frame", row.names = c(NA, -3L))

df2 <- 结构(list(state = c("FL", "FL", "FL"), inc.type = c(321L,
781L, 777L), abc.1 = c(33L, 59L, 154L), abc.2 = c(44L, 317L,
441L), city = c("ORL", "MIAMI", "MIAMI")),
class = "data.frame", row.names = c(NA,
-3L))

<details>
<summary>英文:</summary>

Perhaps

library(dplyr)
library(tidyr)
df2 %>%
mutate(rn = row_number()) %>%
pivot_longer(cols = all_of(df1$id), names_to = 'id') %>%
left_join(df1) %>%
pivot_wider(names_from = 'id', values_from = value)%>%
group_by(rn) %>%
fill(all_of(df1$id), .direction = "downup") %>%
slice(cur_group_id()) %>%
ungroup %>%
select(any_of(union(names(df2), names(df1)))

-output

A tibble: 3 × 7

state inc.type abc.1 abc.2 city codevalue descr
<chr> <int> <int> <int> <chr> <int> <chr>
1 FL 321 33 44 ORL 21 Action taken
2 FL 781 59 317 MIAMI 78 search and rescue
3 FL 777 154 441 MIAMI 7845 transport

### data

df1 <- structure(list(id = c("abc.1", "abc.2", "inc.type"), codevalue = c(21L,
78L, 7845L), descr = c("Action taken", "search and rescue", "transport"
)), class = "data.frame", row.names = c(NA, -3L))

df2 <- structure(list(state = c("FL", "FL", "FL"), inc.type = c(321L,
781L, 777L), abc.1 = c(33L, 59L, 154L), abc.2 = c(44L, 317L,
441L), city = c("ORL", "MIAMI", "MIAMI")),
class = "data.frame", row.names = c(NA,
-3L))

</details>