I would like to create a panel data. My problem is the following.

I want to keep track of 4 variables for more than 1000 cities, over the 1800-1921 time period. As of now, I have a dataframe in R with the following columns: city, id, year, country, latitude, longitude, persecutions, expulsion, pogrom, plague.

Ideally, I would like to expand this dataframe so that I have for each city the following: 121 rows (from 1800 to 1921), with identical city, id, country, latitude, longitude values but persecutions, expulsion, pogrom, plague, all assigned a 0 value.

I have been told that the reshape 2 function could help, although I havn't found yet any working solution. Here is a code that I have tried to (unsucesfully adapt)

g <- with(data, expand.grid(year = seq(1800, 1921), id = unique(id), persecutions = 0, expulsion = 0, pogrom = 0, plague = 0)) #
data <- rbind(data, g) #

wide <- dcast(data, year ~ id, fill = 0, fun = sum, value.var = c("persecutions", "expulsion", "pogrom", "plague))
long <- melt(wide, id = "year", variable.name = "id", value.name = c("persecutions", "expulsion", "pogrom", "plague))

long <- long[order(long$id, long$year), c("id", "year", c("persecutions", "expulsion", "pogrom", "plague))]

All thoughts will be very helpful. enter image description here

Your Answer


Browse other questions tagged or ask your own question.