Merging two Dataset with Dplyr

user14977615

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

AnilGoyal

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"))

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-06-1

Comments

0 comments

Merging two different size datasets, copying the same row conditionally from smaller dataset to several rows in larger dataset

TOP Ranking

Article

Merging two Dataset with Dplyr

Merging two Dataset with Dplyr

Failed to listen on localhost:8000 (reason: Cannot assign requested address)

pump.io port in URL

How to import an asset in swift using Bundle.main.path() in a react-native native module

Loopback Error: connect ECONNREFUSED 127.0.0.1:3306 (MAMP)

Compiler error CS0246 (type or namespace not found) on using Ninject in ASP.NET vNext

BigQuery - concatenate ignoring NULL

Spring Boot JPA PostgreSQL Web App - Internal Authentication Error

ggplotly no applicable method for 'plotly_build' applied to an object of class "NULL" if statements

ngClass error (Can't bind ngClass since it isn't a known property of div) in Angular 11.0.3

How to remove the extra space from right in a webview?

Change dd-mm-yyyy date format of dataframe date column to yyyy-mm-dd

Jquery different data trapped from direct mousedown event and simulation via $(this).trigger('mousedown');

maven-jaxb2-plugin cannot generate classes due to two declarations cause a collision in ObjectFactory class

java.lang.NullPointerException: Cannot read the array length because "<local3>" is null

How to use merge windows unallocated space into Ubuntu using GParted?

flutter: dropdown item programmatically unselect problem

Pandas - check if dataframe has negative value in any column

Nuget add packages gives access denied errors

Can't pre-populate phone number and message body in SMS link on iPhones when SMS app is not running in the background

Generate random UUIDv4 with Elm

Client secret not provided in request error with Keycloak