Блог

Мне нужно обработать данные из списка CSV. Мне нужно импортировать этот список, но для этого мне нужно объединить данные, разделенные запятыми.

В настоящее время список выглядит примерно так:

 COMPANY;CITY;COUNTRY
Comp1;curitiba;brazil
Comp2;curitiba;brazil
Comp3;detroit;usa
Comp4;detroit;usa
  

Что мне нужно, так это сгруппировать значения, разделенные запятой, в соответствии с городом, точно следующим образом:

 COMPANY;CITY;COUNTRY
Comp1,Comp2;curitiba,curitiba;brazil,brazil
Comp3,Comp4;detroit,detroit;usa,usa
  

Список состоит из тысяч строк, что делает невозможным выполнение этого вручную.

Кто-нибудь знает способ сделать это с помощью какого-нибудь инструмента?

Вы могли бы использовать некоторую функцию агрегирования SQL.

В примере sqlite, начиная с такого типа таблицы с именем «ввод»

  --- --- --- 
| a | b | c |
 --- --- --- 
| 2 | 3 | 3 |
| 1 | 3 | 4 |
| 2 | 5 | 7 |
 --- --- --- 
  

и запуск

 SELECT
    a,GROUP_CONCAT(b) b,GROUP_CONCAT(c) c
FROM
    input
group by a
  

у вас будет

  --- ----- ----- 
| a | b   | c   |
 --- ----- ----- 
| 1 | 3   | 4   |
| 2 | 3,5 | 3,7 |
 --- ----- ----- 
  

Эта GROUP_CONCAT функция есть во многих системах БД (не только в sqlite).

