All geek questions in one place

Filling missing values ​​on the table

I have a list of dates and prices:

Date           Price
1/3/2000        10.00
1/5/2000        10.45
1/7/2000        10.25
...               ...

I have a separate list of dates with all dates:

Date
1/1/2000
1/2/2000
1/3/2000
...

I need them to be combined so that the price for the previous period is filled for dates in which there are no prices:

Date            Price
1/1/2000         10.00
1/2/2000         10.00
1/3/2000         10.00
1/4/2000         10.00
1/5/2000         10.45
1/6/2000         10.45
1/7/2000         10.25
...               ...

I am currently trying to iterate over lists of arrays containing data, but I am unable to correctly arrange the dates, especially at the beginning and at the end. I am using Java / Mysql / JDBC right now, but I am also open to R. Thanks for any suggestions.

+3
source share
5 answers

. : -I , .
, . 3 , , , , , .
, .

. .

public static void checkLengths(ArrayList<String> masterTimes, ArrayList<String> testTimes, ArrayList<Double> prices){
    ArrayList<Double> temp = new ArrayList<Double>();
    ArrayList<Integer> matches = new ArrayList<Integer>();
    Double[] temp2 = new Double [masterTimes.size()];
    int mt = masterTimes.size();
    int tt = testTimes.size();
        if(mt == tt){
            return;
        }else{
            int mast = 0;
            int test = 0;
            String mt1 = masterTimes.get(0);
            String tt1 = testTimes.get(0);

            test = 0;
            for(int i = 0; i < masterTimes.size(); i++){
                mt1 = masterTimes.get(i);
                tt1 = testTimes.get(test);
                System.out.println(" | mt1: " + mt1 + " | tt1: " + tt1);
                    if(mt1.equals(tt1)){
                        matches.add(i);
                        System.out.println("Inserting: " + i);
                        if(test < testTimes.size()){
                        test++;
                        }
                        if(test == testTimes.size()){
                            break;
                        }
                    }
            }
            System.out.println("Matches:");
            printAL(matches);

            // puts in known prices.
            for(int i = 0; i < matches.size(); i++){
                int g = matches.get(i);
                temp2[g] =  prices.get(i);
            }

            System.out.println("FirstPrices:");
            printAR(temp2);

            // Finds index of first and last matching times.
            int matcher1 = matches.get(0);
            int ind = matches.size() - 1;
            int matcher2 = matches.get(ind);
            System.out.println("Matcher1:" + matcher1 + " | Matcher2: " + matcher2);

            // If a price is empty/null, it puts the prior price in it.
            for(int i = matcher1; i < matcher2; i ++){
                System.out.println(i + " | " + temp2[i]);
                if(temp2[i] == null){
                    System.out.println(temp2[i] + " | " + temp2[i-1]);
                    temp2[i] = temp2[i-1];
                }
            }
            System.out.println("SecondPrices:");
            printAR(temp2);

            // Deals with start.
            for(int i = matcher1; i >= 0; i--){
                if(temp2[i] == null){
                    temp2[i] = temp2[i+1];
                }
            }

            System.out.println("ThirdPrices:");
            printAR(temp2);

            // Deals with end.
            for(int i = matcher2; i < temp2.length; i++){
                if(temp2[i] == null){
                    temp2[i] = temp2[i-1];
                }
            }
            System.out.println("FourthPrices:");
            printAR(temp2);             

            prices.clear();
            System.out.println("Final Check:");

            for (int i = 0; i < masterTimes.size(); i++){
                System.out.println(i + " | " + masterTimes.get(i) + " | " + temp2[i]);
            }

        }
}
+1

, , , , - .

HashTable HashMap, .

HashTable, , .

0

... , :)

MySQL , , date_prices all_dates. LEFT JOIN .

R MySQL, RMySQL R. R POSIX as.POSIXlt. lag R ( , ).

, Rsqldf`, "" R, SQL. - . - .

:

impute , ... .

0

, . - , .

:

import java.sql.*;
import java.util.*;

public class FillDates
{
    public static void fillUnknownDates(Connection c) throws SQLException
    {
        // Loads in a Vector of Strings of all the dates
        Statement state = c.createStatement();
        ResultSet results = state.executeQuery("SELECT d FROM Dates ORDER BY d;");
        Vector<String> dates = new Vector<String>();
        while (results.next())
        {
            dates.add(results.getString("d"));
        }

        // Load in a list of all date/price combinations
        Vector<DatePrice> pairs = new Vector<DatePrice>();
        state = c.createStatement();
        results = state.executeQuery("SELECT d, p FROM DatePrices ORDER BY d;");
        while (results.next())
        {
            pairs.add(new DatePrice(results.getString("d"), results.getString("p")));
        }

        // Now go through the two lists and add missing prices
        state = c.createStatement();
        int dateIndex = 0;
        DatePrice last = pairs.get(0), current;
        for (int pairIndex = 1; pairIndex < pairs.size(); pairIndex++)
        {
            current = pairs.get(pairIndex);
            while (dateIndex < dates.size() && dates.get(dateIndex).compareTo(current.getDate()) < 0)
            {
                // Batch things up so it takes less time to run
                state.addBatch("INSERT INTO DatePrices VALUES (\""+dates.get(dateIndex)+"\", \""+current.getPrice+"\");");
                dateIndex ++;
            }

            last = current;
        }
        state.executeBatch();
    }

    // A convenience class
    public static class DatePrice
    {
        private String date, price;

        public DatePrice(String date, String price)
        {
            this.date = date;
            this.price = price;
        }
        public String getDate()
        {
            return date;
        }
        public String getPrice()
        {
            return price;
        }
    }
}

, , , .

0

R-.

install.packages, . , textConnection(Lines1) textConnection(Lines2) - "myfile1.dat" "myfile2.dat", , .

It reads the data that creates the zoo object zand the date vector dt. It then combines zwith the zoo object with a zero width (i.e., has dates but not data) whose date index is made from dt. na.locf(the last observation moves forward) fills in the missing values ​​in the reverse order, sincefromLast = TRUE

Lines1 <- "Date           Price
1/3/2000        10.00
1/5/2000        10.45
1/7/2000        10.25"

Lines2 <- "Date
1/1/2000
1/2/2000
1/3/2000"

# install.packages("zoo")
# install.packages("chron")
library(zoo)
library(chron)
z <- read.zoo(textConnection(Lines1), header = TRUE, FUN = as.chron)
dt <- as.chron(scan(textConnection(Lines2), skip = 1, what = ""))
na.locf(merge(z, zoo(, dt)), fromLast = TRUE)

Result:

> na.locf(merge(z, zoo(, dt)), fromLast = TRUE)
01/01/00 01/02/00 01/03/00 01/05/00 01/07/00 
   10.00    10.00    10.00    10.45    10.25

There are three vignettes (PDF documents) that come with the zoo package and R News 4/1. The help desk has information and links to dates.

0
source

Source: https://habr.com/ru/post/1789420/

More articles:


All Articles