Merging two Dataset with Dplyr

Question

I have two dataset that I want to merge and calculate the population per state in a specific year. Dataset 1 contains the output as shown in the picture. Dataset 2 contains the population per state in specific year (see picture 2). I want to merge and mutate the dataset to get the dataset as shown in picture 3. To clarify: I want to create a new column with the population of a state in the noticed year.

states <- states %>%
  rename(
    'yr' = 'Year'
  ) 

merge <- merge(tornadoes, states, by = 'yr')

Example Merge Dataset
 yr num mo dy     time state magnitude injuries fatalities crop_loss
1  1950   1  1  3 11:00:00    MO         3        3         No         0
2  1950   1  1  3 11:10:00    IL         3        0         No         0
3  1950   2  1  3 11:55:00    IL         3        3         No         0
4  1950   3  1  3 16:00:00    OH         1        1         No         0
5  1950   4  1 13 05:25:00    AR         3        1        Yes         0
6  1950   5  1 25 19:30:00    MO         2        5         No         0
7  1950   6  1 25 21:00:00    IL         2        0         No         0
8  1950   7  1 26 18:00:00    TX         2        2         No         0
9  1950   8  2 11 13:10:00    TX         2        0         No         0
10 1950   9  2 11 13:50:00    TX         3       12        Yes         0
11 1950  10  2 11 21:00:00    TX         2        5         No         0
12 1950  11  2 11 23:55:00    TX         2        6         No         0
13 1950  12  2 12 00:30:00    TX         2        8        Yes         0
14 1950  13  2 12 01:15:00    TX         1        0         No         0
15 1950  14  2 12 06:10:00    TX         2        0         No         0
16 1950  15  2 12 11:57:00    TX         1       32         No         0
   property_loss Alabama Arizona Arkansas California Colorado Connecticut Delaware
1              6    3058     756     1908      10677     1325        2016      321
2              5    3058     756     1908      10677     1325        2016      321
3              5    3058     756     1908      10677     1325        2016      321
4              4    3058     756     1908      10677     1325        2016      321
5              3    3058     756     1908      10677     1325        2016      321
6              5    3058     756     1908      10677     1325        2016      321
7              5    3058     756     1908      10677     1325        2016      321
8              0    3058     756     1908      10677     1325        2016      321
9              4    3058     756     1908      10677     1325        2016      321
10             4    3058     756     1908      10677     1325        2016      321
11             5    3058     756     1908      10677     1325        2016      321
12             5    3058     756     1908      10677     1325        2016      321
13             4    3058     756     1908      10677     1325        2016      321
14             4    3058     756     1908      10677     1325        2016      321
15             4    3058     756     1908      10677     1325        2016      321
16             5    3058     756     1908      10677     1325        2016      321
   District.Of.Columbia Florida Georgia Idaho Illinois Indiana Iowa Kansas Kentucky
1                   806    2810    3458   590     8738    3967 2625   1916     2936
2                   806    2810    3458   590     8738    3967 2625   1916     2936
3                   806    2810    3458   590     8738    3967 2625   1916     2936
4                   806    2810    3458   590     8738    3967 2625   1916     2936
5                   806    2810    3458   590     8738    3967 2625   1916     2936
6                   806    2810    3458   590     8738    3967 2625   1916     2936
7                   806    2810    3458   590     8738    3967 2625   1916     2936
8                   806    2810    3458   590     8738    3967 2625   1916     2936
9                   806    2810    3458   590     8738    3967 2625   1916     2936
10                  806    2810    3458   590     8738    3967 2625   1916     2936
11                  806    2810    3458   590     8738    3967 2625   1916     2936
12                  806    2810    3458   590     8738    3967 2625   1916     2936
13                  806    2810    3458   590     8738    3967 2625   1916     2936
14                  806    2810    3458   590     8738    3967 2625   1916     2936
15                  806    2810    3458   590     8738    3967 2625   1916     2936
16                  806    2810    3458   590     8738    3967 2625   1916     2936
   Louisiana Maine Maryland Massachusetts Michigan Minnesota Mississippi Missouri
1       2697   917     2355          4686     6407      2997        2176     3964
2       2697   917     2355          4686     6407      2997        2176     3964
3       2697   917     2355          4686     6407      2997        2176     3964
4       2697   917     2355          4686     6407      2997        2176     3964
5       2697   917     2355          4686     6407      2997        2176     3964
6       2697   917     2355          4686     6407      2997        2176     3964
7       2697   917     2355          4686     6407      2997        2176     3964
8       2697   917     2355          4686     6407      2997        2176     3964
9       2697   917     2355          4686     6407      2997        2176     3964
10      2697   917     2355          4686     6407      2997        2176     3964
11      2697   917     2355          4686     6407      2997        2176     3964
12      2697   917     2355          4686     6407      2997        2176     3964
13      2697   917     2355          4686     6407      2997        2176     3964
14      2697   917     2355          4686     6407      2997        2176     3964
15      2697   917     2355          4686     6407      2997        2176     3964
16      2697   917     2355          4686     6407      2997        2176     3964
   Montana Nebraska Nevada New.Hampshire New.Jersey New.Mexico New.York
