Translate

2022年8月10日水曜日

【R言語】How to break out Multi-Answer responses into separate cells

アンケートの複数回答などで、下図の左端の列のように一つのセルに回答の選択肢が記録されている場合があります。

そのようなデータを、上の図の右6列のように、複数回答の選択肢ごとに「1-0」のデータに変換する処理をする必要があったりします。 

 Pythonで処理しているコードがありましたが、R言語のコードが見つかりませんでした。

 あるサイトのPythonのコードを参考にさせていただき、R言語で処理できるようにしてみました。 

 たどたどしいR言語による翻訳ですが、備忘録として記録しておきます。 

 選択肢ごとに論理値の回答を「1-0」に変換して行列の形にするところをもっと簡単にできるのではないかと思いますが、 何とか無理やり処理しています。 

 ツイッターデータのツイートに含まれるハッシュタグリストの処理にも使えそうなのでR言語のコードにしてみました。

'複数回答' = 'Multiple Answers'
 【R言語による複数回答データの処理】
sample <- c('Japan,China,Korea','China,Korea,America,Germany,France','America,Germany,France','Japan,China,Korea,America','China,America,Germany,France','Germany,France','China,America')​​​​
df_sample <- as.data.frame(sample)
colnames(df_sample) <- '複数回答'
df_sample

answers <- strsplit(df_sample$複数回答, ",")
answers_list <- unlist(answers)
answers_list <- unique(unlist(answers))
answers_list

ma_df <- NULL
  for (i in 1:length(answers)) {
    for (j in 1:length(answers_list)) {
     tempanswer <-  answers_list[j] %in% answers[[i]]
       ma_df <- rbind(ma_df,tempanswer) 
  }
 }
ma_dfdm <- ma_df*1
ma_dfdm

slrnum1 <- length(answers_list)

df_ma <- NULL
for (i in 1:slrnum1) {
tempma <- ma_dfdm[seq(i,nrow(ma_dfdm),slrnum1), ]
df_ma <- cbind(df_ma,tempma)
}

colnames(df_ma) <- answers_list
df_ma

df_macomp <- cbind(df_sample,df_ma) 
df_macomp

rownames(df_macomp) <- NULL
​​df_macomp

############END#################################

【下記は部分的に改良したパターンです:論理値のリストからダミー変数行列を作成する部分をより単純化しています:結果は同じです】
################################################
ma_df <- NULL
  for (i in 1:length(answers)) {
    for (j in 1:length(answers_list)) {
     tempanswer <-  answers_list[j] %in% answers[[i]]
      ma_df <- append(ma_df,list(tempanswer))
        }
  }
storage.mode(ma_df) <- "integer"
mamatrix <-  matrix(ma_df, nrow=length(answers_list), ncol=length(answers))
mamatrixt <- t(mamatrix)
ma_dfdm01 <- as.data.frame(mamatrixt) 
colnames(ma_dfdm01) <- answers_list

df_macomp <- cbind(df_sample,ma_dfdm01) 
df_macomp

-----------------------------------------------------------------------------
【下記のコードで「1」を選択肢の文字列に置き換えることができます。結果は下図のようになります。】
df_matxt <- NULL
qmanamelist <- NULL
for (i in 1:length(answers_list)) {
anstxt <- answers_list[i]
df_matxt0 <- ma_dfdm01[,i]
qmatemp <- df_matxt0 %>% str_replace_all(.,"1",anstxt)
df_matxt <- rbind(df_matxt,qmatemp) 
qmaname <- assign(paste0("q_",i),paste0("qma_",i))
qmanamelist <- rbind(qmanamelist,qmaname) 
}
df_matxtt <- t(df_matxt)
colnames(df_matxtt) <- t(qmanamelist[,1])
df_matxtt
df_macomp1 <- cbind(df_sample,df_matxtt) 
df_macomp1
-----------------------------------------------------------------------

2021年12月19日日曜日

【DAX備忘録】ある列の条件に基づいてデータを割り当てた列を作成する

DAXのアイデアの備忘録です。
ある列の値の範囲ごとに別のテーブルからの値を割り当てる方法のアイデアです。 割り当てる値を別のテーブルから取得するのですが、その値を一意に決める必要があるため処理が面倒です。

下記のメジャーにループ処理を追加できるといいのですが、DAXでのループは難しいようです。

