0

(1.) I have a huge dataset of states with 2 columns namely: Year and State (along with other features). (2.) I want to insert two different values (GDP and Unemployment Rate) from two different data frames. (3.) Such that each row in the final set has GDP and unemployment rate (new column) according to state and year of that row.

I tried creating the list and creating the for loop but failed, also tried manually but almost went haywire. DATA

    sitecode      sitename year age sex grade race7 stheight stweight      bmi qnobese qnowt q8 q9 q12 q13 q15 q16 q17 q18 q25 q26 q27 q28 q29 q30 q32 q33 q41 q42 q46 q47 q48 q49 q50 q55 q57 q58 q59 q60
76035       AR Arkansas (AR) 1999   3   2     1     6     1.75    63.50 20.73469       2     2  1  3   1   1   1   1   4   1   2   2   2   1   1   1   6   4   4   1   6   5   3   1   1   1   1   2   2   1
76036       AR Arkansas (AR) 1999   3   2     1     3     1.63    55.34 20.82879       2     2  4  1   1   1   1   1   1   1   2   2   2   1   1   2   1   1   1   1   1   1   1   1   1   1   1   1   2   1
76037       AR Arkansas (AR) 1999   3   2     1     6     1.75    68.04 22.21714       2     2  4  1   1   1   1   1   2   1   2   2   2   1   1   1   3   4   1   1   1   1   1   1   1   1   1   2   1   5
76039       AR Arkansas (AR) 1999   3   2     1     4     1.75    67.13 21.92000       2     2  3  1   3   1   1   1   1   1   2   2   2   1   1   1   4   2   4   5   5   5   4   1   3   1   1   2   1   2
76041       AR Arkansas (AR) 1999   3   2     1     6     1.60    83.46 32.60156       1     2  3  1   1   1   1   3   8   1   1   2   2   1   1   1   1   1   2   2   2   5   1   1   2   1   1   2   2   1
76043       AR Arkansas (AR) 1999   3   2     1     3     1.83    73.94 22.07889       2     2  5  1   1   1   1   1   4   3   1   2   2   1   1   1   7   4   4   1   5   4   2   1   1   1   1   2   1   2

unemp data

 State X1990 X1991 X1992 X1993 X1994 X1995 X1996 X1997 X1998 X1999 X2000 X2001 X2002 X2003 X2004 X2005 X2006 X2007 X2008 X2009 X2010 X2011 X2012 X2013 X2014 X2015 X2016 X2017 X2018
1    Alabama   6.8   7.3   7.6   7.3   6.2   6.0   5.2   5.0   4.4   4.7   4.6   5.1   5.9   6.0   5.7   4.5   4.0   4.0   5.7  11.0  10.5   9.6   8.0   7.2   6.8   6.1   5.8   4.4   3.9
2     Alaska   7.2   8.5   8.9   7.7   7.6   7.3   7.6   7.1   6.3   6.5   6.4   6.4   7.3   7.8   7.5   6.9   6.6   6.3   6.7   7.7   7.9   7.6   7.1   7.0   6.9   6.5   6.9   7.0   6.6
3    Arizona   5.3   5.9   7.5   6.4   6.1   5.3   5.6   4.6   4.3   4.4   4.0   4.8   6.1   5.7   5.0   4.7   4.2   3.9   6.2   9.9  10.4   9.5   8.3   7.7   6.8   6.1   5.4   4.9   4.8
4   Arkansas   6.9   7.4   7.1   6.1   5.4   4.8   5.3   5.2   5.2   4.6   4.3   5.0   5.5   5.9   5.7   5.2   5.2   5.3   5.5   7.8   8.2   8.3   7.6   7.2   6.0   5.0   4.0   3.7   3.7

percapita income 

