Use certain rows as values in grouped new column (data imported from excel)

user_11819844 0
Viewed 40

I have this data about PM10 concentrations in Germany. It can be downloaded at this [link] (https://www.umweltbundesamt.de/sites/default/files/medien/4640/dokumente/pm10_2019_0.xlsx)

In excel it looks more or less like this:

enter image description here

This problem here is, that for each of the 16 states there is "joined" one row and until the next row of that kind there are the individual stations that measure the PM10 concentration. What I would like to do now is to create a new column for each state each assign the name of the state to each measurement.

I honestly have no clue how to do that in R. I imagine that there might be some workarround with somethin in pseudocode like

state = None
for each row: 
  if (NA in Statiocode):
    state = Name
  else:
    new_col = state

Does this look like some kind of right approach?

tidyverse r excel
2 Answers

I hope the following helps

data <- read_excel("pm10_2019_0.xlsx", skip = 46)
names <- data %>% filter(is.na(Stationscode)) %>% select(`Name / Messnetz`)
index <- which(is.na(data$Stationscode))

for(i in 1:nrow(data)){
  
  data[i,"City"] <- cut(i, breaks = c(index, nrow(data)), labels = names$`Name / Messnetz`)
  
}
data <- data %>% filter(!is.na(Stationscode))
user_112704760

Simple this code will do

library(readxl)
data <- read_excel("pm10_2019_0.xlsx", skip = 46)

library(tidyverse)

data <- data %>% mutate(State = ifelse(is.na(Stationscode), `Name / Messnetz`, NA)) %>%
  fill(State) %>% filter(!is.na(Stationscode)) %>% select(State, everything())

data
# A tibble: 376 x 7
   State     Stationscode `Name / Messnetz`    Stationsumgebung   `Art der Station` `Jahresmittelwert \~ `Zahl der Tageswerte ~
   <chr>     <chr>        <chr>                <chr>              <chr>                            <dbl>                  <dbl>
 1 Baden-Wü~ DEBW029      Aalen                vorstädtisches Ge~ Hintergrund                         14                      0
 2 Baden-Wü~ DEBW076      Baden-Baden          vorstädtisches Ge~ Hintergrund                         12                      0
 3 Baden-Wü~ DEBW042      Bernhausen           vorstädtisches Ge~ Hintergrund                         16                      2
 4 Baden-Wü~ DEBW046      Biberach             vorstädtisches Ge~ Hintergrund                         14                      0
 5 Baden-Wü~ DEBW004      Eggenstein           ländlich stadtnah  Hintergrund                         15                      0
 6 Baden-Wü~ DEBW220      Esslingen Grabbrunn~ städtisches Gebiet Verkehr                             23                     16
 7 Baden-Wü~ DEBW084      Freiburg             städtisches Gebiet Hintergrund                         13                      2
 8 Baden-Wü~ DEBW122      Freiburg Schwarzwal~ städtisches Gebiet Verkehr                             15                      3
 9 Baden-Wü~ DEBW038      Friedrichshafen      städtisches Gebiet Hintergrund                         14                      1
10 Baden-Wü~ DEBW112      Gärtringen           vorstädtisches Ge~ Hintergrund                         13                      1
# ... with 366 more rows
user_28848590
Related