0

I have a csv that is a few data frames appended to each other. However each data frame has different amount of columns. I would like to be able to either a) import the whole csv as a data frame in R or b) filter the CSV for specific rows before pulling it into R.

Type    Field1 Values
Fruits  Name   Size
Fruits  Orange Large
Fruits  Grape  Small
Shoes   Brand  Colour Price  Gender
Shoes   Nike   Blue   $100   Male
Shoes   Adidas Black  $80    Female

In the data frame above there are 2 data frames appended. One that is related to fruits and one that is related to shoes. I would like to pull all the data as a csv in R. Also assume I dont know the amount of columns there are in the csv

In R I would like the data frame to look like this

V1      V2     V3     V4     V5
Type    Field1 Values
Fruits  Name   Size
Fruits  Orange Large
Fruits  Grape  Small
Shoes   Brand  Colour Price  Gender
Shoes   Nike   Blue   $100   Male
Shoes   Adidas Black  $80    Female

Below is picture of real data. It is a csv file enter image description here

I downloaded the file as "csv" and when i look at the properties of the data it is a CSV. enter image description here

Edit* This code chuck actually does the job however, the number of columns needs to be known

read.table("pathtocsv.csv", header = FALSE, sep = ",", 
  col.names = paste0("V",seq_len(5)), fill = TRUE)
Jordan Wrong
  • 1,205
  • 1
  • 12
  • 32
  • That looks like a fixed-width table, not CSV. Is that the raw file contents, or what you hope it may look like eventually in R? – r2evans Feb 03 '22 at 01:26
  • Editing right now with what i want it to look like but that dataframe is what it currently looks like. It is a csv file. – Jordan Wrong Feb 03 '22 at 01:29
  • 2
    CSV stands for Comma Separated Values. The file you show has no commas, so it is not a CSV even if it is labeled as such. So we should check again to make sure we're answering the right question, is that exactly what data file looks like? – Gregor Thomas Feb 03 '22 at 01:42
  • @GregorThomas the file is a csv i added the properties to the data frame above. When i downloaded it from interactive brokers it was also downloaded as a csv – Jordan Wrong Feb 03 '22 at 01:46
  • A filename ending in `.csv` does not *make* it a CSV file. Just because Excel opens it does not *make* it a CSV file, Excel can open other formats too. It doesn't matter that the OS/interface is telling you `Type: Microsoft Excel Comma Separated Values ...`, it says that because the filename ends in `.csv`, not because windows has an intrinsic knowledge of the contents of the file. – r2evans Feb 03 '22 at 01:53
  • I found a solution. I am going to post below let me know your thoughts. – Jordan Wrong Feb 03 '22 at 01:54
  • Does it get you all of the columns you need? If so, I think my (our) thoughts are relatively moot. The code looks fine (I just learned about the `count.fields` function, btw ... nice). – r2evans Feb 03 '22 at 02:01
  • it works great @r2evans! thank you very much for yours and Gregors help – Jordan Wrong Feb 03 '22 at 02:07
  • 1
    (Not sure I *helped*, but glad you found what you needed.) – r2evans Feb 03 '22 at 02:08

1 Answers1

0

I found a solution from this post on SO How can you read a CSV file in R with different number of columns

path = "MyPath.csv"

columnNum = max(count.fields(path, sep = ','))

df =  read.table(path, header = FALSE, sep = ",", 
           col.names = paste0("V",seq_len(columnNum)), fill = TRUE)

This worked fine

Jordan Wrong
  • 1,205
  • 1
  • 12
  • 32