割り当てる値を持っているテーブルで値を特定するためにランク情報を使います。

Rank = RANKX(ALL('Days'),[DATE],,ASC)


ランク情報で特定された値を割り当て先の値の範囲ごとに割り当てます。

割り当てる列 = VAR R1 = MIN('Days'[Rank]) VAR R2 = MIN('Days'[Rank])+1 VAR R3 = MIN('Days'[Rank])+2 VAR R4 = MIN('Days'[Rank])+3 VAR R5 = MIN('Days'[Rank])+4 VAR D1 = CALCULATE(MIN('Days'[DATE]),FILTER('Days','Days'[Rank] = R1)) VAR D2 = CALCULATE(MIN('Days'[DATE]),FILTER('Days','Days'[Rank] = R2)) VAR D3 = CALCULATE(MIN('Days'[DATE]),FILTER('Days','Days'[Rank] = R3)) VAR D4 = CALCULATE(MIN('Days'[DATE]),FILTER('Days','Days'[Rank] = R4)) VAR D5 = CALCULATE(MIN('Days'[DATE]),FILTER('Days','Days'[Rank] = R5)) VAR R = MIN([Day_R]) RETURN SWITCH(TRUE(), 'Power'[DATE] >= D1 && 'Power'[DATE] < D2,D1, 'Power'[DATE] >= D2 && 'Power'[DATE] < D3,D2, 'Power'[DATE] >= D3 && 'Power'[DATE] < D4,D3, 'Power'[DATE] >= D4 ,D4 )

2021年6月12日土曜日

【Power BI 備忘録】インフォグラフィックデザイナーのグラフで、最初と最後のデータにラベルを付けるDAX

 Power BIのカスタムビジュアルであるインフォグラフィックデザイナーの折れ線グラフなどで、最初と最後のデータポイントにラベルをつける方法です。


まず、下記のように、データの日付の最初と最後で値を計算するメジャーを作成します。

この場合の注意点ですが、データを「 FORMAT([OrderQ],"General Number")」のような形にしないと、最初と最後以外のラベルが「0」となってしまうようです。なお、普通の折れ線グラフの場合は、FORMATがなくても最初と最後のみにラベル表示ができます。

なお、数値データの代わりにカテゴリー列を指定すれば、カテゴリーラベルを表示させることができます。

Label_Measure = 

VAR min_label = CALCULATE(MIN('SalesData'[OrderDate]), ALLSELECTED('SalesData'))

VAR max_label = CALCULATE(MAX('SalesData'[OrderDate]), ALLSELECTED('SalesData'))

VAR LABEL = FORMAT([OrderQ],"General Number")

RETURN

IF( OR(

    MIN('Calendar'[Date]) = min_label,

    MAX('Calendar'[Date]) = max_label)

    ,LABEL)


2021年6月6日日曜日

【Power BI 備忘録】動的なカテゴリー軸:スライサーでグラフの軸を変更するDAX

 Power BIで、図表のカテゴリー軸をスライサーで切り替えたい場合があります。


異なるカテゴリーの軸をスライサーで切り替えて利用すると、1つのグラフで2つのグラフの情報を表示できるようになります。


いわゆる「動的な軸」「動的な凡例」というものです。


ネット上には、「リレーションシップのないカテゴリーテーブル」を利用する方法などが紹介されていたりしますが、他にも図表がある場合には、他の図表との相互作用ができなくて困ることになるでしょう。


ネット上の情報は、限定された環境での例であることが多く、あまり役立たないことがあります。


では、どうすればいいのでしょうか。


まず、Power Queryを使ってカテゴリー列があるファクトテーブルに「インデックス列」を作成します。


次に、スライサーの切り替え用のテーブルを作成します。UNION関数、DISTINCT関数、SELECTEDCOLUMNS関数を使って、複数のカテゴリー列を縦方向に結合します。インデックス列も新しいテーブルに取り込んでおきます。インデックス列でファクトテーブルとリレーションシップを作成して、縦に結合したカテゴリー列をスライサーに設定すれば完了です。


新しく作成したテーブルのカテゴリー列をグラフのカテゴリー軸に設定し、スライサーでカテゴリーを選ぶとグラフの軸を切り替えることができます。カテゴリー切り替え用のテーブルとファクトテーブルはインデックス列でリレーションしているので、動的な軸のグラフとファクトテーブルのデータを用いた他のグラフとの相互作用が可能です。






