Excelで複数行のテキストを1つのセルに集約

何がしたいかというと,バラバラのセルに書かれている自由記述アンケートの結果を1つのセルにまとめたい。具体的には,以下のようなアンケート結果があって,

表1:アンケート結果

  A B C
1 Class_ID Instructor_Name comments
2 1 A 良いです。
3 1 A とても良いです。
4 2 B まぁまぁ
5 2 B ダメじゃん
20 10 J 良い!

別のシートに表2のように整理したい。

  A B C
1 Class_ID Instructor_name Comments
2 1 A

良いです。

とても良いです。

3 2 B

まぁまぁ。

ダメじゃん

10 10 J 良い!

表2のC1で,以下の関数を使う。

=TEXTJOIN(CHAR(10),TRUE,IF(‘表1′!A:A=A2,’表1’!C:C,””))

TEXTJOIN(区切り文字,TRUE(空のセルは無視), if(表1のA:Aに,A2にあるClass_IDがあれば,表1のC:Cから書いてあることを取ってきて,区切り文字で分けつつ,結合する), Class_IDなければ,なにもしない)

CHAR(10)は改行を区切り文字とする関数

ggplot2の備忘録

毎回,グラフを作るときに忘れてしまうので,メモとして書き残しておこう。

まずは,サンプルデータで,基本的な折れ線グラフを書く。

library(ggplot2)
data.frame(day = 1:10, access =c(1,2,4,6,8,10,12,14,16,18))
ggplot(dat,aes(x=day, y=access))+geom_line()

見た目の変更1

ggplot(dat,aes(x=day, y=access))+geom_line()+ylim(0,30)+xlim(0,10)+labs(x="日付", y="アクセス数", title="アクセス記録分析")+theme_bw()

見た目の変更2

ggplot(dat, aes(x=day, y=access)) + geom_line()+ylim(0,30)+xlim(0,10)+labs(x="日付", y="アクセス数", title="アクセス記録分析")+theme_bw()+theme(panel.grid.major = element_line(colour = "#AAAAAA"), text=element_text(size=12, family="mincho"), axis.text.x = element_text(size=12),axis.text.y = element_text(size=12))

ポイント