Использование Miller (https://github.com/johnkerl/miller ) очень просто.

Выполняется

 mlr --csv --fs ";" nest --implode --values --across-records  --nested-fs "," -f COMPANY input.csv
  

у вас будет

 COMPANY;CITY;COUNTRY
Comp1,Comp2;curitiba;brazil
Comp3,Comp4;detroit;usa
  

Некоторые примечания:

• --fs ";" , чтобы установить разделитель полей файла;
• nest --implode --values --across-records , чтобы установить вложенность implode по записям;
• --nested-fs "," , чтобы установить разделитель вложенных полей;
• -f COMPANY , чтобы настроить поле на сжатие.

Если я вас правильно понимаю, и вы просто хотите, чтобы каждые две строки были объединены в одну строку, то следующее приведет вас туда, куда вам нужно. В противном случае этого должно быть достаточно, чтобы познакомить вас с Power Query, и вы сможете настроить то, что вам нужно.

Из новой книги в Excel…

1. Выполните запрос Data — New — Из файла — из CSV с ленты Microsoft Excel. Это откроет «Редактор запросов Power»

2. Подтвердите / отрегулируйте, чтобы убедиться, что он правильно импортирует ваш начальный формат.

3. Переименуйте созданный вами запрос в «RawImport». (Найдите свойство «Name» на правой боковой панели.)

4. Создайте новый запрос, который начинается с текущего, с помощью ленты (в редакторе запросов Power), щелкнув Главная — Управление — Ссылка

5. Нажмите «Просмотр» и убедитесь, что флажок «Строка формул» на ленте установлен.

6. Измените имя этого запроса на «RawImportEvenOdd».

7. Нажмите Добавить столбец — Индексный столбец на ленте. Это должно создать новый столбец «Индекс», который начинается с 0 и увеличивается на 1.

8. Нажмите Добавить столбец — Пользовательский столбец на ленте. В диалоговом окне дайте ему имя «IsEvenRowNum» и формулу Number.Mod([Index], 2) = 0

9. Щелкните маленький значок «ABC 123» в левом углу заголовка вашего нового столбца «IsEvenRowNum». Измените его на тип «True / False». В этом нет необходимости, но поверьте мне и заведите привычку строго вводить свои данные уже сегодня. Однажды это спасет вас от самих себя.

10. Создайте новый запрос, в котором будут только четные строки, нажав Home — Manage — Reference

11. Переименуйте этот третий запрос в «RawImportEvenOnly»

12. Нажмите на маленькую кнопку фильтра на правом краю «IsEvenRowNum» и отфильтруйте запрос, чтобы показывать только строки, в которых находится IsEvenRowNum true

13. Нажмите Добавить столбец — Пользовательский столбец и в диалоговом окне назовите его «NewRowNum» с формулой [Index]/2

14. Нажмите Главная — Выберите столбцы и измените его так, чтобы были выбраны только КОМПАНИЯ, ГОРОД, СТРАНА и NewRowNum.

15. Дважды щелкните заголовок столбца «COMPANY» и добавьте «_E» в конец (переименовав его в «COMPANY_E»)

16. Повторите то же самое для ГОРОДА и СТРАНЫ

17. Создайте новый запрос, в котором будут только нечетные строки, нажав Home — Manage — Duplicate (примечание: дублировать, а не ссылаться)

18. Измените имя запроса на «RawImportOddOnly»

19. Нажмите на шаг Отфильтрованные строки в правой боковой панели, а затем отредактируйте отображаемую формулу в строке формул, чтобы изменить true на false

20. Нажмите на следующий шаг «Добавленный столбец», а затем отредактируйте формулу с = Table.AddColumn(#"Filtered Rows", "NewRowNum", each [Index]/2) на = Table.AddColumn(#"Filtered Rows", "NewRowNum", each ([Index]-1)/2)

21. На последнем шаге «Переименованные столбцы» измените его так, чтобы он добавлял «_O» вместо «_E» к КОМПАНИИ, ГОРОДУ и СТРАНЕ.

22. Если левая боковая панель еще не была расширена для вас, вы можете развернуть ее, чтобы просмотреть запросы, которые вы сделали до сих пор. Щелкните запрос «RawImportEvenOnly», чтобы вернуться к его редактированию.

23. Нажмите Главная — Управление — Ссылка, чтобы начать выполнение окончательного запроса.

24. Измените имя на то, что вам нравится.

25. Нажмите Главная — Объединить запросы и в диалоговом окне,

    1. Нажмите на заголовок столбца «NewRowNum», чтобы выделить его.
    2. Измените раскрывающийся список для второй таблицы на «RawImportOddOnly»
    3. Нажмите на заголовок столбца «NewRowNum» во второй таблице, чтобы выделить его.
    4. Нажмите Ok.

26. Хорошо, теперь значение нашего нового столбца для любой заданной строки является подтаблицей соответствующей строки из RawImportOddOnly, и нам нужно ее расширить. Вместо кнопки фильтра кнопка на правом краю нового столбца «RawImportOnly» представляет собой кнопку расширения. Щелкните по нему. В диалоговом окне убедитесь, что выбраны только COMPANY_O, CITY_O и COUNTRY_O, снимите флажок «Использовать исходное имя столбца в качестве префикса» в нижней части диалогового окна, затем нажмите «Ок»

27. Нажмите Добавить столбец — Пользовательский столбец, назовите его «КОМПАНИЯ» с формулой [COMPANY_E]amp;","amp;[COMPANY_O]

28. Нажмите Добавить столбец — Пользовательский столбец, назовите его «ГОРОД» с формулой [CITY_E]amp;","amp;[CITY_O]

29. Нажмите Добавить столбец — Пользовательский столбец, назовите его «СТРАНА» с формулой [COUNTRY_E]amp;","amp;[COUNTRY_O]

30. Нажмите Главная — Выберите столбцы и измените диалоговое окно, чтобы были выбраны только КОМПАНИЯ, ГОРОД И СТРАНА.

31. На главной ленте щелкните крошечную стрелку вниз в кнопке «Закрыть и загрузить» и выберите «Закрыть и загрузить», а затем выберите «Только создать соединение» и нажмите «Ок».

Теперь вы вернулись в Excel, и ничего не произошло (за исключением ваших новых запросов, появляющихся в боковой панели)! Это потому, что мы ничего не загружали в Excel, мы только создавали определения запросов. Мы сделали это, потому что только что создали множество запросов и не хотели сохранять результаты каждого из них; нам нужен только последний! В боковой панели щелкните правой кнопкой мыши на последнем созданном вами запросе, нажмите «Загрузить в …» и загрузите его на новый лист. Это выполнит всю работу, которую мы настроили с самого начала… и это здорово, потому что, если вы понимаете, что хотите изменить какую-либо часть того, как это было сделано, продолжайте, а затем обновите! Может быть, я вас неправильно понял, и вам нужно было сначала отсортировать их? Может быть, вы передумали и хотите разделять косыми чертами вместо запятых? Измените запросы и обновите результирующую таблицу, и ваш новый набор шагов будет выполнен.

Не описано, как поместить это в файл с разделителями, разделенный точкой с запятой, который вы ищете, но я предполагаю, что вы можете взять его оттуда?