Tammy Ma
2013-Mar-28 08:24 UTC
[R] How to search in each excel in my working dir and extract part information of one product in each country?
Hello, I have created many excel files in my working dir. each excel file contain one country's data. In the excel, I have dataframe in sheet1, where each product and its relative information is in each row as following: Product PE Retail_price_Band Country Model_Fit A -2.5 0-50 CN 84% B -2.1 51-100 CN 90% ..... not every product presents in each country, in other words, they may have common products or not. How do I create program in R to search each excel file in my working dir in order to make the list of the such product for each country based on my input(for example, I want to have all information related to product B in each country) the result what I want: Product PE Retail_price_Band Country Model_Fit B -2.4 51-100 IN 80% B -2.1 51-100 CN 90% How do I achieve this? thanks. Kind regards, Lingyi [[alternative HTML version deleted]]
John Kane
2013-Mar-28 14:00 UTC
[R] How to search in each excel in my working dir and extract part information of one product in each country?
> -----Original Message----- > From: metal_licaling at live.com > Sent: Thu, 28 Mar 2013 11:24:43 +0300 > To: r-help at r-project.org > Subject: [R] How to search in each excel in my working dir and extract > part information of one product in each country? > > Hello, > > I have created many excel files in my working dir.Why?>each excel file > contain one country's data. In the excel, I have dataframe in sheet1, > where each product and its relative information is > in each row as following: > > Product PE Retail_price_Band Country Model_Fit > A -2.5 0-50 CN > 84% > B -2.1 51-100 CN 90% > ..... > > > not every product presents in each country, in other words, they may have > common products or not. > How do I create program in R to search each excel file in my working dir > in order to make the list of the such product for each > country based on my input(for example, I want to have all information > related to product B in each country) > the result what I want: > > > Product PE Retail_price_Band Country Model_Fit > B -2.4 51-100 IN 80% > B -2.1 51-100 CN 90% > > > How do I achieve this? thanks. > > Kind regards, > Lingyi >If you are doing the original analyses in R why not just run them put the results for the countries in a list. Then just convert the list to a data.frame and subset. Quick and dirty example. md1 <- data.frame(aa = 1:10, b = letters[1:5]) md2 <- data.frame(aa = 11:20, b = letters[6:10]) mlist <- list(md1,md2) dd1 <- do.call(rbind.data.frame, mlist) ____________________________________________________________ FREE 3D EARTH SCREENSAVER - Watch the Earth right on your desktop!
jim holtman
2013-Mar-28 15:56 UTC
[R] How to search in each excel in my working dir and extract part information of one product in each country?
You can use 'list.files()' to get the list of all the Excel file names and then the XLConnect package makes it easy to read in the files and extract the data that you want from them. On Thu, Mar 28, 2013 at 4:24 AM, Tammy Ma <metal_licaling@live.com> wrote:> Hello, > > I have created many excel files in my working dir. each excel file contain > one country's data. In the excel, I have dataframe in sheet1, where each > product and its relative information is > in each row as following: > > Product PE Retail_price_Band Country Model_Fit > A -2.5 0-50 CN 84% > B -2.1 51-100 CN 90% > ..... > > > not every product presents in each country, in other words, they may have > common products or not. > How do I create program in R to search each excel file in my working dir > in order to make the list of the such product for each > country based on my input(for example, I want to have all information > related to product B in each country) > the result what I want: > > > Product PE Retail_price_Band Country Model_Fit > B -2.4 51-100 IN 80% > B -2.1 51-100 CN 90% > > > How do I achieve this? thanks. > > Kind regards, > Lingyi > > > > > > > > > > > > > > > > [[alternative HTML version deleted]] > > ______________________________________________ > R-help@r-project.org mailing list > stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide > R-project.org/posting-guide.html > and provide commented, minimal, self-contained, reproducible code. >-- Jim Holtman Data Munger Guru What is the problem that you are trying to solve? Tell me what you want to do, not how you want to do it. [[alternative HTML version deleted]]