#python #python-3.x #pivot
#python #python-3.x #сводная
Вопрос:
У меня есть фрейм данных, который выглядит следующим образом:
ACCEPTANCES_EXECUTED_FOR_ACCT____OUT ACCUMULATED_OTH_COMPREHENSIVE_INCOME ALLL_AMT AUDIT_INDICATOR AVAILABLE_FOR_SALE_SECURITIES COMMON_STOCK file
$- $- $979.00 $1.00 $- $1,200.00 FFIEC CDR Call Schedule RC0 03312001.txt
$- $93.00 $127.00 $4.00 $8,546.00 $120.00 FFIEC CDR Call Schedule RC0 03312001.txt
$- $246.00 $278.00 $1.00 $27,398.00 $- FFIEC CDR Call Schedule RC0 03312002.txt
$- $19.00 $130.00 $4.00 $4,047.00 $25.00 FFIEC CDR Call Schedule RC0 03312002.txt
$- $125.00 $1,188.00 $1.00 $21,265.00 $596.00 FFIEC CDR Call Schedule RC0 03312002.txt
$- $- $164.00 $4.00 $- $60.00 FFIEC CDR Call Schedule RC0 03312002.txt
$- $800.00 $2,115.00 $1.00 $55,699.00 $5,400.00 FFIEC CDR Call Schedule RC0 03312003.txt
$- $199.00 $2,372.00 $2.00 $32,306.00 $19.00 FFIEC CDR Call Schedule RC0 03312003.txt
$- $174.00 $1,114.00 $5.00 $18,296.00 $1,600.00 FFIEC CDR Call Schedule RC0 03312004.txt
$- $31.00 $323.00 $5.00 $2,997.00 $240.00 FFIEC CDR Call Schedule RC0 03312004.txt
Как я могу повернуть данные так, чтобы они выглядели так?
Code Schedule Line Item Q12001 Q22002 Q32003 Q42004
RC0 BalanceSheet ACCEPTANCES_EXECUTED_FOR_ACCT____OUT $- $- $- $-
RC0 BalanceSheet ACCUMULATED_OTH_COMPREHENSIVE_INCOME $93.00 390 $999.00 $205.00
RC0 BalanceSheet ALLL_AMT $1,060.00 $1,760.00 $4,487.00 $1,437.00
RC0 BalanceSheet AUDIT_INDICATOR $5.00 $10.00 $3.00 $10.00
RC0 BalanceSheet AVAILABLE_FOR_SALE_SECURITIES $8,546.00 $52,710.00 $88,050.00 $21,293.00
RC0 BalanceSheet COMMON_STOCK $1,320.00 $681.00 $5,419.00 $1,840.00
Я не вижу этого, это стандартное упражнение по сводке, потому что мне нужно разобрать имя файла на кварталы и годы, так что это: ‘Расписание вызовов FFIEC CDR RC0 03312001.txt ‘
Становится следующим: ‘Q12001’
- Я хотел бы добавить «Элемент строки» в качестве заголовка
- Я бы хотел, чтобы в самом первом столбце был заголовок ‘Code’, а под ним должно быть имя файла, которое является ‘RC0’.
- Я хотел бы добавить
Это выглядит примерно так…
output = df_append.pivot(index='headers', columns='file')
print(output)
Но, конечно, нет столбца с именем «заголовки», и я не могу понять, как разобрать файл на кварталы и годы. В этом фрейме данных более 100 полей, а «файл» насчитывает почти 20 лет, разделенных на кварталы и годы. Возможно ли это вообще ?!
Ответ №1:
Хотя это немного сложно, я смог решить эту проблему.
Шаги:
1. Настройте фрейм данных
2. Очистите данные, удалив $, запятую и тире. Затем преобразуйте данные в значения с плавающей запятой
3. Извлеките код (RC0, RC1 …) и дату (MMDDYYYY) из filename
4. Создайте квартальный период с даты
5. Группируйте по коду и квартальному периоду, чтобы получить сумму каждой позиции
6. Транспонируйте результат для создания в запрошенном формате
7. Сделайте квартальный период заголовком столбца и удалите квартальную строку
8. Переименуйте имя оси в строку в соответствии с вашим запросом
9. Добавьте расписание в качестве имени столбца в соответствии с вашим запросом
10. Распечатайте окончательный фрейм данных
Вот код для этого.
import pandas as pd
import numpy as np
#Dataframe setup activities
c = ['ACCEPTANCES_EXECUTED_FOR_ACCT____OUT',
'ACCUMULATED_OTH_COMPREHENSIVE_INCOME',
'ALLL_AMT',
'AUDIT_INDICATOR',
'AVAILABLE_FOR_SALE_SECURITIES',
'COMMON_STOCK',
'file']
d = [
['$-','$-' ,'$979.00' ,'$1.00','$-' ,'$1,200.00','FFIEC CDR Call Schedule RC0 03312001.txt'],
['$-','$93.00' ,'$127.00' ,'$4.00','$8,546.00' ,'$120.00' ,'FFIEC CDR Call Schedule RC0 03312001.txt'],
['$-','$246.00','$278.00' ,'$1.00','$27,398.00','$-' ,'FFIEC CDR Call Schedule RC0 03312002.txt'],
['$-','$19.00' ,'$130.00' ,'$4.00','$4,047.00' ,'$25.00' ,'FFIEC CDR Call Schedule RC0 03312002.txt'],
['$-','$125.00','$1,188.00','$1.00','$21,265.00','$596.00' ,'FFIEC CDR Call Schedule RC0 03312002.txt'],
['$-','$-' ,'$164.00' ,'$4.00','$-' ,'$60.00' ,'FFIEC CDR Call Schedule RC1 03312002.txt'],
['$-','$800.00','$2,115.00','$1.00','$55,699.00','$5,400.00','FFIEC CDR Call Schedule RC1 03312003.txt'],
['$-','$199.00','$2,372.00','$2.00','$32,306.00','$19.00' ,'FFIEC CDR Call Schedule RC0 03312003.txt'],
['$-','$174.00','$1,114.00','$5.00','$18,296.00','$1,600.00','FFIEC CDR Call Schedule RC1 03312004.txt'],
['$-','$31.00' ,'$323.00' ,'$5.00','$2,997.00' ,'$240.00' ,'FFIEC CDR Call Schedule RC0 03312004.txt']]
df = pd.DataFrame(d,columns=c)
#Dataframe cleanup activities. Remove $ sign, comma and dash to enable computing
df[df.columns[:6]] = df[df.columns[:6]].apply(lambda x: x.str.replace('[$,]','',regex=True).replace('-','0',regex=True)).astype(np.float64)
#Extract code from the filename
df['code'] = df['file'].str[24:27]
#Extract Date (MMDDYYYY) from file name
df['year'] = pd.to_datetime(df['file'].str[28:36],format='%m%d%Y').dt.strftime('%m/%d/%Y')
#Create a Quarterly Period from the date
df['qyear'] = pd.PeriodIndex(df['year'], freq='Q')
#Calculate the sum based on Quarteryly values using Groupby
#Transpose to create the dataframe in the format you wanted. Store it into df1
df1 = df.groupby(['code','qyear']).agg({'ACCEPTANCES_EXECUTED_FOR_ACCT____OUT':'sum',
'ACCUMULATED_OTH_COMPREHENSIVE_INCOME':'sum',
'ALLL_AMT':'sum',
'AUDIT_INDICATOR':'sum',
'AVAILABLE_FOR_SALE_SECURITIES':'sum',
'COMMON_STOCK':'sum'}).reset_index().T
#Make Quarterly Period as the column header and delete the quarterly row
df1.columns = df1.loc['qyear']
df1.drop(['qyear'],axis=0,inplace=True)
#Rename the axis name to Line Item as per your request
df1.rename_axis('Line Item',axis="columns",inplace=True)
#Add Schedule as the column name as per your request
df1['Schedule'] = 'BalanceSheet'
#You now have the final dataframe as per your request
print (df1)
Обратите внимание, что если filename имеет значения больше 1 code (RC0, то для каждого RC-кода настраиваются отдельные наборы квартальных периодов (в виде столбцов). Из-за этого мы не можем обобщить код на RC0. Кроме того, у меня возникают некоторые трудности при вычислении значений позиций строк для кода RC и добавлении их в виде отдельных строк. Глядя на то, как организованы данные, лучше всего, чтобы RC0 и RC1 с квартальными периодами отображались в отдельных столбцах, а не дублировали позиции для каждой группы RC.
Окончательный фрейм данных выглядит следующим образом (обратите внимание, что в этом у меня есть RC0 и RC1):
Line Item 2001Q1 2002Q1 2003Q1 2004Q1 2002Q1
code RC0 RC0 RC0 RC0 RC1
ACCEPTANCES_EXECUTED_FOR_ACCT____OUT 0 0 0 0 0
ACCUMULATED_OTH_COMPREHENSIVE_INCOME 93 390 199 31 0
ALLL_AMT 1106 1596 2372 323 164
AUDIT_INDICATOR 5 6 2 5 4
AVAILABLE_FOR_SALE_SECURITIES 8546 52710 32306 2997 0
COMMON_STOCK 1320 621 19 240 60
Line Item 2003Q1 2004Q1 Schedule
code RC1 RC1 BalanceSheet
ACCEPTANCES_EXECUTED_FOR_ACCT____OUT 0 0 BalanceSheet
ACCUMULATED_OTH_COMPREHENSIVE_INCOME 800 174 BalanceSheet
ALLL_AMT 2115 1114 BalanceSheet
AUDIT_INDICATOR 1 5 BalanceSheet
AVAILABLE_FOR_SALE_SECURITIES 55699 18296 BalanceSheet
COMMON_STOCK 5400 1600 BalanceSheet
Вернулся и посмотрел на ваш запрос. Похоже, вы также хотели напечатать знак $ для всех значений в долларах.
Вместо преобразования фрейма данных я делаю это чуть позже. Это позволяет мне добавлять знак $ к значениям.
df1 =
df.groupby(['code','qyear']).agg({'ACCEPTANCES_EXECUTED_FOR_ACCT____OUT':'sum',
'ACCUMULATED_OTH_COMPREHENSIVE_INCOME':'sum',
'ALLL_AMT':'sum',
'AUDIT_INDICATOR':'sum',
'AVAILABLE_FOR_SALE_SECURITIES':'sum',
'COMMON_STOCK':'sum'}).reset_index()
cols = df1.columns[2:]
df1.loc[:, cols] = df1[cols].astype(float).applymap('${:,.2f}'.format)
df1 = df1.T
df1.columns = df1.loc['qyear']
Если я преобразую данные, то RC0/1… также появится, и я не могу добавить значение в размере $. Так что делай это до того, как я трансформируюсь.
Результат выглядит следующим образом:
Line Item 2001Q1 2002Q1 2003Q1
code RC0 RC0 RC0
ACCEPTANCES_EXECUTED_FOR_ACCT____OUT $0.00 $0.00 $0.00
ACCUMULATED_OTH_COMPREHENSIVE_INCOME $93.00 $390.00 $199.00
ALLL_AMT $1,106.00 $1,596.00 $2,372.00
AUDIT_INDICATOR $5.00 $6.00 $2.00
AVAILABLE_FOR_SALE_SECURITIES $8,546.00 $52,710.00 $32,306.00
COMMON_STOCK $1,320.00 $621.00 $19.00
Line Item 2004Q1 2002Q1 2003Q1
code RC0 RC1 RC1
ACCEPTANCES_EXECUTED_FOR_ACCT____OUT $0.00 $0.00 $0.00
ACCUMULATED_OTH_COMPREHENSIVE_INCOME $31.00 $0.00 $800.00
ALLL_AMT $323.00 $164.00 $2,115.00
AUDIT_INDICATOR $5.00 $4.00 $1.00
AVAILABLE_FOR_SALE_SECURITIES $2,997.00 $0.00 $55,699.00
COMMON_STOCK $240.00 $60.00 $5,400.00
Line Item 2004Q1 Schedule
code RC1 BalanceSheet
ACCEPTANCES_EXECUTED_FOR_ACCT____OUT $0.00 BalanceSheet
ACCUMULATED_OTH_COMPREHENSIVE_INCOME $174.00 BalanceSheet
ALLL_AMT $1,114.00 BalanceSheet
AUDIT_INDICATOR $5.00 BalanceSheet
AVAILABLE_FOR_SALE_SECURITIES $18,296.00 BalanceSheet
COMMON_STOCK $1,600.00 BalanceSheet
Если вы хотите заменить все на $0.00 , $- то вы можете сделать:
df1.replace(to_replace = '$0.00', value = '$-', inplace = True)
Если у вас есть разные столбцы и вы хотите объединить их все, то сделайте это.
Предполагая, что последний столбец file равен, и вы хотите объединить все столбцы от индекса 0 до предпоследнего, вы можете сделать это:
cagg = {cx:'sum' for cx in df.columns[:-1]}
df1 = df.groupby(['code','qyear']).agg(cagg).reset_index()
Поскольку вы собираетесь добавить 3 столбца ближе к концу:
df['code'] = df['file'].str[24:27]
df['year'] = pd.to_datetime(df['file'].str[28:36],format='%m%d%Y').dt.strftime('%m/%d/%Y')
df['qyear'] = pd.PeriodIndex(df['year'], freq='Q')
Вы можете изменить код следующим образом [:-4], чтобы исключить столбцы file code year qyear:
df = pd.DataFrame(d,columns=c)
df[df.columns[:6]] = df[df.columns[:6]].apply(lambda x: x.str.replace('[$,]','',regex=True).replace('-','0',regex=True)).astype(np.float64)
df['code'] = df['file'].str[24:27]
df['year'] = pd.to_datetime(df['file'].str[28:36],format='%m%d%Y').dt.strftime('%m/%d/%Y')
df['qyear'] = pd.PeriodIndex(df['year'], freq='Q')
cagg = {cx:'sum' for cx in df.columns[:-4]}
df1 = df.groupby(['code','qyear']).agg(cagg).reset_index()
Комментарии:
1. Да, да! Это довольно сексуально! Ваша версия определенно работает. Мне просто нужно адаптировать это к моим фактическим данным. Большое спасибо!!
2. Быстрый вопрос: вместо этого… Акцепты_выполняются_для_Счет____из’:’сумма’, ‘ACCUMULATED_OTH_COMPREHENSIVE_INCOME’:’сумма’, ‘ALLL_AMT’:’сумма’, ‘AUDIT_INDICATOR’:’сумма’, ‘AVAILABLE_FOR_SALE_SECURITIES’:’сумма’, ‘COMMON_STOCK’ …я могу просто использовать переменную… вот так? c = df_append.столбцы
3. У меня будут разные имена столбцов из нескольких разных файлов. Я хочу динамически извлекать имена заголовков из файла, а не жестко кодировать их вручную. Спасибо.
4. да, ты можешь. Я добавил код, чтобы вы могли его увидеть
5. О, я нашел обходной путь! Это то, что я в конечном итоге использовал. grouped_df = df.groupby([‘code’,’qyear’]) grouped_and_summed = grouped_df.sum() grouped_and_summed = grouped_and_summed.reset_index() . T Еще раз спасибо!!!