Fix and format dataframe of stocks

user_11259187 0
Viewed 52

I have a dataframe with lists of stocks and stock data. In the picture I show the names of example stocks and data. Within each stock in the list are the dates, openings, highs, lows and close. All I want is the close merged with the dates in a single simple merged dataframe. I don't know how to do this, and I've drawn up with a picture to best describe what I want. Feel free to use mtcars data frame for the example.

The names I have are awkward. I want them renamed. for example the names in the list are:["c/user/Tim/R/Tesla.csv"]. I would like them to be renamed what they simply are, for example Tesla, Google (as shown in the picture below) in the dataframe table. If you know how to code to get rid of ["c/user/Tim/R/"] and [".csv"] in all the names in the dataframe table, that would be greatly obliged.

Here is the picture to the question

dataframe r formatting list
1 Answers

For the first part, you can extract the filename and remove the path and extension more easily with the tools package.

For example, if we call your list of data frames in your example above lst, you can do:

library(tools)

names(lst) <- basename(file_path_sans_ext(names(lst)))
names(lst)

[1] "AVK_prices" "RR._prices" "VOD_prices"

Next, there are multiple approaches to combining your data frames from a list. Here's one.

Using dplyr from tidyverse, you can use bind_rows which will append the rows from each data frame and add an id column called source which will contain the data frame name from the lst. In your example, that would include "AVK_prices", "RR._prices", and "VOD_prices".

After that, you can select the columns you need (just the source, Date, and Close). Then pivot_wider will put the data into wide format, which resembles your desired final output.

library(dplyr)
library(tidyr)

bind_rows(lst, .id = 'source') %>%
  select(source, Date, Close) %>%
  pivot_wider(id_cols = Date, names_from = source, values_from = Close)

Output

   Date       AVK_prices RR._prices VOD_prices
   <chr>           <dbl>      <dbl>      <dbl>
 1 13/11/2020      1477        93.7       120.
 2 12/11/2020      1468.       90         118.
 3 11/11/2020      1488        98.4       117.
 4 10/11/2020      1477       107         117.
 5 09/11/2020      1479       100         112.
 6 06/11/2020      1458        69.6       105.
 7 05/11/2020      1450        74.5       106.
 8 04/11/2020      1430        83.9       106.
 9 03/11/2020      1409        84         106.
10 02/11/2020      1377        76.6       106 
11 30/10/2020      1360        71.3       103 
12 29/10/2020      1380        72.4       104.
13 28/10/2020      1373        84.5       103.
14 27/10/2020      1412        75.2       106.
15 26/10/2020      1408        77.6       110.
user_34606700
Related