GeoName X1990 X1991 X1992 X1993 X1994 X1995 X1996 X1997 X1998 X1999 X2000 X2001 X2002 X2003 X2004 X2005 X2006 X2007 X2008 X2009 X2010 X2011 X2012 X2013 X2014 X2015 X2016 X2017 X2018
1    Alabama 20683 21008 21621 21633 22253 22813 23407 32887 33736 34783 35165 35008 35908 36636 38737 39692 39922 39776 39273 37416 38087 38562 38687 38954 38479 38885 38977 39600 40279
2     Alaska 52002 45285 44418 43454 43407 45678 44812 68947 66390 65178 62929 64657 66758 65006 66337 67549 71845 75135 74018 79894 75852 75683 78957 74283 72265 72652 70947 70956 70936
3    Arizona 22151 21630 23056 23347 24523 25272 26351 35582 37503 39431 40265 40326 40641 42346 43034 45052 45991 46194 44182 40214 40183 40660 40919 40716 40602 40891 41564 42164 43096

Expected Result (something like this)

sitecode      sitename q13 q15 q16 q17 q18 q25 q26 q27 q28 q29 q30 q32 q33 q41 q42 q46 q47 q48 q49 q50 q55 q57 q58 q59 q60 q61 q62 q63 q64 q65 q68 q69 q70 q71 q80 q82 qbikehelmet qtaughtHIV unemp
       AR Arkansas (AR)   1   1   1   4   1   2   2   2   1   1   1   6   4   4   1   6   5   3   1   1   1   1   2   2   1   1   1   1   1   1   3   2   1   3   7   6           2          1   4.6
       AR Arkansas (AR)   1   1   1   1   1   2   2   2   1   1   2   1   1   1   1   1   1   1   1   1   1   1   1   2   1   1   1   1   1   1   3   4   2   2   7   6           2          1   4.6
       AR Arkansas (AR)   1   1   1   2   1   2   2   2   1   1   1   3   4   1   1   1   1   1   1   1   1   1   2   1   5   2   3   3   2   4   2   3   1   1   4   6           2          1   4.6
       AR Arkansas (AR)   1   1   1   1   1   2   2   2   1   1   1   4   2   4   5   5   5   4   1   3   1   1   2   1   2   7   5   3   2   4   3   3   5   5   6   6           2          1   4.6
       AR Arkansas (AR)   1   1   3   8   1   1   2   2   1   1   1   1   1   2   2   2   5   1   1   2   1   1   2   2   1   1   1   1   1   1   4   4   7   3   7   6           2          1   4.6
       AR Arkansas (AR)   1   1   1   4   3   1   2   2   1   1   1   7   4   4   1   5   4   2   1   1   1   1   2   1   2   7   4   3   2   7   2   2   1   1   7   1           2          2   4.6
      percapita
     33681
     33681
     33681
     33681
     33681
     33681

I need the respective unemployment rate and per capita for every state in df

Shivam Dave
  • 1
  • 2
  • Have you checked out `?merge` or (since you have `dplyr` tagged) `?dplyr::join` – duckmayr Jun 05 '19 at 14:00
  • 1
    Before merging, you need to convert your unemployment and income data frames to long form, so they also have state and year columns. The FAQ on that operation is here: [Convert data from wide to long](https://stackoverflow.com/q/2185252/903061). Once the columns match, you can just do `merge(data, unemployement_data)` – Gregor Thomas Jun 05 '19 at 14:07
  • 1
    If you continue to have trouble with those methods, please edit the question to show the code you are trying and the errors you get, and we can re-open the question and get you over the stumbling blocks. – Gregor Thomas Jun 05 '19 at 14:09
  • I have created columns > head(unemp1$mix) [1] "Alabama 1990" [2] "Alaska 1990" [3] "Arizona 1990" [4] "Arkansas 1990" [5] "California 1990" [6] "Colorado 1990" > head(data1$mix) [1] "Arkansas 1999" [2] "Arkansas 1999" [3] "Arkansas 1999" [4] "Arkansas 1999" [5] "Arkansas 1999" [6] "Arkansas 1999" [but it is unable to match and merge accordingly – Shivam Dave Jun 07 '19 at 09:06

0 Answers0