How to write the dataframes in a list to a single csv file

Question

I have a list with 15 data frames and they look like this

> head(final_data[[1]])
      DateTime # Unemployed Persons.csv
147 2013-03-01                  2320.58
148 2013-04-01                  2336.89
149 2013-05-01                  2213.78
150 2013-06-01                  2135.90
151 2013-07-01                  2302.79
152 2013-08-01                  2177.01

> head(final_data[[2]])
     DateTime Business Confidence.csv
46 2013-03-01                    -6.2
47 2013-04-01                    -1.3
48 2013-05-01                    -2.4
49 2013-06-01                    -5.1
50 2013-07-01                    -2.0
51 2013-08-01                    -1.8

and so on. The first column DateTime is common across all the 15 dataframes. I would like to create a final output file which joins all these dataframes together and contains 16 columns something like this

 DateTime #           Unemployed Persons.csv       Business Confidence.csv
147 2013-03-01                  2320.58               -6.2
148 2013-04-01                  2336.89               -1.3
149 2013-05-01                  2213.78               -2.4
150 2013-06-01                  2135.90               -5.1
151 2013-07-01                  2302.79               -2.0
152 2013-08-01                  2177.01               -1.8

I can do this manually by using the merge function but I would need your help to loop it over the entire list.

Thank You.

EDIT: If I use reduce, I get the following result ;

a <- Reduce(function(...‌) merge(..., by = "DateTime"), final_data)

view(a)

> a
      DateTime Value
1   1994-01-31   455
2   1994-02-28   470
3   1994-03-31   455
4   1994-04-30   455
5   1994-05-31   356
6   1994-06-30   425
7   1994-07-31   445
8   1994-08-31   470
9   1994-09-30   470
10  1994-10-31   470
11  1994-11-30   445
12  1994-12-31   485
13  1995-01-31   497
14  1995-02-28   536
15  1995-03-31   546
16  1995-04-30   546
17  1995-05-31   556
18  1995-06-30   601
19  1995-07-31   611
20  1995-08-31   616
21  1995-09-30   641
22  1995-10-31   631
23  1995-11-30   601
24  1995-12-31   636
25  1996-01-31   620
26  1996-02-29   620
27  1996-03-31   635
28  1996-04-30   605
29  1996-05-31   605
30  1996-06-30   605
31  1996-07-31   615
32  1996-08-31   630
33  1996-09-30   630
34  1996-10-31   630
35  1996-11-30   640
36  1996-12-31   640
37  1997-01-31   640
38  1997-02-28   631
39  1997-03-31   611
40  1997-04-30   640
41  1997-05-31   640
42  1997-06-30   631
43  1997-07-31   596
44  1997-08-31   626
45  1997-09-30   650
46  1997-10-31   705
47  1997-11-30   734
48  1997-12-31   719
49  1998-01-31   690
50  1998-02-28   685
51  1998-03-31   699
52  1998-04-30   699
53  1998-05-31   709
54  1998-06-30   709
55  1998-07-31   709
56  1998-08-31   725
57  1998-09-30   748
58  1998-10-31   781
59  1998-11-30   815
60  1998-12-31   817
61  1999-01-31   842
62  1999-02-28   829
63  1999-03-31   829
64  1999-04-30   839
65  1999-05-31   814

Try `Reduce(function(...) merge(..., by = "DateTime"), final_data)` — akrun, Aug 16 '16 at 11:50
If I do that, rbind happens on all the dataframes within the list — Vinay billa, Aug 16 '16 at 11:56
If the 'DateTime' are similar across the `list`, then you should get the output as you showed — akrun, Aug 16 '16 at 11:58
The DateTime is similar, but I want a `Cbind` instead of an `Rbind` with only one DateTime column — Vinay billa, Aug 16 '16 at 12:00
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/121060/discussion-between-vinay-billa-and-akrun). — Vinay billa, Aug 16 '16 at 12:03
Could we please have a [reproducible example](http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) with data and/or code? — Ben Bolker, Aug 16 '16 at 12:04

score 3 · Accepted Answer · answered Aug 16 '16 at 12:03

We can use Reduce with merge

Reduce(function(...) merge(..., by = "DateTime"), final_data)
#    DateTime Unemployed_Persons.csv Business_Confidence.csv
#1 2013-03-01                2320.58                    -6.2
#2 2013-04-01                2336.89                    -1.3
#3 2013-05-01                2213.78                    -2.4
#4 2013-06-01                2135.90                    -5.1
#5 2013-07-01                2302.79                    -2.0
#6 2013-08-01                2177.01                    -1.8

data

 final_data <- list(structure(list(DateTime = c("2013-03-01", "2013-04-01", 
"2013-05-01", "2013-06-01", "2013-07-01", "2013-08-01"), Unemployed_Persons.csv = c(2320.58, 
2336.89, 2213.78, 2135.9, 2302.79, 2177.01)), .Names = c("DateTime", 
"Unemployed_Persons.csv"), class = "data.frame", row.names = c("147", 
"148", "149", "150", "151", "152")), structure(list(DateTime = c("2013-03-01", 
"2013-04-01", "2013-05-01", "2013-06-01", "2013-07-01", "2013-08-01"
), Business_Confidence.csv = c(-6.2, -1.3, -2.4, -5.1, -2, -1.8
)), .Names = c("DateTime", "Business_Confidence.csv"), class = "data.frame", row.names = c("46", 
"47", "48", "49", "50", "51")))

I wasn't aware of the REDUCE-function till now. I like it. Thanx! — floe, Aug 16 '16 at 12:10

score 0 · Answer 2 · answered Aug 16 '16 at 12:02

you can use the join-functions from the dplyr-package an put them in a loop like this:

library(dplyr)
#example list
list <- list(
  data.frame("Date" = seq(as.Date("2016-01-01"), as.Date("2016-02-01"), "days"), 
             "a" = 1:32), 
  data.frame("Date" = seq(as.Date("2016-01-01"), as.Date("2016-02-01"), "days"), 
             "b" = 33:64), 
  data.frame("Date" = seq(as.Date("2016-01-01"), as.Date("2016-02-01"), "days"), 
             "c" = 65:96))

for (i in 1:length(list))
  if(i == 1) df <- list[[1]] else
    df <- left_join(df, list[[i]])

How to write the dataframes in a list to a single csv file

2 Answers2

data

Linked

Related