【Power BI備忘録】カテゴリー(軸)の表示順を並べ替えるDAX

 Power BIで、国/地域や製品分類、製品型番などのカテゴリーを使って図表を作成する際に、カテゴリーを意図した順番で並べたい場合があります。


カテゴリーの列は、別の「列」によって並べ替えを行うことができますが、並び順の列がない場合は、並べ替え用の順序の列を作成する必要があります。

並べ替え用の順序の列を作成するには以下のような方法があります。

・新たに「カテゴリー」と「並び順」の2列のテーブルを作成し、「カテゴリー」によってファクトテーブルとリレーションを作成するという方法があります。そして、ファクトテーブルに「RELATED関数」を使って並べ替え順の列を作成することができます。


・SWITCH関数を使って、カテゴリーに対応した順序の数字の列を直接ファクトテーブルに作成するという方法があります。


いずれの場合も、「カテゴリー」の文字列は手入力しない方がいいでしょう。

DISTINCT([カテゴリー])でカテゴリーのテーブルを作成し、テキストをコピーして、新しいテーブル作成やSWITCH関数を使って作成するDAXにテキストをコピペするのがよさそうです。


テーブル作成が不要なので、SWITCH関数を使って並べ替え用の列を作成する方法がいいのではないかと思います。

新しい列の作成:

Sort_By = SWITCH (TRUE (),

    Table[Category] = "Category A", 2,

    Table[Category] = "Category B", 1,

    Table[Category] = "Category C", 3,

    BLANK ()

)





【Power BI備忘録】選択した月から、過去Nカ月分のデータを表示するDAX

 「Power BIで選択した月から、過去Nカ月分のデータを表示する方法」をネットで検索すると、「切断された日付テーブル(リレーションシップのない日付テーブル)」をスライサーに利用する方法がよく紹介されています。


「リレーションシップのない日付テーブル」で選択した「月」の数字によって、図表の軸の「月」の数字をフィルターするという方法です。


しかし、「リレーションシップのない日付テーブル」を使わなくても、DATESINPERIOD関数を使えば「選択した月から、過去Nカ月分のデータを表示する」ことが可能です。

ただし、図表の日付軸にファクトテーブルの日付列を設定する必要があったりします。

DAXもDATESINPERIOD関数を使った場合は、下記のような簡単な内容です。

Measure (last n months) =

CALCULATE (

    SUM ( Table[Sales] ),

    DATESINPERIOD ( ‘Date'[Date], MAX ( ‘Date'[Date] ), – [N Value], MONTH )

)


「リレーションシップのない日付テーブル」など作成せずに済むので、ファクトテーブルの日付列と日付テーブルの日付を利用して試してみる価値はあります。


ネット上には、「ベストプラクティス」ではない事例が数多くあるようです。


Power BIは日本語の情報が少なく、ネット上の「ベストでない情報」に翻弄されることがよくあるようです。


私もDATESINPERIOD関数が役立つことに気づくのにかなり時間がかかりました。





2021年3月14日日曜日

【Power BI備忘録】日付テーブル作成のDAX関数

 Power BIで時系列データを扱う場合は、日付テーブルをDAX関数で作成すると便利になるようです。


一意で、連続している日付列を持ったテーブルがあると、タイムインテリジェンス関数の利用などが可能になるようです。


ネット上でいろいろな日付テーブル作成のDAX関数が紹介されているので参考にしていきたいと思います。


下記は、ネット上にあるDAX関数です。変数やCALENDARAUTO()を使うもの、使わないものなどがあります。


MAX([データが持っている日付列])とMIN([データが持っている日付列])のデータを使って日付テーブルを作成するのがいいかもしれません。この場合は下記のような形になるようです。

CalendarTable  =  CALENDAR(MIN([データが持っている日付列]),MAX([データが持っている日付列]))

この場合、範囲の終わりの方を「EOMONTH(MAX([データが持っている日付列]), 0))」とすれば、該当月の月末までの日付が生成されます。

作成した日付テーブルは、ファクトテーブルとの間にリレーションシップを作成して利用します。この場合、日付テーブルを「日付テーブルとしてマークする」という設定にするといいようです。

「日付テーブルとしてマークする」の設定をしていないとタイムインテリジェンス関数がうまく機能しないことがあるようです。ただし、日付型の日付列でリレーションしていれば「日付テーブルとしてマークする」は必須でもないようです。