theme(panel.grid.major = element_line(colour = “#AAAAAA”), text=element_text(size=12, family=”mincho”), axis.text.x = element_text(size=12),axis.text.y = element_text(size=12))

theme(panel.grid.major = element_line(colour = "#AAAAAA"), #補助線の目盛りのある方(major)の色を少し濃い目のグレーにする
text=element_text(size=12, family="mincho"), #図に中の文字フォントのサイズとフォントを変更
axis.text.x = element_text(size=12),#x軸の目盛りの数字を大きく
axis.text.y = element_text(size=12))#y軸の目盛りの数字を大きく

リッカートスケールで得たアンケート・データの視覚化:likert Package + HH package (その3)

以前の「リッカートスケールで得たアンケート・データの視覚化:likert Package + HH package (その2)」への追記です。

回答の中に0%があると,グラフにも0%と出てしまうので,それを消したいと思っていたころ,displaying percentage per category in likert stacked proportion barplots に方法があったので,そちらを用いることにした。myPanelFuncの後半部分の変更。

myPanelFunc <- function(...){
  panel.likert(...)
  vals <- list(...)
  DF <- data.frame(x=vals$x, y=vals$y, groups=vals$groups)

  ### some convoluted calculations here...
  grps <- as.character(DF$groups)
  for(i in 1:length(origNames)){
    grps <- sub(paste0('^',origNames[i]),i,grps)
  }

  DF <- DF[order(DF$y,grps),]

  DF$correctX <- ave(DF$x,DF$y,FUN=function(x){
    x[x < 0] <- rev(cumsum(rev(x[x < 0]))) - x[x < 0]/2
    x[x > 0] <- cumsum(x[x > 0]) - x[x > 0]/2
    return(x)
  })

  subs <- sub(' Positive$','',DF$groups)
  collapse <- subs[-1] == subs[-length(subs)] & DF$y[-1] == DF$y[-length(DF$y)]
  DF$abs <- abs(DF$x)
  DF$abs[c(collapse,FALSE)] <- DF$abs[c(collapse,FALSE)] + DF$abs[c(FALSE,collapse)]
  DF$correctX[c(collapse,FALSE)] <- 0
  DF <- DF[c(TRUE,!collapse),]

  DF$perc <- round(ave(DF$abs,DF$y,FUN=function(x){x/sum(x) * 100}), 0)


  ## Here goes 6 lines that have been changes - AK
  # here we modify the column with labels a bit:
  DF$perc <- paste0(DF$perc,'%')
  # change all "0%" to blanks
  DF$perc[DF$perc == "0%"] <- ""
  # the argument label is a bit modified too
  panel.text(x=DF$correctX, y=DF$y, label=DF$perc, cex=0.7)
}

Pavia, N., Webb, S., & Faez, F. (2019). Incidental Vocabulary Learning Through Listening To Songs. Studies in Second Language Acquisition, 41, 1–24.

Pavia, N., Webb, S., & Faez, F. (2019). Incidental Vocabulary Learning Through Listening To Songs. Studies in Second Language Acquisition, 41, 1–24. https://doi.org/10.1017/S0272263119000020

これまでの歌を聴くことによる偶発的な語彙学習に関する研究は,主に参加者のリスニング行動とその行動と語彙知識の関係を自己報告させるものであった(Kuppens, 2010)。歌を聴くことが語彙学習に寄与するということを実証的に調査した研究は一つしかない(Medina, 1993)。その結果から,歌を聴くことで語彙学習が起こると結論づけた。しかしながら,どれほどの学習効果があったかは示されなかった。本研究では,2つの歌を聴くことで語彙の3つの側面(話された語の認識,形式と意味のつながり,コロケーションの認識)において偶発的な学習が起こるかを調査した。1曲を繰り返し聞くこと(1回,3回,5回)と目標となる語彙への接触頻度の学習への寄与も調査された。結果として,以下のことが分かった。(a) 歌を聴くことは語彙学習に寄与する,(b) 繰り返し聞くことは語彙学習に寄与する,そして,(c) 接触頻度も語彙学習効果に肯定的な効果がある。

[Abstract]

Research investigating incidental vocabulary learning through listening to songs has primarily relied on participant’s self-report surveys on listening behaviors and its relationship with their vocabulary knowledge (Kuppens, 2010). Only one experimental study has investigated vocabulary learning gains from listening to songs (Medina, 1993). From the results, the researcher concluded that learning does occur from listening to songs. However, the learning gains were not provided. The present study investigated incidental learning of three vocabulary knowledge dimensions (spoken-form recognition, form-meaning connection, and collocation recognition) through lis- tening to two songs. The effects of repeated listening to a single song (one, three, or five times) and the relationship between frequency of exposure to the targeted vocabulary items and learning gains were also explored. The results indicated that (a) listening to songs contributed to vocabulary learning, (b) repeated listening had a positive effect on vocabulary gains, and (c) frequency of exposure positively affected learning gains.
INTRODUCTION

集計データを使って信頼区間付きのグラフを描く

タイトル通りで、95%信頼区間付きのグラフを描くのだけれど、今回は、集計したデータが手元にあって、それをggplot2を用いてグラフにする。エラーバーとかも同じ方法でよい…と言うか、エラーバーを95%信頼区間にしているだけ。

データとして、以下のような感じで平均点と[95%信頼区間]が与えられているとする。

  事前テスト平均点 事後テスト平均点
A群 30 [28, 32] 40 [38,42]
B群 50 [48, 52] 65 [63, 67]
C群 70 [68, 72] 85 [83, 87]

 

まずは、データの読み込み

x = data.frame (
time =c(1,2,1,2,1,2), #事前と事後
group =c("A", "A", "B", "B", "C", "C"),#群
means =c (30, 40, 50, 65, 70, 85),#平均点
lower =c (28, 38, 48, 63, 68, 83),#信頼区間の上限
upper =c (32, 42, 52, 67, 72, 87) #信頼区間の下限
)

それをグラフにする。

g = ggplot(x, aes(x = time, y = means, group = group))+ #データの読み込み
geom_line(aes(linetype=group),size=1)+ #折れ線グラフの作成。線種は群ごと。
geom_point(aes(shape=group),size=3)+ #折れ線の先に印を付ける。印の種類は群ごと
scale_x_continuous(breaks=seq(1,2,1), labels= c("事前", "事後")) + #x目盛りの制御
ylim(0,100) + #y軸の範囲指定
scale_linetype_manual(values=c("solid","twodash", "dotted")) + #線種の指定
theme_classic() + #全体のテーマ
geom_errorbar(aes(ymin = lower, ymax = upper,linetype=group), width = 0.8, size =1,) #信頼区間の付加

それを描画する。

plot(g)

合計点で成績をつける場合の#N/Aの処理

Excelで合計点を出して,成績をつける場合に,関数でIfsを使う。例えば,

  • 90点以上はS
  • 80点以上はA
  • 70点以上はB
  • 60点以上はC
  • 60点未満はD

なので,以下の場合は問題なし。

  A B C
1 ID テストの得点 評価
2 S001 70 B
3 S002 90 S
4 S003 50 D
….    
41 S040 60 C

この場合,

  • 評価のC1は,=IFS(B2 >= 90,”S”,B2 >= 80,”A”,B2 >= 70,”B”,B2 >= 60,”C”,B2 <= 59.9,”D”)

問題になるのは,途中に#N/Aがある場合,上のようにすると,以下のように#N/Aが評価に出る。

  A B C
1 ID 合計点 評価
2 S001 #N/A #N/A
3 S002 90 S
4 S003 50 D
….    
41 S040 60 C

 

解決策としては,#N/Aはエラーの扱いなので,IfERRORでエラーが出たらDをつけるようにする。

  • 評価のC1は,=IFERROR(IFS(B2 >= 90,”S”,B2 >= 80,”A”,B2 >= 70,”B”,B2 >= 60,”C”,B2 <= 59.9,”D”),”D”)

とすると,まずは,エラーかどうかを評価し,エラー(#N/A)なら”D”を返し。そうでなければ,IFS以下の評価を返す。

 

 

Excelでクロス表から成績を決める

タイトルが良く分からない感じになりましたが,以下のように,クロス表で成績を決める場合にどうするかという話。例えば,5段階評価で,横方向と縦方向で評価の観点が異なり,それぞれの合算で成績をつけたい場合を想定しています。一番上の行(A-F)と一番右の列(1-6)は,エクセルの外枠にある記号と思ってください。

54321
555443
454432
343332
232221
111111

こちらのサイトの記事「【エクセル時短】縦横で交わるデータを取り出す!「クロス抽出」を実現する関数の組み合わせとは?」を参考というか,ほとんどそのまま使わせて頂いています。イメージとしては,以下のようになれば良い。エクセルのシートに,以下のように,A~Dは成績処理,F~Lは上のクロス表が配置されているとする。

ABCDEFGHIKL
1ID 観点1(縦) 観点2(横) 成績(縦と横) 54321
2A01555555443
3A02545454432
4A03323343332
5A04141232221
6A05513111111

上のD2に入っているのは,

=VLOOKUP(B2,$F$2:$K$6,MATCH(C2,$G$1:$L$1,0),FALSE)

vloolupは(検索値,範囲,列番号,検索の型)であり,縦に検索する値(上だとB2の5)をFの列から探し,F2~L6の四角の範囲の中で,列番号で指定された場所にあるものを完全一致(False)で取ってくる。

matchは(検索値,検索範囲,照合の種類)なので,横に検索する値(上だとC2の5)をG1~L1の範囲で探し,完全に一致するもの(照合の種類が0)を探す。返す値は,範囲の中の列番号なので,上の場合には,1(列目)を返す。

関数の中身は,vlookup(検索値(B2=5), 範囲(F2~K6), match(検索値(C2=5), 検索範囲(G1~L1), 検索の型(完全一致=0)), False(vlookupの完全に一致))となる

リッカートスケールで得たアンケート・データの視覚化:likert Package + HH package (その2)

以前のブログ記事で,likert packageを使った視覚化について書いたけれど,今度はHH packageも使う方法。データとしては以前と同じ,以下のイメージ。質問項目は,Q1からQ5まであって,例えば,1が「全く良くない」で,5が「とても良い」のような5件法だとする。データのcsvはこちら

Q1Q2Q5
145
533
….
library(HH)
library(likert)
library(plyr)
dat=read.csv("likert_test.csv", header= T) #データの読み込み
  dat2=data.frame(lapply(dat, factor, levels=1:5)) 
#datの中身をfactor(文字列)に変換。その際に,五件法なので範囲を1から5までとする。つまり,ある設問への反応として1が含まれていない場合などでも,1が0回とするための処置。その上で,データ・フレームにする
  HH::plot.likert(  #HHでPlotを出す
  likert::likert(dat2), #likert packageで集計する
  col=rev(grey(seq(0,1,0.25))), #1を白,5を黒,中間がグレーの指定。revをしているので,これを外すと5が黒,1が白。また,1から5段階なので,0.25ずつグラデーションにする。
  main ="TEST1" タイトルはTEST1とする
  )

Itemの順番がQ5からQ1になっているのが気になるのと,1が白字なので,枠線がないと見えない。これらを解消。

HH::plot.likert(
  likert::likert(dat2),
  col=rev(grey(seq(0,1,0.25))),
  main ="TEST2", 
  reverse=F,  #Itemの順番を上からQ1の順に
  border = c("#111111", "#111111", "#111111", "#111111", "#111111") #グラフの周りを黒枠で囲む
  )

グラフの中に%で数値を入れたい。

以下のFunctionはStakFlowのこちらの答えから引用し,変更したもの。グラフ内に数値%を表示する。

myPanelFunc = function(...){
  panel.likert(...)
  vals <- list(...)
  DF <- data.frame(x=vals$x, y=vals$y, groups=vals$groups)
  
  ### some convoluted calculations here...
  grps <- as.character(DF$groups)
  for(i in 1:length(origNames)){
    grps <- sub(paste0('^',origNames[i]),i,grps)
  }
  
  DF <- DF[order(DF$y,grps),]
  
  DF$correctX <- ave(DF$x,DF$y,FUN=function(x){
    x[x < 0] <- rev(cumsum(rev(x[x < 0]))) - x[x < 0]/2
    x[x > 0] <- cumsum(x[x > 0]) - x[x > 0]/2
return(x)
}) subs <- sub(' Positive ,'',DF$groups) collapse <- subs[-1] == subs[-length(subs)] & DF$y[-1] == DF$y[-length(DF$y)] DF$abs <- abs(DF$x) DF$abs[c(collapse,FALSE)] <- DF$abs[c(collapse,FALSE)] + DF$abs[c(FALSE,collapse)] DF$correctX[c(collapse,FALSE)] <- 0 DF <- DF[c(TRUE,!collapse),] DF$perc <- round(ave(DF$abs,DF$y,FUN=function(x){x/sum(x) * 100})) ### panel.text(x=DF$correctX, y=DF$y, label=paste0(DF$perc,'%'), cex=1.0,col=c("black","black","black","white","white")) } #ここは数値と%の表示色を決める。今回は白字とグレーと黒なので,それに合わせて変更している。

以下がPackageを使用したスクリプト

origNames=rownames(dat) #Functionで使うデータの定義
HH::plot.likert(
  likert::likert(dat2), 
  col=rev(grey(seq(0,1,0.25))),
  main ="TEST3", 
  reverse=F, 
  border = c("#111111", "#111111", "#111111", "#111111", "#111111"), 
 panel =myPanelFunc #上で定義したFunctionを使う。
 )

Goodwin et al. (2017). Exploring the dimensionality of morphological knowledge for adolescent readers

Goodwin, A. P., Petscher, Y., Carlisle, J. F., & Mitchell, A. M. (2017). Exploring the dimensionality of morphological knowledge for adolescent readers. Journal of Research in Reading, 40(1), 91–117. https://doi.org/10.1111/1467-9817.12064

この研究では,形態論的知識の次元性について調査をする。371人の7年生と8年生が7つの形態論的知識を問う課題を行った成績を検証的因子分析により調べた。結果として,形態論的知識を1つの全般的な因子,そして7つの特定の因子としたbifactorモデルが最も適合した。その7つの特定の因子は,課題が形態論的知識の特定の側面を切り取ったものを示している。次に,構造方程式モデルを用いて,リテラシーの結果との関連を調査した。結果として,全般的な因子と形態論的知識の意味を処理する特定の因子が読解理解と語彙と有意な正の関係を示した。また,形態論的な語を読むという特定の因子や綴りを処理するという特定の因子は,読解理解や語彙とわずかに負の関係を見せたが,形態論的に関連のある語を作り出すという特定の因子は語彙と有意な性の関係を示した。形態論的知識の複雑性に焦点を当てて分かったこととして,研究を計画したり,解釈する場合には,形態論の本質的を認識しておくことの重要性が示唆される。

[Abstract]
This study examined the dimensionality of morphological knowledge. The performance of 371 seventh- and eighth-graders on seven morphological knowledge tasks was investigated using confirmatory factor analysis. Results suggested that morphological knowledge was best fit by a bifactor model with a general factor of morphological knowledge and seven specific factors, representing tasks that tap different facets of morphological knowledge. Next, structural equation modelling was used to explore links to literacy outcomes. Results indicated the general factor and the specific factor of morphological meaning processing showed significant positive associations with reading comprehension and vocabulary. Also, the specific factor of generating morphologically related words showed significant positive associations with vocabulary, while specific factors of morphological word reading and spelling processing showed small negative relationships to reading comprehension and vocabulary. Findings highlight the complexity of morphological knowledge and suggest the importance of being cognizant of the nature of morphology when designing and interpreting studies.

Northbrook, J., & Conklin, K. (2018). Is What You Put in What You Get Out? —Textbook-derived Lexical Bundle Processing in Beginner English Learners.

Northbrook, J., & Conklin, K. (2018). Is What You Put in What You Get Out? —Textbook-derived Lexical Bundle Processing in Beginner English Learners. Applied Linguistics, 1–19. https://doi.org/10.1093/applin/amy027

Abstract

第二言語習得に対する用例基盤モデルは,学習者が受ける言語入力に非常に重きを置き,学習者が頻繁に出会う語の連続が処理の優位性をもたらすと予測する。この研究では,日本の中学で英語を学ぶ初級学習者における,Lexical bundles(語彙連結)と呼ばれる高頻度な語の連続の処理を調査する。指導教材から直接抜き出した項目で生徒を調査するために,句判断課題を用いる。生徒は,教科書に出てこないものより,出てくるLexical bundlesへより速く,より正確に反応することから,教科書での出現頻度に敏感であった。本研究では,おそらく初めて,とても習熟度が低い,中学生の初級学習者であっても指導教材から得た入力に出てくるLexical bundlesの頻度に敏感であることを示しており,この発見は,教授もしくは教材のデザインへの重要な示唆を含んでいる。

Usage-based approaches to second language acquisition put a premium on the linguistic input that learners receive and predict that any sequences of words that learners encounter frequently will experience a processing advantage. The current study explores the processing of high-frequency sequences of words known as ‘lexical bundles’ in beginner learners of English in Japanese secondary school. To do this we use a phrasal judgment task, testing students on items taken directly from their teaching materials. Students responded to lexical bundles that occurred in their textbooks significantly faster and more accurately compared with non-lexical bundles, and were sensitive to the frequency of oc- currence in their textbooks. This study shows, perhaps for the first time, that even very low-level, beginner secondary students are sensitive to the frequency of lexical bundles which appear in the input they receive from teaching materials, a finding that has important implications for teaching and material design.

 

1 4 5 6 7 8 13