将多列表格转换为两列。

huangapple
go评论95阅读模式
英文:

Convert multi column table into two column

问题

以下是您要翻译的代码部分：

  1. import pandas as pd
  2. data = pd.read_excel("https://geostat.ge/media/52189/visits-by-visited-region.xlsx", skiprows=[0])
  3. data.dropna(axis=0, inplace=True)
  4. data.drop(["Year", "Quarter", "Total", "Other regions"], axis=1, inplace=True)
  5. data["Country"] = "Georgia"
  6. print(data.head())

结果如下：

  1.        Tbilisi   Adjara A/R     Imereti  ...  Kvemo Kartli  Shida Kartli  Country
  2. 0   314.835582  106.598779  239.673489  ...     67.557908     80.719441  Georgia
  3. 3   261.135399   89.256953  201.047910  ...     65.957077     62.978426  Georgia
  4. 7   250.610817  104.218447  217.913793  ...     69.989150     68.448837  Georgia
  5. 11  263.794270  102.331560  197.538537  ...     61.636733     71.306660  Georgia
  6. 15  272.579135  123.327306  190.075834  ...     68.220004     64.016689  Georgia
  8. [5 rows x 10 columns]

如果您有关于如何使用melt函数将"Regions"转换成两列"Regions"和"Visitors"的问题，请提出。

英文:

let us suppose i want to analyze tourism visitors in different regions/cities of Georgia(Country), here is simple for getting data from the following site :
https://www.geostat.ge/en/modules/categories/101/domestic-tourism

  1. import pandas as pd
  2. data =pd.read_excel("https://geostat.ge/media/52189/visits-by-visited-region.xlsx",skiprows=[0])
  3. data.dropna(axis=0,inplace=True)
  4. data.drop(["Year","Quarter","Total","Other regions"],axis=1,inplace=True)
  5. data["Country"] ="Georgia"
  6. print(data.head())

result is :

  1.        Tbilisi  Adjara A/R     Imereti  ...  Kvemo Kartli  Shida Kartli  Country
  2. 0   314.835582  106.598779  239.673489  ...     67.557908     80.719441  Georgia
  3. 3   261.135399   89.256953  201.047910  ...     65.957077     62.978426  Georgia
  4. 7   250.610817  104.218447  217.913793  ...     69.989150     68.448837  Georgia
  5. 11  263.794270  102.331560  197.538537  ...     61.636733     71.306660  Georgia
  6. 15  272.579135  123.327306  190.075834  ...     68.220004     64.016689  Georgia
  8. [5 rows x 10 columns]

now instead of having Regions in columns i want to create a two column called as Regions and Second column as Visitors and place them according given table, i know that there is function melt, but how can i use in this? please help me

Edited : Country column should stay

答案1

得分: 2

