0

Not sure how to go about cleaning this dataset.. Our database system exports reports by having the field names in one column and the field values in one column, for example:

Field Label 01 Field Value 01
Age 11
Proceeds 4000
Age 4
Proceeds 1000
Last Reported 05-20-2021

My goal is to split the fields in column 1 to become their own columns but I also need to pull in the values from column 2 to.

My expected outcome:

Age Proceeds Last Reported
11 N/A N/A
4 N/A N/A
N/A 4000 N/A
N/A 1000 N/A
N/A N/A 05-20-2021

I'm not entirely sure how to go about this. I'm new to data wrangling/cleaning and am vaguely familiar with using the tidyverse package.

My plan is to turn the rownames in Field Label 01 into column names then for every instance of each category in Field Label 01 to take the value from Field Value 01 and place it to the right column.

If Field Label 01 = "Age", pull the value in Field Value 01 in the same row into the column Age.

Any help is greatly appreciated!

jollibee474
  • 9
  • 1

1 Answers1

1
df <- read.table(text = "Field_Label_01 Field_Value_01
Age 11
Proceeds    4000
Age 4
Proceeds    1000
Last_Reported   05-20-2021", header = T)

df %>%
  mutate(row = row_number()) %>%
  pivot_wider(names_from = Field_Label_01, values_from = Field_Value_01) %>%
  select(-row)

  Age   Proceeds Last_Reported
  <chr> <chr>    <chr>        
1 11    NA       NA           
2 NA    4000     NA           
3 4     NA       NA           
4 NA    1000     NA           
5 NA    NA       05-20-2021
denisafonin
  • 1,116
  • 1
  • 7
  • 16
  • Thank you! Is there a way to build a function out of this or a for loop? – jollibee474 Oct 25 '21 at 12:18
  • What should the function do and what inputs should it take? – denisafonin Oct 25 '21 at 13:37
  • so i would like to use the same idea but over 100 columns. similarly if they have the name "Field_Label" for each column name instance that has this to pull the column next to it that has the values which is in "Field_Value_i" if that makes sense. – jollibee474 Oct 25 '21 at 19:49