--------------------------------------------------

CalendarTable = 

var BaseCalendar = CALENDARAUTO()

RETURN

    GENERATE(

        BaseCalendar,

        var BaseDate = [Date]

        var YearNumber = YEAR(BaseDate)

        var MonthNumber = MONTH(BaseDate)

        var DateNumber = DAY(BaseDate)

        var DayNumber = WEEKDAY(BaseDate, 1)


        RETURN 

        ROW(

            "年_番号", YearNumber,

            "年", FORMAT(BaseDate, "yyyy年"),

            "月_番号", MonthNumber,

            "月", FORMAT(BaseDate, "mm月"),

            "年月_番号", FORMAT(BaseDate, "yyyymm"),

            "年月", FORMAT(BaseDate, "yyyy年mm月"),

            "日_番号", DateNumber,

            "日", FORMAT(BaseDate, "dd日"),

            "曜日_番号", DayNumber,

            "曜日", SWITCH(DayNumber, 1, "日", 2, "月", 3, "火", 4, "水", 5, "木", 6, "金", 7, "土",""),

            "相対月数", DATEDIFF(TODAY(), BaseDate, MONTH)

        )

    )

---------------------------------------------------

Calendar = 

ADDCOLUMNS (

CALENDAR (DATE(2000,1,1), DATE(2025,12,31)),

"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),

"Year", YEAR ( [Date] ),

"Monthnumber", FORMAT ( [Date], "MM" ),

"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),

"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),

"MonthNameShort", FORMAT ( [Date], "mmm" ),

"MonthNameLong", FORMAT ( [Date], "mmmm" ),

"DayOfWeekNumber", WEEKDAY ( [Date] ),

"DayOfWeek", FORMAT ( [Date], "dddd" ),

"DayOfWeekShort", FORMAT ( [Date], "ddd" ),

"Quarter", "Q" & FORMAT ( [Date], "Q" ),

"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" )

)

---------------------------------------------------

---------------------------------------------------

日付テーブル = 

VAR StartDate="2016/04/01"

VAR EndDate="2020/3/31"

VAR Today=UTCTODAY()+9/24

VAR ThisFY=if(MONTH(Today)>3,YEAR(Today),YEAR(Today)-1)

VAR BaseCalendar =CALENDAR (StartDate,EndDate)

RETURN


ADDCOLUMNS(

    BaseCalendar,

    "年_数値", YEAR ( [Date] ),

    "年_yyyy年",FORMAT([Date],"yyyy年"),

    "月_数値", MONTH ([Date]),

    "月_MM月", FORMAT ([Date], "MM月" ),

    "日_数値", DAY ([Date]),

    "日_DD日", FORMAT([Date],"DD日"),

    "年月_数値", VALUE(FORMAT([Date], "yyyyMM" )),

    "年月_yy年MM月", FORMAT ( [Date], "yy年MM月" ),

    "年度_数値",if(MONTH([Date])>3,YEAR([Date]),YEAR([Date])-1),

    "年度_0年度",FORMAT(if(MONTH([Date])>3,YEAR([Date]),YEAR([Date])-1),"0年度"),

    "年度四半期_数値", if(MONTH([Date])<4,4,if(MONTH([Date])<7,1,if(MONTH([Date])<10,2,3))),

    "年度四半期_0Q", if(MONTH([Date])<4,"4Q",if(MONTH([Date])<7,"1Q",if(MONTH([Date])<10,"2Q","3Q"))),

    "週_数値", WEEKNUM ( [Date], 1 ),

    "週_第00週", FORMAT ( WEEKNUM ( [Date], 1 ), "第00週" ),

    "曜日番号月曜から_数値", WEEKDAY ( [Date], 2 ),

    "曜日_aaa", FORMAT([Date],"aaa"),

    "日と曜日", FORMAT ( [Date], "DDaaa" ),

    "相対日付",DATEDIFF(Today,[Date],DAY),

    "相対月",DATEDIFF(Today,[Date],MONTH),

    "相対年度",if(MONTH([Date])>3,YEAR([Date])-ThisFY,YEAR([Date])-1-ThisFY)

    )

---------------------------------------------------

https://docs.microsoft.com/ja-jp/learn/modules/dax-power-bi-add-calculated-tables/1-introduction