1      593     1327    162           532       4872        689    14865
2      593     1327    162           532       4872        689    14865
3      593     1327    162           532       4872        689    14865
4      593     1327    162           532       4872        689    14865
5      593     1327    162           532       4872        689    14865
6      593     1327    162           532       4872        689    14865
7      593     1327    162           532       4872        689    14865
8      593     1327    162           532       4872        689    14865
9      593     1327    162           532       4872        689    14865
10     593     1327    162           532       4872        689    14865
11     593     1327    162           532       4872        689    14865
12     593     1327    162           532       4872        689    14865
13     593     1327    162           532       4872        689    14865
14     593     1327    162           532       4872        689    14865
15     593     1327    162           532       4872        689    14865
16     593     1327    162           532       4872        689    14865
   North.Carolina North.Dakota Ohio Oklahoma Oregon Pennsylvania Rhode.Island
1            4068          619 7980     2229   1532        10507          786
2            4068          619 7980     2229   1532        10507          786
3            4068          619 7980     2229   1532        10507          786
4            4068          619 7980     2229   1532        10507          786
5            4068          619 7980     2229   1532        10507          786
6            4068          619 7980     2229   1532        10507          786
7            4068          619 7980     2229   1532        10507          786
8            4068          619 7980     2229   1532        10507          786
9            4068          619 7980     2229   1532        10507          786
10           4068          619 7980     2229   1532        10507          786
11           4068          619 7980     2229   1532        10507          786
12           4068          619 7980     2229   1532        10507          786
13           4068          619 7980     2229   1532        10507          786
14           4068          619 7980     2229   1532        10507          786
15           4068          619 7980     2229   1532        10507          786
16           4068          619 7980     2229   1532        10507          786
   South.Carolina South.Dakota Tennessee Texas Utah Vermont Virginia Washington
1            2113          655      3315  7776  696     379     3315       2387
2            2113          655      3315  7776  696     379     3315       2387
3            2113          655      3315  7776  696     379     3315       2387
4            2113          655      3315  7776  696     379     3315       2387
5            2113          655      3315  7776  696     379     3315       2387
6            2113          655      3315  7776  696     379     3315       2387
7            2113          655      3315  7776  696     379     3315       2387
8            2113          655      3315  7776  696     379     3315       2387
9            2113          655      3315  7776  696     379     3315       2387
10           2113          655      3315  7776  696     379     3315       2387
11           2113          655      3315  7776  696     379     3315       2387
12           2113          655      3315  7776  696     379     3315       2387
13           2113          655      3315  7776  696     379     3315       2387
14           2113          655      3315  7776  696     379     3315       2387
15           2113          655      3315  7776  696     379     3315       2387
16           2113          655      3315  7776  696     379     3315       2387
   West.Virginia Wisconsin Wyoming
1           2006      3438     290
2           2006      3438     290
3           2006      3438     290
4           2006      3438     290
5           2006      3438     290
6           2006      3438     290
7           2006      3438     290
8           2006      3438     290
9           2006      3438     290
10          2006      3438     290
11          2006      3438     290
12          2006      3438     290
13          2006      3438     290
14          2006      3438     290
15          2006      3438     290
16          2006      3438     290

The dataset are now merged based on the yr. I want a new column 'population' where shows the population of the state in that year. So first row must be MO with a population of 3964

Please dont add screenshot. Please add some reproducible data that can be copied — AnilGoyal, Jan 14 '21 at 16:07
`Pivot_longer` second data for all columns except first, add column of full state name in first, then use `left_join` on first data. If you want a demonstration, please add some data here — AnilGoyal, Jan 14 '21 at 16:11
What is the best way to do that, because the dataset are quite large? Can I somewhere upload the datasets? I'm quite new here — , Jan 14 '21 at 16:17
Use `dput` command for your datasets and add the result in the question by editing it. I think there must be a third table linking state codes to state names — AnilGoyal, Jan 14 '21 at 16:28
See this link https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example — AnilGoyal, Jan 14 '21 at 16:32
Is the above dataset sufficient? It is quite big because of the many states that I merged — , Jan 14 '21 at 16:35
Please paste the two datasets without merging. Moreover please also give the state short name and full name table — AnilGoyal, Jan 14 '21 at 16:41
Welcome to Stack Overflow! Do not vandalize your posts. By posting on this site, you've irrevocably granted the Stack Exchange network the right to distribute that content under the [CC BY-SA 4.0 license](//creativecommons.org/licenses/by-sa/4.0/) for as long as it sees fit to do so. For alternatives to deletion, see: [I've thought better of my question; can I delete it?](https://stackoverflow.com/help/what-to-do-instead-of-deleting-question) — Sabito stands with Ukraine, Jan 15 '21 at 13:54

score 0 · Accepted Answer · answered Jan 14 '21 at 16:50

0

Something like this will work, once you replace state short names with full names in tornados table

states %>% pivot_longer(cols = -Year, names_to = "state", values_to = "population") %>% right_join(tornadoes, by = c("Year" = "yr", "State" = "state"))

answered Jan 14 '21 at 16:50

AnilGoyal

25,297
4
27
45

Thank you! I tried to short names with full names. Do you maybe know a code to do that because it has 50 states and thus 50 abbriviations? Thanks in forward! – Jan 14 '21 at 16:53
Use appropriate join type, if you have a table – AnilGoyal Jan 14 '21 at 16:56
Glad that it helped. Please accept it as correct answer as usual practice on stack overflow – AnilGoyal Jan 15 '21 at 01:27

Merging two Dataset with Dplyr

1 Answers1