英文:
Convert multi column table into two column
问题
以下是您要翻译的代码部分:
import pandas as pd
data = pd.read_excel("https://geostat.ge/media/52189/visits-by-visited-region.xlsx", skiprows=[0])
data.dropna(axis=0, inplace=True)
data.drop(["Year", "Quarter", "Total", "Other regions"], axis=1, inplace=True)
data["Country"] = "Georgia"
print(data.head())
结果如下:
Tbilisi Adjara A/R Imereti ... Kvemo Kartli Shida Kartli Country
0 314.835582 106.598779 239.673489 ... 67.557908 80.719441 Georgia
3 261.135399 89.256953 201.047910 ... 65.957077 62.978426 Georgia
7 250.610817 104.218447 217.913793 ... 69.989150 68.448837 Georgia
11 263.794270 102.331560 197.538537 ... 61.636733 71.306660 Georgia
15 272.579135 123.327306 190.075834 ... 68.220004 64.016689 Georgia
[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
import pandas as pd
data =pd.read_excel("https://geostat.ge/media/52189/visits-by-visited-region.xlsx",skiprows=[0])
data.dropna(axis=0,inplace=True)
data.drop(["Year","Quarter","Total","Other regions"],axis=1,inplace=True)
data["Country"] ="Georgia"
print(data.head())
result is :
Tbilisi Adjara A/R Imereti ... Kvemo Kartli Shida Kartli Country
0 314.835582 106.598779 239.673489 ... 67.557908 80.719441 Georgia
3 261.135399 89.256953 201.047910 ... 65.957077 62.978426 Georgia
7 250.610817 104.218447 217.913793 ... 69.989150 68.448837 Georgia
11 263.794270 102.331560 197.538537 ... 61.636733 71.306660 Georgia
15 272.579135 123.327306 190.075834 ... 68.220004 64.016689 Georgia
[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
列是在最后分配的):
data.columns.name = 'Regions'
data = pd.melt(data, value_name='Visitors').assign(Country='Georgia')
print(data)
Regions Visitors Country
0 Tbilisi 314.835582 Georgia
1 Tbilisi 261.135399 Georgia
2 Tbilisi 250.610817 Georgia
3 Tbilisi 263.794270 Georgia
4 Tbilisi 272.579135 Georgia
5 Tbilisi 298.764963 Georgia
6 Tbilisi 305.112243 Georgia
7 Tbilisi 261.974202 Georgia
8 Tbilisi 331.928109 Georgia
9 Adjara A/R 106.598779 Georgia
10 Adjara A/R 89.256953 Georgia
11 Adjara A/R 104.218447 Georgia
12 Adjara A/R 102.331560 Georgia
13 Adjara A/R 123.327306 Georgia
14 Adjara A/R 129.820220 Georgia
15 Adjara A/R 115.448963 Georgia
16 Adjara A/R 140.962496 Georgia
17 Adjara A/R 122.815398 Georgia
18 Imereti 239.673489 Georgia
19 Imereti 201.047910 Georgia
20 Imereti 217.913793 Georgia
21 Imereti 197.538537 Georgia
22 Imereti 190.075834 Georgia
23 Imereti 213.599758 Georgia
24 Imereti 178.503183 Georgia
25 Imereti 210.046339 Georgia
26 Imereti 211.687675 Georgia
27 Kakheti 83.976312 Georgia
28 Kakheti 82.903209 Georgia
29 Kakheti 72.473283 Georgia
30 Kakheti 69.910040 Georgia
31 Kakheti 76.881032 Georgia
32 Kakheti 76.714403 Georgia
33 Kakheti 75.422254 Georgia
34 Kakheti 101.719093 Georgia
35 Kakheti 126.646269 Georgia
36 Mtskheta-Mtianeti 68.094149 Georgia
37 Mtskheta-Mtianeti 39.898430 Georgia
38 Mtskheta-Mtianeti 61.196603 Georgia
39 Mtskheta-Mtianeti 53.892373 Georgia
40 Mtskheta-Mtianeti 45.605619 Georgia
41 Mtskheta-Mtianeti 76.702671 Georgia
42 Mtskheta-Mtianeti 51.146409 Georgia
43 Mtskheta-Mtianeti 103.417359 Georgia
44 Mtskheta-Mtianeti 130.709472 Georgia
45 Samegrelo-Zemo Svaneti 78.144514 Georgia
46 Samegrelo-Zemo Svaneti 72.868002 Georgia
47 Samegrelo-Zemo Svaneti 73.109213 Georgia
48 Samegrelo-Zemo Svaneti 77.090360 Georgia
49 Samegrelo-Zemo Svaneti 74.340778 Georgia
50 Samegrelo-Zemo Svaneti 79.723074 Georgia
51 Samegrelo-Zemo Svaneti 58.731581 Georgia
52 Samegrelo-Zemo Svaneti 76.525402 Georgia
53 Samegrelo-Zemo Svaneti 88.756872 Georgia
54 Samtskhe-Javakheti 70.469410 Georgia
55 Samtskhe-Javakheti 62.890917 Georgia
56 Samtskhe-Javakheti 42.984470 Georgia
57 Samtskhe-Javakheti 46.061863 Georgia
58 Samtskhe-Javakheti 43.466759 Georgia
59 Samtskhe-Javakheti 54.557168 Georgia
60 Samtskhe-Javakheti 45.954961 Georgia
61 Samtskhe-Javakheti 52.756540 Georgia
62 Samtskhe-Javakheti 55.530282 Georgia
63 Kvemo Kartli 67.557908 Georgia
64 Kvemo Kartli 65.957077 Georgia
65 Kvemo Kartli 69.989150 Georgia
66 Kvemo Kartli 61.636733 Georgia
67 Kvemo Kartli 68.220004 Georgia
68 Kvemo Kartli 75.571344 Georgia
69 Kvemo Kartli 79.618624 Georgia
70 Kvemo Kartli 127.320872 Georgia
71 Kvemo Kartli 122.897431 Georgia
72 Shida Kartli 80.719441 Georgia
73 Shida Kartli 62.978426 Georgia
74 Shida Kartli 68.448837 Georgia
75 Shida Kartli 71.306660 Georgia
76 Shida Kartli 64.016689 Georgia
77 Shida Kartli 66.703733 Georgia
78 Shida Kartli 84.690400 Georgia
79 Shida Kartli 114.779793 Georgia
80 Shida Kartli 137.791
英文:
Use the following approach with pd.melt
:<br>
(note that Country
column is assigned at the end)
data.columns.name = 'Regions'
data = pd.melt(data, value_name='Visitors').assign(Country='Georgia')
print(data)
Regions Visitors Country
0 Tbilisi 314.835582 Georgia
1 Tbilisi 261.135399 Georgia
2 Tbilisi 250.610817 Georgia
3 Tbilisi 263.794270 Georgia
4 Tbilisi 272.579135 Georgia
5 Tbilisi 298.764963 Georgia
6 Tbilisi 305.112243 Georgia
7 Tbilisi 261.974202 Georgia
8 Tbilisi 331.928109 Georgia
9 Adjara A/R 106.598779 Georgia
10 Adjara A/R 89.256953 Georgia
11 Adjara A/R 104.218447 Georgia
12 Adjara A/R 102.331560 Georgia
13 Adjara A/R 123.327306 Georgia
14 Adjara A/R 129.820220 Georgia
15 Adjara A/R 115.448963 Georgia
16 Adjara A/R 140.962496 Georgia
17 Adjara A/R 122.815398 Georgia
18 Imereti 239.673489 Georgia
19 Imereti 201.047910 Georgia
20 Imereti 217.913793 Georgia
21 Imereti 197.538537 Georgia
22 Imereti 190.075834 Georgia
23 Imereti 213.599758 Georgia
24 Imereti 178.503183 Georgia
25 Imereti 210.046339 Georgia
26 Imereti 211.687675 Georgia
27 Kakheti 83.976312 Georgia
28 Kakheti 82.903209 Georgia
29 Kakheti 72.473283 Georgia
30 Kakheti 69.910040 Georgia
31 Kakheti 76.881032 Georgia
32 Kakheti 76.714403 Georgia
33 Kakheti 75.422254 Georgia
34 Kakheti 101.719093 Georgia
35 Kakheti 126.646269 Georgia
36 Mtskheta-Mtianeti 68.094149 Georgia
37 Mtskheta-Mtianeti 39.898430 Georgia
38 Mtskheta-Mtianeti 61.196603 Georgia
39 Mtskheta-Mtianeti 53.892373 Georgia
40 Mtskheta-Mtianeti 45.605619 Georgia
41 Mtskheta-Mtianeti 76.702671 Georgia
42 Mtskheta-Mtianeti 51.146409 Georgia
43 Mtskheta-Mtianeti 103.417359 Georgia
44 Mtskheta-Mtianeti 130.709472 Georgia
45 Samegrelo-Zemo Svaneti 78.144514 Georgia
46 Samegrelo-Zemo Svaneti 72.868002 Georgia
47 Samegrelo-Zemo Svaneti 73.109213 Georgia
48 Samegrelo-Zemo Svaneti 77.090360 Georgia
49 Samegrelo-Zemo Svaneti 74.340778 Georgia
50 Samegrelo-Zemo Svaneti 79.723074 Georgia
51 Samegrelo-Zemo Svaneti 58.731581 Georgia
52 Samegrelo-Zemo Svaneti 76.525402 Georgia
53 Samegrelo-Zemo Svaneti 88.756872 Georgia
54 Samtskhe-Javakheti 70.469410 Georgia
55 Samtskhe-Javakheti 62.890917 Georgia
56 Samtskhe-Javakheti 42.984470 Georgia
57 Samtskhe-Javakheti 46.061863 Georgia
58 Samtskhe-Javakheti 43.466759 Georgia
59 Samtskhe-Javakheti 54.557168 Georgia
60 Samtskhe-Javakheti 45.954961 Georgia
61 Samtskhe-Javakheti 52.756540 Georgia
62 Samtskhe-Javakheti 55.530282 Georgia
63 Kvemo Kartli 67.557908 Georgia
64 Kvemo Kartli 65.957077 Georgia
65 Kvemo Kartli 69.989150 Georgia
66 Kvemo Kartli 61.636733 Georgia
67 Kvemo Kartli 68.220004 Georgia
68 Kvemo Kartli 75.571344 Georgia
69 Kvemo Kartli 79.618624 Georgia
70 Kvemo Kartli 127.320872 Georgia
71 Kvemo Kartli 122.897431 Georgia
72 Shida Kartli 80.719441 Georgia
73 Shida Kartli 62.978426 Georgia
74 Shida Kartli 68.448837 Georgia
75 Shida Kartli 71.306660 Georgia
76 Shida Kartli 64.016689 Georgia
77 Shida Kartli 66.703733 Georgia
78 Shida Kartli 84.690400 Georgia
79 Shida Kartli 114.779793 Georgia
80 Shida Kartli 137.791063 Georgia
答案2
得分: 1
根据 @RomanPerekhrest 的回答,这是我的最终代码 - 仅供我自己使用,也供其他用户使用,如果他们有相同的问题:
import pandas as pd
data = pd.read_excel("https://geostat.ge/media/52189/visits-by-visited-region.xlsx", skiprows=[0])
# data.dropna(axis=0, inplace=True)
data.drop(["Year", "Quarter", "Total", "Other regions"], axis=1, inplace=True)
data["Country"] = "Georgia"
data.columns.name = 'Regions'
data = pd.melt(data, value_name='Visitors').assign(Country='Georgia')
data = data[data["Regions"].str.contains("Country") == False]
data.dropna(axis=0, inplace=True)
print(data)
结果如下:
Regions Visitors Country
0 Tbilisi 314.835582 Georgia
1 Tbilisi 304.596833 Georgia
2 Tbilisi 268.638159 Georgia
3 Tbilisi 261.135399 Georgia
4 Tbilisi 254.392611 Georgia
.. ... ... ...
326 Shida Kartli 135.76658 Georgia
327 Shida Kartli 137.791063 Georgia
328 Shida Kartli 84.743258 Georgia
329 Shida Kartli 96.737279 Georgia
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 :
import pandas as pd
data =pd.read_excel("https://geostat.ge/media/52189/visits-by-visited-region.xlsx",skiprows=[0])
# data.dropna(axis=0,inplace=True)
data.drop(["Year","Quarter","Total","Other regions"],axis=1,inplace=True)
data["Country"] ="Georgia"
data.columns.name = 'Regions'
data = pd.melt(data, value_name='Visitors').assign(Country='Georgia')
data = data[data["Regions"].str.contains("Country") == False]
data.dropna(axis=0,inplace=True)
print(data)
result is :
Regions Visitors Country
0 Tbilisi 314.835582 Georgia
1 Tbilisi 304.596833 Georgia
2 Tbilisi 268.638159 Georgia
3 Tbilisi 261.135399 Georgia
4 Tbilisi 254.392611 Georgia
.. ... ... ...
326 Shida Kartli 135.76658 Georgia
327 Shida Kartli 137.791063 Georgia
328 Shida Kartli 84.743258 Georgia
329 Shida Kartli 96.737279 Georgia
330 Shida Kartli 100.517074 Georgia
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论