以下是使用 pd.melt 的方法（请注意，Country 列是在最后分配的）：

  1. data.columns.name = 'Regions'
  2. data = pd.melt(data, value_name='Visitors').assign(Country='Georgia')
  3. print(data)
  1.                    Regions    Visitors  Country
  2. 0                  Tbilisi  314.835582  Georgia
  3. 1                  Tbilisi  261.135399  Georgia
  4. 2                  Tbilisi  250.610817  Georgia
  5. 3                  Tbilisi  263.794270  Georgia
  6. 4                  Tbilisi  272.579135  Georgia
  7. 5                  Tbilisi  298.764963  Georgia
  8. 6                  Tbilisi  305.112243  Georgia
  9. 7                  Tbilisi  261.974202  Georgia
  10. 8                  Tbilisi  331.928109  Georgia
  11. 9               Adjara A/R  106.598779  Georgia
  12. 10              Adjara A/R   89.256953  Georgia
  13. 11              Adjara A/R  104.218447  Georgia
  14. 12              Adjara A/R  102.331560  Georgia
  15. 13              Adjara A/R  123.327306  Georgia
  16. 14              Adjara A/R  129.820220  Georgia
  17. 15              Adjara A/R  115.448963  Georgia
  18. 16              Adjara A/R  140.962496  Georgia
  19. 17              Adjara A/R  122.815398  Georgia
  20. 18                 Imereti  239.673489  Georgia
  21. 19                 Imereti  201.047910  Georgia
  22. 20                 Imereti  217.913793  Georgia
  23. 21                 Imereti  197.538537  Georgia
  24. 22                 Imereti  190.075834  Georgia
  25. 23                 Imereti  213.599758  Georgia
  26. 24                 Imereti  178.503183  Georgia
  27. 25                 Imereti  210.046339  Georgia
  28. 26                 Imereti  211.687675  Georgia
  29. 27                 Kakheti   83.976312  Georgia
  30. 28                 Kakheti   82.903209  Georgia
  31. 29                 Kakheti   72.473283  Georgia
  32. 30                 Kakheti   69.910040  Georgia
  33. 31                 Kakheti   76.881032  Georgia
  34. 32                 Kakheti   76.714403  Georgia
  35. 33                 Kakheti   75.422254  Georgia
  36. 34                 Kakheti  101.719093  Georgia
  37. 35                 Kakheti  126.646269  Georgia
  38. 36       Mtskheta-Mtianeti   68.094149  Georgia
  39. 37       Mtskheta-Mtianeti   39.898430  Georgia
  40. 38       Mtskheta-Mtianeti   61.196603  Georgia
  41. 39       Mtskheta-Mtianeti   53.892373  Georgia
  42. 40       Mtskheta-Mtianeti   45.605619  Georgia
  43. 41       Mtskheta-Mtianeti   76.702671  Georgia
  44. 42       Mtskheta-Mtianeti   51.146409  Georgia
  45. 43       Mtskheta-Mtianeti  103.417359  Georgia
  46. 44       Mtskheta-Mtianeti  130.709472  Georgia
  47. 45  Samegrelo-Zemo Svaneti   78.144514  Georgia
  48. 46  Samegrelo-Zemo Svaneti   72.868002  Georgia
  49. 47  Samegrelo-Zemo Svaneti   73.109213  Georgia
  50. 48  Samegrelo-Zemo Svaneti   77.090360  Georgia
  51. 49  Samegrelo-Zemo Svaneti   74.340778  Georgia
  52. 50  Samegrelo-Zemo Svaneti   79.723074  Georgia
  53. 51  Samegrelo-Zemo Svaneti   58.731581  Georgia
  54. 52  Samegrelo-Zemo Svaneti   76.525402  Georgia
  55. 53  Samegrelo-Zemo Svaneti   88.756872  Georgia
  56. 54      Samtskhe-Javakheti   70.469410  Georgia
  57. 55      Samtskhe-Javakheti   62.890917  Georgia
  58. 56      Samtskhe-Javakheti   42.984470  Georgia
  59. 57      Samtskhe-Javakheti   46.061863  Georgia
  60. 58      Samtskhe-Javakheti   43.466759  Georgia
  61. 59      Samtskhe-Javakheti   54.557168  Georgia
  62. 60      Samtskhe-Javakheti   45.954961  Georgia
  63. 61      Samtskhe-Javakheti   52.756540  Georgia
  64. 62      Samtskhe-Javakheti   55.530282  Georgia
  65. 63            Kvemo Kartli   67.557908  Georgia
  66. 64            Kvemo Kartli   65.957077  Georgia
  67. 65            Kvemo Kartli   69.989150  Georgia
  68. 66            Kvemo Kartli   61.636733  Georgia
  69. 67            Kvemo Kartli   68.220004  Georgia
  70. 68            Kvemo Kartli   75.571344  Georgia
  71. 69            Kvemo Kartli   79.618624  Georgia
  72. 70            Kvemo Kartli  127.320872  Georgia
  73. 71            Kvemo Kartli  122.897431  Georgia
  74. 72            Shida Kartli   80.719441  Georgia
  75. 73            Shida Kartli   62.978426  Georgia
  76. 74            Shida Kartli   68.448837  Georgia
  77. 75            Shida Kartli   71.306660  Georgia
  78. 76            Shida Kartli   64.016689  Georgia
  79. 77            Shida Kartli   66.703733  Georgia
  80. 78            Shida Kartli   84.690400  Georgia
  81. 79            Shida Kartli  114.779793  Georgia
  82. 80            Shida Kartli  137.791
英文:

