Questions[base-de-datos]

People, I hope you understand that this is a special situation. It is a question that does not follow the rules, but right now the world is a mess and I really need if someone can give me a hand with this. Fill me with negatives if you want but please don't close the question.

At my work we are manually uploading some files referring to the complaints reported by people regarding COVID-19. The issue is that this virus is growing exponentially, which is why our work is also growing, and we are behind in statistics, which is partly why the number of known cases is lower than it actually is. Without further ado, I'll show you what I have:

We receive a .doc file (ask in the chat before asking the question here, and they asked me the version of Word. Actually we work with different versions, I have the latest version at home because I am a student and MS gives it away and always updates it , but at work I have 2010, I think the version may not matter since I want to work with text, I could even copy it and take it to a notepad for example)

This .doc file has about 100 texts that have more or less this form

Illicit Investigation – Illicit Port (COVID-19 Coronavirus) – Senior Female 03/19 – PU. 81027 – High 07:06 am – CP Campana; 05:00 a.m. She received a call to XXXX (Dda. XXXX 1000), realizing that her neighbor XXX hers, returned from Brazil the day before, not complying with the isolation protocol. Personal proceeded to provide the corresponding telephone numbers for such cases, giving notice to Health personnel.

Each of these texts must be passed to a table in a database, which has about 20 fields. I would like to at least be able to get the 3 data that I put in bold, which are the date, the Urgent Part number, which is always after PU.and the time, which is always the one after the word alta(since there may be other times in the paragraph, anyway the time I'm looking for is the first one that appears, so you can have those two criteria, or the first appearance, or after "high")

If you don't know how to do it but you know tools that can do it, it would also be of great help to me

The thing is that we are passing them by hand, and this virus is growing by leaps and bounds with it the complaints, and we are not being able to upload this.

The idea is that each new text (could be identified by the appearance of the string PU.) creates a new row (it can be in an excel or access table, it doesn't matter) and place those 3 fields.

I hope you can understand an exceptional question at an exceptional time.

I am working with the national health survey in Spain (ENSE). In it, the respondents are asked about their nationality in the form of 4 binary response questions (1 in the affirmative case, 2 in the negative case) :

• Spanish nationality?: E2_1a
• foreign nationality?: E2_1b
• Don't know: E2_1c
• Does not answer: E2_1d

Well, what I need is to create a single variable from these 4 , assigning a value to each affirmative answer of the first two questions and another for when it was answered that he does not know or does not answer, add one more value for those that have the double nationality (a 1 in the first two questions).

In addition, if in any case the respondent gave an affirmative answer to one or both of the first 2 questions and also to the last 2 (This would mean that the respondent claims to have a nationality but at the same time does not know it or does not confess it, which is illogical) or if your answer was negative in all cases, delete these observations.

E2_1a   E2_1b   E2_1c   E2_1d   E2
1       2       2       2       A
1       1       2       2       C
1       2       2       2       A
1       2       2       2       A
1       2       2       2       A
1       2       2       2       A
1       2       2       2       A
1       2       2       2       A
2       1       2       2       B
1       2       2       2       A
1       1       2       2       D

In the previous table of the dataframe, the variable E2 is the one I need and the others are the ones from the dataframe (the ones I already have). All values ​​are NOT numeric and I prefer variable E2 to be numeric as well, so I use the following values:

• A: born Spanish
• B: born foreign
• C: double born
• D: Does not know/does not answer

Lastly, the code I've tried so far in reference to this question simply assigns a value (from 1 to 4) to each yes in the dataframe variables. My sample is 57068 observations, that's why I first create the variable E2 and give all of them any value (5), the rest of the variables are in the dataframe called DATA1.

E2<-rep(5,times=57068)

for(i in 1:nrow(DATA1)){ 

  if   (DATA1$E2_1a=="1"){E2[i,]=1}
  else (DATA1$E2_1b=="1"){E2[i,]=2}
  else (DATA1$E2_1c=="1"){E2[i,]=3}
  else (DATA1$E2_1d=="1"){E2[i,]=4}
}

Fixing this snippet of my code would be enough for me to continue to answer the main question on my own.

I try to apply a simple normalization function to the numeric variables in the R database iristhrough an forand using lapplyin order to obtain a new database containing only the normalized variables:

data(iris)

normal <- function (x) {
num <- x - min(x)
den <- max(x) - min(x)
return (num/den)
}

iris_n <- data.frame()

for (i in 1:length(iris)){
if (is.numeric(iris[,i])) {
}
iris_n[,i] <- as.data.frame(lapply(iris[,i], normal))
}

Error in Summary.factor(1L, na.rm = FALSE) : 
  ‘min’ not meaningful for factors
Además: There were 50 or more warnings (use warnings() to see the first 50)

iris_n

[1] NaN.   NaN..1 NaN..2 NaN..3
<0 rows> (or 0-length row.names)

Now, I try to lapply directly with:

iris_n <- as.data.frame(lapply(iris, function(x) {if (is.numeric(x))  normal(x)}))

Error in (function (..., row.names = NULL, check.rows = FALSE, check.names = TRUE,  : 
  arguments imply differing number of rows: 150, 0

No matter how hard I turn it, I can't find the error. Any guidance will be greatly appreciated (Note: I am interested in solving this specific problem, I know there are other types of solutions to achieve what I need)

I have an example where I don't quite understand. What is the optimal way to solve it?

A table called usuarios.

A table called publicaciones.

If I plan to save the records of a post with that idof the user who registered it; is the following structure correct?

But for example considering that an intermediate table called publicaciones-usuarioswith a structure similar to the following could be created:

What is the optimal way?, since for example if I make the first structure with a inner joinlike the following:

SELECT titulo, cuerpo, username FROM publicaciones
INNER JOIN usuarios
ON usuarios.id = publicaciones.usuarios_id
WHERE usuarios.id = 2;

I can extract the publications that a user makes taking into account that the link between both tables is that idof the user as a foreign key in the table publicaciones; but I don't quite understand under what scenarios each one is used

It is just an example considering that I seek to clarify this doubt

Evaluation for example is like a school subject, mathematics physics chemistry.

Attempts for example are the exams that are given for each subject

I want to know the attempts of a user regarding an evaluation

The user_id must be in the entity Attempt?

Attached the relational model