Explore
For Enterprise
Join for Free
Log In

Гуманитарные науки и искусства
Бизнес
Компьютерные науки
Наука о данных
Информационные технологии
здоровье
Математика и логика
Личное развитие
Естественные и технические науки
Социальные науки
Изучение языков
Степени и сертификаты

Полный обзор Coursera

Обзор
Поиск
Для организаций
Войти
Присоединиться бесплатно

Reading Excel Files

Loading...

Сбор и сортировка данных

Университет Джонса Хопкинса

4.6 (оценок: 6,103) | Зарегистрировано учащихся: 120K

Курс 1 из 10 — Наука о данных Специализация

Просмотреть программу курса

Before you can work with data you have to get some. This course will cover the basic ways that data can be obtained. The course will cover obtaining data from the web, from APIs, from databases and from colleagues in various formats. It will also cover the basics of data cleaning and how to make data “tidy”. Tidy data dramatically speed downstream data analysis tasks. The course will also cover the components of a complete data set including raw data, processing instructions, codebooks, and processed data. The course will cover the basics needed for collecting, cleaning, and sharing data.

Получаемые навыки

Data Manipulation, Regular Expression (REGEX), R Programming, Data Cleansing

Рецензии

4.6 (оценок: 6,103)

5 stars
4,112 ratings
4 stars
1,505 ratings
3 stars
335 ratings
2 stars
87 ratings
1 star
64 ratings

AR

May 14, 2018

Loved the structure of the course. Learned a lot. The course project seemed a little funky , especially creating the codebook for an already existing set of data but was a useful teaching aid.

VV

Nov 26, 2017

A lot of insight and practical knowledge of cleaning data that is available in many places in the Internet. I loved this course and it took me 2 tries to pass the peer graded assignment. ;)

Из урока

Week 1

In this first week of the course, we look at finding data and reading different file types.

Obtaining Data Motivation5:38

Raw and Processed Data7:07

Components of Tidy Data9:25

Downloading Files7:09

Reading Local Files4:55

Reading Excel Files3:50

Reading XML12:39

Reading JSON5:03

The data.table Package11:18

Преподаватели

Jeff Leek, PhD
Associate Professor, Biostatistics
Roger D. Peng, PhD
Associate Professor, Biostatistics
Brian Caffo, PhD
Professor, Biostatistics

Some data scientists show a certain snobbery about Excel files.

But Excel files are still probably the most widely used format for sharing data.

I think the reason is because a lot of people are used to using spreadsheets,

so whether you're working in business applications or in science, people know

how to use spreadsheets to collect, and put data in, and share it with each other.

It's true now, even more than ever, with things like Google spreadsheets,

which allow people to share them collaboratively on the internet.

The problem for you is that when you're analyzing data with a scripting

language like R you need to be able to extract the data out of those files so

that you can do downstream analyses and processing on them.

So we're gonna go back to the Baltimore camera data.

I guess I must be getting a lot of speeding tickets lately cuz I've been

thinking about this data set a lot.

And so now we're gonna actually download the Excel version of this data set.

And so in this case the URL is a little bit different.

Again you just pick the URL off the website.

And then what we're gonna do is we're going to actually download the file as

a .xlsx file because that's the extension for Excel files.

And so now we've downloaded this file.

We began to use download file which remember is agnostic to the file type so

it just downloads the file and

puts it into this file name that you've stated here.

And again we're gonna collect information on when the file was downloaded so

that we can know when the analysis was performed.

So the R library that is useful for this is the xlsx package.

There's other packages, XLConnect that can also can be use if you would like to.

They might provide a little bit more flexible interface but I like xlsx.

So what we do is we actually use the command read.xlsx and

we assign it again to the cameraData variable.

And now we need to pass a couple of parameters so

we need to pass the sheetIndex.

So we need to say which sheet is the data stored on.

And again we're gonna tell it that there's a header line so

that the column names are labeled.

And so when we load that into R again it looks almost exactly like the data that

you got when you read the CSV file and you read it straight out of an Excel file.

One thing to keep in mind is that you can read specific rows and specific columns.

So for example, if you wanna read just the second and third columns and

you wanna read just the first through fourth rows, you can actually pass those

as variables to colIndex and rowIndex to this file read.xlsx.

And you'll actually only read a subset of the Excel file which might be

useful if you only wanna abstract a little part of the file that you have.

So some further notes that might be useful for you are that you can use write.xlsx.

So if you're working with people that like Excel files,

you can actually write them back out after your analysis.

Share them with people.

It's very similar and you pass it the object that you want to write out and

the file name and it will write that file out.

read.xlsx2 I found is quite a bit faster than read.xlsx.

But especially if you're reading subsets of rows,

it might be a little bit unstable at least in my experience.

XLConnect, as I mentioned, is a lot more flexible for reading, writing and

manipulating Excel files.

And so if you really need to do a lot of serious processing of Excel files,

I've found that XLConnect might be a little bit better.

If you're gonna be doing that,

the XL Connect vignette is actually a really great place to start.

It has lot of information about how you can do all sorts of different things.

How you can manipulate and create files directly from R.

In general, and for the purposes of this class but

also the purposes of most analyses it's a little bit faster and easier to

read files if you store them as comma separated, or tab separated flat files.

They're also a little bit easier to distribute, as not everybody has Excel.

It's not necessarily as cross-platform as using something

like a plain text file, with just comma separated or tab separated values.

Ознакомьтесь с нашим каталогом

Присоединяйтесь бесплатно и получайте персонализированные рекомендации, обновления и предложения.

Coursera делает лучшее в мире образование доступным каждому, предлагая онлайн-курсы от ведущих университетов и организаций.

© Coursera Inc., 2019 Все права защищены.

Загрузить из App Store

Загрузить в Google Play

Coursera
О проекте
Руководство
Карьера
Каталог
Сертификаты
Сертификаты MasterTrack™
Степени
Для организаций
Для правительственных организаций

Сообщество
Learners
Partners
Developers
Beta Testers
Translators

На связи
Блог
Facebook
LinkedIn
Twitter
Instagram
YouTube
Технический блог

Еще
Условия
Конфиденциальность
Помощь
Доступность
Пресса
Контакты
Справочник
Филиалы