Use the following approach with pd.melt:<br>
(note that Country column is assigned at the end)

  1. data.columns.name = &#39;Regions&#39;
  2. data = pd.melt(data, value_name=&#39;Visitors&#39;).assign(Country=&#39;Georgia&#39;)
  3. print(data)
  1.                    Regions    Visitors  Country
  2. 0                  Tbilisi  314.835582  Georgia
  3. 1                  Tbilisi  261.135399  Georgia
  4. 2                  Tbilisi  250.610817  Georgia
  5. 3                  Tbilisi  263.794270  Georgia
  6. 4                  Tbilisi  272.579135  Georgia
  7. 5                  Tbilisi  298.764963  Georgia
  8. 6                  Tbilisi  305.112243  Georgia
  9. 7                  Tbilisi  261.974202  Georgia
  10. 8                  Tbilisi  331.928109  Georgia
  11. 9               Adjara A/R  106.598779  Georgia
  12. 10              Adjara A/R   89.256953  Georgia
  13. 11              Adjara A/R  104.218447  Georgia
  14. 12              Adjara A/R  102.331560  Georgia
  15. 13              Adjara A/R  123.327306  Georgia
  16. 14              Adjara A/R  129.820220  Georgia
  17. 15              Adjara A/R  115.448963  Georgia
  18. 16              Adjara A/R  140.962496  Georgia
  19. 17              Adjara A/R  122.815398  Georgia
  20. 18                 Imereti  239.673489  Georgia
  21. 19                 Imereti  201.047910  Georgia
  22. 20                 Imereti  217.913793  Georgia
  23. 21                 Imereti  197.538537  Georgia
  24. 22                 Imereti  190.075834  Georgia
  25. 23                 Imereti  213.599758  Georgia
  26. 24                 Imereti  178.503183  Georgia
  27. 25                 Imereti  210.046339  Georgia
  28. 26                 Imereti  211.687675  Georgia
  29. 27                 Kakheti   83.976312  Georgia
  30. 28                 Kakheti   82.903209  Georgia
  31. 29                 Kakheti   72.473283  Georgia
  32. 30                 Kakheti   69.910040  Georgia
  33. 31                 Kakheti   76.881032  Georgia
  34. 32                 Kakheti   76.714403  Georgia
  35. 33                 Kakheti   75.422254  Georgia
  36. 34                 Kakheti  101.719093  Georgia
  37. 35                 Kakheti  126.646269  Georgia
  38. 36       Mtskheta-Mtianeti   68.094149  Georgia
  39. 37       Mtskheta-Mtianeti   39.898430  Georgia
  40. 38       Mtskheta-Mtianeti   61.196603  Georgia
  41. 39       Mtskheta-Mtianeti   53.892373  Georgia
  42. 40       Mtskheta-Mtianeti   45.605619  Georgia
  43. 41       Mtskheta-Mtianeti   76.702671  Georgia
  44. 42       Mtskheta-Mtianeti   51.146409  Georgia
  45. 43       Mtskheta-Mtianeti  103.417359  Georgia
  46. 44       Mtskheta-Mtianeti  130.709472  Georgia
  47. 45  Samegrelo-Zemo Svaneti   78.144514  Georgia
  48. 46  Samegrelo-Zemo Svaneti   72.868002  Georgia
  49. 47  Samegrelo-Zemo Svaneti   73.109213  Georgia
  50. 48  Samegrelo-Zemo Svaneti   77.090360  Georgia
  51. 49  Samegrelo-Zemo Svaneti   74.340778  Georgia
  52. 50  Samegrelo-Zemo Svaneti   79.723074  Georgia
  53. 51  Samegrelo-Zemo Svaneti   58.731581  Georgia
  54. 52  Samegrelo-Zemo Svaneti   76.525402  Georgia
  55. 53  Samegrelo-Zemo Svaneti   88.756872  Georgia
  56. 54      Samtskhe-Javakheti   70.469410  Georgia
  57. 55      Samtskhe-Javakheti   62.890917  Georgia
  58. 56      Samtskhe-Javakheti   42.984470  Georgia
  59. 57      Samtskhe-Javakheti   46.061863  Georgia
  60. 58      Samtskhe-Javakheti   43.466759  Georgia
  61. 59      Samtskhe-Javakheti   54.557168  Georgia
  62. 60      Samtskhe-Javakheti   45.954961  Georgia
  63. 61      Samtskhe-Javakheti   52.756540  Georgia
  64. 62      Samtskhe-Javakheti   55.530282  Georgia
  65. 63            Kvemo Kartli   67.557908  Georgia
  66. 64            Kvemo Kartli   65.957077  Georgia
  67. 65            Kvemo Kartli   69.989150  Georgia
  68. 66            Kvemo Kartli   61.636733  Georgia
  69. 67            Kvemo Kartli   68.220004  Georgia
  70. 68            Kvemo Kartli   75.571344  Georgia
  71. 69            Kvemo Kartli   79.618624  Georgia
  72. 70            Kvemo Kartli  127.320872  Georgia
  73. 71            Kvemo Kartli  122.897431  Georgia
  74. 72            Shida Kartli   80.719441  Georgia
  75. 73            Shida Kartli   62.978426  Georgia
  76. 74            Shida Kartli   68.448837  Georgia
  77. 75            Shida Kartli   71.306660  Georgia
  78. 76            Shida Kartli   64.016689  Georgia
  79. 77            Shida Kartli   66.703733  Georgia
  80. 78            Shida Kartli   84.690400  Georgia
  81. 79            Shida Kartli  114.779793  Georgia
  82. 80            Shida Kartli  137.791063  Georgia

答案2

得分: 1

根据 @RomanPerekhrest 的回答，这是我的最终代码 - 仅供我自己使用，也供其他用户使用，如果他们有相同的问题：

  1. import pandas as pd
  2. data = pd.read_excel("https://geostat.ge/media/52189/visits-by-visited-region.xlsx", skiprows=[0])
  3. # data.dropna(axis=0, inplace=True)
  4. data.drop(["Year", "Quarter", "Total", "Other regions"], axis=1, inplace=True)
  5. data["Country"] = "Georgia"
  6. data.columns.name = 'Regions'
  7. data = pd.melt(data, value_name='Visitors').assign(Country='Georgia')
  8. data = data[data["Regions"].str.contains("Country") == False]
  9. data.dropna(axis=0, inplace=True)
  10. print(data)

结果如下：

  1.              Regions    Visitors  Country
  2. 0         Tbilisi  314.835582  Georgia
  3. 1         Tbilisi  304.596833  Georgia
  4. 2         Tbilisi  268.638159  Georgia
  5. 3         Tbilisi  261.135399  Georgia
  6. 4         Tbilisi  254.392611  Georgia
  7. ..            ...         ...      ...
  8. 326  Shida Kartli   135.76658  Georgia
  9. 327  Shida Kartli  137.791063  Georgia
  10. 328  Shida Kartli   84.743258  Georgia
  11. 329  Shida Kartli   96.737279  Georgia
  12. 330  Shida Kartli  100.517074  Georgia
英文:

Based on @RomanPerekhrest answer, here is my final code -it is just for me and also for other users if they would have same question :

  1. import pandas as pd
  2. data =pd.read_excel(&quot;https://geostat.ge/media/52189/visits-by-visited-region.xlsx&quot;,skiprows=[0])
  3. # data.dropna(axis=0,inplace=True)
  4. data.drop([&quot;Year&quot;,&quot;Quarter&quot;,&quot;Total&quot;,&quot;Other regions&quot;],axis=1,inplace=True)
  5. data[&quot;Country&quot;] =&quot;Georgia&quot;
  6. data.columns.name = &#39;Regions&#39;
  7. data = pd.melt(data, value_name=&#39;Visitors&#39;).assign(Country=&#39;Georgia&#39;)
  8. data = data[data[&quot;Regions&quot;].str.contains(&quot;Country&quot;) == False]
  9. data.dropna(axis=0,inplace=True)
  10. print(data)

result is :

  1.          Regions    Visitors  Country
  2. 0         Tbilisi  314.835582  Georgia
  3. 1         Tbilisi  304.596833  Georgia
  4. 2         Tbilisi  268.638159  Georgia
  5. 3         Tbilisi  261.135399  Georgia
  6. 4         Tbilisi  254.392611  Georgia
  7. ..            ...         ...      ...
  8. 326  Shida Kartli   135.76658  Georgia
  9. 327  Shida Kartli  137.791063  Georgia
  10. 328  Shida Kartli   84.743258  Georgia
  11. 329  Shida Kartli   96.737279  Georgia
  12. 330  Shida Kartli  100.517074  Georgia

huangapple
  • 本文由 发表于 2023年5月22日 03:27:57
  • 转载请务必保留本文链接：https://go.coder-hub.com/76301579.html
  • pandas
  • python
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定